Unable to sync data on first time

[hiepabh]

[root@dev ~]# mysqld -V /usr/sbin/mysqld Ver 8.0.39-30 for Linux on x86_64 (Percona Server (GPL), Release 30, Revision 41ebc5d9) [root@dev ~]# clickhouse-server -V ClickHouse server version 24.3.5.47.altinitystable (altinity build).

I use Percona and when run sync first time, it show like this

[root@dev ~]# mysql_ch_replicator --config /etc/clickhouse-server/syn_mysql.yaml db_replicator --db db_source(inMySQL) --target_db db_dst(inClickHouse) --initial_only=True Traceback (most recent call last): File "/usr/local/bin/mysql_ch_replicator", line 8, in <module> sys.exit(main()) File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/main.py", line 120, in main run_db_replicator(args, config) File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/main.py", line 78, in run_db_replicator db_replicator = DbReplicator( File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/db_replicator.py", line 108, in __init__ self.mysql_api = MySQLApi( File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/mysql_api.py", line 15, in __init__ self.reconnect_if_required() File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/mysql_api.py", line 25, in reconnect_if_required self.db = mysql.connector.connect( File "/usr/local/lib64/python3.9/site-packages/mysql/connector/pooling.py", line 322, in connect return CMySQLConnection(*args, **kwargs) File "/usr/local/lib64/python3.9/site-packages/mysql/connector/connection_cext.py", line 151, in __init__ self.connect(**kwargs) File "/usr/local/lib64/python3.9/site-packages/mysql/connector/abstracts.py", line 1426, in connect self._open_connection() File "/usr/local/lib64/python3.9/site-packages/mysql/connector/connection_cext.py", line 335, in _open_connection self._cmysql.connect(**cnx_kwargs) TypeError: argument 3 must be str or None, not int

[bakwc]

It looks like you don't use quotes for mysql password in config file:

This is wrong:

mysql:
  host: 'localhost'
  port: 9306
  user: 'root'
  password: 12345

This is correct:

mysql:
  host: 'localhost'
  port: 9306
  user: 'root'
  password: '12345'

I added validation and more meaningful error message.

[hiepabh]

My config is

`mysql:
host: 'localhost'
port: 3306
user: 'ch_rep'
password: '123'

clickhouse:
host: 'localhost'
port: 8123
user: 'default'
password: 'clickhouse'

binlog_replicator:
data_dir: '/var/data/mysql/'
records_per_file: 100000

databases: 'database(InMySQL'
tables: 'source_table(inMySQL)'`

After inlcude '' to password then it become password unknow

