MySQL Bugs: #13032: show processlist doesn't report properly the query of prepared statement

Bug #13032	show processlist doesn't report properly the query of prepared statement
Submitted:	7 Sep 2005 7:09	Modified:	3 Oct 2008 20:59
Reporter:	Gleb Paharenko	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S4 (Feature request)
Version:	5.0.12	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
'show processlist' doesn't show the values of parameters in the prepared statements, and therefore produces incomplete information about the query

How to repeat:
mysql> show create table t\G;
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int(11) default NULL,
  `b` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8

mysql> select * from t;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
+------+------+

[gleb@blend mysql-debug-5.0.12-beta-linux-i686-glibc23]$ cat /home/gleb/mysqls/sql/t1.sql
use test;
PREPARE stmt1 FROM 'SELECT a FROM t WHERE b = ?';
SET @a = 1;
EXECUTE stmt1 USING @a;
DEALLOCATE PREPARE stmt1;

Now I have to open connections to MySQL Server:
window1:

mysql> lock tables t write;
Query OK, 0 rows affected (0.00 sec)

window2:

source /home/gleb/mysqls/sql/t1.sql

window1:

mysql> show full processlist;
+----+------+-----------+------+---------+------+--------+-----------------------------+
| Id | User | Host      | db   | Command | Time | State  | Info                        |
+----+------+-----------+------+---------+------+--------+-----------------------------+
| 20 | root | localhost | test | Prepare |    8 | Locked | SELECT a FROM t WHERE b = ? |
| 21 | root | localhost | test | Query   |    0 | NULL   | show full processlist       |
+----+------+-----------+------+---------+------+--------+-----------------------------+

+-----------------------+
| version()             |
+-----------------------+
| 5.0.12-beta-debug-log |
+-----------------------+

Suggested fix:
Make the 'show processlist' command to report the parameters with which 'EXECUTE PREPARE' was invoked

Yes, it works just as you described. 

But in the manual (http://dev.mysql.com/doc/mysql/en/show-processlist.html) I found no words stating that parameters of the prepared statement should be shown. Server is really preparing or executing the command shown by SHOW PROCESSLIST, with ? instead of parameters.

So, I changed severity to (new) feature request.

This is an optimization. There is no query expansion in case you don't run either the binary log, the slow log or the general log.
If you start the server with the slow log on you get:
kostja@bodhi:~/work/mysql-6.0-runtime/sql$ ./mysqld --slow-query-log
081004 0:57:09 InnoDB: Started; log sequence number 0 1331099
081004 0:57:09 [Note] Event Scheduler: Loaded 0 events
081004 0:57:09 [Note] ./mysqld: ready for connections.
Version: '6.0.8-alpha-valgrind-max-debug-log' socket: '/opt/local/var/mysql/mysql.sock' port: 3307 Source distribution

------------------------------------------------------------------
kostja@bodhi:~$ mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.8-alpha-valgrind-max-debug-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> prepare stmt from "select sleep(100) from t1 where a=?";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> set @a=1;
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @a; -- hangs
------------------------------------------------------------------------
kostja@bodhi:~$ mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 6.0.8-alpha-valgrind-max-debug-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show processlist\G
*************************** 1. row ***************************
Id: 1
User: kostja
Host: localhost
db: test
Command: Query
Time: 10
State: User sleep
Info: select sleep(100) from t1 where a=1
*************************** 2. row ***************************
Id: 2
User: kostja
Host: localhost
db: test
Command: Query
Time: 0
State: NULL
Info: show processlist
2 rows in set (0.00 sec)

This is available starting from 4.1.

Based on an excellent workaround, setting to Won't fix.