Bug #99200 CREATE USER get stuck on SHOW PROCESSLIST and ps.threads on slave
Submitted: 7 Apr 18:56 Modified: 30 Apr 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

[7 Apr 18:56] Marcelo Altmann
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.
[7 Apr 19:01] Marcelo Altmann
5.7 patch

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

[7 Apr 19:11] Marcelo Altmann
8.0 patch

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

[7 Apr 19:11] Marcelo Altmann
(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
[8 Apr 5:26] MySQL Verification Team
Hello Marcelo,

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

Thanks,
Umesh
[8 Apr 10:29] MySQL Verification Team
I filed this already

Bug 30521198 - IDLE & CAUGHT UP SLAVE HAS OLD QUERY SHOWN IN "INFO" SECTION OF PROCESSLIST...
[30 Apr 12:13] Marcelo Altmann
https://github.com/mysql/mysql-server/commit/69b75d3d24bb  fixes the issue on SHOW PROCESSLIST, however we still show the statement on performance_schema.threads