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

MySQL-python: Commands out of sync

MAR 13TH, 2012

在给 MySQL 数据库访问层增加新功能时遇到了这样的错误:

1
ProgrammingError: (2014, "Commands out of sync; you can't run this command now")

之前零星地见到过几次,因为发生频度很低,就没有太在意,这次找了一下原因,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.

Commands out of sync dev.mysql.com/doc/refman/5.1/en/…

MySQL 和客户端通信使用的是“半双工”的应答协议,客户端每发送一个查询,服务器“强行推送”结果给客户端,客户端需要执行 mysql_use_result() 或 mysql_store_result() 从服务器端取回结果,这是一次完整的查询交互,只提交查询而不取回结果就会导致 Commands out of sync

由于使用的是 MySQL-python,所以第一种情况所说的没有调用 mysql_free_result() 的问题不大可能存在,并且 MySQLdb 的默认 cursor 使用的是 mysql_store_result() 而不是 mysql_use_result(),所以应该是第二种情况。

抓取了一些有可能会导致这个问题的查询,发现了类似这样的语句:

1
SELECT a, b FROM t LIMIT 1; -- some comments

执行了这样的查询之后,再执行任何查询都会遇到 Commands out of sync 问题。

因为这其实是由分号隔开的两个独立的查询语句,使用 MySQLdb 执行时,作为一个语句发送给 MySQL server,之后 MySQLdb 执行了一次 mysql_store_result()

之前需要自动地给每一个查询增加注释,而个别的查询在末尾写了分号,追加注释之后就触发了这个问题,那么只需要在加注释前 strip 掉分号就好了。

MySQLdb 有四种 cursor:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
class Cursor(CursorStoreResultMixIn, CursorTupleRowsMixIn,                      
             BaseCursor):                                                       
                                                                                
    """This is the standard Cursor class that returns rows as tuples            
    and stores the result set in the client."""                                 
                                                                                
                                                                                
class DictCursor(CursorStoreResultMixIn, CursorDictRowsMixIn,                   
                 BaseCursor):                                                   
                                                                                
     """This is a Cursor class that returns rows as dictionaries and            
    stores the result set in the client."""                                     
                                                                                
                                                                                
class SSCursor(CursorUseResultMixIn, CursorTupleRowsMixIn,                      
               BaseCursor):                                                     
                                                                                
    """This is a Cursor class that returns rows as tuples and stores            
    the result set in the server."""                                            
                                                                                
                                                                                
class SSDictCursor(CursorUseResultMixIn, CursorDictRowsMixIn,                   
                   BaseCursor):                                                 
                                                                                
    """This is a Cursor class that returns rows as dictionaries and             
    stores the result set in the server."""

默认使用的是 Cursor,在提交查询之后执行 mysql_store_result(),将 MySQL Server 返回的所有数据都取回并缓存在本地,SSCursor 则是使用 mysql_use_result(),将结果“缓存”在服务器端,客户端逐行取回结果,好处是速度会比 mysql_store_result() 快,并且客户端可以节省内存,但在高并发环境下,还是使用默认的 Cursor 比较好,因为 MySQL server 只有在客户端取回所有的结果之后才会释放相关的锁(如果有的话),而逐行取回并分别对每行做处理大多会需要更长的时间。

本来想所有语句一次性执行,用了;来连接sql语句,于是便出现了该错误,最后解决方案是每个sql分别execute

原文链接

MYSQL:Commands out of sync; you can't run this command now

04-21

昨天请高人帮忙改了个存储过程,也觉得代码已经没问题了,但跑起来总是提示这个错误,头都大了。rnrn存储过程代码如下:rn[code=SQL]rn-- --------------------------------------------------------------------------------rn-- Routine DDLrn-- --------------------------------------------------------------------------------rnDELIMITER $$rnrnCREATE DEFINER=`root`@`%` PROCEDURE `USP_CreateTrackerLogTable`(IN Pointer DATETIME,IN Counts INT)rnbeginrn declare CountPointer int;rn declare DateString varchar(20);rn declare TableName varchar(50);rn declare SQLTxt varchar(5000);rn declare ExecuteSQL varchar(8000);rn rn Set CountPointer = Counts;rn rn Set SQLTxt = ' rn CREATE TABLE IF NOT EXISTS Tracker_Log(rn LogID bigint PRIMARY KEY auto_increment NOT NULL, /*自增ID*/rn TrackerID varchar(50) NOT NULL, /*日志ID*/rn LogTime datetime NOT NULL, /*日志时间*/rn SessionId varchar(50) NOT NULL, /*会话ID*/rn PassportID bigint NULL, /*账户ID*/rn UserID bigint NULL, /*用户ID*/rn ClientIP varchar(64) NULL, /*客户端IP*/rn ServerIP varchar(64) NULL, /*应用服务器IP*/rn CurrentUrl varchar(255) NULL, /*当前页链接*/rn ReferrerUrl varchar(255) NULL, /*前一页链接*/rn ClickLinkUrl varchar(255) NULL, /*超链接链接地址*/rn ClickLinkTitle nvarchar(200) NULL, /*超链接链接名*/rn CurrentClientTime datetime NULL, /*当前客户端时间*/rn CurrentServerTime datetime NULL, /*当前应用服务器时间*/rn ClientServerTimeDif bigint Null, /*客户端与服务器时间差*/rn ServerExecuteTime bigint NULL, /*应用服务器执行请求时间*/rn NetTransTime bigint NULL, /*网络传输时间*/rn ServerResponseTime datetime NULL, /*服务器输出时时间*/rn ClientLoadTime bigint NULL, /*客户端页面加载时间*/rn UserDwellTime bigint NULL /*用户在当前页停留时间*/rn );';rnrn WHILE (CountPointer > 0) DOrn BEGINrn /*处理日期*/rn SET DateString = CONCAT(RIGHT(CAST(YEAR(Pointer) AS CHAR(4)), 2),'_',rn RIGHT('0' + CAST(MONTH(Pointer) AS CHAR(2)), 2),'_',rn RIGHT('0' + CAST(DAY(Pointer) AS CHAR(2)), 2));rn rn Set TableName = 'Tracker_Log_' + DateString;rn rn /*替换脚本字符串*/rn Select ExecuteSQL = Replace(SQLTxt, 'Tracker_Log', TableName);rnrn /*执行*/rn Select SQLTxt;rn /*SET @asql=ExecuteSQL;rn PREPARE tt FROM @asql;rn EXECUTE tt;*/rn /*print ExecuteSQL*/rn rn /*准备下一次*/rn Set CountPointer = CountPointer - 1;rn SET Pointer = DATE_ADD(CURDATE(), INTERVAL CountPointer DAY );rn END;rn END WHILE;rnENDrnrn[/code]rnrnrn另外,我可能要在SQLTxt中放入创建多个表的代码,用于定时任务,不知道是否也行?rn因为现在一个表我都跑不过去,多个表一起创建就没有测试,特请教大家。 论坛

没有更多推荐了,返回首页