- Build used cat docs/INFO_SRC commit: c0b73ae948b4b1362c65c99291e5c573c6891813 date: 2019-04-13 13:41:06 +0200 build-date: 2019-04-13 13:47:03 +0200 short: c0b73ae branch: mysql-8.0.16-release MySQL source 8.0.16 -- Setup replication rm -rf master95612 bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/master95612 --log-error-verbosity=3 bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/master95612 --core-file --socket=/tmp/mysql_master.sock --port=3333 --log-error=$PWD/master95612/log.err --gtid_mode=ON --log-slave-updates --enforce-gtid-consistency --log-bin=master-bin --server_id=1 --binlog_format=ROW --binlog_row_image=minimal --transaction_write_set_extraction=XXHASH64 --binlog_transaction_dependency_tracking=WRITESET --slave_parallel_workers=10 --slave_parallel_type=LOGICAL_CLOCK 2>&1 & rm -rf slave95612 bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/slave95612 --log-error-verbosity=3 bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/slave95612 --core-file --socket=/tmp/mysql_slave.sock --port=6666 --log-error=$PWD/slave95612/log.err --gtid_mode=ON --log-slave-updates --enforce-gtid-consistency --log-bin=slave-bin --server_id=2 --binlog_format=ROW --binlog_row_image=minimal --transaction_write_set_extraction=XXHASH64 --binlog_transaction_dependency_tracking=WRITESET --slave_parallel_workers=10 --slave_parallel_type=LOGICAL_CLOCK 2>&1 & - bin/mysql -uroot -S /tmp/mysql_master.sock --prompt='Master>' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.16 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. Master>CREATE USER 'repl'@'localhost' IDENTIFIED BY 'slavepass'; Query OK, 0 rows affected (0.00 sec) Master>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'localhost'; Query OK, 0 rows affected (0.00 sec) Master> Master>CREATE USER 'repl'@'127.0.0.1' IDENTIFIED BY 'slavepass'; Query OK, 0 rows affected (0.00 sec) Master>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'127.0.0.1'; Query OK, 0 rows affected (0.00 sec) Master>FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) Master> - bin/mysql -uroot -S /tmp/mysql_slave.sock --prompt='Slave>' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.16 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. Slave>CHANGE MASTER TO -> MASTER_HOST='127.0.0.1', -> MASTER_PORT=3333, -> MASTER_USER='repl', -> MASTER_PASSWORD='slavepass', -> MASTER_AUTO_POSITION = 1, GET_MASTER_PUBLIC_KEY=1; Query OK, 0 rows affected, 2 warnings (0.02 sec) Slave>start slave; Query OK, 0 rows affected (0.01 sec) Slave>show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: repl Master_Port: 3333 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 1419 Relay_Log_File: hod03-relay-bin.000002 Relay_Log_Pos: 1635 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1419 Relay_Log_Space: 1843 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: b58e9a73-8766-11e9-ab07-0010e05f3e06 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: b58e9a73-8766-11e9-ab07-0010e05f3e06:1-5 Executed_Gtid_Set: b58e9a73-8766-11e9-ab07-0010e05f3e06:1-5 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 1 Network_Namespace: 1 row in set (0.00 sec) - Once GTID based replication is in place, run below against master - master cat 95461.sh #!/bin/bash for (( c=1; c<=5000; c++ )) do bin/mysql -uroot -S /tmp/mysql_master.sock -e "DROP USER IF EXISTS 'bug$c'@'localhost';CREATE USER IF NOT EXISTS 'bug$c'@'localhost';ALTER USER 'bug$c'@'localhost' IDENTIFIED BY 'Pa$$123';GRANT ALL ON bug$c.* TO 'bug$c'@'localhost';" done bash 95461.sh - Open second slave session and try to change one of existing/non-existing user's password bin/mysql -uroot -S /tmp/mysql_slave.sock --prompt='Slave>' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 20 Server version: 8.0.16 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. Slave>alter user 'bug1@localhost' identified by 'test'; ERROR 1396 (HY000): Operation ALTER USER failed for 'bug1@localhost'@'%' Slave> - observe slave broke with Slave>show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: repl Master_Port: 3333 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 2066221 Relay_Log_File: hod03-relay-bin.000002 Relay_Log_Pos: 1674113 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 3547 Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'b58e9a73-8766-11e9-ab07-0010e05f3e06:6671' at master log master-bin.000001, end_log_pos 1686073. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. Skip_Counter: 0 Exec_Master_Log_Pos: 1673897 Relay_Log_Space: 2066645 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 3547 Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'b58e9a73-8766-11e9-ab07-0010e05f3e06:6671' at master log master-bin.000001, end_log_pos 1686073. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: b58e9a73-8766-11e9-ab07-0010e05f3e06 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 190605 09:53:18 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: b58e9a73-8766-11e9-ab07-0010e05f3e06:1-8169 Executed_Gtid_Set: b58e9a73-8766-11e9-ab07-0010e05f3e06:1-6670 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 1 Network_Namespace: 1 row in set (0.00 sec) Slave>select * from performance_schema.replication_applier_status_by_worker\G *************************** 1. row *************************** CHANNEL_NAME: WORKER_ID: 1 THREAD_ID: NULL SERVICE_STATE: OFF LAST_ERROR_NUMBER: 3547 LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'b58e9a73-8766-11e9-ab07-0010e05f3e06:6671' at master log master-bin.000001, end_log_pos 1686073; Error 'Can not lock user management caches for processing.' on query. Default database: ''. Query: 'CREATE USER IF NOT EXISTS 'bug1667'@'localhost' IDENTIFIED WITH 'caching_sha2_password'' LAST_ERROR_TIMESTAMP: 2019-06-05 09:53:18.712307 LAST_APPLIED_TRANSACTION: b58e9a73-8766-11e9-ab07-0010e05f3e06:6670 LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2019-06-05 09:53:18.650175 LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2019-06-05 09:53:18.650175 LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 2019-06-05 09:53:18.650586 LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 2019-06-05 09:53:18.659329 APPLYING_TRANSACTION: b58e9a73-8766-11e9-ab07-0010e05f3e06:6671 APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2019-06-05 09:53:18.655608 APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2019-06-05 09:53:18.655608 APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 2019-06-05 09:53:18.659353 LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0 LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0 LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000 APPLYING_TRANSACTION_RETRIES_COUNT: 0 APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0 APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000 ^^ Query usually one of in "'CREATE USER IF NOT EXISTS" or "DROP USER IF EXISTS.." or "GRANT.." but error remains same i.e Error 'Can not lock user management caches for processing.'