#307 python MySQLdb returns -1 on cursor.execute() for concurrent

[thushara wijeratna]

I'm executing a transaction from about 15 machines to a mysql server using python.
Some of these queries return -1 on cursor.execute()
Since there is no exception thrown, I'm unable to determine the reason.

I looked through cursors.py, connections.py and it seems that cursor.execute() is returning the result of get_affected_rows() in the C api. however, it seems that if a mysql error is encountered during the query, before get_affected_rows() is called, MySQLdb should raise an exception as that is what the default error handler does.

I ran the same scenario in perl (with DBI) and it returns an error.

DBD::mysql::st execute failed: Lock wait timeout exceeded; try restarting transaction at /scripts/db.pl line 16.
DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at /scripts/db.pl line 19.

somehow, the python bindings seem to not give this error. at other times, i have seen this error from python bindings, the only difference this time is i'm using transactions.

[Anonymous]

I encountered the same problem. The reason is this age old MySQL bug/feature: http://bugs.mysql.com/bug.php?id=12567 and the fact that MySQLdb does not raise appropriate exceptions for errors happening during calls to mysql_store_result/mysql_use_result.

In my case, I tried to get a write lock on some innodb rows which are already locked by another connection. According to the MySQL API, one has to request the actual data to make sure the select succeeded:

cursor.execute('select id from foo for update');
cursor.fetchall()

That should raise an exception on the second line (OperationalError: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction) after waiting some time (default is 50s, see http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout). By only looking at the return value of fetchall, its not possible to decide wether the locking was successful.

But it does just return None. A workaround would be to use the mysql_errno() function to get the last error and raise an exception manually:

cursor = conn.cursor()
cursor.execute('select id from foo for update');
cursor.fetchall()
if conn._db.errno():
raise ....

Or use the connection.affected_rows() function, which returns -1 casted as an unsigned long (2**64) for those failed statements, but I'm afraid this is not really documented behavior.

[Anonymous]

Index: src/results.c

--- src/results.c (Revision 650)
+++ src/results.c (Arbeitskopie)
@@ -71,13 +71,19 @@
result = mysql_store_result(&(conn->connection));
self->result = result;
Py_END_ALLOW_THREADS ;
- if (!result) {
- return 0;
+
+ / see http://dev.mysql.com/doc/refman/5.1/en/null-mysql-store-result.html /
+ if (result) {
+ n = mysql_num_fields(result);
+ self->nfields = n;
+ self->fields = _mysql_ResultObject_get_fields(self, NULL);
+ } else {
+ if (mysql_field_count(&(conn->connection))) {
+ // the query should have returned at least one result
+ // but it didn't, so we raise an exception
+ return _mysql_Exception(conn);
+ }
}
- n = mysql_num_fields(result);
- self->nfields = n;
- self->fields = _mysql_ResultObject_get_fields(self, NULL);
-
return 0;
}

[Andy Dustman]

I think maybe this was fixed here:

http://mysql-python.svn.sourceforge.net/viewvc/mysql-python?view=revision&revision=655

Can you try to replicate it with the current SVN source? branch MySQLdb-1.2