Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-6979

Modify the processing to call clean up functions to remove CREATE USER statement from the processlist after the statement has completed

    XMLWordPrintable

Details

    • Bug
    • Status: Done
    • Medium
    • Resolution: Fixed
    • 5.7.29-32, 8.0.19-10
    • 5.7.30-33, 8.0.20-11
    • None
    • None

    Description

      Hi Team,

      It is strange that the SHOW PROCESSLIST shows CREATE USER statement with the Sleep connection and goes of when next write-set comes in for that thread. But this is not happening for other DDLs like CREATE TABLE, ALTER TABLE, DROP etc.. But Not sure why  only CREATE USER statements are listed. Either this should happen to all DDLs or nothing.

       

      How to repeat:

      Node 1:

       

      node1 [localhost:26529] {msandbox} (vinodh) > create table t2(id int primary key); create table t1(id int primary key); create table t3(id int primary key);
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)

node1 [localhost:26529] {msandbox} (vinodh) > drop table t1; drop table t2; drop table t3;
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)

node1 [localhost:26529] {msandbox} (vinodh) > create table t2(id int primary key); create table t1(id int primary key); create table t3(id int primary key);
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)

node1 [localhost:26529] {msandbox} (vinodh) > drop table t1; drop table t2; drop table t3;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)

node1 [localhost:26529] {msandbox} (vinodh) > create table t2(id int primary key); create table t1(id int primary key); create table t3(id int primary key);
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)

       

      At the same in Node 2, those queries shows up randomly:

       

      $ while (true); do ./node2/use -N -e "show full processlist"  | grep -v 'CREATE USER' | grep -iE 'create|drop|insert|delete|alter|update'  ; done2 system user vinodh Sleep 0 checking permissions drop table t1 0 0
4 system user vinodh Sleep 0 creating table create table t1(id int primary key) 0 0
4 system user vinodh Sleep 0 creating table create table t3(id int primary key) 0 0
2 system user vinodh Sleep 0 innobase_commit_low (48) create table t1(id int primary key) 0 0

       

       

      Now do the same for CREATE USER:

      Node1:

       

      node1 [localhost:26529] {msandbox} (vinodh) > create user test@localhost  ;
Query OK, 0 rows affected (0.00 sec)

       

      Node 2:

       

      $ while (true); do ./node2/use -e "show full processlist" | grep -iE 'create|delete|alter|drop'; done$ while (true); do ./node2/use -e "show full processlist" | grep -iE 'create|delete|alter|drop'; done4 system user vinodh Sleep 0 Opening tables create user test@localhost 0 04 system user NULL Sleep 0 wsrep: committed write set (18) CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' 0 04 system user NULL Sleep 0 wsrep: committed write set (18) CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' 0 04 system user NULL Sleep 0 wsrep: committed write set (18) CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' 0 04 system user NULL Sleep 0 wsrep: committed write set (18) CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' 0 04 system user NULL Sleep 0 wsrep: committed write set (18) CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' 0 04 system user NULL Sleep 0 wsrep: committed write set (18) CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' 0 04 system user NULL Sleep 0 wsrep: committed write set (18) CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' 0 04 system user NULL Sleep 0 wsrep: committed write set (18) CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' 0 04 system user NULL Sleep 0 wsrep: committed write set (18) CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' 0 04 system user NULL Sleep 0 wsrep: committed write set (18) CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' 0 04 system user NULL Sleep 0 wsrep: committed write set (18) CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' 0 04 system user NULL Sleep 0 wsrep: committed write set (18) CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' 0 04 system user NULL Sleep 0 wsrep: committed write set (18) CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' 0 04 system user NULL Sleep 0 wsrep: committed write set (18) CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' 0 04 system user NULL Sleep 0 wsrep: committed write set (18) CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' 0 0
...
...

       

      this continues till next writes comes in:

      Node 1:

      node1 [localhost:26529] {msandbox} (vinodh) > insert into t1 values (1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

      Node 2:

      $ ./node2/use -e "show full processlist" 
+-------+-------------+-----------+------+---------+------+---------------------------------+------------------------------------------------------------------------+-----------+---------------+
| Id    | User        | Host      | db   | Command | Time | State                           | Info                                                                   | Rows_sent | Rows_examined |
+-------+-------------+-----------+------+---------+------+---------------------------------+------------------------------------------------------------------------+-----------+---------------+
|     1 | system user |           | NULL | Sleep   | 1472 | wsrep: aborter idle             | NULL                                                                   |         0 |             0 |
|     2 | system user |           | NULL | Sleep   |  781 | wsrep: committed write set (19) | NULL                                                                   |         0 |             0 |
|     4 | system user |           | NULL | Sleep   |  839 | wsrep: committed write set (18) | CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' |         0 |             0 |
| 16690 | msandbox    | localhost | NULL | Query   |    0 | starting                        | show full processlist                                                  |         0 |             0 |
+-------+-------------+-----------+------+---------+------+---------------------------------+------------------------------------------------------------------------+-----------+---------------+


$ ./node2/use -e "show full processlist" 
+-------+-------------+-----------+------+---------+------+---------------------------------+------------------------------------------------------------------------+-----------+---------------+
| Id    | User        | Host      | db   | Command | Time | State                           | Info                                                                   | Rows_sent | Rows_examined |
+-------+-------------+-----------+------+---------+------+---------------------------------+------------------------------------------------------------------------+-----------+---------------+
|     1 | system user |           | NULL | Sleep   | 1652 | wsrep: aborter idle             | NULL                                                                   |         0 |             0 |
|     2 | system user |           | NULL | Sleep   |  961 | wsrep: committed write set (19) | NULL                                                                   |         0 |             0 |
|     4 | system user |           | NULL | Sleep   | 1019 | wsrep: committed write set (18) | CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' |         0 |             0 |
| 16691 | msandbox    | localhost | NULL | Query   |    0 | starting                        | show full processlist                                                  |         0 |             0 |
+-------+-------------+-----------+------+---------+------+---------------------------------+------------------------------------------------------------------------+-----------+---------------+


$ ./node2/use -e "show full processlist" 
+-------+-------------+-----------+------+---------+------+---------------------------------+-----------------------+-----------+---------------+
| Id    | User        | Host      | db   | Command | Time | State                           | Info                  | Rows_sent | Rows_examined |
+-------+-------------+-----------+------+---------+------+---------------------------------+-----------------------+-----------+---------------+
|     1 | system user |           | NULL | Sleep   | 1676 | wsrep: aborter idle             | NULL                  |         0 |             0 |
|     2 | system user |           | NULL | Sleep   |  985 | wsrep: committed write set (19) | NULL                  |         0 |             0 |
|     4 | system user |           | NULL | Sleep   |    3 | wsrep: committed write set (20) | NULL                  |         0 |             0 |
| 16692 | msandbox    | localhost | NULL | Query   |    0 | starting                        | show full processlist |         0 |             0 |
+-------+-------------+-----------+------+---------+------+---------------------------------+-----------------------+-----------+---------------+

       

      Attachments

        Issue Links

          is triggering

          Bug - A problem which impairs or prevents the functions of the product. PXC-3190 Added to the execution of Cleanup functions to remove CREATE USER from SHOW PROCESSLIST.

          • Medium - Minor loss of function, or other problem where easy workaround is present.
          • Done

          Activity

            People

              marcelo.altmann Marcelo Altmann
              vinodh.krishnaswamy Vinodh Krishnaswamy
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - 0 minutes
                  0m
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 3 days, 6 hours
                  3d 6h

                  Smart Checklist