业务突然反馈写失败,报错:Lost connection to MySQL server during query。读没有问题
检查proxysql报如下日志:
2019-04-28 16:53:06 MySQL_Session.cpp:2934:handler(): [WARNING] Error during query on (1,10.x.x.87,3306): 1290, The MySQL server is running with the -- read-only option so it cannot execute this statement
2019-04-28 16:53:06 MySQL_Session.cpp:95:kill_query_thread(): [WARNING] KILL CONNECTION 2740507 on 10.x.x.87:3306
2019-04-28 16:54:06 MySQL_Session.cpp:2934:handler(): [WARNING] Error during query on (1,10.x.x.87,3306): 1290, The MySQL server is running with the -- read-only option so it cannot execute this statement
2019-04-28 16:54:06 MySQL_Session.cpp:95:kill_query_thread(): [WARNING] KILL CONNECTION 2737976 on 10.x.x.87:3306
2019-04-28 16:55:29 MySQL_Session.cpp:2934:handler(): [WARNING] Error during query on (1,10.x.x.87,3306): 1290, The MySQL server is running with the -- read-only option so it cannot execute this statement
2019-04-28 16:55:29 MySQL_Session.cpp:95:kill_query_thread(): [WARNING] KILL CONNECTION 2796368 on 10.x.x.87:3306
2019-04-28 16:59:07 MySQL_Session.cpp:2934:handler(): [WARNING] Error during query on (1,10.x.x.87,3306): 1054, Unknown column 'date' in 'order clause'
2019-04-28 17:05:10 MySQL_Session.cpp:2934:handler(): [WARNING] Error during query on (1,10.x.x.87,3306): 1290, The MySQL server is running with the -- read-only option so it cannot execute this statement
2019-04-28 17:05:10 MySQL_Session.cpp:95:kill_query_thread(): [WARNING] KILL CONNECTION 2797386 on 10.x.x.87:3306
2019-04-28 17:06:45 MySQL_Session.cpp:2934:handler(): [WARNING] Error during query on (1,10.x.x.87,3306): 1290, The MySQL server is running with the -- read-only option so it cannot execute this statement
2019-04-28 17:06:45 MySQL_Session.cpp:95:kill_query_thread(): [WARNING] KILL CONNECTION 2797798 on 10.x.x.87:3306
意思就是有写操作到从了,由于从是只读的,写失败kill了连接,导致报lost connection to mysql during query.
为什么写会路由到从呢?
先看路由配置:

读写分离路由是没有问题的,通过日志看到有set autocommit=0这样开启事务。检查配置发现:
mysql-forward_autocommit=false
mysql-autocommit_false_is_transaction=false
这两个设置为false,会导致set autocommit = 0不会开启事务,所以如果set autocommit=0后,先select,再有写操作,有可能会导致写到从库(也存在bug)。
如下有issue类似问题链接:
- [Write Queries were routing to read hostgroup](https://github.com/sysown/proxysql/issues/1658)
- [When mysql-forward_autocommit=true , treat autocommit=0 as a transaction](https://github.com/sysown/proxysql/issues/1155)
- ["set autocommit = 0" is not a transaction](https://github.com/sysown/proxysql/issues/1716)
所以建议将如下配置为true:
mysql-forward_autocommit=true
mysql-autocommit_false_is_transaction=true
还有写请求会落到从库的,有可能是路由规划配置有问题,如用match_pattern匹配,就有可能在sql换行就匹配不到的情况。最好是用match_digest来匹配。
它们的区别是:
match_pattern :原始的sql进行正则匹配
match_digest :签名后的sql进行正则匹配,如stats_mysql_query_digest里的sql
###总结:
ProxySQL是MySQL中件间中比较优秀的,但在使用上还需要多注意,尽量简单使用,理解配置产生的效果及影响。
文章最后更新时间:
2019年04月29日 10:02:10
write my essays writemypaper.online
write my essays writemypaper.online
write my essays writemypaper.online
write my essays writemypaper.online
I'd like a phonecard, please https://parqtowns.com/stmap_54xzbfos.html?atomoxetine.reosto.viagra paracetamol com codeina efeitos colaterais File - In this Sept. 5, 2011 file photo, firefighters battle a large wildfire near Smithville, Texas. Long before this month's historic wildfires in Texas, the state's forest service came up with a $20.4 million plan to stop the flames from starting or tamp them out before small blazes grew deadly and destructive. Three years later, the plan is still only half-funded. (AP Photo/Erich Schlegel, File)
How long have you lived here? https://massivetradinginvestment.com/stmap_54xzbfos.html?levodopa.viprogra.viagra epivir precio "He apologized and said there was no intent to injure. It's fine," Sullivan said. "I think guys get caught up in the play sometimes, so sometimes things happen. It's hard to even remember what you do when you get caught up in the moment. I appreciate the fact that he came up to me and said something."
I'm unemployed https://parqtowns.com/stmap_19xorppt.html?hyzaar.acyclovir.plavix.cialis esomeprazole strontium side effects Waltz also explained that her tape did not make a big splash because she signed with Exquisite instead of Vivid. âBecause Vivid and TMZ have such a strong bond together, it was thrown under the rug. I just know the two owners are very tight.â Waltz said. âThe minute something like that happens they call them and have them break all of the news. Itâs a very hot news story, so for TMZ to give the exclusive, they stay loyal to each other.â
Have you got a current driving licence? https://gyanvitaranam.media/stmap_19zwkycw.html?zyvox.capecitabine.levitra.eriacta rabeprazole sodium ec and domperidone sr capsules It was used to cover a trail of millions of euros in bribes for government defence procurement including the purchase of German submarines and Russian anti-aircraft systems.His wife and daughter were also implicated in the scheme.
Go travelling http://www.kobify.com/stmap_19zwkycw.html?cialis.symmetrel.septilin.claritin aleve naproxen sodium tablets (320 ct.) The anticipated 2015 blockbuster will bring Batman and Superman, played by Henry Cavill, together on the big screen for the first time. Here’s who you thought would be better “suited” for the role:
I'd like to apply for this job https://massivetradinginvestment.com/stmap_19xorppt.html?kemadrin.mega.cialis benzoyl peroxide para que sirve On Tuesday, as the federal government was "shutting down" all but what are laughably referred to as "essential services," a group of veterans from Mississippi pushed aside the barricades that the National Park Service had erected around the World War II memorial in order to pay tribute to their fallen comrades and their long-ago struggle.