#280 Some SELECT statements are failing silently using cursors

[Claudio Martínez]

I found out the hard way that cursors are not raising exceptions in some cases when a "SELECT" statement fails.

For example when there is a deadlock or lock timeout the statement fails silently and fetchall() returns an empty list.

I have confirmed this bug on these environments:
1. Debian Etch 64 Bits, mysql 5.0.32, python 2.4.4, mysqldb 1.2.2
2. Ubuntu 8.10 64 Bits, mysql 5.0.67, python 2.5.2, mysqldb 1.2.2/1.2.3rc

How to reproduce:

First create some tables and insert data (the inserts are needed, otherwise the locks won't lock the selects, only the inserts):
CREATE TABLE testing1 (a int(11) NOT NULL, PRIMARY KEY (a)) ENGINE=InnoDB;
CREATE TABLE testing2 (a int(11) NOT NULL, PRIMARY KEY (a)) ENGINE=InnoDB;
INSERT INTO testing1 VALUES (12345);
INSERT INTO testing2 VALUES (34567);

The run the attached python program (adjust the connection configuration):

Running this program may take a while depending on your innodb_lock_wait_timeout.
I lowered mine to 5 seconds for these tests otherwise it may take up to 10 minutes, make sure you set innodb_lock_wait_timeout to something low.

This is the output of running bug_mysqldb.py:
c1 ((12345L,),) 0
c2 ((34567L,),) 0
c1 () 0
REAL WARNINGS (('Error', 1205L, 'Lock wait timeout exceeded; try restarting transaction'),)

You can see in the third row that the statement failed silently. fetchall() returned an empty list and there were no warnings or exceptions.
On the last line is the result of running show_warnings() manually, there are indeed errors there.

After applying the attached patch here is the result of running the test again:
c1 ((12345L,),) 0
c2 ((34567L,),) 0
Traceback (most recent call last):
File "bug_mysqldb.py", line 19, in <module>
c1.execute('''SELECT * FROM testing2 FOR UPDATE;''')
File "/home/koba/tmp/mysqldb/mysqldbbug/lib/python2.5/site-packages/MySQL_python-1.2.3c1-py2.5-linux-x86_64.egg/MySQLdb/cursors.py", line 185, in execute
self.errorhandler(self, exc, value)
File "/home/koba/tmp/mysqldb/mysqldbbug/lib/python2.5/site-packages/MySQL_python-1.2.3c1-py2.5-linux-x86_64.egg/MySQLdb/connections.py", line 36, in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1205, 'Lock wait timeout exceeded; try restarting transaction')

With the patch, the third statement raised the expected exception.

[Claudio Martínez]

Patch that solves the problem for me

[Claudio Martínez]

Where I said "mysqldb 1.2.2/1.2.3rc" I meant "mysqldb 1.2.2/1.2.3c1".

[Claudio Martínez]

Script to reproduce the problem

[Kyle VanderBeek]

It's a good thing I read /r/Python. I'll have a look at this. :-)

[Kyle VanderBeek]

More concise repro, it only takes 2 statements to cause the deadlock

[Sean Blanchfield]

I have also encountered this bug, using the serializable isolation level.

I have found that a "writer" connection waiting on a "reader" connection to complete will receive the OperationalError exception as expected, but that a "reader" waiting on a "writer" will silently fail (with interesting consequences in our app).

I noted that in _mysql.c, mysql_real_query was returning 0 in this case, instead of a non-zero value - this is perhaps the reason. I can't find more on this as a known bug to do with that function though.

The patch supplied here worked - thanks.

[Anonymous]

We're big users of python and we ran into this problem as well. The root of the problem is in _mysql.c in the call to mysql_use_result()/mysql_store_result(). It's possible for these calls to return an error, but the code is not properly handling that case.

The originally supplied patch might work, but I think there is a cleaner fix. I'll supply a patch.

[Anonymous]

My sourceforge.net access seems to be too limited to actually attach a patch file. I'll just paste it in here:

--- x/_mysql.c 2010-06-17 00:21:56.000000000 -0700
+++ y/_mysql.c 2012-03-20 14:13:27.000000000 -0700
@@ -383,6 +383,11 @@
self->result = result;
Py_END_ALLOW_THREADS ;
if (!result) {
+ if (mysql_errno(&(conn->connection))) {
+ _mysql_Exception(conn);
+ return -1;
+ }
+
self->converter = PyTuple_New(0);
return 0;
}