Bug #31840 Slave displays queries that should be filtered out in SHOW PROCESSLIST
Submitted: 25 Oct 2007 10:00 Modified: 14 Dec 2007 17:42
Reporter: Victoria Reznichenko Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:4.1,5.0 OS:Any
Assigned to: CPU Architecture:Any
Triage: D3 (Medium)

[25 Oct 2007 10:00] Victoria Reznichenko
Description:
If you use replicate-* rules on the slave, it still displays queries that should be filtered out in SHOW PROCESSLIST output though doesn't execute these queries.

I have replicate-do-table=test.t1 in my.cnf but if I run mysqladmin -i 1 proc I get the following:

+----+-------------+-----------+------+---------+------+----------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User        | Host      | db   | Command | Time | State                            | Info                                                                                                 |
+----+-------------+-----------+------+---------+------+----------------------------------+------------------------------------------------------------------------------------------------------+
| 1  | root        | localhost | test | Sleep   | 2    |                                  |                                                                                                      |
| 5  | system user |           |      | Connect | 51   | Waiting for master to send event |                                                                                                      |
| 6  | root        | localhost |      | Query   | 0    |                                  | show processlist                                                                                     |
| 7  | system user |           | test | Connect | 29   | Reading event from the relay log | INSERT INTO `t2` VALUES (101723,'ZX5S7YbVQOlQ0c0v1IhXhvdqJZGqHn','6w7sSYAyZ7NNh3gInlKcJH4zleeHQZ','8 |
+----+-------------+-----------+------+---------+------+----------------------------------+------------------------------------------------------------------------------------------------------+

as you can see when SQL thread reads event from relay log it displays this query in SHOW PROCESSLIST.

This is a bit confusing.

How to repeat:
1. setup replication, specify replicate-do-table=test.t1 on the slave and start IO thread only.
2. restore data from test.sql file (will be uploaded). It contains dump of 2 tables t1 (empty) and t2 with many INSERTS.
3. run "mysqladmin -uroot -i 1 proc" to monitor processlist
4. start slave SQL thread
5. check mysqladmin output

Suggested fix:
It would be better to not display queries that are just read and will not be executed on the slave as it confuses people.
[25 Oct 2007 10:38] Victoria Reznichenko
file bug31840.zip (with test.sql) uploaded to ftp. 18M.
[4 Dec 2007 10:42] Mats Kindahl
I question whether this should really be considered a bug. The state clearly says that an event is being read from the relay log, and the extra information is just used to tell what the event is.

By filtering out the queries, it make it harder to debug the the replication process since it is no longer possible to see what the query being read looks like. I don't know how useful this is in practice, but I question whether removing this information in this case actually is helpful.
[4 Dec 2007 11:28] Sergei Golubchik
Right, it's not a bug.

replication thread needs to read the event first, than filter it out. That is, replicate-* rules are applied after reading, not before.