Bug #96999 Connector/Python attempts to parse TEXT/BLOB column value as JSON
Submitted: 24 Sep 2019 14:48 Modified: 25 Sep 2019 9:49
Reporter: Mikko Nylén Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / Python Severity:S3 (Non-critical)
Version:8.0.17 OS:Any
Assigned to: CPU Architecture:Any

[24 Sep 2019 14:48] Mikko Nylén
Description:
I was surprised today to find that Connector/Python tries to interpret TEXT/BLOB column as JSON when using cursors from PooledMySQLConnection.

Notably, if the value on the row is a numeric value, it comes out of the cursor as integer instead of string as one would expect for a TEXT/BLOB column. If the value is just a normal string, it comes out as string.

But this happens only with PooledMySQLConnection - with normal MySQLConnection the behaviour is to return strings.

The logic that converts the TEXT/BLOBs is in MySQLConverter class _BLOB_to_python method which internally calls _JSON_to_python.

I'm using MySQL 5.6.

How to repeat:
1. Create a table with a single TEXT column and INSERT some data into it:

```
CREATE TABLE testdata (
  value TEXT
);

INSERT INTO testdata (value) VALUES
  ('just a string', '123456', 'hello', 'false', 'true');

```

2. Query the values with MySQL Connector / Python using just regular, unpooled connection:

```
import mysql.connector

connection_details = {'host': 'localhost','port': 3306, 'database': 'testdb', 'user': 'johndoe', 'password': 'secret' } 

connection_regular = mysql.connector.connect(**connection_details)
cursor_regular  = connection_regular.cursor()
cursor_regular.execute("SELECT value FROM testdata")

for row in cursor_regular:
    print(f"value fetched via regular connection: {row[0]} {type(row[0])}")
```

The output will be:

```
value fetched via regular connection: just a string <class 'str'>
value fetched via regular connection: 123456 <class 'str'>
value fetched via regular connection: true <class 'str'>
value fetched via regular connection: false <class 'str'>
value fetched via regular connection: hello <class 'str'>
```

3. Create a connection pool and query the values through a pooled connection:

```
connection_pool = mysql.connector.pooling.MySQLConnectionPool(**connection_details)
connection_pooled = connection_pool.get_connection()
cursor_pooled = connection_pooled.cursor()
cursor_pooled.execute("SELECT value FROM testdata")

for row in cursor_pooled:
    print(f"value fetched via pooled connection: {row[0]} {type(row[0])}")
```

The output from this will be:

```
value fetched via pooled connection: just a string <class 'str'>
value fetched via pooled connection: 123456 <class 'int'>
value fetched via pooled connection: True <class 'bool'>
value fetched via pooled connection: False <class 'bool'>
value fetched via pooled connection: hello <class 'str'>
```

Note the difference in the printed data types for the values. 

Suggested fix:
* Make the documentation clear that there's difference on how TEXT/BLOB columns are handled with cursors from pooled connections.

* Allow users to opt-out from the JSON parsing. Preferably this should be default, as this is very surprising behaviour and inconsistent.
[25 Sep 2019 9:49] MySQL Verification Team
Hello Mikko,

Thank you for the report and feedback.

regards,
Umesh