Hello,all friends
in a master -slave setup ,that's all Ver 14.7 Distrib 4.1.14
I create a test table in master without any records, with auto-increment property.
and then,I insert three rows in slave's test table.
again ,I return to master,insert the same three rows in test table, in order to simulate the 'duplicate key' integrity constraint violation.
as the result,the Slave_SQL_Running thread on Slave stops.
I can issue the following commands to restart the slave:
SET GLBAL SQL_SLAVE_SKIP_****ER = 1 ;
slave start;
my problem is
why not SET GLBAL SQL_SLAVE_SKIP_****ER = 2 , as to the mysql manual reference said?
begin
3. If you decide that you can skip the next statement from the master, issue the following
statements:
mysqlSET GLBAL SQL_SLAVE_SKIP_****ER = n;
mysqlSTART SLAVE;
The value of n should be 1 if the next statement from the master does not use
AUTINCREMENT or LAST_INSERT_ID(). , the value should be 2. The
reason for using a value of 2 for statements that use AUTINCREMENT or
LAST_INSERT_ID() is that they take two events in the binary log of the master.
end
the following is the process of test
step 1. create the test table on Master
mysqlcreate table tmp_test_0208(id int not null auto_increment,name varchar(30),primary key(id)) engine=innodb;
Query K, 0 rows affected (0.20 sec)
step 2. insert three rows in Slave
mysqlinsert into tmp_test_0208 values(1,'a'),(2,'b'),(3,'c');
Query K, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysqlselect * from tmp_test_0208;
| id | name |
| 1 | a |
| 2 | b |
| 3 | c |
3 rows in set (0.00 sec)
step 3. insert the same rows to Master
mysqlinsert into tmp_test_0208(name) values('a'),('b'),('c');
Query K, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysqlselect * from tmp_test_0208;
| id | name |
| 1 | a |
| 2 | b |
| 3 | c |
3 rows in set (0.00 sec)
step 4. look the slave status
/usr/local/mysql/bin/mysql -uroot -pxxx c2cdb -s -e "show slave status\G" |egrep "Slave_IRunning|Sl
ave_SQL_Running"
Slave_IRunning: Yes
Slave_SQL_Running: No
step 5. SKIP the sql, and start slave
mysqlSET GLBAL SQL_SLAVE_SKIP_****ER = 1 ;
Query K, 0 rows affected (0.00 sec)
mysqlslave start;
Query K, 0 rows affected (0.00 sec)
/usr/local/mysql/bin/mysql -uroot -pxxx c2cdb -s -e "show slave status\G" |egrep "Slave_IRunning|Sl
ave_SQL_Running"
Slave_IRunning: Yes
Slave_SQL_Running: Yes
the following is the errlog in slave
070208 16:07:59 [ERRR] Slave: Error 'Duplicate entry '1' for key 1' on query. Default database: 'c2cdb'. Query: 'insert into tmp_te
st_0208(name) values('a'),('b'),('c')', Error_code: 1062
070208 16:07:59 [ERRR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE
START". We stopped at log 'db_auction1-bin.000203' position 14215101
070208 16:09:59 [Note] Slave SQL thread initialized, starting replication in log 'db_auction1-bin.000203' at position 14215101, rela
y log '' position: 200682931
the following is the binlog during the test
# at 14215101
#070208 16:08:00 server id 1 log_pos 14215101 Intvar
SET INSERT_ID=1;
# at 14215129
#070208 16:08:00 server id 1 log_pos 14215129 Query thread_id=2744782 exec_time=0 error_code=0
SET TIMESTAMP=1170922080;
insert into tmp_test_0208(name) values('a'),('b'),('c');
G20M