MySQL Bugs: #99200: CREATE USER get stuck on SHOW PROCESSLIST and ps.threads on slave

Bug #99200	CREATE USER get stuck on SHOW PROCESSLIST and ps.threads on slave
Submitted:	7 Apr 2020 18:56	Modified:	30 Apr 2020 12:13
Reporter:	Marcelo Altmann (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.7.29, 8.0.19	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
If a query had been rewritten by the parser because it contains sensitive information ( mysql_rewrite_query ), it wont be cleaned up when sql thread applies it, making it get stuck on SHOW PROCESSLIST and performance_schema.threads:

mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User        | Host      | db   | Command | Time | State                                                  | Info                                                                                                 |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------------------------------------------------------------------------------------------+
|  1 | system user |           | NULL | Connect |  240 | Waiting for master to send event                       | NULL                                                                                                 |
|  2 | system user |           | NULL | Connect |  223 | Slave has read all relay log; waiting for more updates | CREATE USER 'bugddl'@'localhost' |
|  5 | root        | localhost | NULL | Query   |    0 | starting                                               | show processlist                                                                                     |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

How to repeat:
Create a master slave with all defaults configurations:

On Master:

CREATE USER `bugddl`@`localhost`

-- do not execute any other command, as following commands will clean up.

On Slave:

SHOW PROCESSLIST;

Suggested fix:
Call proper cleanup functions as part of Query_log_event::do_apply_event end block.

5.7 patch

Attachment: bug99200_5.7.patch (application/octet-stream, text), 2.23 KiB.

8.0 patch

Attachment: bug99200_8.0.patch (application/octet-stream, text), 2.22 KiB.

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Hello Marcelo,

Thank you for the report and contribution.
Verified as described.

Thanks,
Umesh

I filed this already

Bug 30521198 - IDLE & CAUGHT UP SLAVE HAS OLD QUERY SHOWN IN "INFO" SECTION OF PROCESSLIST...

https://github.com/mysql/mysql-server/commit/69b75d3d24bb  fixes the issue on SHOW PROCESSLIST, however we still show the statement on performance_schema.threads