Description:
RESET SLAVE already does.
CHANGE MASTER or START SLAVE should too.
START SLAVE will not reset errors until a *query* event is executed (so if master is idle, error can last for long).
When you do CHANGE MASTER or START SLAVE, you don't want to see the old error.
This has already confused a customer in the past.
How to repeat:
Create an error on slave:
on master do
create table t(a int primary key);
on slave do
start slave;
insert into t values(1);
on master do
insert into t values(1);
then you see the error on slave with
show slave status;
Two ways then:
- Doing a CHANGE MASTER to skip the bad query, then START SLAVE,
- or doing SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1, then START SLAVE,
in both cases you still see the error.
Example for second way:
LAVE> show slave status\G
*************************** 1. row ***************************
Master_Host: 127.0.0.1
Master_User: test
Master_Port: 3306
Connect_retry: 3
Master_Log_File: gbichot2-bin.002
Read_Master_Log_Pos: 130
Relay_Log_File: gbichot2-relay-bin.001
Relay_Log_Pos: 110
Relay_Master_Log_File: gbichot2-bin.002
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_do_db:
Replicate_ignore_db:
Last_errno: 1062
Last_error: Error 'Duplicate entry '1' for key 1' on query 'insert into test.t values(1)' (default database was 'test')
Skip_counter: 0
Exec_master_log_pos: 67
Relay_log_space: 173
1 row in set (0.00 sec)
SLAVE> set global sql_slave_skip_counter=1;
Query OK, 0 rows affected (0.00 sec)
SLAVE> start slave;
Query OK, 0 rows affected (0.00 sec)
SLAVE> show slave status\G
*************************** 1. row ***************************
Master_Host: 127.0.0.1
Master_User: test
Master_Port: 3306
Connect_retry: 3
Master_Log_File: gbichot2-bin.002
Read_Master_Log_Pos: 130
Relay_Log_File: gbichot2-relay-bin.001
Relay_Log_Pos: 173
Relay_Master_Log_File: gbichot2-bin.002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_do_db:
Replicate_ignore_db:
Last_errno: 1062
Last_error: Error 'Duplicate entry '1' for key 1' on query 'insert into test.t values(1)' (default database was 'test')
Skip_counter: 0
Exec_master_log_pos: 130
Relay_log_space: 173
1 row in set (0.00 sec)
Error is still here even if replication is fine now (see exec_master_log_pos advanced and both threads are running).
Suggested fix:
CHANGE MASTER and START SLAVE should both reset the errors.
Description: RESET SLAVE already does. CHANGE MASTER or START SLAVE should too. START SLAVE will not reset errors until a *query* event is executed (so if master is idle, error can last for long). When you do CHANGE MASTER or START SLAVE, you don't want to see the old error. This has already confused a customer in the past. How to repeat: Create an error on slave: on master do create table t(a int primary key); on slave do start slave; insert into t values(1); on master do insert into t values(1); then you see the error on slave with show slave status; Two ways then: - Doing a CHANGE MASTER to skip the bad query, then START SLAVE, - or doing SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1, then START SLAVE, in both cases you still see the error. Example for second way: LAVE> show slave status\G *************************** 1. row *************************** Master_Host: 127.0.0.1 Master_User: test Master_Port: 3306 Connect_retry: 3 Master_Log_File: gbichot2-bin.002 Read_Master_Log_Pos: 130 Relay_Log_File: gbichot2-relay-bin.001 Relay_Log_Pos: 110 Relay_Master_Log_File: gbichot2-bin.002 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_do_db: Replicate_ignore_db: Last_errno: 1062 Last_error: Error 'Duplicate entry '1' for key 1' on query 'insert into test.t values(1)' (default database was 'test') Skip_counter: 0 Exec_master_log_pos: 67 Relay_log_space: 173 1 row in set (0.00 sec) SLAVE> set global sql_slave_skip_counter=1; Query OK, 0 rows affected (0.00 sec) SLAVE> start slave; Query OK, 0 rows affected (0.00 sec) SLAVE> show slave status\G *************************** 1. row *************************** Master_Host: 127.0.0.1 Master_User: test Master_Port: 3306 Connect_retry: 3 Master_Log_File: gbichot2-bin.002 Read_Master_Log_Pos: 130 Relay_Log_File: gbichot2-relay-bin.001 Relay_Log_Pos: 173 Relay_Master_Log_File: gbichot2-bin.002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_do_db: Replicate_ignore_db: Last_errno: 1062 Last_error: Error 'Duplicate entry '1' for key 1' on query 'insert into test.t values(1)' (default database was 'test') Skip_counter: 0 Exec_master_log_pos: 130 Relay_log_space: 173 1 row in set (0.00 sec) Error is still here even if replication is fine now (see exec_master_log_pos advanced and both threads are running). Suggested fix: CHANGE MASTER and START SLAVE should both reset the errors.