MySQL Bugs: #31840: Slave displays queries that should be filtered out in SHOW PROCESSLIST

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

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.

file bug31840.zip (with test.sql) uploaded to ftp. 18M.

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.

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.