Last_Error: Slave SQL thread retried transaction 10 time(s) in vain, giving up. Consider raising the
来源:互联网 发布:网络招商政策 编辑:程序博客网 时间:2024/05/17 02:34
1.版本
1)操作系统
cat /etc/issue
Red Hat Enterprise Linux Server release 5.5 (Tikanga)
Kernel \r on an \m
cat /proc/version
Linux version 2.6.32-504.el6.x86_64 (mockbuild@c6b9.bsys.dev.centos.org) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-11) (GCC) ) #1 SMP Wed Oct 15 04:27:16 UTC 2014
2)mysql数据库版本
mysql --version
mysql Ver 14.14 Distrib 5.6.26, for linux-glibc2.5 (x86_64) using EditLine wrapper
2.问题描述
2.1 发现问题
1)收到生产库的某个从库告警,登录从库 show slave status\G;进行查看,发现如下错误:
Last_Error: Slave SQL thread retried transaction 10 time(s) in vain, giving up. Consider raising the value of the slave_transaction_retries variable.2)查看从库的errorlog,发现有如下错误:(敏感信息我使用xx替换了)
2016-03-07 16:20:19 8349 [Warning] Slave SQL: Error 'Lock wait timeout exceeded; try restarting transaction' on query. Default database: 'oms_orders'. Query: 'INSERT INTO table1 (xx,xx,xx,xx,xx,xx,xx,xx,xx) VALUES('xx','xx','xx','','x','x','xx','xx','x') ON DUPLICATE KEY UPDATE class_id='xx',parent_class_id='x',class_name='xx',class_desc='',map_id='x',contains_metaclass='0',sort='10000',property='262144',state='0'', Error_code: 1205##errorlog中如上的错误一共报了10次(因为slave_transaction_retries默认值为10),也就是说在1025错误发生10次以后,从库的Slave_SQL_Running变为NO,并报如上1)中错误。
3. 问题分析
通过从库的errorlog我们可以判断是有什么事物阻塞了 从库的 insert into table1操作,从而导致insert into table1 操作等待加锁超发生超时。根据这个思路跟研发问了一下当时他们在从库上做了什么操作没有。果然他们正好在那个时间点对table1 进行操作。他们的操作可以概括为如下:
create temporary table test1 select * from table1 where;(当然他们的语句复杂很多对同一个表table1进行了多次关联)这个操作执行完需要很长时间。
##ok 分析到现在问题已经很清楚了,是因为研发的create temporary table test1 select * from table1 where ;(会加next-key locks)操作阻塞了 insert into table1 操作。(按照这个思路,在测试环境也重现了该问题)
##注意本篇博客描述的只是该报错的一种可能,该报错也有可能是其他某些原因导致的(没有深入研究)
4. 问题追加
上面从库的报错已经搞清楚了,但是可能细心的朋友有会疑问。怎么在从库上能够建表?
有人可能会说因为从库没有设置read-only为on,还有可能研发用的用户具有super权限。答案呢?,从库已经设置了 read_only,研发使用的也是普通用户没有super权限。 有兴趣的朋友可以试一下在从库上建如下用户,然后使用该用户创建执行 create table和create temporary table
grant select,create,create temporary tables on *.* to 'test1'@'xxxx' identified by 'xxxx';
mysql> create table test1 select * from test;ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement##我们可以看到设置了read_only为on的情况下,在从库建普通表会报错。
mysql> create temporary table test1 as select * from test;Query OK, 3 rows affected (0.18 sec)Records: 3 Duplicates: 0 Warnings: 0##在read_only为on的情况下,test用户在从库执行create temporary table 成功。
- Last_Error: Slave SQL thread retried transaction 10 time(s) in vain, giving up. Consider raising the
- Expression was too complex to be solved in reasonable time; consider breaking up the expression into
- Last_Error: Unable to use slave's temporary directory /var/tmp
- Not a life in vain, it's enough.
- AH00484: server reached MaxRequestWorkers setting, consider raising the MaxRequestWorkers setting
- What's Up in 2012 at the Movies?
- Emily Dickinson 《Not In Vain》
- Apache [error] server reached MaxClients setting, consider raising the MaxClients setting问题及解决办法
- Apache:Server ran out of threads to serve requests. Consider raising the ThreadsPerChild setting
- apache [error]:server reached MaxClients setting, consider raising the MaxClients setting
- Apache [error] server reached MaxClients setting, consider raising the MaxClients setting问题及解决办法
- 解决“server reached MaxClients setting,consider raising the Max Clients setting”
- Server ran out of threads to serve requests. Consider raising the ThreadsPerChild setting的解决方法
- Apache [error] server reached MaxClients setting, consider raising the MaxClients setting
- Giving the true opinion is hard in a meeting.
- For different transaction types,What's the meaning of fields in MTL_MATERIAL_TRANSACTIONS
- The Giving Tree
- the function modules called in the transaction
- Web安全之点击劫持(ClickJacking)
- abap开发内表基础知识总结
- JAVA与C++,C与C++的区别
- EJB客户端应用通过JNDI调用远程EJB的方法详解
- session cookie cache 的区别与特点
- Last_Error: Slave SQL thread retried transaction 10 time(s) in vain, giving up. Consider raising the
- 如何在Windows Server中查询.Net Framework版本信息?
- 利用Jmeter 实现Json格式接口测试
- 【Android】【性能优化】 Android 性能优化
- python项目之 爬虫爬取煎蛋jandan的妹子图-上
- 常用的排序算法整理
- 有两个序列a,b,大小都有n,序列元素的值任意整数,无序; 要求:通过交换a,b中的元素,使[序列a元素的和]与[序列b无素的和]之间的差最大。
- hdu5638 秘之贪心
- Codeforces--630B--Moore's Law(快速幂)