Mysql 5.1存储过程中的TRUNCATE语句导致主从同步中断

问题：主从同步中断

报错信息：

Relay_Log_File: relay-bin.000165

Relay_Log_Pos: 22231498

Slave_SQL_Running: No

Last_Errno: 1062

Last_Error: Error 'Duplicate entry '1' for key 'PRIMARY'' on query. Default database: 'dbtest'. Query: INSERT INTO dbtest_table (xx...xx) SELECT xx...xx;

原因：

经查看relay log，发现这条insert的sql，是触发器里面的。这个触发器：

 CREATE DEFINER=`root`@`localhost` PROCEDURE `p_problemtotal_statistics`()

BEGIN

        TRUNCATE dbtest_table; --sql1

        INSERT INTO dbtest_table (xx...xx) SELECT xx...xx; --sql2

INSERT INTO dbtest_table (xx...xx) SELECT xx...xx; --sql3

        ...

END

查看relay log：

...  thread_id=614272 

TRUNCATE dbtest_table

...  thread_id=614281

TRUNCATE dbtest_table

...

# at 22231498

BEGIN

SET INSERT_ID=1/*!*/;

INSERT INTO dbtest_table (xx...xx) SELECT xx...xx; 

SET INSERT_ID=1/*!*/;

INSERT INTO dbtest_table (xx...xx) SELECT xx...xx; 

...

COMMIT/*!*/;

在一个事务中，为什么有2个SET INSERT_ID=1？

在第2个SET INSERT_ID=1 insert的时候，sql就会报错：Duplicate entry '1' for key 'PRIMARY'' on query. Default database

所以，可以推出在主从执行sql2 跟 sql3 中间有个一次truncate，所以才会导致relay log中在同一事务中有2次SET INSERT_ID=1。

故导致主从实际执行的sql顺序不一样，主要是2、3的顺序对换了：

执行	主库		从库
顺序	session1	session2	session1	session2
1	sql1		sql1
2	sql2			sql1
3		sql1	sql2
4	sql3		sql3
5		sql2		sql2
6		sql3		sql3

为什么从库执行的顺序会跟主库不一样？

因为在主库上执行顺序2（session1 的sql2）的时候，开启的事务还未提交，所以在执行完sql2后，这条sql并不会写入binlog里面；

而在执行顺序3（session2 的sql1）的时候，TRUNCATE 在mysql中是属于ddl语句，执行完后，会自动commit，提交后则写入binlog。所以主库上的顺序3比顺序2先执行完写入到binlog，所以导致了主从库的执行顺序不一样。

在主库上执行顺序2的时候，明明加了锁。为什么顺序3的sql还是可以执行？

这个跟mysql的版本有关，在mysql5.1的时候，在某个事务执行过程中，如果某个表被事务里的语句引用，那么在这个语句的执行过程中，这个表上会有DDL排它锁，当这个语句执行完后（可能事务还没有结束），这个表上的DDL排它锁将会被释放，可能会造成非法事务。所以在主库上的顺序2执行完后，truncate操作可以直接执行。

而Mysql 5.5 的版本中，改进了事务引擎的表级锁完整性。会把这个锁的时效一直延续到事务结束，避免DBA的一些正常操作以至于破坏事务完整性。

建议：

1.如果确实有需要实现这类存储过程，可以考升级mysql版本到5.5以上；

2.更改存储过程，比如加上标志位，若有其他进程在调用这个存储过程，则等待下次调用。