C语言操作mysql遇到[2014]: Commands out of sync; you can't run this command now问题,如何解决?

作者站长头像
站长
· 阅读数 9

如题,我自己尝试写的一个视频增删改查的点播系统,会遇到mysql的如下报错,导致什么sql都执行不了。

代码有点长,如果有好心的大佬能帮忙看看就太好了。https://github.com/musnows/Vedio-On-Demand/blob/main/server/data/mysql.hpp

日志如下,从第一个 mysql store result failed 的错误开始,后续的命令全都是sync错误;这个问题只要前端快速操作几次就一定能出现…… 改成了sqlite3到没发现类似问题。

chatgpt说,sync错误是因为结果集没有释放,就二次执行select语句导致的,学的时候老师也是这么说的。但查询的函数中加了锁,也检查过结果集释放,所以我还是没能发现哪里有问题。(原谅我,有时候眼瞎看不出来一些显而易见的bug……)

23-05-05 11:30:11 | INFO | Server.GetAll | Select All success
23-05-05 11:30:11 | INFO | MysqlQuery.Success | sql: select * from tb_video;
23-05-05 11:30:11 | INFO | Video SelectAll | select all finished
23-05-05 11:30:11 | INFO | Server.GetAll | Select All success
23-05-05 11:30:20 | INFO | Server.GetOneView | get recv from 127.0.0.1
23-05-05 11:30:20 | INFO | Server.GetOneView | video id recv! id: [0105a290]
23-05-05 11:30:20 | INFO | Server.GetOne | video id recv! id: [0105a290]
23-05-05 11:30:20 | INFO | Server.GetOne | video id recv! id: [0105a290]
23-05-05 11:30:20 | INFO | MysqlQuery.Success | sql: select * from tb_video where id='0105a290';
23-05-05 11:30:20 | INFO | Video SelectOne | id '0105a290' found
23-05-05 11:30:20 | INFO | MysqlQuery.Success | sql: select * from tb_views where id='0105a290';
23-05-05 11:30:20 | INFO | SelectVideoView | id '0105a290' found
23-05-05 11:30:20 | INFO | MysqlQuery.Success | sql: select * from tb_video where id='0105a290';
23-05-05 11:30:20 | ERROR | Video SelectOne | mysql store result failed | err[0]: 
23-05-05 11:30:20 | ERROR | Server.GetOne | video id not exists! id: [0105a290]
23-05-05 11:30:20 | INFO | MysqlQuery.Success | sql: update tb_views set view=15 where id='0105a290';
23-05-05 11:30:20 | INFO | Server.GetOneView | get success! id: [0105a290]
23-05-05 11:30:27 | INFO | Server.GetOne | get recv from 127.0.0.1
23-05-05 11:30:27 | INFO | Server.GetOne | video id recv! id: [6d1db3db]
23-05-05 11:30:27 | ERROR | MysqlQuery.Err | sql: select * from tb_video where id='6d1db3db';
23-05-05 11:30:27 | ERROR | MysqlQuery.Err | err[2014]: Commands out of sync; you can't run this command now
23-05-05 11:30:27 | ERROR | Video SelectOne | query failed
23-05-05 11:30:27 | ERROR | Server.GetOne | video id not exists! id: [6d1db3db]
23-05-05 11:30:28 | INFO | Server.GetOneView | get recv from 127.0.0.1
23-05-05 11:30:28 | INFO | Server.GetOneView | video id recv! id: [6d1db3db]
23-05-05 11:30:28 | ERROR | MysqlQuery.Err | sql: select * from tb_video where id='6d1db3db';
23-05-05 11:30:28 | ERROR | MysqlQuery.Err | err[2014]: Commands out of sync; you can't run this command now
23-05-05 11:30:28 | ERROR | Video SelectOne | query failed
23-05-05 11:30:28 | ERROR | Server.GetOneView | video id not exists! id: [6d1db3db]
23-05-05 11:32:39 | ERROR | MysqlQuery.Err | sql: select * from tb_video;
23-05-05 11:32:39 | ERROR | MysqlQuery.Err | err[2014]: Commands out of sync; you can't run this command now
23-05-05 11:32:39 | ERROR | Video SelectAll | query failed
23-05-05 11:32:39 | ERROR | Server.GetAll | database query err!
23-05-05 11:33:04 | ERROR | MysqlQuery.Err | sql: select * from tb_video;
23-05-05 11:33:04 | ERROR | MysqlQuery.Err | err[2014]: Commands out of sync; you can't run this command now
23-05-05 11:33:04 | ERROR | Video SelectAll | query failed
23-05-05 11:33:04 | ERROR | Server.GetAll | database query err!
23-05-05 11:33:14 | ERROR | MysqlQuery.Err | sql: select * from tb_video where name like '%鹅%';
23-05-05 11:33:14 | ERROR | MysqlQuery.Err | err[2014]: Commands out of sync; you can't run this command now
23-05-05 11:33:14 | ERROR | Video SelectLike | query failed
23-05-05 11:33:14 | ERROR | Server.GetAll.Like | database query err!
23-05-05 11:33:15 | ERROR | MysqlQuery.Err | sql: select * from tb_video where name like '%鹅%';
23-05-05 11:33:15 | ERROR | MysqlQuery.Err | err[2014]: Commands out of sync; you can't run this command now
23-05-05 11:33:15 | ERROR | Video SelectLike | query failed
23-05-05 11:33:15 | ERROR | Server.GetAll.Like | database query err!
23-05-05 11:33:19 | ERROR | MysqlQuery.Err | sql: select * from tb_video where name like '%鹅%';
23-05-05 11:33:19 | ERROR | MysqlQuery.Err | err[2014]: Commands out of sync; you can't run this command now
23-05-05 11:33:19 | ERROR | Video SelectLike | query failed
23-05-05 11:33:19 | ERROR | Server.GetAll.Like | database query err!
23-05-05 11:33:20 | ERROR | MysqlQuery.Err | sql: select * from tb_video where name like '%鹅%';
23-05-05 11:33:20 | ERROR | MysqlQuery.Err | err[2014]: Commands out of sync; you can't run this command now

