master and slave: mysql> select * from myisam_test; +----+-----+ | id | uid | +----+-----+ | 1 | 1| | 2 | 1| | 3 | 1| +----+-----+ 3 rows in set (0.00 sec)
slave: 強制破環一致,刪除主鍵為2的 delete from myisam_test where id=2; mysql> select * from myisam_test; +----+-----+ | id | uid | +----+-----+ | 1 | 1| | 3 | 1| +----+-----+ 2 rows in set (0.00 sec)
master: mysql> insert into myisam_test values(100,1),(2,1),(101,1) ERROR 1062 (23000): Duplicate entry '2' for key 1 mysql> select * from myisam_test; +-----+-----+ | id | uid | +-----+-----+ | 1| 1 | | 2| 1 | | 3| 1 | | 100 | 1 | +-----+-----+ 4 rows in set (0.01 sec)
slave: 這個時候,由於主鍵沒有為2的,在slave上,上面insert語句,不會報錯,結果如下: mysql> select * from myisam_test; +-----+-----+ | id | uid | +-----+-----+ | 1| 1 | | 2| 1 | | 3| 1 | | 100 | 1 | | 101 | 1 | +-----+-----+ 5 rows in set (0.01 sec) 而這時候,重現了上次出的那個問題: Last_Error: Query caused different errors onmaster and slave. Error on master: 'Duplicate entry '%-.64s' forkey %d' (1062), Error on slave: 'no error' (0). Default database:'test'. Query: 'insert into myisam_test values(100,1),(2,1),(101,1)' 這就很明顯了,當錯誤在master上發生錯誤時記錄的binlog,而在slave確沒有發生錯誤,就會出現導致同步中斷的那個錯誤,跟type沒有關係!當然如果主上是innodb type,那麼他肯定不會出現上面問題,因為那Duplicate會直接回滾,根本不會記錄binlog,故不可能出現上面錯誤!
其實這個錯誤,也在讓我了解了一點, # at 6864 #101205 21:13:30 server id 111613321 end_log_pos6979 Query thread_id=29 exec_time=0 error_code=1062 SET TIMESTAMP=1291554810; 對於error_code=1062錯誤的binlog,他其實在slave也會執行,這個error_code只是告訴slave,這條語句是會報1062錯誤,而slave確會像正常語句那樣執行!