今天在使用mysql curosr的时候使用了SScursor将结果存在了server端，但是总是会在运行到一定时间左右就报lost connect during query，于是加上了conn.ping()结果报了如上错误，这个是由于打开一个cursor默认相当于在连接上开了一个事务，这个时候未完成不允许修改这个链接。
后来追查问题看到了如下链接，但是后来同样的数据换成了ssd也就没再出现，这个问题也就没有再深究了，事情太多了，不过感觉下文真的写的很好，不外乎下面这几种情况了：
There are times when your query returns a very large number of rows. If you use the default cursor, chances are your process will be killed while retrieving the rows. The reason is by default MySQL clients (e.g. Java connector, Python driver) retrieve all rows and buffer them in memory before passing the result set to your code. If you run out of memory while doing that, your process is certainly killed.
The fix is to use streaming result set. In Python, you can use MySQLdb.cursors.SSCursor for this purpose.
There are two important things to remember here:
- You use an SSCursor instead of the default cursor. This can be done like shown above, or by passing the class name to cursor() call such as conn.cursor(MySQLdb.SSCursor).
- Use fetchone to fetch rows from the result set, one row at a time. Do not use fetchall. You can use fetchmany but it is the same as calling fetchone that many times.
One common misconception is to treat SSCursor as a server side cursor. It is not! This class is in fact only an unbuffered cursor. It does not read all result set into memory like the default cursor does (hence a buffered cursor). What it does is reading from the response stream in chunks and returning record by record to you. There is another more appropriate name for this: a streaming result set.
Because SSCursor is only an unbuffered cursor, (I repeat, not a real server side cursor), there are several restrictions applied to it:
- You must read ALL records. The rational is that you send one query, and the server replies with one answer, albeit a really long one. Therefore, before you can do anything else, even a simple ping, you must completely finish this response.
- This brings another restriction that you must process each row quickly. If your processing takes even half a second for each row, you will find your connection dropped unexpectedly with error 2013, “Lost connection to MySQL server during query.” The reason is by default MySQL will wait for a socket write to finish in 60 seconds. The server is trying to dump large amount of data down the wire, yet the client is taking its time to process chunk by chunk. So, the server is likely to just give up. You can increase this timeout by issuing a query SET NET_WRITE_TIMEOUT = xx where xx is the number of seconds that MySQL will wait for a socket write to complete. But please do not rely on that to be a workable remedy. Fix your processing instead. Or if you cannot reduce processing time any further, you can quickly chuck the rows somewhere local to complete the query first, and then read them back later at a more leisure rate.
- The first restriction also means that your connection is totally held up while you are retrieving the rows. There is no way around it. If you need to run another query in parallel, do it in another connection. Otherwise, you will get error 2014, “Commands out of sync; you can’t run this command now.”
转载请注明来源链接 http://just4fun.im/2017/04/04/python-mysql-e6-8a-a5-e9-94-99-ef-bc-9acommands-out-of-sync-you-cant-run-this-command-now/ 尊重知识,谢谢:)