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 GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 ;
slave start;
my problem is
why not SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 2 , as to the mysql manual
reference said?
-------------------quote begin------------------------
3. If you decide that you can skip the next statement from the master, issue
the following
statements:
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n;
mysql> START SLAVE;
The value of n should be 1 if the next statement from the master does not use
AUTO_INCREMENT or LAST_INSERT_ID(). Otherwise, the value should be 2. The
reason for using a value of 2 for statements that use AUTO_INCREMENT or
LAST_INSERT_ID() is that they take two events in the binary log of the master.
-------------------quote end---------------------------
the following is the process of test
step 1. create the test table on Master
-----------------------------------------------
mysql> create table tmp_test_0208(id int not null auto_increment,name
varchar(30),primary key(id)) engine=innodb;
Query OK, 0 rows affected (0.20 sec)
step 2. insert three rows in Slave
-----------------------------------------------
mysql> insert into tmp_test_0208 values(1,'a'),(2,'b'),(3,'c');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * 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
-----------------------------------------------
mysql> insert into tmp_test_0208(name) values('a'),('b'),('c');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * 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_IO_Running|Sl
ave_SQL_Running"
Slave_IO_Running: Yes
Slave_SQL_Running: No
step 5. SKIP the sql, and start slave
-----------------------------------------------
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 ;
Query OK, 0 rows affected (0.00 sec)
mysql> slave start;
Query OK, 0 rows affected (0.00 sec)
/usr/local/mysql/bin/mysql -uroot -pxxx c2cdb -s -e "show slave status\G"
|egrep "Slave_IO_Running|Sl
ave_SQL_Running"
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
the following is the errlog in slave
-----------------------------------------------
070208 16:07:59 [ERROR] 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 [ERROR] 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 './db_auction1_b-relay-bin.000457' 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');
---------------------------------
雅虎免费邮箱-3.5G容量，20M附件