[root@dev ~]# mysql_ch_replicator --config /etc/clickhouse-server/syn_mysql.yaml db_replicator --db database(inMySQL) --target_db database(inClickHouse) --initial_only=True
Traceback (most recent call last):
File "/usr/local/lib64/python3.9/site-packages/mysql/connector/connection_cext.py", line 335, in _open_connection
self._cmysql.connect(**cnx_kwargs)
_mysql_connector.MySQLInterfaceError: Access denied for user 'ch_rep'@'::1' (using password: YES)

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.9/site-packages/mysql_ch_replicator/main.py", line 120, in main
run_db_replicator(args, config)
File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/main.py", line 78, in run_db_replicator
db_replicator = DbReplicator(
File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/db_replicator.py", line 108, in init
self.mysql_api = MySQLApi(
File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/mysql_api.py", line 15, in init
self.reconnect_if_required()
File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/mysql_api.py", line 25, in reconnect_if_required
self.db = mysql.connector.connect(
File "/usr/local/lib64/python3.9/site-packages/mysql/connector/pooling.py", line 322, in connect
return CMySQLConnection(*args, **kwargs)
File "/usr/local/lib64/python3.9/site-packages/mysql/connector/connection_cext.py", line 151, in init
self.connect(**kwargs)
File "/usr/local/lib64/python3.9/site-packages/mysql/connector/abstracts.py", line 1426, in connect
self._open_connection()
File "/usr/local/lib64/python3.9/site-packages/mysql/connector/connection_cext.py", line 340, in _open_connection
raise get_mysql_exception(
mysql.connector.errors.ProgrammingError: 1045 (28000): Access denied for user 'ch_rep'@'::1' (using password: YES)
`

The user ch_rep able to access to MySQL

[root@dev ~]# mysql -u ch_rep -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 18 Server version: 8.0.39-30 Percona Server (GPL), Release 30, Revision 41ebc5d9

If remove '' like this

mysql: host: 'localhost' port: 3306 user: ch_rep password: 123

then i saw your update message

Traceback (most recent call last): File "/usr/local/bin/mysql_ch_replicator", line 8, in <module> sys.exit(main()) File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/main.py", line 116, in main config.load(args.config) File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/config.py", line 91, in load self.validate() File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/config.py", line 113, in validate self.mysql.validate() File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/config.py", line 29, in validate raise ValueError(f'mysql password should be string and not {stype(self.password)}') ValueError: mysql password should be string and not int

[bakwc]

Please try to set:
host: '127.0.0.1'

Probably user doesn't have permissions to connect with IPv6 address, and by default localhost resolving to IPv6

[hiepabh]

127.0.0.1 is work @bakwc

Not sure if i need to open new thread or follow here

i have a table in MySQL then i sync it to Clickhouse and with Engine is MySQL, after i create another database in CH and create a table with CH structure with ENGINE MergeTree.

I do insert from the table with MySQL on CH to the table on different database on CH with ENGINE MergeTree, it works perfect

I test with mysql_ch_replicator then it show error ==> Exception: unknown mysql type "tinyint"

[root@dev ~]# mysql_ch_replicator --config /etc/clickhouse-server/syn_mysql.yaml db_replicator --db db(inMySQL) --target_db db(InCH) --initial_only=True [dbrepl regina 2024-10-30 14:58:40,488 INFO] launched db_replicator [dbrepl regina 2024-10-30 14:58:40,488 INFO] recreating database [dbrepl regina 2024-10-30 14:58:40,527 INFO] last known transaction None [dbrepl regina 2024-10-30 14:58:40,593 ERROR] unhandled exception Traceback (most recent call last): File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/db_replicator.py", line 150, in run self.create_initial_structure() File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/db_replicator.py", line 160, in create_initial_structure self.create_initial_structure_table(table) File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/db_replicator.py", line 170, in create_initial_structure_table clickhouse_structure = self.converter.convert_table_structure(mysql_structure) File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/converter.py", line 119, in convert_table_structure clickhouse_field_type = self.convert_field_type(field.field_type, field.parameters) File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/converter.py", line 110, in convert_field_type clickhouse_type = self.convert_type(mysql_type) File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/converter.py", line 104, in convert_type raise Exception(f'unknown mysql type "{mysql_type}"') Exception: unknown mysql type "tinyint" Traceback (most recent call last): File "/usr/local/bin/mysql_ch_replicator", line 8, in <module> sys.exit(main()) File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/main.py", line 120, in main run_db_replicator(args, config) File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/main.py", line 84, in run_db_replicator db_replicator.run() File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/db_replicator.py", line 150, in run self.create_initial_structure() File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/db_replicator.py", line 160, in create_initial_structure self.create_initial_structure_table(table) File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/db_replicator.py", line 170, in create_initial_structure_table clickhouse_structure = self.converter.convert_table_structure(mysql_structure) File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/converter.py", line 119, in convert_table_structure clickhouse_field_type = self.convert_field_type(field.field_type, field.parameters) File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/converter.py", line 110, in convert_field_type clickhouse_type = self.convert_type(mysql_type) File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/converter.py", line 104, in convert_type raise Exception(f'unknown mysql type "{mysql_type}"') Exception: unknown mysql type "tinyint"

I already create the table structure with CH standard, do i need to drop it then the script auto create or ?
Any suggest?

[bakwc]

1. The problem was due to missing tinyint type support. I added tinyint, please upgrade mysql_ch_replicator (pip install --upgrade mysql_ch_replicator) and try again
2. No need to create structure manually, mysql_ch_replicator will create structure himself (existing tables will be removed)

[hiepabh]

Update and it works well for 4s then it show error
AttributeError: 'NoneType' object has no attribute 'timestamp'

I post and see if you may miss anything else

``idUInt32,employee_id` String,
`szid` String,
`first_name` String,
`middle_name` String,
`last_name` String,
`display_name` String,
`english_name` String,
`chinese_name` String,
`password` String,
`hash` String,
`salt` String,
`password_hint` String,
`payslip_password` String,
`card_id` String,
`identify_id` String,
`issued_date` Date,
`issued_place` String,
`expiry_date` Date,
`personal_identify_id` String,
`personal_issued_date` Date,
`personal_issued_place` String,
`personal_expiry_date` Date,
`identify_district_code` Int8,
`identify_id_old` String,
`issued_date_old` Date,
`issued_place_old` String,
`expiry_date_old` Date,
`sex` UInt8,
`ethnic` String,
`original` String,
`religion` String,
`nationality` String,
`birthday` Date,
`place_of_birth` String,
`place_of_birth_certificate` String,
`married_status` UInt8,
`cell_phone` String,
`home_phone` String,
`email` String,
`other_email` String,
`profile` UInt8,
`photo` String,
`image_path` String,
`onboard_date` Date,
`sz_onboard_date` Date,
`probation_ended_date` Date,
`probation_status` UInt8,
`probation_started_date` Date,
`probation_ended_date_original` Date,
`probation_ended_date_old_position` Date,
`probation_started_date_new_position` Date,
`contract_status` UInt8,
`pass_note` Nullable(String),
`base_salary` Float64,
`currency` UInt16,
`line_manager` UInt32,
`report_to` UInt32,
`salary_type` UInt8,
`payment_method` UInt8,
`group` UInt8,
`position` UInt16,
`probation_day` UInt32,
`job_title` Int16,
`department` UInt16,
`location` UInt16,
`job_level` UInt16,
`job_grade` UInt16,
`shift` UInt8,
`working_type` UInt8,
`contract_type` UInt8,
`worker_type` UInt8,
`team` UInt16,
`level` UInt16,
`category` UInt16,
`highest_degree` Int16,
`year_of_graduation` Date,
`city_of_graduation` String,
`speciality_of_graduation` String,
`cost_center` String,
`project_code` UInt16,
`activity_code` UInt16,
`role` UInt8,
`reset_uiid` String,
`reset_expired` UInt32,
`no_of_dependants` UInt8,
`first_login` UInt8,
`check_ip` UInt8,
`checkout_salary` UInt8,
`checkout_salary_date` Date,
`off_cycle` UInt8,
`off_cycle_type` UInt8,
`off_cycle_paid_on` Date,
`off_cycle_comment` Nullable(String),
`off_cycle_release` UInt8,
`setting` String,
`receive_email_payslip` UInt8,
`is_sent` UInt8,
`sent_by` UInt8,
`sent_2_tac` UInt8,
`fte` String,
`main_fullname` String,
`main_address` String,
`main_country` String,
`main_city` String,
`main_province` String,
`main_zip_code` String,
`main_district` String,
`main_phone` String,
`main_email` String,
`main_ward` Nullable(String),
`second_ward` Nullable(String),
`second_fullname` String,
`second_address` String,
`second_country` String,
`second_city` String,
`second_province` String,
`second_zip_code` String,
`second_district` String,
`second_phone` String,
`second_email` String,
`passport_number` String,
`passport_issued_date` Date,
`passport_issued_place` String,
`passport_expiry_date` Date,
`work_permit_number` String,
`work_permit_expiry_date` Date,
`labor_book_number` String,
`tax_resident` UInt8,
`si_number` String,
`si_effective_date` Date,
`si_place_issued` String,
`si_hospital` String,
`si_district` String,
`pit_number` String,
`pit_date_issued` Date,
`pit_place_issued` String,
`k_nk` String,
`allocate_expense_to` String,
`lock_account` UInt8,
`type` UInt8,
`use_ess` UInt8,
`is_hr` UInt8,
`user_is_ldap` UInt8,
`user_type` UInt8,
`user_description` String,
`last_login` DateTime,
`is_resignation` UInt8,
`leaving_date` Date,
`is_final_payment` UInt8,
`language` String,
`year_of_experience` String,
`recent_company` String,
`status` UInt8,
`employee_annex` UInt8,
`employee_related` UInt32,
`hide_db_probation` UInt8,
`hide_db_shui` UInt8,
`deleted_type` UInt8,
`deleted` UInt8,
`seaman` UInt16,
`height` Float32,
`weight` Float32,
`shoes_size` Float32,
`boiler` String,
`modified_by` UInt32,
`modified_date` DateTime,
`modified_by_name` String,
`created_date` DateTime,
`created_by` UInt32,
`created_by_name` String,
`probation_effective_date` DateTime,
`pass_probation_by` UInt32,
`entity` Int32,
`forgot_password_expired_date` DateTime,
`pin_code` String,
`pin_code_created_date` DateTime,
`status_pin` Int8,
`pin_hash` String,
`pin_salt` String,
`tk` Nullable(String),
`factory` UInt16,
`block` UInt16,
`section` UInt16,
`line` Nullable(String),
`tax_commitment` UInt8,
`tax_type` UInt8,
`is_temporary_suspension` UInt8,
`temporary_suspension_date` Date,
`temporary_expiration_date` Date,
`pit_number_modified_date` DateTime,
`pit_number_modified_by` UInt32,
`working_day` Float64,
`is_new` String,
`entitled_work_permit` Int8,
`reason_entitled_work_permit` Nullable(String),
`document_no_work_permit` String,
`approved_date` DateTime,
`approved_by` UInt32,
`rejected_date` DateTime,
`rejected_by` UInt32,
`rejected_reason` Nullable(String),
`healthy_info` Int8,
`is_intern` Int8,
`use_zalo` Int8,
`language_code` String`

Thanks @bakwc

[bakwc]

Could you please share the full exception?

[hiepabh]

[root@abh-dev04 ~]# mysql_ch_replicator --config /etc/clickhouse-server/syn_mysql.yaml db_replicator --db regina --target_db regina_clickhouse --initial_only=True [dbrepl regina 2024-10-30 15:28:22,353 INFO] launched db_replicator [dbrepl regina 2024-10-30 15:28:22,353 INFO] recreating database [dbrepl regina 2024-10-30 15:28:22,376 INFO] last known transaction None [dbrepl regina 2024-10-30 15:28:22,430 INFO] running initial replication [dbrepl regina 2024-10-30 15:28:22,430 INFO] running initial replication for table employee_positions [dbrepl regina 2024-10-30 15:28:22,431 INFO] replicating from scratch [dbrepl regina 2024-10-30 15:28:24,518 ERROR] Error serializing column modified_dateinto data typeDateTime64(3) Traceback (most recent call last): File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/driver/transform.py", line 104, in chunk_gen col_type.write_column(data, output, context) File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/datatypes/base.py", line 209, in write_column self.write_column_data(column, dest, ctx) File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/datatypes/base.py", line 224, in write_column_data self._write_column_binary(column, dest, ctx) File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/datatypes/temporal.py", line 215, in _write_column_binary column = [((int(x.timestamp()) * 1000000 + x.microsecond) * prec) // 1000000 for x in column] File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/datatypes/temporal.py", line 215, in <listcomp> column = [((int(x.timestamp()) * 1000000 + x.microsecond) * prec) // 1000000 for x in column] AttributeError: 'NoneType' object has no attribute 'timestamp' [dbrepl regina 2024-10-30 15:28:24,533 ERROR] unhandled exception Traceback (most recent call last): File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/db_replicator.py", line 151, in run self.perform_initial_replication() File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/db_replicator.py", line 193, in perform_initial_replication self.perform_initial_replication_table(table) File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/db_replicator.py", line 260, in perform_initial_replication_table self.clickhouse_api.insert(table_name, records) File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/clickhouse_api.py", line 144, in insert self.client.insert(table=full_table_name, data=records_to_insert) File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/driver/client.py", line 625, in insert return self.data_insert(context) File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/driver/httpclient.py", line 269, in data_insert response = self._raw_request(block_gen, params, headers, error_handler=error_handler, server_wait=False) File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/driver/httpclient.py", line 459, in _raw_request error_handler(response) File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/driver/httpclient.py", line 254, in error_handler raise ex File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/driver/transform.py", line 104, in chunk_gen col_type.write_column(data, output, context) File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/datatypes/base.py", line 209, in write_column self.write_column_data(column, dest, ctx) File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/datatypes/base.py", line 224, in write_column_data self._write_column_binary(column, dest, ctx) File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/datatypes/temporal.py", line 215, in _write_column_binary column = [((int(x.timestamp()) * 1000000 + x.microsecond) * prec) // 1000000 for x in column] File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/datatypes/temporal.py", line 215, in <listcomp> column = [((int(x.timestamp()) * 1000000 + x.microsecond) * prec) // 1000000 for x in column] AttributeError: 'NoneType' object has no attribute 'timestamp' Traceback (most recent call last): File "/usr/local/bin/mysql_ch_replicator", line 8, in <module> sys.exit(main()) File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/main.py", line 120, in main run_db_replicator(args, config) File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/main.py", line 84, in run_db_replicator db_replicator.run() File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/db_replicator.py", line 151, in run self.perform_initial_replication() File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/db_replicator.py", line 193, in perform_initial_replication self.perform_initial_replication_table(table) File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/db_replicator.py", line 260, in perform_initial_replication_table self.clickhouse_api.insert(table_name, records) File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/clickhouse_api.py", line 144, in insert self.client.insert(table=full_table_name, data=records_to_insert) File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/driver/client.py", line 625, in insert return self.data_insert(context) File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/driver/httpclient.py", line 269, in data_insert response = self._raw_request(block_gen, params, headers, error_handler=error_handler, server_wait=False) File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/driver/httpclient.py", line 459, in _raw_request error_handler(response) File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/driver/httpclient.py", line 254, in error_handler raise ex File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/driver/transform.py", line 104, in chunk_gen col_type.write_column(data, output, context) File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/datatypes/base.py", line 209, in write_column self.write_column_data(column, dest, ctx) File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/datatypes/base.py", line 224, in write_column_data self._write_column_binary(column, dest, ctx) File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/datatypes/temporal.py", line 215, in _write_column_binary column = [((int(x.timestamp()) * 1000000 + x.microsecond) * prec) // 1000000 for x in column] File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/datatypes/temporal.py", line 215, in <listcomp> column = [((int(x.timestamp()) * 1000000 + x.microsecond) * prec) // 1000000 for x in column] AttributeError: 'NoneType' object has no attribute 'timestamp'

[bakwc]

Thank you, exception related to DateTime handling. We support DateTime, but probably there are some non supported values, investigating.

[hiepabh]

This is how MySQL structure, hope it help

``idint unsigned NOT NULL AUTO_INCREMENT,employee` int unsigned NOT NULL,
`position` smallint unsigned NOT NULL,
`job_title` smallint NOT NULL DEFAULT '0',
`department` smallint unsigned NOT NULL DEFAULT '0',
`job_level` smallint unsigned NOT NULL DEFAULT '0',
`job_grade` smallint unsigned NOT NULL DEFAULT '0',
`level` smallint unsigned NOT NULL DEFAULT '0',
`team` smallint unsigned NOT NULL DEFAULT '0',
`factory` smallint unsigned NOT NULL DEFAULT '0',
`ship` smallint unsigned NOT NULL DEFAULT '0',
`report_to` int unsigned NOT NULL DEFAULT '0',
`line_manager` int unsigned NOT NULL DEFAULT '0',
`location` smallint unsigned NOT NULL DEFAULT '0',
`customer` int unsigned NOT NULL DEFAULT '0',
`effective_date` date NOT NULL DEFAULT '0000-00-00',
`status` tinyint unsigned NOT NULL DEFAULT '0',
`promotion` tinyint unsigned NOT NULL DEFAULT '0',
`promotion_id` int unsigned NOT NULL DEFAULT '0',
`note` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
`is_change_probation_time` tinyint unsigned NOT NULL DEFAULT '0',
`deleted` tinyint unsigned NOT NULL DEFAULT '0',
`created_by` int unsigned NOT NULL DEFAULT '0',
`created_by_name` varchar(125) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '',
`created_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified_by` int unsigned NOT NULL DEFAULT '0',
`modified_by_name` varchar(125) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '',
`modified_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`entity` int NOT NULL DEFAULT '0',
`sent_2_tac` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `position` (`position`),
KEY `employee` (`employee`,`position`)`

[bakwc]

Thank you for sharing structure.
Prepared the fix, please update mysql_ch_replicator and try again.

[hiepabh]

When i ran the script first time, it create a table xxx_tmp but the script i point the db to right db that i already create.

I drop the xxx_tmp db and update the final one and it fail because it unable to find xxx_tmp db.

It should write to target_db on the script, right? and i believed it cache last run somewhere to cause this issues

`[root@abh-dev04 ~]# mysql_ch_replicator --config /etc/clickhouse-server/syn_mysql.yaml db_replicator --db regina --target_db regina_clickhouse --initial_only=True
[dbrepl regina 2024-10-30 16:35:53,626 INFO] launched db_replicator
[dbrepl regina 2024-10-30 16:35:53,627 INFO] running initial replication
[dbrepl regina 2024-10-30 16:35:53,627 INFO] running initial replication for table employee_positions
[dbrepl regina 2024-10-30 16:35:53,627 INFO] continue from primary key None
[dbrepl regina 2024-10-30 16:35:55,499 ERROR] unhandled exception
Traceback (most recent call last):
File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/db_replicator.py", line 136, in run
self.perform_initial_replication()
File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/db_replicator.py", line 193, in perform_initial_replication
self.perform_initial_replication_table(table)
File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/db_replicator.py", line 260, in perform_initial_replication_table
self.clickhouse_api.insert(table_name, records)
File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/clickhouse_api.py", line 144, in insert
self.client.insert(table=full_table_name, data=records_to_insert)
File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/driver/client.py", line 614, in insert
context = self.create_insert_context(table,
File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/driver/client.py", line 713, in create_insert_context
describe_result = self.query(f'DESCRIBE TABLE {full_table}')
File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/driver/client.py", line 218, in query
return self._query_with_context(query_context)
File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/driver/httpclient.py", line 228, in _query_with_context
response = self._raw_request(body,
File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/driver/httpclient.py", line 461, in _raw_request
self._error_handler(response)
File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/driver/httpclient.py", line 384, in _error_handler
raise OperationalError(err_str) if retried else DatabaseError(err_str) from None
clickhouse_connect.driver.exceptions.DatabaseError: HTTPDriver for http://localhost:8123 received ClickHouse error code 81
Code: 81. DB::Exception: Database regina_clickhouse_tmp does not exist. Maybe you meant regina_clickhouse?. (UNKNOWN_DATABASE) (version 24.3.5.47.altinitystable (altinity build))

Traceback (most recent call last):
File "/usr/local/bin/mysql_ch_replicator", line 8, in
sys.exit(main())
File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/main.py", line 120, in main
run_db_replicator(args, config)
File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/main.py", line 84, in run_db_replicator
db_replicator.run()
File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/db_replicator.py", line 136, in run
self.perform_initial_replication()
File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/db_replicator.py", line 193, in perform_initial_replication
self.perform_initial_replication_table(table)
File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/db_replicator.py", line 260, in perform_initial_replication_table
self.clickhouse_api.insert(table_name, records)
File "/usr/local/lib/python3.9/site-packages/mysql_ch_replicator/clickhouse_api.py", line 144, in insert
self.client.insert(table=full_table_name, data=records_to_insert)
File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/driver/client.py", line 614, in insert
context = self.create_insert_context(table,
File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/driver/client.py", line 713, in create_insert_context
describe_result = self.query(f'DESCRIBE TABLE {full_table}')
File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/driver/client.py", line 218, in query
return self._query_with_context(query_context)
File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/driver/httpclient.py", line 228, in _query_with_context
response = self._raw_request(body,
File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/driver/httpclient.py", line 461, in _raw_request
self._error_handler(response)
File "/usr/local/lib64/python3.9/site-packages/clickhouse_connect/driver/httpclient.py", line 384, in _error_handler
raise OperationalError(err_str) if retried else DatabaseError(err_str) from None
clickhouse_connect.driver.exceptions.DatabaseError: HTTPDriver for http://localhost:8123 received ClickHouse error code 81
Code: 81. DB::Exception: Database regina_clickhouse_tmp does not exist. Maybe you meant regina_clickhouse?. (UNKNOWN_DATABASE) (version 24.3.5.47.altinitystable (altinity build))`

[bakwc]

It will remove tmp database itself when finish replication. It works following way:

1. Replicate everything into a _tmp database
2. Rename _tmp database into a main database.

(it's done this way to prevent removing main database in production).

You need to do following:

1. Remove binlog_replicator data_dir on your file system.
2. Run script the second time (and wait for completion). It will start from scratch instead of continue from saved position.

[hiepabh]

Thanks @bakwc for explanation

I unable to find binlog_replicator

[root@abh-dev04 ~]# find / -name binlog_replicator* /usr/local/lib/python3.9/site-packages/mysql_ch_replicator/binlog_replicator.py /usr/local/lib/python3.9/site-packages/mysql_ch_replicator/__pycache__/binlog_replicator.cpython-39.pyc [root@abh-dev04 ~]# find / -name *binlog_replicator* /usr/local/lib/python3.9/site-packages/mysql_ch_replicator/binlog_replicator.py /usr/local/lib/python3.9/site-packages/mysql_ch_replicator/__pycache__/binlog_replicator.cpython-39.pyc

[bakwc]

You set the directory path in config.yaml

binlog_replicator:
data_dir: '/var/data/mysql/'
records_per_file: 100000

It looks like it is '/var/data/mysql/'
I would propose to use another directory for the script, better dont mix it with mysql.

(it is the directory where script store it's own state, binary log, temporary data, etc.)