unknown mysql type "set('1','2','3','4','5','6','7')"

[jaredmdobson]

It looks like there are a few data types that need to be added and i was just wondering if you had any contribution guidelines or new of any good places to start with this.

Thank you, i am excited to try this project 👍🏿

[runner 2024-12-31 05:28:42,728 INFO] http server disabled
[runner 2024-12-31 05:28:42,728 INFO] running replication for mysql_default_db (initial replication not finished - waiting)
[binlogrepl 2024-12-31 05:28:42,980 INFO] state start position: None
[binlogrepl 2024-12-31 05:28:42,980 INFO] connection_settings: {'host': 'host.docker.internal', 'port': 3306, 'user': 'root', 'passwd': 'xxxxxxx', 'charset': 'utf8'}
[binlogrepl 2024-12-31 05:28:42,981 INFO] resume_stream: True
[binlogrepl 2024-12-31 05:28:42,981 INFO] allowed_events_in_packet: [event, event, event, row_event, event, event, event, event, event, event, event, row_event, row_event, event, event, row_event, row_event, event, event, event, event, event, event, event]
[binlogrepl 2024-12-31 05:28:42,981 INFO] server_id: 3787456570
[dbrepl mysql_default_db 2024-12-31 05:28:43,364 INFO] launched db_replicator
[dbopt None 2024-12-31 05:28:43,389 INFO] running optimizer
[dbrepl mysql_default_db 2024-12-31 05:28:43,401 INFO] recreating database
[dbrepl mysql_default_db 2024-12-31 05:28:43,581 INFO] last known transaction None
[dbrepl mysql_default_db 2024-12-31 05:28:46,402 ERROR] unhandled exception
Traceback (most recent call last):
File "/usr/local/lib/python3.12/site-packages/mysql_ch_replicator/db_replicator.py", line 195, in run
self.create_initial_structure()
File "/usr/local/lib/python3.12/site-packages/mysql_ch_replicator/db_replicator.py", line 205, in create_initial_structure
self.create_initial_structure_table(table)
File "/usr/local/lib/python3.12/site-packages/mysql_ch_replicator/db_replicator.py", line 216, in create_initial_structure_table
clickhouse_structure = self.converter.convert_table_structure(mysql_structure)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/mysql_ch_replicator/converter.py", line 264, in convert_table_structure
clickhouse_field_type = self.convert_field_type(field.field_type, field.parameters)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/mysql_ch_replicator/converter.py", line 253, in convert_field_type
clickhouse_type = self.convert_type(mysql_type, mysql_parameters)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/mysql_ch_replicator/converter.py", line 247, in convert_type
raise Exception(f'unknown mysql type "{mysql_type}"')
Exception: unknown mysql type "set('1','2','3','4','5','6','7')"

[bakwc]

Fixed set in v0.0.51, please update and check if it helps.

In general - most types handling happens in converter.py. When adding some new types would be good to add some unit-tests.

There is a docker-compose prepared with all required environment (clickhouse, mysql, mariadb).

[jaredmdobson]

Yes that fixed that issue, thank you so much. And now i've got a new one 🤓 I'm running mysql 8.0.50 if that matters.

[dbrepl rematter_default 2025-01-03 14:27:57,334 INFO] launched db_replicator
[dbrepl rematter_default 2025-01-03 14:27:57,415 INFO] recreating database
[dbrepl rematter_default 2025-01-03 14:27:57,590 INFO] last known transaction None
[dbrepl rematter_default 2025-01-03 14:28:00,868 ERROR] unhandled exception
Traceback (most recent call last):
File "/usr/local/lib/python3.12/site-packages/mysql/connector/connection_cext.py", line 706, in cmd_query
self._cmysql.query(
_mysql_connector.MySQLInterfaceError: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group' at line 1

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/usr/local/lib/python3.12/site-packages/mysql_ch_replicator/db_replicator.py", line 195, in run
self.create_initial_structure()
File "/usr/local/lib/python3.12/site-packages/mysql_ch_replicator/db_replicator.py", line 205, in create_initial_structure
self.create_initial_structure_table(table)
File "/usr/local/lib/python3.12/site-packages/mysql_ch_replicator/db_replicator.py", line 211, in create_initial_structure_table
mysql_create_statement = self.mysql_api.get_table_create_statement(table_name)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/mysql_ch_replicator/mysql_api.py", line 88, in get_table_create_statement
self.cursor.execute(f'SHOW CREATE TABLE {table_name}')
File "/usr/local/lib/python3.12/site-packages/mysql/connector/cursor_cext.py", line 357, in execute
result = self._connection.cmd_query(
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/mysql/connector/opentelemetry/context_propagation.py", line 97, in wrapper
return method(cnx, *args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/mysql/connector/connection_cext.py", line 714, in cmd_query
raise get_mysql_exception(
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group' at line 1
Traceback (most recent call last):
File "/usr/local/lib/python3.12/site-packages/mysql/connector/connection_cext.py", line 706, in cmd_query
self._cmysql.query(
_mysql_connector.MySQLInterfaceError: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group' at line 1

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/usr/local/bin/mysql_ch_replicator", line 8, in
sys.exit(main())
^^^^^^
File "/usr/local/lib/python3.12/site-packages/mysql_ch_replicator/main.py", line 152, in main
run_db_replicator(args, config)
File "/usr/local/lib/python3.12/site-packages/mysql_ch_replicator/main.py", line 98, in run_db_replicator
db_replicator.run()
File "/usr/local/lib/python3.12/site-packages/mysql_ch_replicator/db_replicator.py", line 195, in run
self.create_initial_structure()
File "/usr/local/lib/python3.12/site-packages/mysql_ch_replicator/db_replicator.py", line 205, in create_initial_structure
self.create_initial_structure_table(table)
File "/usr/local/lib/python3.12/site-packages/mysql_ch_replicator/db_replicator.py", line 211, in create_initial_structure_table
mysql_create_statement = self.mysql_api.get_table_create_statement(table_name)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/mysql_ch_replicator/mysql_api.py", line 88, in get_table_create_statement
self.cursor.execute(f'SHOW CREATE TABLE {table_name}')
File "/usr/local/lib/python3.12/site-packages/mysql/connector/cursor_cext.py", line 357, in execute
result = self._connection.cmd_query(
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/mysql/connector/opentelemetry/context_propagation.py", line 97, in wrapper
return method(cnx, *args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/mysql/connector/connection_cext.py", line 714, in cmd_query
raise get_mysql_exception(
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group' at line 1
[runner 2025-01-03 14:28:01,813 WARNING] Restarting dead process: < /usr/local/bin/mysql_ch_replicator --config /etc/mysql_ch_replicator/config.yaml --db rematter_default db_replicator >

[bakwc]

Fixed in v0.0.53

[jaredmdobson]

It synced my db over!!! I'll test it more, but so far so good! Thanks! :)