[BUG] Replicator crashes with DUPLICATE_COLUMN when MySQL migration re-adds existing column

[Hussam3bd]

Hi team,

I’m using mysql_ch_replicator to replicate data from MySQL to ClickHouse.

When I apply a migration to MySQL that includes ALTER TABLE ADD COLUMN enable_deadline, the replicator crashes with the following error from ClickHouse:

clickhouse_connect.driver.exceptions.DatabaseError: HTTPDriver for http://clickhouse:8123 received ClickHouse error code 15
Code: 15. DB::Exception: Cannot add column `enable_deadline`: column with this name already exists. (DUPLICATE_COLUMN)

Steps to Reproduce

1. Run the replicator with a MySQL table already replicated to ClickHouse.
2. Apply a MySQL migration:

ALTER TABLE your_table ADD COLUMN enable_deadline TINYINT(1) DEFAULT 0;

3. Replicator crashes with the error above.

What I’ve Tried

• I manually deleted the enable_deadline column from ClickHouse.
• After re-running the migration and restarting the replicator, the column gets re-added, but the replicator still crashes with the same error.
• It seems the replicator does not check if a column already exists before generating and executing the ALTER TABLE ADD COLUMN statement in ClickHouse.

Expected Behavior:

The replicator should check if the column already exists in ClickHouse and skip the ADD COLUMN statement if it does, or use a safe mechanism to avoid duplicate column errors.

Suggested Fix:

Before issuing an ALTER TABLE ADD COLUMN in ClickHouse, the replicator should:

• Query the system.columns table to verify whether the column exists.
• Skip or log a warning instead of throwing a fatal exception.

Thanks for the great work on the project!

[bakwc]

Please share your config file and how exactly you running replicator.

The bug you describe should never happens.
We have a tons of test for scenario you describe and we use it in production and applying migrations every day.

Probably you are doing something else what not described here.
Please share the detailed video with capture every single step you doing.

Also share the full stack trace and the full logs.

[Hussam3bd]

Thanks for the quick response.

Docker Compose Setup:

services:
  clickhouse:
    image: clickhouse/clickhouse-server:latest
    container_name: clickhouse
    restart: unless-stopped
    ports:
      - "8123:8123"
      - "9000:9000"
    volumes:
      - /var/lib/clickhouse:/var/lib/clickhouse
      - ./config/config.xml:/etc/clickhouse-server/config.xml:ro
      - ./config/users.xml:/etc/clickhouse-server/users.xml:ro
      - ./logs:/var/log/clickhouse-server
    ulimits:
      nofile:
        soft: 262144
        hard: 262144

  replicator:
    image: fippo/mysql-ch-replicator:latest
    container_name: ch_replicator
    restart: unless-stopped
    volumes:
      - ./config/replicator/config.yaml:/app/config.yaml:ro
      - /var/lib/clickhouse/replicator/data:/app/data
    command: ["--config", "/app/config.yaml", "run_all"]

Replicator Config (config.yaml):

mysql:
  host: 'our_mysql_host'
  port: 3306
  user: 'user'
  password: 'pass'

clickhouse:
  host: 'clickhouse'
  port: 8123
  user: 'user'
  password: 'password'
  connection_timeout: 60
  send_receive_timeout: 600

binlog_replicator:
  data_dir: '/app/data'
  records_per_file: 100000
  binlog_retention_period: 43200

databases: [ "db_name" ]
tables: '*'

exclude_tables:
  - campaigns_*
  - cart_*
  - coupon_*

Issue Trigger:
The issue happens after I run this MySQL query:

ALTER TABLE members ADD COLUMN enable_deadline TINYINT(1) DEFAULT 0;

MySQL Configuration (my.cnf)

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
binlog_row_image = FULL

What I ended up doing

1. Stopping the replicator.
2. Deleting all files in the replicator’s data dir:

rm -rf /var/lib/clickhouse/replicator/data/*

3. Dropping the entire ClickHouse database.
4. Restarting the replicator to resync everything from scratch.

[bakwc]

Is there a chance you applied migration until initial replication happened? We don't support migrations during initial replication.

Have you tried to:

1. Delete all files.
2. Run replication
3. Wait until initial replication is fully finished (important)
4. Apply migration

[Hussam3bd]

no the migration applied 3 days after the initial replication
I try to remove all the files and db and run it again it worked