| 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: | |
| 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 | ||
[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.
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.