MySQL Bugs: #73967: Incorrect string value for sql

Bug #73967	Incorrect string value for sql_text
Submitted:	18 Sep 2014 12:35	Modified:	13 Jan 2015 11:51
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Performance Schema	Severity:	S3 (Non-critical)
Version:	5.6.20	OS:	Any
Assigned to:	Marc ALFF	CPU Architecture:	Any
Tags:	character set, charset, encoding, performance_schema, sql_text

Description:
A select on sql_text of events_statements_history_long sometimes returns a warning.

Incorrect string value: '\x8B\x08\0\0...' for column 'SQL_TEXT' at row 904

How to repeat:
mysql> select sql_text from events_statements_history_long where errors > 0;
Empty set (0.00 sec)

mysql> select sql_text from events_statements_history_long where errors > 0;
Empty set (0.01 sec)

mysql> select sql_text from events_statements_history_long where errors > 0;
Empty set, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------------------------------+
| Level   | Code | Message                                                                    |
+---------+------+----------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\x8B\x08\0\0...' for column 'SQL_TEXT' at row 904 |
+---------+------+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

Thank you for the bug report. I couldn't repeat with 5.6.20 and latest source build on Windows. Any clue how to make that repeatable?. Thanks.

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --debug-info --prompt="mysql 5.6 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.22 Source distribution

Copyright (c) 2000, 2014, 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;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > USE performance_schema
Database changed
mysql 5.6 > select sql_text from events_statements_history_long where errors > 0;
Empty set (0.06 sec)

mysql 5.6 > select sql_text from events_statements_history_long where errors > 0;
Empty set (0.00 sec)

mysql 5.6 > select sql_text from events_statements_history_long where errors > 0;
Empty set (0.00 sec)

mysql 5.6 > select sql_text from events_statements_history_long where errors > 0;
Empty set (0.00 sec)

mysql 5.6 > select sql_text from events_statements_history_long where errors > 0;
Empty set (0.00 sec)

mysql 5.6 > select sql_text from events_statements_history_long where errors > 0;
Empty set (0.00 sec)

mysql 5.6 > select sql_text from events_statements_history_long where errors > 0;
Empty set (0.00 sec)

mysql 5.6 > select sql_text from events_statements_history_long where errors > 0;
Empty set (0.00 sec)

mysql 5.6 > select sql_text from events_statements_history_long where errors > 0;
Empty set (0.00 sec)

mysql 5.6 > select sql_text from events_statements_history_long where errors > 0;
Empty set (0.00 sec)

mysql 5.6 > select sql_text from events_statements_history_long where errors > 0;
Empty set (0.00 sec)

make sure you have run some random queries with non-utf8 chars in them.  with blobs for example!

I tried to reproduce it on a dev instance, but no luck yet.

I will try to get more info from the server where it occurred.

It occurred on a Linux machine running 5.6.20.

Please set it to Need feedback..

mysql [localhost] {msandbox} (performance_schema) > truncate table events_statements_history_long;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (performance_schema) > select '

mysql [localhost] {msandbox} (performance_schema) > truncate table events_statements_history_long;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (performance_schema) > select 'X' as 'Y' from dual;                            
+------+
| Y    |
+------+
|

mysql [localhost] {msandbox} (performance_schema) > truncate table events_statements_history_long;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (performance_schema) > select 'X' as 'Y' from dual;                            
+------+
| Y    |
+------+
| X     |
+------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (performance_schema) > select sql_text from events_statements_history_long;
+-----------------------------------------------+
| sql_text                                      |
+-----------------------------------------------+
| truncate table events_statements_history_long |
| select '                                      |
+-----------------------------------------------+
2 rows in set, 1 warning (0.01 sec)

mysql [localhost] {msandbox} (performance_schema) > show warnings;
+---------+------+--------------------------------------------------------------------------------+
| Level   | Code | Message                                                                        |
+---------+------+--------------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xF0\x90\x8D\x83' ...' for column 'SQL_TEXT' at row 2 |
+---------+------+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (performance_schema) > select hex('

mysql [localhost] {msandbox} (performance_schema) > truncate table events_statements_history_long;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (performance_schema) > select 'X' as 'Y' from dual;                            
+------+
| Y    |
+------+
| X     |
+------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (performance_schema) > select sql_text from events_statements_history_long;
+-----------------------------------------------+
| sql_text                                      |
+-----------------------------------------------+
| truncate table events_statements_history_long |
| select '                                      |
+-----------------------------------------------+
2 rows in set, 1 warning (0.01 sec)

mysql [localhost] {msandbox} (performance_schema) > show warnings;
+---------+------+--------------------------------------------------------------------------------+
| Level   | Code | Message                                                                        |
+---------+------+--------------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xF0\x90\x8D\x83' ...' for column 'SQL_TEXT' at row 2 |
+---------+------+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (performance_schema) > select hex('X');
+----------+
| hex('?') |
+----------+
| F0908D83 |
+----------+
1 row in set (0.00 sec)

I did do a 'SET NAMES utf8mb4' for this session. I'm using gnome-terminal which is mostly unicode capable.

(special chars replaces with X and Y for bugs.mysql.com)

Thank you for the feedback.
Imho the reason could be that this table is defined as utf8 and with random chars this can be triggered: 

mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)

mysql>  select sql_text from performance_schema.events_statements_history_long;
+------------------------------------------------------------------------+
| sql_text                                                               |
+------------------------------------------------------------------------+
| truncate performance_schema.events_statements_history_long             |
| select sql_text from performance_schema.events_statements_history_long |
| NULL                                                                   |
| select @@version_comment limit 1                                       |
|                                                                       |
| NULL                                                                   |
| select @@version_comment limit 1                                       |
| select sql_text from performance_schema.events_statements_history_long |
| show warnings                                                          |
| set names utf8mb                                                       |
| set names utf8mb4                                                      |
+------------------------------------------------------------------------+
11 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------+
| Level   | Code | Message                                                                           |
+---------+------+-----------------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xDD\xFB*\xB5\xC2\x19...' for column 'SQL_TEXT' at row 5 |
+---------+------+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Also, see related https://bugs.mysql.com/bug.php?id=55397

test results

Attachment: 73967.txt (text/plain), 3.90 KiB.

An example of this bug:
http://nbviewer.ipython.org/gist/dveeden/dfe2f11b64871cb4d234

This happens when inserting binary data into a blob. I think the sql_text field should be a longblob instead of a longtext. Or it should truncate/remove the binary data, but then Bug #71001 comes into play.