MySQL Bugs: #67649: DB session unusable if unbuffered cursor is closed before fetching all results

Bug #67649	DB session unusable if unbuffered cursor is closed before fetching all results
Submitted:	20 Nov 2012 11:42	Modified:	10 Jul 2013 13:18
Reporter:	Rudy Metzger	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / Python	Severity:	S3 (Non-critical)
Version:	1.0.7	OS:	Linux (Fedora 17)
Assigned to:	Geert Vanderkelen	CPU Architecture:	Any

Description:
If you open an unbuffered cursor and you do not read the WHOLE result set before closing the cursor, the next query will fail with

  File "/usr/lib/python3.2/site-packages/mysql/connector/connection.py", line 1075, in cursor
    raise errors.OperationalError("MySQL Connection not available.")
mysql.connector.errors.OperationalError: MySQL Connection not available.

and all subsequent database calls will fail too.

How to repeat:
mysql-connector-python3-1.0.7-1.fc17.noarch
python3-3.2.3-7.fc17.x86_64
mysql-server-5.5.28-1.fc17.x86_64
Fedora release 17 (Beefy Miracle)
Linux xxxxx 3.6.2-4.fc17.x86_64 #1 SMP Wed Oct 17 02:43:21 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux

-----------------------------------------------------------------------------------------------------------------

mysql> create table foo( a int );
mysql> insert into foo values( 1 ),( 2 );

------------------------------------------------------------------------------------------------------------------

#!/usr/bin/env python3

import sys
import mysql.connector

dbh = mysql.connector.Connect( host='host', port=1234, database='database', user='user', password='password' )
dbh.set_autocommit( True )

# --- query, but do not fetch all records
sql = "SELECT a FROM foo"
cursor = dbh.cursor()
cursor.execute( sql )
dbRec = cursor.fetchone()
#while cursor.fetchone():       # comment this out to fix the "bug"
#    pass
cursor.close()

print( dbRec )

# --- fetch records and list them
sql = "SELECT a FROM foo"
cursor = dbh.cursor( True )
cursor.execute( sql )
row = cursor.fetchone()
while row:
    print( row )
    row = cursor.fetchone()
cursor.close()

Suggested fix:
Preferred: At a call to cursor.close(), discard all unread results
Possible: Adjust the error message to better reflect what caused the problem

Unfortunately, they only ways out is either to read the complete the result, or close/open the connection (reconnect). You can't tell MySQL to just forget about the result and simply continue.

But, I agree, the error message could be better, or should.

Noted in 1.0.12, 1.1.1 changelogs.

An unclear OperationalError was raised if a cursor object was closed
while there were unread results. Connector/Python now raises an
InternalError indicating that there are still unread results. This
provides information that to avoid the error it is necessary to
consume the result by reading all rows.