Stored procedures returning data (error 2014)

[dave17]

I have been able to use MySQLdb fine until I tried to return data from a stored procedure (e.g. using SELECT or OUT) and it's driving me crazy. Here is the basic python program to show my problem:

import MySQLdb

conn = MySQLdb.connect(host="localhost", user="user1", passwd="pass1", db="test1")
conn.autocommit(1)
cursor = conn.cursor()
cursor.execute("CALL Test(123)")
print cursor.fetchall()
cursor.execute("SELECT 1")
print cursor.fetchall()
conn.close()

And here is the stored procedure Test:

CREATE PROCEDURE Test(
_i INTEGER
)
BEGIN
INSERT test (i) VALUES (-i);
SELECT LAST_INSERT_ID();
END

And test is just a 2 column table with an Id key and an integer i.

The error occurs when trying to execute a MySQL command after calling cursor.execute("CALL Test(123)"), here is the traceback:

Traceback (most recent call last):
File "\Avl-tst\dave\dcgps\Current checkout\python\dp_MySQL.py", line 47, in <module>
cursor.execute("SELECT 1")
File "C:\Python25\lib\site-packages\MySQLdb\cursors.py", line 166, in execute
self.errorhandler(self, exc, value)
File "C:\Python25\lib\site-packages\MySQLdb\connections.py", line 35, in defaulterrorhandler
raise errorclass, errorvalue
ProgrammingError: (2014, "Commands out of sync; you can't run this command now")

Calling the test stored procedure does affect the database and it does return the right last inserted id, but I get the 2014 error from MySQL. I looked on MySQL.com and found this about commands being out of sync http://dev.mysql.com/doc/refman/5.0/en/commands-out-of-sync.html.

"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."

Does this mean that python is failing to call something like mysql_free_result() after calling the stored procedure?

Here are some things that might help figuring this out:
- Python 2.5.1, MySQL 5.0.22, Windows XP
- I get the same error if I dont use SELECT in the stored procedure, but use an OUT variable instead. There are no errors if the stored procedure does not return anything.
- The stored procedure works fine from the mysql command prompt and in a php mysql script I wrote.
- If you try closing the connection after calling the stored procedure (as opposed to calling a mysql command like a new query or commit), then it gives me this error: "Exception _mysql_exceptions.OperationalError: (2013, 'Lost connection to MySQL server during query') in <bound method Cursor.del of <MySQLdb.cursors.Cursor object at 0x00BDC1D0>> ignored".
- Changing the table engine of test doesnt affect anything.
- This blogger is doing exactly what I am trying to do, but I am assuming that he is able to do it successfully http://2centspoorer.blogspot.com/2006/05/using-mysql-50-stored-procedures-with.html, so I am not sure what the implications of that could be.

If anyone has any idea of why I am having this problem or how to fix it, then I would be very appreciative.

Dave

[dave17]

I couldn't figure out how to edit a message, but a few corrections:

INSERT test (i) VALUES (_i); (not -i)

and the link to the blogger is http://2centspoorer.blogspot.com/2006/05/using-mysql-50-stored-procedures-with.html (it took the comma with it and couldnt find the page before).