Chinaunix首页 | 论坛 | 博客
  • 博客访问: 502663
  • 博文数量: 65
  • 博客积分: 2925
  • 博客等级: 上尉
  • 技术积分: 1306
  • 用 户 组: 普通用户
  • 注册时间: 2009-07-01 10:56
个人简介

2013

文章分类

全部博文(65)

分类: Mysql/postgreSQL

2013-07-29 15:58:48

问题:主从同步中断

报错信息

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,发现这条insertsql,是触发器里面的。这个触发器:

 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/*!*/;


在一个事务中,为什么有2SET INSERT_ID=1?

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


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


故导致主从实际执行的sql顺序不一样,主要是23的顺序对换了:

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

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

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

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


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

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


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


建议

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

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


阅读(9622) | 评论(0) | 转发(0) |
0

上一篇:结合explain extended浅析使用mysql in 的效率

下一篇:没有了

给主人留下些什么吧!~~