Bug #57306 SHOW PROCESSLIST does not display string literals well.
Submitted: 7 Oct 2010 10:34 Modified: 11 Dec 2010 17:27
Reporter: Alexander Barkov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Charsets Severity:S3 (Non-critical)
Version:5.1, 5.5 OS:Any
Assigned to: Alexander Barkov CPU Architecture:Any

[7 Oct 2010 10:34] Alexander Barkov
Description:
SHOW PROCESSLIST displays extended string literals (with non-ASCII letters)
either as garage or as question marks.

How to repeat:
It can be demonstrated this way:

- Run two consoles, using 8-latin1 character set.
  For example, in gnome-terminal, in both consoles do:
  "Terminal->Character Encoding->ISO 8859-1".

- In the first console run:

mysql --default-character-set=latin1 test

mysql> select sleep(10),'ÁÂÃÄÅ';

- In the second console run:

mysqladmin --default-character-set=latin1 processlist

It will print the string literal as question marks:
+----+------+-----------+------+---------+------+-----------+--------------------------+
| Id | User | Host      | db   | Command | Time | State     | Info                |
+----+------+-----------+------+---------+------+-----------+--------------------------+
| 83 | root | localhost | doc  | Sleep   | 331  |           |                |
| 98 | root | localhost | test | Query   | 1    | executing | select sleep(10),'?????' |
| 99 | root | localhost |      | Query   | 0    |           | show processlist         |
+----+------+-----------+------+---------+------+-----------+--------------------------+

The string is displayed as question marks.

Suggested fix:
The error happens because of wrong call in sql_show.cc,
in functions mysqld_list_processes():

    protocol->store(thd_info->query, system_charset_info);

This call should be fixed as:

   protocol->store(thd_info->query, thd->variables.character_set_client);

Explanation:
query string is not converted to system_character_info after receiving from the client and parsing.
It remains in @@character_set_client.
[7 Oct 2010 14:28] MySQL Verification Team
Thank you for the bug report. Verified as described.
[12 Nov 2010 15:01] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/123739
[16 Nov 2010 14:55] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/124053
[16 Nov 2010 17:51] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/124065
[16 Nov 2010 19:23] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/124076
[18 Nov 2010 10:48] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/124239
[18 Nov 2010 14:13] Guilhem Bichot
no second reviewer needed
[18 Nov 2010 15:47] Alexander Barkov
Pushed into mysql-5.5-bugteam [5.5.8]
[18 Nov 2010 19:20] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/124287
[19 Nov 2010 5:50] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/124354
[5 Dec 2010 12:42] Bugs System
Pushed into mysql-trunk 5.6.1 (revid:alexander.nozdrin@oracle.com-20101205122447-6x94l4fmslpbttxj) (version source revid:alexander.nozdrin@oracle.com-20101205122447-6x94l4fmslpbttxj) (merge vers: 5.6.1) (pib:23)
[11 Dec 2010 17:27] Paul DuBois
Noted in 5.5.8 changelog.

SHOW PROCESSLIST displayed non-ASCII characters improperly.
[16 Dec 2010 22:32] Bugs System
Pushed into mysql-5.5 5.5.9 (revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (version source revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (merge vers: 5.5.9) (pib:24)
[15 Nov 2013 19:38] Jeremy Cole
I'm not so sure about this fix actually. This has "fixed" the problem by converting the query to the character set of the connected client, but this is a potentially lossy conversion, and in any case one which may make non-trivial changes to the query string. I would think that the proper solution to this is to return the query strings in these commands as my_charset_bin instead, avoiding all conversion but also declaring the field to be binary data so that the client does not attempt any conversion.

Additionally, this bug did not fix the behavior of the information_schema.processlist table which still returns the query strings as system_charset_info.