EC
1666
MySQLERRORCommonReplicationHIGH confidence

Cannot run CHANGE MASTER when parallel replication has gaps

Production Risk

High — replica may have permanent data gaps if not resolved properly.

What this means

CHANGE MASTER TO cannot be executed while multi-threaded slave (MTS) replication has relay log gaps (uncommitted worker transactions). The replica must be restarted or the gaps must be resolved first.

Why it happens
  1. 1Multi-threaded replication was stopped with incomplete transactions, leaving gaps in the relay log.
  2. 2CHANGE MASTER TO was attempted before the MTS recovery was complete.
How to reproduce
trigger — this will error
trigger — this will error
-- After unclean MTS stop:
CHANGE MASTER TO MASTER_AUTO_POSITION=1;

expected output

ERROR 1666 (HY000): Cannot change master with a running slave sql thread; use STOP SLAVE SQL_THREAD first.

Fix

Restart replica with SLAVE_PARALLEL_WORKERS = 0 to recover gaps

Restart replica with SLAVE_PARALLEL_WORKERS = 0 to recover gaps
SET GLOBAL slave_parallel_workers = 0;
START SLAVE UNTIL SQL_AFTER_MTS_GAPS;
STOP SLAVE;
CHANGE MASTER TO MASTER_AUTO_POSITION=1;
SET GLOBAL slave_parallel_workers = 4;
START SLAVE;

Why this works

Using SQL_AFTER_MTS_GAPS lets the single-thread SQL thread fill in all gaps before the CHANGE MASTER.

What not to do

✕

Version notes

Sources
Official documentation ↗

MySQL 8.0 — 1666 ER_MTS_CHANGE_MASTER_CANT_RUN_WITH_GAPS

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MySQL errors