MySQL Bugs: #81333: Error message is generated by using explain command through mysql shell

Bug #81333	Error message is generated by using explain command through mysql shell
Submitted:	6 May 2016 3:19	Modified:	24 Mar 2017 13:12
Reporter:	Shinya Sugiyama	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Document Store: MySQL Shell	Severity:	S3 (Non-critical)
Version:	1.0.3 Development Preview	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Unicode

Description:
Following  protobuf error is generated when we executed explain command to world.city sample database.

[libprotobuf ERROR google/protobuf/wire_format.cc:1091] 
String field 'msg' contains invalid UTF-8 data when parsing a protocol buffer. 
Use the 'bytes' type if you intend to send raw bytes. 

https://github.com/mysql/mysql-server/blob/5.7/extra/protobuf/protobuf-2.6.0/src/google/pr...

How to repeat:
root@localhost [world]> show create table City\G
*************************** 1. row ***************************
       Table: City
Create Table: CREATE TABLE `City` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

root@localhost [world]> 

■ mysqlsh -u demo_user -ppassword --sql --schema=world

mysql-sql> select * from City where id = 3;
+----+-------+-------------+----------+------------+
| ID | Name  | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
|  3 | Herat | AFG         | Herat    |     186800 |
+----+-------+-------------+----------+------------+
1 row in set (0.00 sec)

mysql-sql> select * from City where id = 100;
+-----+--------+-------------+------------+------------+
| ID  | Name   | CountryCode | District   | Population |
+-----+--------+-------------+------------+------------+
| 100 | Paran| ARG         | Entre Rios |     207041 |
+-----+--------+-------------+------------+------------+
1 row in set (0.00 sec)

mysql-sql> 

mysql-sql> explain select * from City where id = 3;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | City  | null       | const | PRIMARY       | PRIMARY | 4       | const |    1 |      100 | null  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
Note (Code 1003): /* select#1 */ select '3' AS `ID`,'Herat' AS `Name`,'AFG' AS `CountryCode`,'Herat' AS `District`,'186800' AS `Population` from `world`.`City` where 1

mysql-sql> explain select * from City where id = 100;
[libprotobuf ERROR google/protobuf/wire_format.cc:1091] String field 'msg' contains invalid UTF-8 data when parsing a protocol buffer. Use the 'bytes' type if you intend to send raw bytes. 
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | City  | null       | const | PRIMARY       | PRIMARY | 4       | const |    1 |      100 | null  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
Note (Code 1003): /* select#1 */ select '100' AS `ID`,'Paran AS `Name`,'ARG' AS `CountryCode`,'Entre Rios' AS `District`,'207041' AS `Population` from `world`.`City` where 1

mysql-sql> 

■ mysqlsh -u demo_user -ppassword --classic --schema=world

mysql-sql> select * from City where id = 3;
+----+-------+-------------+----------+------------+
| ID | Name  | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
|  3 | Herat | AFG         | Herat    |     186800 |
+----+-------+-------------+----------+------------+
1 row in set (0.00 sec)

mysql-sql> select * from City where id = 100;
+-----+--------+-------------+------------+------------+
| ID  | Name   | CountryCode | District   | Population |
+-----+--------+-------------+------------+------------+
| 100 | Paran| ARG         | Entre Rios |     207041 |
+-----+--------+-------------+------------+------------+
1 row in set (0.00 sec)

mysql-sql> 

mysql-sql> explain select * from City where id = 3;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | City  | null       | const | PRIMARY       | PRIMARY | 4       | const |    1 |      100 | null  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set (0.00 sec)

mysql-sql> explain select * from City where id = 100;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | City  | null       | const | PRIMARY       | PRIMARY | 4       | const |    1 |      100 | null  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set (0.00 sec)

mysql-sql> 

Suggested fix:
Please handle Unicode character inside or outside of MySQL shell.

I got same comment from user, so I change Severity from S4 to S3.
We understand it is still Development Preview.

Hello Sugiyama-San,

Thank you for the report.
Verified as described.

Thanks,
Umesh

It seems fixed in MySQL Shell 1.0.8 RC.

-bash-4.2$ /usr/local/mysqlshell/bin/mysqlsh --sql root@localhost:63301
Creating a Session to 'root@localhost:63301'
Enter password: 
Classic Session successfully established. No default schema selected.
Welcome to MySQL Shell 1.0.8-rc

Copyright (c) 2016, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help', '\h' or '\?' for help, type '\quit' or '\q' to exit.

Currently in SQL mode. Use \js or \py to switch the shell to a scripting language.
mysql-sql> use world;
Query OK, 0 rows affected (0.00 sec)
mysql-sql> select * from city where id = 3;
+----+-------+-------------+----------+------------+
| ID | Name  | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
|  3 | Herat | AFG         | Herat    |     186800 |
+----+-------+-------------+----------+------------+
1 row in set (0.00 sec)
mysql-sql> select * from city where id = 100;
+-----+--------+-------------+------------+------------+
| ID  | Name   | CountryCode | District   | Population |
+-----+--------+-------------+------------+------------+
| 100 | Paran| ARG         | Entre Rios |     207041 |
+-----+--------+-------------+------------+------------+
1 row in set (0.00 sec)
mysql-sql> explain select * from city where id = 3;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | city  | null       | const | PRIMARY       | PRIMARY | 4       | const |    1 |      100 | null  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set (0.01 sec)
mysql-sql> explain select * from city where id = 100;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | city  | null       | const | PRIMARY       | PRIMARY | 4       | const |    1 |      100 | null  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set (0.01 sec)
mysql-sql>

Posted by developer:
 
Here's my analysis:

The issue occurs because the world DB uses latin1, while the shell (and the xplugin) use UTF8 internally for most meta-data related things, including warnings sent from the server in the form of Notices.

The sample query produces a warning, which contains a sample of the table data in the error text. Because that data is not UTF8, protobuf does not like it when it tries to decode the warning text using UTF8 (because the msg field in the warning Notice is a string, assumed to be UTF8).

I pasted the same test case using mysqlxtest at the bottom.

A possible fix would be to make the plugin sanitize text messages sent through notices before sending it to the client, ensuring that invalid UTF8 data is represented with something like ?.

$ ./mysqlxtest -uroot -h0 
CONNECT
-->sql
use world;
explain select * from City where id = 100;
show warnings
-->endsql
RUN use world

0 rows affected
RUN explain select * from City where id = 100
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	City	null	const	PRIMARY	PRIMARY	4	const	1	100.00	null
[libprotobuf ERROR ../extra/protobuf/protobuf-2.6.0/src/google/protobuf/wire_format.cc:1091] String field 'msg' contains invalid UTF-8 data when parsing a protocol buffer. Use the 'bytes' type if you intend to send raw bytes. 
0 rows affected
RUN show warnings

Level	Code	Message
Note	1003	/* select#1 */ select '100' AS `ID`,'Paran?' AS `Name`,'ARG' AS `CountryCode`,'Entre Rios' AS `District`,'207041' AS `Population` from `world`.`city` where 1
0 rows affected

(Note the Paran?, which is supposed to be Paraná in latin1)