The script has encountered an error

[liandong00]

_mysql_connector.MySQLInterfaceError: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-data' at line 1

MariaDB version is 10.0.17
The detailed debug logs are as follows

mchr  | [binlogrepl 2025-01-23 06:14:02,414     INFO] server_id: 837311062
mchr  | [binlogrepl 2025-01-23 06:14:02,414     INFO] log_pos: 326
mchr  | [binlogrepl 2025-01-23 06:14:02,414     INFO] log_file: mysql-bin.000006
mchr  | [binlogrepl 2025-01-23 06:14:02,419    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.RotateEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:02,419    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.FormatDescriptionEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:02,723    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.RotateEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:02,723    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.FormatDescriptionEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:03,028    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.RotateEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:03,028    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.FormatDescriptionEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:03,332    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.RotateEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:03,332    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.FormatDescriptionEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:03,637    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.RotateEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:03,637    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.FormatDescriptionEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:03,942    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.RotateEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:03,942    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.FormatDescriptionEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:04,247    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.RotateEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:04,247    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.FormatDescriptionEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:04,551    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.RotateEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:04,552    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.FormatDescriptionEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:04,857    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.RotateEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:04,857    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.FormatDescriptionEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:05,161    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.RotateEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:05,162    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.FormatDescriptionEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:05,466    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.RotateEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:05,466    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.FormatDescriptionEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:05,770    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.RotateEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:05,771    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.FormatDescriptionEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:06,075    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.RotateEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:06,076    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.FormatDescriptionEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:06,380    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.RotateEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:06,380    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.FormatDescriptionEvent'>, ('mysql-bin.000006', 326)
mchr  | [runner 2025-01-23 06:14:06,632     INFO] running replication for youtube-data (initial replication not finished - waiting)
mchr  | [binlogrepl 2025-01-23 06:14:06,686    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.RotateEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:06,686    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.FormatDescriptionEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:06,990    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.RotateEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:06,991    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.FormatDescriptionEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:07,295    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.RotateEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:07,295    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.FormatDescriptionEvent'>, ('mysql-bin.000006', 326)
mchr  | Traceback (most recent call last):
mchr  |   File "/usr/local/lib/python3.10/site-packages/mysql/connector/connection_cext.py", line 755, in cmd_query
mchr  |     self._cmysql.query(
mchr  | _mysql_connector.MySQLInterfaceError: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-data' at line 1
mchr  |
mchr  | The above exception was the direct cause of the following exception:
mchr  |
mchr  | Traceback (most recent call last):
mchr  |   File "/usr/local/bin/mysql_ch_replicator", line 8, in <module>
mchr  |     sys.exit(main())
mchr  |   File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/main.py", line 152, in main
mchr  |     run_db_replicator(args, config)
mchr  |   File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/main.py", line 92, in run_db_replicator
mchr  |     db_replicator = DbReplicator(
mchr  |   File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator.py", line 115, in __init__
mchr  |     self.mysql_api = MySQLApi(
mchr  |   File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/mysql_api.py", line 15, in __init__
mchr  |     self.reconnect_if_required()
mchr  |   File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/mysql_api.py", line 42, in reconnect_if_required
mchr  |     self.cursor.execute(f'USE {self.database}')
mchr  |   File "/usr/local/lib/python3.10/site-packages/mysql/connector/cursor_cext.py", line 351, in execute
mchr  |     self._connection.cmd_query(
mchr  |   File "/usr/local/lib/python3.10/site-packages/mysql/connector/opentelemetry/context_propagation.py", line 97, in wrapper
mchr  |     return method(cnx, *args, **kwargs)
mchr  |   File "/usr/local/lib/python3.10/site-packages/mysql/connector/connection_cext.py", line 763, in cmd_query
mchr  |     raise get_mysql_exception(
mchr  | mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-data' at line 1
mchr  | [binlogrepl 2025-01-23 06:14:07,600    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.RotateEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 06:14:07,600    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.FormatDescriptionEvent'>, ('mysql-bin.000006', 326)

[bakwc]

Looks like the issue with database name which contains - character. Prepared a fix in v0.0.60, but there could be a few other places that needs to be addressed, please check if it helped.

[liandong00]

Clickhouse also has this error

mchr  | [binlogrepl 2025-01-23 10:14:10,331     INFO] server_id: 1150481937
mchr  | [binlogrepl 2025-01-23 10:14:10,331     INFO] log_pos: 326
mchr  | [binlogrepl 2025-01-23 10:14:10,331     INFO] log_file: mysql-bin.000006
mchr  | [binlogrepl 2025-01-23 10:14:10,336    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.RotateEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 10:14:10,336    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.FormatDescriptionEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 10:14:10,640    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.RotateEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 10:14:10,640    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.FormatDescriptionEvent'>, ('mysql-bin.000006', 326)
mchr  | [dbopt None 2025-01-23 10:14:10,663     INFO] running optimizer
mchr  | [dbopt None 2025-01-23 10:14:10,676    ERROR] error HTTPDriver for http://clickhouse:8123 received ClickHouse error code 62
mchr  |  Code: 62. DB::Exception: Syntax error: failed at position 12 ('-'): -data. Expected end of query. (SYNTAX_ERROR) (version 24.8.12.28 (official build))
mchr  | Traceback (most recent call last):
mchr  |   File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/db_optimizer.py", line 105, in run
mchr  |     self.optimize_database(db_name=db_to_optimize)
mchr  |   File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/db_optimizer.py", line 85, in optimize_database
mchr  |     self.clickhouse_api.execute_command(f'USE {db_name}')
mchr  |   File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/clickhouse_api.py", line 120, in execute_command
mchr  |     self.client.command(query)
mchr  |   File "/usr/local/lib/python3.10/site-packages/clickhouse_connect/driver/httpclient.py", line 361, in command
mchr  |     response = self._raw_request(payload, params, headers, method, fields=fields, server_wait=False)
mchr  |   File "/usr/local/lib/python3.10/site-packages/clickhouse_connect/driver/httpclient.py", line 472, in _raw_request
mchr  |     self._error_handler(response)
mchr  |   File "/usr/local/lib/python3.10/site-packages/clickhouse_connect/driver/httpclient.py", line 395, in _error_handler
mchr  |     raise OperationalError(err_str) if retried else DatabaseError(err_str) from None
mchr  | clickhouse_connect.driver.exceptions.DatabaseError: HTTPDriver for http://clickhouse:8123 received ClickHouse error code 62
mchr  |  Code: 62. DB::Exception: Syntax error: failed at position 12 ('-'): -data. Expected end of query. (SYNTAX_ERROR) (version 24.8.12.28 (official build))
mchr  |
mchr  | [binlogrepl 2025-01-23 10:14:10,944    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.RotateEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 10:14:10,945    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.FormatDescriptionEvent'>, ('mysql-bin.000006', 326)

[bakwc]

Fixed in v0.0.61

[liandong00]

@bakwc There are still errors

mchr  | [binlogrepl 2025-01-23 13:00:30,311    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.FormatDescriptionEvent'>, ('mysql-bin.000006', 326)
mchr  | [dbrepl youtube-data 2025-01-23 13:00:30,367     INFO] launched db_replicator
mchr  | [dbrepl youtube-data 2025-01-23 13:00:30,375     INFO] recreating database
mchr  | [dbrepl youtube-data 2025-01-23 13:00:30,377    ERROR] unhandled exception
mchr  | Traceback (most recent call last):
mchr  |   File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator.py", line 187, in run
mchr  |     self.clickhouse_api.recreate_database()
mchr  |   File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/clickhouse_api.py", line 129, in recreate_database
mchr  |     self.execute_command(f'DROP DATABASE IF EXISTS {self.database}')
mchr  |   File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/clickhouse_api.py", line 120, in execute_command
mchr  |     self.client.command(query)
mchr  |   File "/usr/local/lib/python3.10/site-packages/clickhouse_connect/driver/httpclient.py", line 361, in command
mchr  |     response = self._raw_request(payload, params, headers, method, fields=fields, server_wait=False)
mchr  |   File "/usr/local/lib/python3.10/site-packages/clickhouse_connect/driver/httpclient.py", line 472, in _raw_request
mchr  |     self._error_handler(response)
mchr  |   File "/usr/local/lib/python3.10/site-packages/clickhouse_connect/driver/httpclient.py", line 395, in _error_handler
mchr  |     raise OperationalError(err_str) if retried else DatabaseError(err_str) from None
mchr  | clickhouse_connect.driver.exceptions.DatabaseError: HTTPDriver for http://clickhouse:8123 received ClickHouse error code 62
mchr  |  Code: 62. DB::Exception: Syntax error: failed at position 32 ('-'): -data_tmp. Expected one of: ON, NO DELAY, SYNC, INTO OUTFILE, FORMAT, SETTINGS, end of query. (SYNTAX_ERROR) (version 24.8.12.28 (official build))
mchr  |
mchr  | Traceback (most recent call last):
mchr  |   File "/usr/local/bin/mysql_ch_replicator", line 8, in <module>
mchr  |     sys.exit(main())
mchr  |   File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/main.py", line 152, in main
mchr  |     run_db_replicator(args, config)
mchr  |   File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/main.py", line 98, in run_db_replicator
mchr  |     db_replicator.run()
mchr  |   File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator.py", line 187, in run
mchr  |     self.clickhouse_api.recreate_database()
mchr  |   File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/clickhouse_api.py", line 129, in recreate_database
mchr  |     self.execute_command(f'DROP DATABASE IF EXISTS {self.database}')
mchr  |   File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/clickhouse_api.py", line 120, in execute_command
mchr  |     self.client.command(query)
mchr  |   File "/usr/local/lib/python3.10/site-packages/clickhouse_connect/driver/httpclient.py", line 361, in command
mchr  |     response = self._raw_request(payload, params, headers, method, fields=fields, server_wait=False)
mchr  |   File "/usr/local/lib/python3.10/site-packages/clickhouse_connect/driver/httpclient.py", line 472, in _raw_request
mchr  |     self._error_handler(response)
mchr  |   File "/usr/local/lib/python3.10/site-packages/clickhouse_connect/driver/httpclient.py", line 395, in _error_handler
mchr  |     raise OperationalError(err_str) if retried else DatabaseError(err_str) from None
mchr  | clickhouse_connect.driver.exceptions.DatabaseError: HTTPDriver for http://clickhouse:8123 received ClickHouse error code 62
mchr  |  Code: 62. DB::Exception: Syntax error: failed at position 32 ('-'): -data_tmp. Expected one of: ON, NO DELAY, SYNC, INTO OUTFILE, FORMAT, SETTINGS, end of query. (SYNTAX_ERROR) (version 24.8.12.28 (official build))
mchr  |
mchr  | [binlogrepl 2025-01-23 13:00:30,614    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.RotateEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 13:00:30,615    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.FormatDescriptionEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 13:00:30,918    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.RotateEvent'>, ('mysql-bin.000006', 326)
mchr  | [binlogrepl 2025-01-23 13:00:30,918    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.FormatDescriptionEvent'>, ('mysql-bin.000006', 326)

[bakwc]

Fixing, added tests and it appeared to be a lot of places to fix, will take some time..

[bakwc]

Please try v0.0.62

[liandong00]

Now it can run successfully, thank you