[BUG] Resuming initial replication fails

[LGMAM]

Bug Description

Stopping and resuming during initial replication fails with an error stating the the _tmp database does not exists

Steps to Reproduce

1. Start a replica. Database is called 'sirocco'
2. Initial replication starts
3. Stop
4. Restart process. Log correctly says resuming initial replication from PK values
5. Fails with error indicating that 'sirocco_tmp' does not exists

Expected Behavior

I would expect initial replication to continue replicating data

Actual Behavior

[dbrepl sirocco 2025-07-17 19:45:00,207     INFO] running initial replication
[dbrepl sirocco 2025-07-17 19:45:00,211     INFO] running initial replication for table mt
[dbrepl sirocco 2025-07-17 19:45:00,212     INFO] continue from primary key [143574706, '6017e0b6-90d9-4024-9b07-20093b85622a']
[dbrepl sirocco 2025-07-17 19:45:21,130    ERROR] unhandled exception
Traceback (most recent call last):
  File "/opt/sirocco/mysql-clickhouse-replicator/venv/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator.py", line 199, in run
    self.initial_replicator.perform_initial_replication()
  File "/opt/sirocco/mysql-clickhouse-replicator/venv/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator_initial.py", line 103, in perform_initial_replication
    self.perform_initial_replication_table(table)
  File "/opt/sirocco/mysql-clickhouse-replicator/venv/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator_initial.py", line 198, in perform_initial_replication_table
    self.replicator.clickhouse_api.insert(table_name, records, table_structure=clickhouse_table_structure)
  File "/opt/sirocco/mysql-clickhouse-replicator/venv/lib/python3.10/site-packages/mysql_ch_replicator/clickhouse_api.py", line 230, in insert
    self.client.insert(table=full_table_name, data=records_to_insert)
  File "/opt/sirocco/mysql-clickhouse-replicator/venv/lib/python3.10/site-packages/clickhouse_connect/driver/client.py", line 664, in insert
    context = self.create_insert_context(table,
  File "/opt/sirocco/mysql-clickhouse-replicator/venv/lib/python3.10/site-packages/clickhouse_connect/driver/client.py", line 773, in create_insert_context
    describe_result = self.query(f'DESCRIBE TABLE {full_table}')
  File "/opt/sirocco/mysql-clickhouse-replicator/venv/lib/python3.10/site-packages/clickhouse_connect/driver/client.py", line 237, in query
    return self._query_with_context(query_context)
  File "/opt/sirocco/mysql-clickhouse-replicator/venv/lib/python3.10/site-packages/clickhouse_connect/driver/httpclient.py", line 243, in _query_with_context
    response = self._raw_request(body,
  File "/opt/sirocco/mysql-clickhouse-replicator/venv/lib/python3.10/site-packages/clickhouse_connect/driver/httpclient.py", line 481, in _raw_request
    self._error_handler(response)
  File "/opt/sirocco/mysql-clickhouse-replicator/venv/lib/python3.10/site-packages/clickhouse_connect/driver/httpclient.py", line 402, 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 sirocco_tmp does not exist. (UNKNOWN_DATABASE) (version 25.6.4.12 (official build))

Environment

• mysql_ch_replicator version: 0.0.92
• Operating System: Ubuntu 22.04.05 LTS
• Python version: 3.10.12

Replicator Configuration

mysql:
  host: '192.168.X.X'
  port: 3306
  user: 'crXXXXXX'
  password: 'XXXXXXX'

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

log_level: info
binlog_replicator:
  data_dir: '/var/lib/data/mysql-clickhouse-replicator/binlog'
  records_per_file: 100000

databases: ['sirocco','sirocco_shorturl']
ignore_deletes: true

Additional Information

[bakwc]

Could you please double check:

1. When you interrupting initial replication - does the clickhouse database has sirocco_tmp database or not? (it should have sirocco_tmp until initial replication finished
2. On resume - does it still has sirocco_tmp database?

It looks like for some reason this tmp database being dropped. Is there a chance something deleted it externally between replicaion?

[LGMAM]

It seems that siroco_tmp does not exists, even at replication start (note that ignore_deletes is true in my config)

[runner 2025-07-18 10:33:31,140     INFO] running replication for sirocco (initial replication not finished - waiting)
[dbrepl sirocco 2025-07-18 10:33:32,228     INFO] launched db_replicator
[dbrepl sirocco 2025-07-18 10:33:32,243     INFO] using existing database (ignore_deletes=True)
[dbrepl sirocco 2025-07-18 10:33:32,251     INFO] creating database sirocco

This is replication running for the first time

SHOW DATABASES

Query id: ae0d3382-7fb5-4609-9972-68436806e548

"INFORMATION_SCHEMA"
"default"
"information_schema"
"sirocco"
"system"

5 rows in set. Elapsed: 0.013 sec.

and replication is running just fine (db_replicator.log)

[dbrepl sirocco 2025-07-18 10:35:46,759     INFO] replicating mt
[dbrepl sirocco 2025-07-18 10:35:49,110     INFO] replicating mt, replicated 700000 records, primary key: [136824706, 'a26085f0-f5b5-41c3-b93e-33e10d75467f']
[dbrepl sirocco 2025-07-18 10:35:56,906     INFO] replicating mt
[dbrepl sirocco 2025-07-18 10:36:07,023     INFO] replicating mt
[dbrepl sirocco 2025-07-18 10:36:17,799     INFO] replicating mt
[dbrepl sirocco 2025-07-18 10:36:27,875     INFO] replicating mt
[dbrepl sirocco 2025-07-18 10:36:38,236     INFO] replicating mt
[dbrepl sirocco 2025-07-18 10:36:48,640     INFO] replicating mt

When I stop replication, sirocco stills exists and no sirocco_tmp either

SHOW DATABASES

Query id: a557035b-a889-40fd-899d-42957da2d62c

"INFORMATION_SCHEMA"
"default"
"information_schema"
"sirocco"
"system"

5 rows in set. Elapsed: 0.006 sec. 

Restart replication

[dbrepl sirocco 2025-07-18 10:39:35,055     INFO] launched db_replicator
[dbrepl sirocco 2025-07-18 10:39:35,079     INFO] running initial replication
[dbrepl sirocco 2025-07-18 10:39:35,082     INFO] running initial replication for table mt
[dbrepl sirocco 2025-07-18 10:39:35,083     INFO] continue from primary key [137524706, '4800d0af-5f0a-4d04-b704-aa51ead53e0a']
[dbrepl sirocco 2025-07-18 10:39:44,197     INFO] replicating mt
[dbrepl sirocco 2025-07-18 10:39:45,316    ERROR] unhandled exception
Traceback (most recent call last):
  File "/opt/sirocco/mysql-clickhouse-replicator/venv/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator.py", line 199, in run
    self.initial_replicator.perform_initial_replication()
  File "/opt/sirocco/mysql-clickhouse-replicator/venv/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator_initial.py", line 103, in perform_initial_replication
    self.perform_initial_replication_table(table)
  File "/opt/sirocco/mysql-clickhouse-replicator/venv/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator_initial.py", line 198, in perform_initial_replication_table
    self.replicator.clickhouse_api.insert(table_name, records, table_structure=clickhouse_table_structure)
  File "/opt/sirocco/mysql-clickhouse-replicator/venv/lib/python3.10/site-packages/mysql_ch_replicator/clickhouse_api.py", line 230, in insert
    self.client.insert(table=full_table_name, data=records_to_insert)
  File "/opt/sirocco/mysql-clickhouse-replicator/venv/lib/python3.10/site-packages/clickhouse_connect/driver/client.py", line 664, in insert
    context = self.create_insert_context(table,
  File "/opt/sirocco/mysql-clickhouse-replicator/venv/lib/python3.10/site-packages/clickhouse_connect/driver/client.py", line 773, in create_insert_context
    describe_result = self.query(f'DESCRIBE TABLE {full_table}')
  File "/opt/sirocco/mysql-clickhouse-replicator/venv/lib/python3.10/site-packages/clickhouse_connect/driver/client.py", line 237, in query
    return self._query_with_context(query_context)
  File "/opt/sirocco/mysql-clickhouse-replicator/venv/lib/python3.10/site-packages/clickhouse_connect/driver/httpclient.py", line 243, in _query_with_context
    response = self._raw_request(body,
  File "/opt/sirocco/mysql-clickhouse-replicator/venv/lib/python3.10/site-packages/clickhouse_connect/driver/httpclient.py", line 481, in _raw_request
    self._error_handler(response)
  File "/opt/sirocco/mysql-clickhouse-replicator/venv/lib/python3.10/site-packages/clickhouse_connect/driver/httpclient.py", line 402, 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 sirocco_tmp does not exist. (UNKNOWN_DATABASE) (version 25.6.4.12 (official build))

[bakwc]

Thanks! Yeah, it's an issue with ignore_deletes, we don't create any temporary database in this case, will prepare a fix.

[bakwc]

Fixed in v0.0.93