Bug #46203 WHERE clause on Information_schema.processlist makes an unpredictable filter
Submitted: 15 Jul 2009 13:01 Modified: 16 Jul 2009 18:41
Reporter: Kristofer Pettersson Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1, azalea OS:Any
Assigned to: CPU Architecture:Any

[15 Jul 2009 13:01] Kristofer Pettersson
Description:
When I try to filter out a result from the process list I end up with more rows than I expect. How can Info="show processlist" match info LIKE "%sleep%" ?

How to repeat:
mysql> show processlist;
+----+------+-----------------+------+---------+------+------------+--------------------+
| Id | User | Host            | db   | Command | Time | State      | Info               |
+----+------+-----------------+------+---------+------+------------+--------------------+
|  2 | root | localhost       | test | Sleep   |   31 |            | NULL               | 
|  3 | root | localhost       | test | Query   |   31 | User sleep | SELECT SLEEP(1000) | 
|  4 | root | localhost:52760 | NULL | Query   |    0 | NULL       | show processlist   | 
+----+------+-----------------+------+---------+------+------------+--------------------+
3 rows in set (0.00 sec)

mysql> select id from information_schema.processlist where info like '%sleep%';
+----+
| id |
+----+
|  4 | 
|  3 | 
+----+
2 rows in set (0.00 sec)

mysql> select id from information_schema.processlist where info like '%sleep%' limit 1;
+----+
| id |
+----+
|  4 | 
+----+
1 row in set (0.00 sec)

mysql> select id from information_schema.processlist where info like 'select sleep%' limit 1;
+----+
| id |
+----+
|  3 | 
+----+
1 row in set (0.00 sec)

mysql> select id from information_schema.processlist where info like '%proceslist%' limit 1;
+----+
| id |
+----+
|  4 | 
+----+
1 row in set (0.00 sec)
[15 Jul 2009 13:18] Peter Gulutzan
It does look like a bug that a search for '%proceslist%' [sic]
finds something that has 'processlist' in it.

But for the rest:
Isn't this expectable behaviour if we don't lock the PROCESSLIST table,
or make a frozen copy?
[15 Jul 2009 13:22] Peter Gulutzan
Bah, I must amend that last remark. It finds 'proceslist' because it's in
"select id from information_schema.processlist where info like '%proceslist%' limit 1;". So isn't that expectable too?
[16 Jul 2009 6:55] Sveta Smirnova
Thank you for the report.

Verified as described.

Really word "processlist" is not related: it can be anything:

select id from information_schema.processlist where info like '%anything%';
id
2
[16 Jul 2009 16:06] Peter Gulutzan
Perhaps this would be clearer if one puts INFO in the select list.

For example:
mysql> select id,info from information_schema.processlist
    -> where info like '%anything%'\G
*************************** 1. row ***************************
  id: 1
info: select id,info from information_schema.processlist
where info like '%anything%'
1 row in set (0.01 sec)

Who is ID=1? Me! I'm looking at my own query, which contains
'anything' because I'm searching for 'anything'. Of course I
will always find a string that's in the text of my own query.
[16 Jul 2009 18:41] Kristofer Pettersson
Peter: My mistake. This is of course expected behaviour and not a bug.