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
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.1, azalea
• OS: Any
• 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.