Menu

Cursor gotcha and suggested patch

2009-04-20
2012-09-19
  • 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 checked this against 1.2.3c1.

    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 (12083718293819723);
    INSERT INTO testing2 VALUES (12708478123789323);

    Python:
    db1 = MySQLdb.connect(host='localhost', db='TESTS',
    read_default_file='~/.my.cnf')
    db2 = MySQLdb.connect(host='localhost', db='TESTS',
    read_default_file='~/.my.cnf')

    c1 = db1.cursor()
    c2 = db2.cursor()

    c1.execute('BEGIN')
    c1.execute('''SELECT * FROM testing1 FOR UPDATE;''')
    print 'c1', c1.fetchall(), c1._warnings

    c2.execute('BEGIN')
    c2.execute('''SELECT * FROM testing2 FOR UPDATE;''')
    print 'c2', c2.fetchall(), c2._warnings

    c1.execute('''SELECT * FROM testing2 FOR UPDATE;''')
    print 'c1', c1.fetchall(), c1._warnings

    print 'REAL WARNINGS', c1._get_db().show_warnings()

    This is the output of running this program:
    c1 ((2147483647L,),) 0
    c2 ((2147483647L,),) 0
    c1 () 0
    REAL WARNINGS (('Error', 1205L, 'Lock wait timeout exceeded; try restarting transaction'), ('Error', 1105L, 'Unknown error'))

    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 ((2147483647L,),) 0
    c2 ((2147483647L,),) 0
    Traceback (most recent call last):
    File "testing_mdb.py", line 107, in ?
    c1.execute('''SELECT * FROM testing2 FOR UPDATE;''')
    File "/home/koba/Desarollo/InetPub/IBSRL/VirtualEnv-1.0-p2.4/lib/python2.4/site-packages/MySQL_python-1.2.3c1-py2.4-linux-x86_64.egg/MySQLdb/cursors.py", line 185, in execute
    self.errorhandler(self, exc, value)
    File "/home/koba/Desarollo/InetPub/IBSRL/VirtualEnv-1.0-p2.4/lib/python2.4/site-packages/MySQL_python-1.2.3c1-py2.4-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')

    Here's the patch:
    --- cursors.py.old 2009-04-19 22:10:32.590996164 -0300
    +++ cursors.py 2009-04-19 22:11:15.914994931 -0300
    @@ -114,6 +114,18 @@
    def _do_get_result(self):
    db = self._get_db()
    self._result = self._get_result()
    +
    + # Check if the query should have returned a result,
    + # as shown here:
    + # http://dev.mysql.com/doc/refman/5.0/en/mysql-field-count.html
    + if not self._result and db.field_count() != 0:
    + # Similar to _mysql_Exception. Not perfect.
    + if (db.errno() < 1000):
    + e = db.InternalError
    + else:
    + e = db.OperationalError
    + raise e(db.errno(), db.error())
    +
    self.rowcount = db.affected_rows()
    self.rownumber = 0
    self.description = self._result and self._result.describe() or None

     

Log in to post a comment.