系统是CentOS8,Mariadb版本如下

mysql  Ver 15.1 Distrib 10.3.28-MariaDB, for Linux (x86_64) using readline 5.1
回复
1个回答
avatar
test
2024-07-03

自回答,问题已解决。从日志顺序上看,是在select one的时候,又进行了tb_views的update操作导致的。只需要在UpdateVideoView函数中加锁,就能解决此问题!

https://github.com/musnows/Vedio-On-Demand/blob/db8713e8f44259716e5a1fc050a21e060067a30c/server/data/mysql.hpp#L357-L364

23-05-05 11:30:20 | INFO | SelectVideoView | id '0105a290' found
23-05-05 11:30:20 | INFO | MysqlQuery.Success | sql: select * from tb_video where id='0105a290';
23-05-05 11:30:20 | ERROR | Video SelectOne | mysql store result failed | err[0]: 
23-05-05 11:30:20 | ERROR | Server.GetOne | video id not exists! id: [0105a290]
23-05-05 11:30:20 | INFO | MysqlQuery.Success | sql: update tb_views set view=15 where id='0105a290';
23-05-05 11:30:20 | INFO | Server.GetOneView | get success! id: [0105a290]
23-05-05 11:30:27 | INFO | Server.GetOne | get recv from 127.0.0.1
回复
likes
适合作为回答的
  • 经过验证的有效解决办法
  • 自己的经验指引,对解决问题有帮助
  • 遵循 Markdown 语法排版,代码语义正确
不该作为回答的
  • 询问内容细节或回复楼层
  • 与题目无关的内容
  • “赞”“顶”“同问”“看手册”“解决了没”等毫无意义的内容