Bug #105012 MySQL Connector/Python conversion issue after 8.0.24
Submitted: 22 Sep 2021 17:20 Modified: 3 Nov 2021 21:14
Reporter: Hao Gong Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / Python Severity:S1 (Critical)
Version:8.0.24, 8.0.26 OS:MacOS (11.5.2)
Assigned to: CPU Architecture:Any
Tags: python

[22 Sep 2021 17:20] Hao Gong
Description:
Sorry to report the same bug with #104991, because I found something new, but the status won't be changed to open again.

Recently I have just upgraded MySQL Connector/Python to latest 8.0.26, but I found a bug for JSON column conversion issue produced after 8.0.24. 

My client platform is MacOS 11.5.2 with brew installed Python 3.9.5, and the server MySQL is Percona Server Ver 5.7.30-33. 

When I try to fetch one row with JSON type, the error occurred: 
... 
.../lib/python3.9/site-packages/mysql/connector/conversion.py", line 589, in _STRING_to_python 
return value.decode(self.charset) 
AttributeError: 'str' object has no attribute 'decode' 

After I downgraded to 8.0.23, everything just works. So I found something related conversion change from 8.0.24 to address "BUG#30416704: Binary columns returned as strings". I think these changes may cause this situation. 

And with discovering the change in 8.0.24, I found the column protocol retuned tuple also changed, but in connection_cext.py the `_columns` info is not updated with adding the 8th tuple item. 

Currently I resolved this issue with rewriting ConvertClass, is there any idea about this situation? 

Thanks.

How to repeat:
1. The table schema is as simple as possible: 
CREATE TABLE `user` ( 
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 
`data` json NOT NULL 
) ENGINE=InnoDB AUTO_INCREMENT=69799 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; 

2. Init db connection with Python Connector, then
from mysql.connector.conversion import MySQLConverter
conn = mysql.connector.connect(**{..., 'converter_class': MySQLConverter})
cursor = conn.cursor()
cursor.execute('select id, data from user')
cursor.fetchall()

3. Exception occurred

Suggested fix:
1. Fix JSON converter part in MySQLConverter
2. Fix column information in connection_cext.py with new 8th tuple item.
[29 Sep 2021 7:30] MySQL Verification Team
Hello Hao Gong,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh
[29 Sep 2021 7:34] MySQL Verification Team
-- MySQL Server 5.7.35

 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.35 MySQL Community Server (GPL)

.
.
mysql> create user 'ushastry'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to 'ushastry'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> CREATE TABLE `user` (
    -> `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT primary key,
    -> `data` json NOT NULL
    -> ) ENGINE=InnoDB AUTO_INCREMENT=69799 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @data = '{
    '>     "Person": {
    '>        "Name": "Homer",
    '>        "Hobbies": ["Eating", "Sleeping"]
    '>     }
    '>  }';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO user VALUES(1,@data);
Query OK, 1 row affected (0.00 sec)

--
[ushastry@myhost:~/work/binaries/MySQLPython]$ python3.8 -m venv venv2
[ushastry@myhost:~/work/binaries/MySQLPython]$  source venv2/bin/activate
(venv2) [ushastry@myhost:~/work/binaries/MySQLPython]$ vi bug105012.py
(venv2) [ushastry@myhost:~/work/binaries/MySQLPython]$ pip install --proxy=http://xxxxxxxxxxxxxx.oracle.com:80 mysql-connector-python
Collecting mysql-connector-python
  Using cached mysql_connector_python-8.0.26-cp38-cp38-manylinux1_x86_64.whl (30.9 MB)
Collecting protobuf>=3.0.0
  Downloading protobuf-3.18.0-cp38-cp38-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (1.1 MB)
     |████████████████████████████████| 1.1 MB 523 kB/s
Installing collected packages: protobuf, mysql-connector-python
Successfully installed mysql-connector-python-8.0.26 protobuf-3.18.0
WARNING: You are using pip version 20.2.1; however, version 21.2.4 is available.
You should consider upgrading via the '/home/umshastr/work/binaries/MySQLPython/venv2/bin/python3.8 -m pip install --upgrade pip' command.
(venv2) [ushastry@myhost:~/work/binaries/MySQLPython]$
 cat bug105012.py
import mysql.connector
from mysql.connector.conversion import MySQLConverter
import sys
import platform

print("OS: {} {}".format(platform.system(), platform.release()))
print("Python:", format(sys.version))
driver  = mysql.connector
print("Driver: {} {}".format(driver.__name__, driver.__version__))

connection_details = {'host': 'xx.yy.xx.abc.com','port': 3306, 'database': 'test', 'user': 'ushastry', 'password': '', 'converter_class': MySQLConverter}

conn = mysql.connector.connect(**connection_details)
cursor = conn.cursor()
cursor.execute("SELECT id, data FROM user")
row=cursor.fetchall()

print("Total rows are:  ", len(row))
print("Printing each row")
for row in row:
    print("Id: ", row[0])
    print("Data: ", row[1])
    print("\n")

cursor.close()

(venv2) [ushastry@myhost:~/work/binaries/MySQLPython]$ python3.8 bug105012.py
OS: Linux 5.4.17-2011.1.2.el7uek.x86_64
Python: 3.8.6 (default, Nov  4 2020, 10:47:58)
[GCC 4.8.5 20150623 (Red Hat 4.8.5-39.0.3)]
Driver: mysql.connector 8.0.26
Traceback (most recent call last):
  File "bug105012.py", line 17, in <module>
    row=cursor.fetchall()
  File "/home/umshastr/work/binaries/MySQLPython/venv2/lib/python3.8/site-packages/mysql/connector/cursor_cext.py", line 510, in fetchall
    rows = self._cnx.get_rows()
  File "/home/umshastr/work/binaries/MySQLPython/venv2/lib/python3.8/site-packages/mysql/connector/connection_cext.py", line 350, in get_rows
    row[i] = self.converter.to_python(self._columns[i],
  File "/home/umshastr/work/binaries/MySQLPython/venv2/lib/python3.8/site-packages/mysql/connector/conversion.py", line 204, in to_python
    return self._cache_field_types[vtype[1]](value, vtype)
  File "/home/umshastr/work/binaries/MySQLPython/venv2/lib/python3.8/site-packages/mysql/connector/conversion.py", line 589, in _STRING_to_python
    return value.decode(self.charset)
AttributeError: 'str' object has no attribute 'decode'
(venv2) [ushastry@myhost:~/work/binaries/MySQLPython]$
[3 Nov 2021 21:14] Philip Olson
Posted by developer:
 
Fixed as of the upcoming MySQL Connector/Python 8.0.28 release, and here's the proposed changelog entry from the documentation team:

Fixed the JSON conversion class in CMySQLConnection; and fixed the related
exception raised when using a conversion class in a C extension connection
object.

Thank you for the bug report.