How to Fix and Resume MySQL Replication After “Table Already Exists” Error (1050)


2 views

When working with MySQL master-slave replication, schema mismatches can abruptly halt replication. Here's a classic case:

Last_SQL_Errno: 1050
Last_SQL_Error: Error 'Table 'checklist' already exists' on query. Default database: 'dbname'. 
Query: 'CREATE TABLE checklist (...)

From your SHOW SLAVE STATUS output, key positions are:

Relay_Master_Log_File: mysql-bin.024476
Exec_Master_Log_Pos: 32574952
Relay_Log_Pos: 32575097

For immediate resolution (when table structures match):

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

Verify with:

SHOW SLAVE STATUS \G

For proper schema alignment (recommended):

-- On slave:
STOP SLAVE;
DROP TABLE IF EXISTS dbname.checklist;

-- Get exact position from relay log
CHANGE MASTER TO 
MASTER_LOG_FILE='mysql-bin.024476',
MASTER_LOG_POS=32574952;

START SLAVE;

If using GTIDs (MySQL 5.6+):

STOP SLAVE;
SET GTID_NEXT='aaa-bbb-ccc-ddd:1050';
BEGIN; COMMIT;
SET GTID_NEXT='AUTOMATIC';
START SLAVE;

Add to slave's my.cnf:

[mysqld]
read_only = 1
super_read_only = 1  # MySQL 5.7+

The slave should now catch up with the master's binary log position. Monitor replication lag with:

SHOW SLAVE STATUS \G
SELECT * FROM performance_schema.replication_group_member_stats;

When examining your SHOW SLAVE STATUS output, we see the critical error:

Last_SQL_Errno: 1050
Last_SQL_Error: Error 'Table 'checklist' already exists' on query.

This occurred because someone manually created the table on the slave before the same DDL statement replicated from the master. The replication thread stops precisely at this conflict point.

First, confirm the current replication position:

mysql> SHOW SLAVE STATUS \G
Relay_Master_Log_File: mysql-bin.024476
Exec_Master_Log_Pos: 32574952
Slave_SQL_Running: No

If the table structures are identical and you want to continue replication:

mysql> STOP SLAVE;
mysql> SET GLOBAL sql_slave_skip_counter = 1;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS \G

This skips exactly one event. For multiple skips, adjust the counter accordingly.

For proper data consistency:

mysql> STOP SLAVE;
mysql> DROP TABLE dbname.checklist;
mysql> START SLAVE;

If you need to preserve the slave's data:

-- Create temporary backup
CREATE TABLE dbname.checklist_backup LIKE dbname.checklist;
INSERT INTO dbname.checklist_backup SELECT * FROM dbname.checklist;

-- Resume replication
STOP SLAVE;
DROP TABLE dbname.checklist;
START SLAVE;

-- Compare and merge data later
-- Using pt-table-sync or custom scripts

Add these to my.cnf for stricter control:

read_only = 1
super_read_only = 1

For temporary maintenance access:

mysql> SET GLOBAL read_only = 0;
-- Perform maintenance
mysql> SET GLOBAL read_only = 1;

Use this query to verify replication health:

SELECT 
  UNIX_TIMESTAMP() - UNIX_TIMESTAMP(ts) AS lag_seconds,
  thread_id, event_name, sql_text
FROM performance_schema.events_statements_history
WHERE thread_id IN (
  SELECT thread_id FROM performance_schema.replication_applier_status_by_worker
);