Description:
When MySQL explains a primary key query for a table that has a varbinary field, show warnings is truncated by the binary
Detail:
When querying the primary key, the message content in show warnings will be changed to the format of "select column_value as column_name from db.table where true", but when concatenating the varbinary field, it will be concatenated as a string, resulting in the binary content formatting failure and the display being truncated
What problems can it cause:
When I use Python 3.9.16 + PyMySQL==1.1.0, my code:
///
cursor=pymysql.connect(xxx).cursor()
sql = "desc SELECT * FROM `varbinary_test` WHERE `RefundTokenID`=123"
cursor.execute(sql)
warnings = cursor.connection.show_warnings()
///
When I want to get the warning result, the code gives the following error:
File "/xxx/xxx/xxx/xxx.py", line 120, in xxx_function
warnings = cursor.connection.show_warnings()
File "/xxx/xxx/xxx/venv/lib64/python3.9/site-packages/pymysql/connections.py", line 494, in show_warnings
result.read()
File "/xxx/xxx/xxx/venv/lib64/python3.9/site-packages/pymysql/connections.py", line 1207, in read
self._read_result_packet(first_packet)
File "/xxx/xxx/xxx/venv/lib64/python3.9/site-packages/pymysql/connections.py", line 1284, in _read_result_packet
self._read_rowdata_packet()
File "/xxx/xxx/xxx/venv/lib64/python3.9/site-packages/pymysql/connections.py", line 1335, in _read_rowdata_packet
rows.append(self._read_row_from_packet(packet))
File "/xxx/xxx/xxx/venv/lib64/python3.9/site-packages/pymysql/connections.py", line 1351, in _read_row_from_packet
data = data.decode(encoding)
UnicodeDecodeError: 'utf-8' codec can't decode byte 0x8b in position 112: invalid start byte
Which means that the MESSAGE content returned by show warnings cannot be parsed using the utf-8 character set encoding method, And I suspect that it's not just Python; any method of obtaining results from show warnings in all mainstream languages that use UTF-8 should encounter this issue.
How to repeat:
CREATE TABLE `varbinary_test` (
`RefundTokenID` bigint NOT NULL DEFAULT '0' ,
`HashKey` varchar(40) COLLATE utf8mb4_general_ci DEFAULT NULL ,
`FrequentlyChangeValue` varbinary(1000) DEFAULT NULL ,
`AdditionalValue` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL ,
`DataChange_Lasttime` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) ,
`Status` varchar(30) COLLATE utf8mb4_general_ci DEFAULT NULL ,
`userdata_location` varchar(10) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' ,
PRIMARY KEY (`RefundTokenID`),
KEY `ix_DataChange_LastTime` (`DataChange_Lasttime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
INSERT INTO `varbinary_test` (`RefundTokenID`,`HashKey`,`FrequentlyChangeValue`,`AdditionalValue`,`DataChange_Lasttime`,`Status`,`userdata_location`) VALUES (123, 'abcdefghabcdefghabcdefghabcdefghabcdefgh', 0x1F8B08000000000000FFED954D6F82401086FFCB5C0B66F60358B8D9DA83497BA327E90165B5A40A06E9C118FE7B97051411BD699A064216B233FB3EB0FB66E6002178B34339C278F20106CCC1731CC2393360717A8DC04303643526519AEDE4FB4F1ECED7F22DDEE58D0411A8057064E9D5CC46BDD4B4296196E30A8EE5A58588C399C58F334BF501EA8598FAF6113D7D3FA150DFB452418AD432919B487D744EC1C2D0644AEC9A6C9F913B948ACBB92B448B7B45FA9C4B582FD7A235D7E972DB949A6B098BF6703BD21D2EEFE50A5173C505B745A9B96A13EC3E2EBFC5B57D64175C4679CD752FB82D8A2C778671A454D9A78F7C2E5E915D7599849A4C1DBF68913FF55AD4496AFC6ADC76EDCC1FE0B6EBA77E57B7316CFE58580FB5DBAD63BFABDDECAAA090479A2D1ECAD950CEEEE8B0D263F3A1EB0E36FDDB361DBAEED07587AE3B94B37F52CE4A8F45A7D6AAC4012ABD7A2582AE72A8AD8823A2F664534553F53804B089D7325CC9974C46713E4D96E96B92C7F93E00AF1B545326298C0032B9CBB37891C769B2ABF2166116F9FBAD7C5E878BEFB28BABE959009406A0F25D573F0806F0699C929B3C3DA704B7E35526E54626F974728C150514BFD47826A3CF0F0000, '{"xxx":"T","yyy":0}', '2025-04-11 13:45:16.465', 'xxx', 'XX_XXXXX');
desc SELECT * FROM `varbinary_test` WHERE `RefundTokenID`=123;
mysql> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select '123' AS `RefundTokenID`,'abcdefghabcdefghabcdefghabcdefghabcdefgh' AS `HashKey`,' |
+-------+------+-------------------------------------------------------------------------------------------------------------+
Suggested fix:
in sql/item.cc (about 7900 rows):
field->val_str(&tmp);
// delete this row
if (field->is_null())
// add following rows
if ( thd->lex->is_explain() && field->binary() && (field->type() == MYSQL_TYPE_STRING || field->type() == MYSQL_TYPE_VARCHAR ||
field->type() == MYSQL_TYPE_BLOB || field->type() == MYSQL_TYPE_VAR_STRING)) {
char hex_buff[2*MAX_FIELD_WIDTH + 1];
bin_to_hex_str(hex_buff, 2*MAX_FIELD_WIDTH + 1, tmp.c_ptr(), tmp.length());
str->append("\'0x");
str->append(hex_buff);
str->append('\'');
} else if (field->is_null())
Description: When MySQL explains a primary key query for a table that has a varbinary field, show warnings is truncated by the binary Detail: When querying the primary key, the message content in show warnings will be changed to the format of "select column_value as column_name from db.table where true", but when concatenating the varbinary field, it will be concatenated as a string, resulting in the binary content formatting failure and the display being truncated What problems can it cause: When I use Python 3.9.16 + PyMySQL==1.1.0, my code: /// cursor=pymysql.connect(xxx).cursor() sql = "desc SELECT * FROM `varbinary_test` WHERE `RefundTokenID`=123" cursor.execute(sql) warnings = cursor.connection.show_warnings() /// When I want to get the warning result, the code gives the following error: File "/xxx/xxx/xxx/xxx.py", line 120, in xxx_function warnings = cursor.connection.show_warnings() File "/xxx/xxx/xxx/venv/lib64/python3.9/site-packages/pymysql/connections.py", line 494, in show_warnings result.read() File "/xxx/xxx/xxx/venv/lib64/python3.9/site-packages/pymysql/connections.py", line 1207, in read self._read_result_packet(first_packet) File "/xxx/xxx/xxx/venv/lib64/python3.9/site-packages/pymysql/connections.py", line 1284, in _read_result_packet self._read_rowdata_packet() File "/xxx/xxx/xxx/venv/lib64/python3.9/site-packages/pymysql/connections.py", line 1335, in _read_rowdata_packet rows.append(self._read_row_from_packet(packet)) File "/xxx/xxx/xxx/venv/lib64/python3.9/site-packages/pymysql/connections.py", line 1351, in _read_row_from_packet data = data.decode(encoding) UnicodeDecodeError: 'utf-8' codec can't decode byte 0x8b in position 112: invalid start byte Which means that the MESSAGE content returned by show warnings cannot be parsed using the utf-8 character set encoding method, And I suspect that it's not just Python; any method of obtaining results from show warnings in all mainstream languages that use UTF-8 should encounter this issue. How to repeat: CREATE TABLE `varbinary_test` ( `RefundTokenID` bigint NOT NULL DEFAULT '0' , `HashKey` varchar(40) COLLATE utf8mb4_general_ci DEFAULT NULL , `FrequentlyChangeValue` varbinary(1000) DEFAULT NULL , `AdditionalValue` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL , `DataChange_Lasttime` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) , `Status` varchar(30) COLLATE utf8mb4_general_ci DEFAULT NULL , `userdata_location` varchar(10) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' , PRIMARY KEY (`RefundTokenID`), KEY `ix_DataChange_LastTime` (`DataChange_Lasttime`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci INSERT INTO `varbinary_test` (`RefundTokenID`,`HashKey`,`FrequentlyChangeValue`,`AdditionalValue`,`DataChange_Lasttime`,`Status`,`userdata_location`) VALUES (123, 'abcdefghabcdefghabcdefghabcdefghabcdefgh', 0x1F8B08000000000000FFED954D6F82401086FFCB5C0B66F60358B8D9DA83497BA327E90165B5A40A06E9C118FE7B97051411BD699A064216B233FB3EB0FB66E6002178B34339C278F20106CCC1731CC2393360717A8DC04303643526519AEDE4FB4F1ECED7F22DDEE58D0411A8057064E9D5CC46BDD4B4296196E30A8EE5A58588C399C58F334BF501EA8598FAF6113D7D3FA150DFB452418AD432919B487D744EC1C2D0644AEC9A6C9F913B948ACBB92B448B7B45FA9C4B582FD7A235D7E972DB949A6B098BF6703BD21D2EEFE50A5173C505B745A9B96A13EC3E2EBFC5B57D64175C4679CD752FB82D8A2C778671A454D9A78F7C2E5E915D7599849A4C1DBF68913FF55AD4496AFC6ADC76EDCC1FE0B6EBA77E57B7316CFE58580FB5DBAD63BFABDDECAAA090479A2D1ECAD950CEEEE8B0D263F3A1EB0E36FDDB361DBAEED07587AE3B94B37F52CE4A8F45A7D6AAC4012ABD7A2582AE72A8AD8823A2F664534553F53804B089D7325CC9974C46713E4D96E96B92C7F93E00AF1B545326298C0032B9CBB37891C769B2ABF2166116F9FBAD7C5E878BEFB28BABE959009406A0F25D573F0806F0699C929B3C3DA704B7E35526E54626F974728C150514BFD47826A3CF0F0000, '{"xxx":"T","yyy":0}', '2025-04-11 13:45:16.465', 'xxx', 'XX_XXXXX'); desc SELECT * FROM `varbinary_test` WHERE `RefundTokenID`=123; mysql> show warnings; +-------+------+-------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select '123' AS `RefundTokenID`,'abcdefghabcdefghabcdefghabcdefghabcdefgh' AS `HashKey`,' | +-------+------+-------------------------------------------------------------------------------------------------------------+ Suggested fix: in sql/item.cc (about 7900 rows): field->val_str(&tmp); // delete this row if (field->is_null()) // add following rows if ( thd->lex->is_explain() && field->binary() && (field->type() == MYSQL_TYPE_STRING || field->type() == MYSQL_TYPE_VARCHAR || field->type() == MYSQL_TYPE_BLOB || field->type() == MYSQL_TYPE_VAR_STRING)) { char hex_buff[2*MAX_FIELD_WIDTH + 1]; bin_to_hex_str(hex_buff, 2*MAX_FIELD_WIDTH + 1, tmp.c_ptr(), tmp.length()); str->append("\'0x"); str->append(hex_buff); str->append('\''); } else if (field->is_null())