MySQL Bugs: #73187: Cursor tries to decode linestring data as UTF-8 raising an Exception

Bug #73187	Cursor tries to decode linestring data as UTF-8 raising an Exception
Submitted:	3 Jul 2014 15:14	Modified:	17 Jul 2014 13:58
Reporter:	Blaat Schaap	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / Python	Severity:	S3 (Non-critical)
Version:	1.2.2-py3.3	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	InterfaceError, linestring, mysql.connector, python

Description:
When performing a SELECT query containing binary data (VARBINARY) in Python 3.3.3. Iterating over the result will raise an InterfaceError on Line 312 of mysql/connector/conversion.py:

        except KeyError:
            # If one type is not defined, we just return the value as str
            return value.decode('utf-8')

Appearantly it tries to return the binary content in string format even though it would be more logical to just return it as bytes type.

How to repeat:
""" Bug test mysql.connector """
import mysql.connector

conn = mysql.connector.connect(host='localhost', port=3306, user='username', database='db_bug_test')
cur = conn.cursor()

cur.execute("""CREATE TABLE IF NOT EXISTS bug_test (
  id SERIAL PRIMARY KEY AUTO_INCREMENT NOT NULL,
  bin_field VARBINARY(16) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=ascii;""")
cur.execute('INSERT IGNORE INTO bug_test (id, bin_field) VALUES (0, %s)', (b'\xcbwL\x05', ))

cur.execute("SELECT * FROM valley LIMIT 1")
for x in cur:
    print(x)

cur.close()

Suggested fix:
Instead of :

return value.decode('utf-8')

return the raw binary data:

return value

This way it will not raise an error when the data cannot be UTF-8 decoded.

Edited version number, had Python version instead of Connector version.

Sorry, just realized I made a big mistake in the bug test case.
I insert data into bug_test and then I select from valley, which is a table that was not in the test case. If the table valley is replaced with bug_test it will work just fine.

The reason that it raised an Exception in the data from valley is that the valley table contains a column with type LINESTRING, which will be tried to be converted to a string.

I believe this way you can reproduce the error:

""" Bug test mysql.connector """
import mysql.connector

conn = mysql.connector.connect(host='localhost', port=3306, user='username', database='db_bug_test')
cur = conn.cursor()

cur.execute("""DROP TABLE IF EXISTS bug_test;""")
cur.execute("""CREATE TABLE IF NOT EXISTS bug_test (
  id SERIAL PRIMARY KEY AUTO_INCREMENT NOT NULL,
  ls LINESTRING NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=ascii;""")
cur.execute('INSERT IGNORE INTO bug_test (id, ls) VALUES (0, LINESTRING(POINT(124,5646), POINT(1221,455)))')

cur.execute("SELECT * FROM bug_test LIMIT 1")
for x in cur:
    print(x)

cur.close()

Thanks for your bug report.
Verified using code analysis.

Noted in 1.2.3 changelog.

An exception was raised when a cursor tried to convert LINESTRING
data as UTF-8 data. Now such values are returned without decoding.

Noted in 2.0.1 changelog.