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: | |
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 |
[18 Sep 2014 12:35]
Daniël van Eeden
[18 Sep 2014 15:44]
MySQL Verification Team
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)
[18 Sep 2014 15:58]
MySQL Verification Team
make sure you have run some random queries with non-utf8 chars in them. with blobs for example!
[18 Sep 2014 19:50]
Daniël van Eeden
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.
[18 Sep 2014 19:51]
Daniël van Eeden
Please set it to Need feedback..
[18 Sep 2014 20:01]
Daniël van Eeden
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 '
[18 Sep 2014 20:02]
Daniël van Eeden
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 | +------+ |
[18 Sep 2014 20:03]
Daniël van Eeden
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('
[18 Sep 2014 20:04]
Daniël van Eeden
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)
[19 Sep 2014 13:01]
MySQL Verification Team
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
[19 Sep 2014 13:02]
MySQL Verification Team
test results
Attachment: 73967.txt (text/plain), 3.90 KiB.
[13 Jan 2015 11:51]
Daniël van Eeden
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.