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

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 5.7.29-32, 8.0.19-10
    • Fix Version/s: 5.7.30-33, 8.0.20-11
    • Component/s: None
    • Labels:
      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 |
      +-------+-------------+-----------+------+---------+------+---------------------------------+-----------------------+-----------+---------------+
      
      

       

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                Assignee:
                marcelo.altmann Marcelo Altmann
                Reporter:
                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