python mysql 2014 Commands out of sync; you can't run this command now

最新推荐文章于 2023-11-30 20:04:06 发布

转载最新推荐文章于 2023-11-30 20:04:06 发布 · 7.4k 阅读

MYSQL 专栏收录该内容

40 篇文章

订阅专栏

本文详细介绍了在使用MySQL与C API进行数据库操作时遇到的Commands out of sync错误，解释了其原因及解决方法。通过实例演示了如何正确处理存储结果集与执行命令之间的顺序问题，确保程序顺利执行多条SQL语句。

这个问题出现再 mysql和c 的api。

简单的解决方法是不使用api直接把整个连接和命令传过去。

例如，cmd = 'mysql -h 192.168.32.210 -P 3316 -u bfdroot -pqianfendian -D DMP_GDMP_Cbehe -e "%s"' % update_sql2

问题原因：

Mysql文档：Commands out of sync

If you get Commands out of sync; you can't run this command now in your client code, you are calling client functions in the wrong order.

This can happen, for example, if you are using mysql_use_result() and try to execute a new query before you have called mysql_free_result(). It can also happen if you try to execute two queries that return data without calling mysql_use_result() or mysql_store_result() in between.

第一种存储结果未释放，然后又查询

第二种两次查询之间没有存储结果

解决方案：

do
{
result = mysql_store_result( mysql );
mysql_free_result(result);
}while( !mysql_next_result( mysql ) );

例子 http://www.linuxidc.com/Linux/2013-04/82619.htm

: Update OK!sql: insert into t_alarm_record_file (recordPath,recordName,hostIp,startTime,endTime,deviceId,programNumber,deviceType,interfaceNo,alarmType,alarmTime) values ('/figure/data/AlarmRecord/StreamTS/1-码流_魅力音乐主路/2011-11-07/20111107150116.ts','','10.0.60.2','2011-11-07 15:01:16','1970-01-01 08:00:00',37486602,3905,'0',1,12,'2011-11-07 15:01:20');update t_alarm set fileId = LAST_INSERT_ID() where deviceId = 37486602 and programNumber = 3905 and alarmType = 12 and alarmDate = '2011-11-07 15:01:20' and fileId is null

2011-11-07 15:01:35,331: ERROR : Update failed!sql: insert into t_alarm_record_file (recordPath,recordName,hostIp,startTime,endTime,deviceId,programNumber,deviceType,interfaceNo,alarmType,alarmTime) values ('/figure/data/AlarmRecord/StreamTS/1-码流_魅力音乐主路/2011-11-07/20111107150116.ts','','10.0.60.2','2011-11-07 15:01:16','1970-01-01 08:00:00',37486602,3905,'0',1,13,'2011-11-07 15:01:27');update t_alarm set fileId = LAST_INSERT_ID() where deviceId = 37486602 and programNumber = 3905 and alarmType = 13 and alarmDate = '2011-11-07 15:01:27' and fileId is null, ERROR:Commands out of sync; you can't run this command now

在第一次调用Update()执行多条sql语句时成功，但以后的所有调用都失败了。

经过查找，发现问题在于：在Update()中执行多条sql语句时，

如果仅仅是插入等不需要返回值的SQL语句，也一样得读完整个resault集并释放，最小化的写法：

do
{
result = mysql_store_result( mysql );
mysql_free_result(result);
}while( !mysql_next_result( mysql ) );

经过这么一处理，问题终于解决了。