Errors while trying to setup mysql replica #329
Comments
|
Hi @aszenz, The first error message appears to be related to issue #38; I'll investigate it shortly. The second error message seems to involve |
Sure: CREATE TABLE `messenger_messages` (
`id` bigint NOT NULL AUTO_INCREMENT,
`body` longtext NOT NULL,
`headers` longtext NOT NULL,
`queue_name` varchar(190) NOT NULL,
`created_at` datetime(6) NOT NULL,
`available_at` datetime(6) NOT NULL,
`delivered_at` datetime(6) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `IDX_75EA56E0FB7336F0` (`queue_name`),
KEY `IDX_75EA56E0E3BD61CE` (`available_at`),
KEY `IDX_75EA56E016BA31DB` (`delivered_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ciThis error also occurs for some other tables which use int as primary key auto_increment |
|
Thank you so much! I’ve addressed the issues with auto-increment columns and check constraints in PRs #333 and #335. The reported errors should now be resolved, though I’m not entirely certain. We would greatly appreciate it if you could try the latest Docker image again and let us know if you encounter any further issues. |
Thanks for the quick fix, just tried with the latest image, it now runs further than before but ends up with these errors: docker run \
--network qt_default \
-p 127.0.0.1:13306:3306 \
--env=SETUP_MODE=REPLICA \
--env=SOURCE_DSN="mysql://root@mysql2:3306/demo" \
apecloud/myduckserver:latestFor the first issue I found a related issue in duckdb: Second issue regarding csv error seems like column type auto-inference is wrong while loading the dump, maybe we can use A strange thing I noticed is that mysql db server has two db's ( |
|
Hi @aszenz, Apologies for the delayed reply—I've been traveling. I’ve addressed the "Binder Error: There are no UNIQUE/PRIMARY KEY Indexes that refer to this table" issue and added support for database/table replication filters in PR #336. I also tried to find a workaround for replicating your This row contains a JSON field with a string: Fortunately, I’ve contributed a fix for this to DuckDB (duckdb/duckdb#14464), and it will be available in the upcoming DuckDB v1.2 release, scheduled for 2025-01-13. I’ve confirmed that the latest nightly build of DuckDB can read such fields correctly: ➜ duckdb-nightly
v1.1.4-dev3741 ab8c909857
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D .mode line
D from read_csv('testdata/issue329.tsv', header = false, columns = {'code': 'VARCHAR', 'domain': 'VARCHAR', 'translations': 'JSON'}, sep = '\t', escape = '\');
code = _ORDER_LOADINGDATE_RESTRICTION
domain = messages
translations = {"de": "Das Ladedatum darf nicht kleiner oder gleich dem eingeschränkten Ladedatensatz im Bildschirm \"Verschiedenes\" sein", "en": "Loading date cannot be less than or equal to the restricted loading date set in root data / date limitation screen", "es": "Loading Date cannot be less than or equal to the restricted loading date set in miscelaneous screen", "fr": "Loading Date cannot be less than or equal to the restricted loading date set in miscelaneous screen", "nl": "Laaddatum mag niet kleiner zijn dan of gelijk zijn aan de beperkte laadgegevensset in het scherm stamgegevens"} To work around this before the new release, I’ve implemented database and table replication filtering. You can now specify
Thank you for pointing this out! With the changes in PR #336, the database name specified in the DSN is now automatically added to the database filter. As a result, only data from this database (and any other databases explicitly specified via Let me know if you have any further questions or issues! |
|
Great work on fixing the previous issues so quickly, I now manage to get the central db replica up and running after ignoring translations table but for the demo db I get new errors: The schema for this table CREATE TABLE `peildatum` (
`datum` date DEFAULT NULL,
`vanaf` date DEFAULT NULL,
`tot` date DEFAULT NULL,
`doel` enum('phprapporten','excelrapporten','opslagkosten') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci It seems to fail here at the enum value Similar errors occur for other tables with enum columns. Note that the table |
|
Thank you so much for your continued testing and verification of our bug fixes! I'm glad to hear you were able to get the central db replica up and running after excluding the translations table. Regarding the new errors with the demo db, the issue appears to stem from the fact that duckdb does not accept We appreciate your detailed reports — they help us improve quickly! Let me know if you encounter anything else or need further assistance. |
|
Thanks for the update, I'm facing another enum related issue: |
|
I have successfully reproduced and resolved the new ENUM issue in #350. Thank you for reporting it! |
Great now the replica is up and running at least on the demo db. I'm still exploring working with myduck, so far I see that views/functions are not replicated, and when i try to recreate them I see syntax errors. My understanding was it supports full mysql syntax, so not sure what's the issue. This is what happens when i connect to the replica via CREATE OR REPLACE SQL SECURITY INVOKER VIEW log_report_count AS (
SELECT
report_code,
report_name,
report_count
FROM report_logs
WHERE report_count > 0
);I get this: Running the query directly works. Do i need to use duckdb syntax for creating views/functions? Views are quite important for me since the whole idea of trying out myduck is that I can shift running heavy views to the replica without changing their definitions and get a perf lift. I also faced some errors related to not using mysql strict mode, unfortunately mysql is quite accepting like accepting empty string for enums, not requiring full group by etc. Not sure myduck wants to support these, which in my experience are quite common in mysql installations. A related question:
|
|
EDIT:
Seems like demo db didn't have any views/functions when I try a Here's the output from | Function | sql_mode | Create Function | character_set_client | collation_connection | Database Collation |
|----------|------------------------|------------------------------------------------------------|----------------------|----------------------|--------------------|
| toDate | NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`%` FUNCTION `toDate`() RETURNS date | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |
| | | DETERMINISTIC | | | |
| | | SQL SECURITY INVOKER | | | |
| | | return IFNULL(@todate, '2999-12-31') | | | | |
|
Creating views via a MySQL connection should be supported, as we have test cases for it. Let me investigate what might be going wrong. Stored routines/functions are not currently considered due to the lack of support for them in DuckDB. I will verify if they are implemented in the go-mysql-server framework. If they are, it should be feasible to implement them; otherwise, it may be challenging due to the complexity involved.
This is not supported at the moment. I recommend sending raw DuckDB queries via a PostgreSQL connection for now. However, this is a feature I have considered and would like to implement if it is a must-have for you. It will take some time, but it would be a valuable addition.
Yes, non-strict SQL modes are still quite common in real-world systems. We can report this issue to SQLGlot to see if it is possible to resolve it there. |
If routines/procedures/functions are not supported, then myduck shouldn't try to replicate them, of course some views may reference these functions and would fail to be created as well. |
This feature is not important for me, just nice to have, primary importance is to get views working, these views have queries with many joins and aggregations often written in non-strict sql (maybe we can use duckdb's |
Okay looks like it works without brackets surrounding the view: Works CREATE OR REPLACE SQL SECURITY INVOKER VIEW log_report_count AS
SELECT
report_code,
report_name,
report_count
FROM report_logs
WHERE report_count > 0But this doesn't CREATE OR REPLACE SQL SECURITY INVOKER VIEW log_report_count AS (
-- same query
) |
Great work on the the project, I'm trying to replicate a mysql db, but got these errors:
The text was updated successfully, but these errors were encountered: