Bug #104991 MySQL Connector/Python conversion issue after 8.0.24

• Submitted: 21 Sep 2021 5:07
• Modified: 22 Sep 2021 4:47
• Reporter: Hao Gong
• Status: Closed
• Category: Connector / Python
• Severity: S1 (Critical)
• Version: 8.0.24 and Later
• OS: MacOS (11.5.2)
• CPU Architecture: Any (Python 3.9.5)
• Tags: python

[21 Sep 2021 5:07] Hao Gong

Description:
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
cursor.execute('select id, data from user')
cursor.fetchall()

3. Exception occurred

[21 Sep 2021 9:51] MySQL Verification Team

Hello Hao Gong,

Thank you for the report and feedback.
I quickly tried to reproduce at my end with dummy data but not seeing any issues. Could you please provide exact test case(python script, sample data etc)? Thank you.

--
--
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; 

INSERT INTO user VALUES(1,'
{
	"id": "0001",
	"type": "donut",
	"name": "Cake",
	"ppu": 0.55,
	"batters":
		{
			"batter":
				[
					{ "id": "1001", "type": "Regular" },
					{ "id": "1002", "type": "Chocolate" },
					{ "id": "1003", "type": "Blueberry" },
					{ "id": "1004", "type": "Devil Food" }
				]
		},
	"topping":
		[
			{ "id": "5001", "type": "None" },
			{ "id": "5002", "type": "Glazed" },
			{ "id": "5005", "type": "Sugar" },
			{ "id": "5007", "type": "Powdered Sugar" },
			{ "id": "5006", "type": "Chocolate with Sprinkles" },
			{ "id": "5003", "type": "Chocolate" },
			{ "id": "5004", "type": "Maple" }
		]
}');
--
import mysql.connector
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': 'localhost','port': 3306, 'database': 'test', 'user': 'root', 'password': '' } 

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("Name: ", row[1])
    print("\n")

cursor.close()

OS: Windows 10
Python: 3.9.7 (tags/v3.9.7:1016ef3, Aug 30 2021, 20:19:38) [MSC v.1929 64 bit (AMD64)]
Driver: mysql.connector 8.0.26
Total rows are:   1
Printing each row
Id:  1
Name:  {"id": "0001", "ppu": 0.55, "name": "Cake", "type": "donut", "batters": {"batter": [{"id": "1001", "type": "Regular"}, {"id": "1002", "type": "Chocolate"}, {"id": "1003", "type": "Blueberry"}, {"id": "1004", "type": "Devil Food"}]}, "topping": [{"id": "5001", "type": "None"}, {"id": "5002", "type": "Glazed"}, {"id": "5005", "type": "Sugar"}, {"id": "5007", "type": "Powdered Sugar"}, {"id": "5006", "type": "Chocolate with Sprinkles"}, {"id": "5003", "type": "Chocolate"}, {"id": "5004", "type": "Maple"}]}

Press any key to continue . . .

regards,
Umesh

[22 Sep 2021 4:47] Hao Gong

I think the issue is because incorrect config in my development environment. So closed for now.

[22 Sep 2021 5:11] MySQL Verification Team

Thank you for confirming.

regards,
Umesh