MySQL Unread Result with Python

Question

I use mysql.connector to do SQL operations. I have a short scripts which executes the following operations (strings) on the cursor with cursor.execute(...):

"use {}".format(db)

"show tables"

command = """
ALTER TABLE Object DROP PRIMARY KEY;
ALTER TABLE Object ADD `id` bigint(20) NOT NULL PRIMARY KEY AUTO_INCREMENT FIRST;
ALTER TABLE Object ADD INDEX (`uid`);"""

The script iterates over several databases db.

The problem is that at some point I get an "Unread result found" error. It seems when I run the script, at some point "use mydb" returns a result (cursor._have_result=True), when I didn't expect one. The weird thing is that if I rerun the full script it runs a little longer with more databases giving the same error later.

Can you suggest a way to solve or investigate this problem? Is there something I can do to prevent "unread results"?

PS: When I rerun the script the ALTER commands fails for the databases which are already done. Not sure if that causes problems.

Answer 1

Using MySQL Connector/Python, the Unread results found might happen when you use the connection object in different places without reading the result. It's not something one can go around. You can use the buffered option to read result immediately.

As mentioned in the comments, it's best to split the statements and execute them separately.

If you want to execute multiple statements, you'll need to use the multi=True option for the MySQLCursor.execute() method (since Connector/Python v1.0.4). Actually, if you don't use the multi option and send multiple statements, an InterfaceError will raise. (I do suspect a bug here as well..)

Additional remarks:

• Instead of executing the USE-command to change databases, you can MySQLConnection.database property.

• You best group the changes into one ALTER TABLE statement, like this:

  ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT NOT NULL AUTO_INCREMENT KEY FIRST, ADD INDEX(c1)