Description:
Hi,
with partial revokes, doing a GRANT after DROP current USER (in the same session) cause a replication breakage. See How to repeat for details.
I traced this down to the GRANT being made with the AS of the dropped user in the binary logs. Because this user is dropped, the GRANT fails on the replica.
I filed this as S2 / Serious because replication breaking is not a minor thing.
I also filed this as Category Replication because this is a replication breakage, but maybe the solution is failing the GRANT when the current user does not exist, which would be in the Server.
Many thanks for looking into this,
Jean-François Gagné
How to repeat:
dbdeployer deploy replication 8.0.40 -c partial_revokes=ON -c print_identified_with_as_hex=O
# Create user root2@localhost like root@localhost and GRANT ALL to it.
./m -N <<< "SHOW CREATE USER root@localhost" | sed -e 's/CREATE USER .root/&2/' | ./m
./m -u root <<< "GRANT ALL ON *.* TO 'root2'@'localhost' WITH GRANT OPTION"
# Create user u1.
./m <<< "CREATE USER u1"
# With user root2, drop itself and grant something to u1.
./m -u root2 <<< "
DROP USER 'root2'@'localhost';
GRANT SELECT ON *.* TO u1"
# Check replication status.
./s1 <<< "SHOW REPLICA STATUS\G" | grep -e Replica_SQL_Running: -e Last_SQL_Error:
Replica_SQL_Running: No
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at source log mysql-bin.000001, end_log_pos 9924. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
# Check replication error.
./s1 <<< "select * from performance_schema.replication_applier_status_by_worker\G" | grep LAST_ERROR_MESSAGE
LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'ANONYMOUS' at source log mysql-bin.000001, end_log_pos 9924; Error 'Either some of the authorization IDs in the AS clause are invalid or the current user lacks privileges to execute the statement.' on query. Default database: ''. Query: 'GRANT SELECT ON *.* TO 'u1'@'%' AS 'root2'@'localhost' WITH ROLE NONE'
LAST_ERROR_MESSAGE:
LAST_ERROR_MESSAGE:
LAST_ERROR_MESSAGE:
# Check binlog content: the GRANT is made with the AS of the dropped user, which explains the replication breakage.
. master/sb_include
$CLIENT_BASEDIR/bin/mysqlbinlog master/data/mysql-bin.000001 | grep -e DROP.USER -e GRANT | grep -e DROP.USER -A 1
DROP USER 'root2'@'localhost'
GRANT SELECT ON *.* TO 'u1'@'%' AS 'root2'@'localhost' WITH ROLE NONE
# For 8.4.3 (and 9.1.0).
dbdeployer deploy multiple 8.4.3 -c partial_revokes=ON -c print_identified_with_as_hex=O
./n1 <<< "RESET BINARY LOGS AND GTIDS"
./n1 <<< "CREATE USER 'repl'@'%' IDENTIFIED BY 'password'"
./n1 -u root <<< "GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'"
port=$(./n1 -N <<< "select @@global.port")
sql="change replication source to SOURCE_HOST='127.0.0.1', SOURCE_PORT=$port, SOURCE_USER='repl', SOURCE_PASSWORD='password'"
./n3 <<< "$sql, SOURCE_SSL=1; start replica";
ln -s n1 m
ln -s n3 s1
ln -s node1 master
# Then same as above.
Description: Hi, with partial revokes, doing a GRANT after DROP current USER (in the same session) cause a replication breakage. See How to repeat for details. I traced this down to the GRANT being made with the AS of the dropped user in the binary logs. Because this user is dropped, the GRANT fails on the replica. I filed this as S2 / Serious because replication breaking is not a minor thing. I also filed this as Category Replication because this is a replication breakage, but maybe the solution is failing the GRANT when the current user does not exist, which would be in the Server. Many thanks for looking into this, Jean-François Gagné How to repeat: dbdeployer deploy replication 8.0.40 -c partial_revokes=ON -c print_identified_with_as_hex=O # Create user root2@localhost like root@localhost and GRANT ALL to it. ./m -N <<< "SHOW CREATE USER root@localhost" | sed -e 's/CREATE USER .root/&2/' | ./m ./m -u root <<< "GRANT ALL ON *.* TO 'root2'@'localhost' WITH GRANT OPTION" # Create user u1. ./m <<< "CREATE USER u1" # With user root2, drop itself and grant something to u1. ./m -u root2 <<< " DROP USER 'root2'@'localhost'; GRANT SELECT ON *.* TO u1" # Check replication status. ./s1 <<< "SHOW REPLICA STATUS\G" | grep -e Replica_SQL_Running: -e Last_SQL_Error: Replica_SQL_Running: No Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at source log mysql-bin.000001, end_log_pos 9924. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. # Check replication error. ./s1 <<< "select * from performance_schema.replication_applier_status_by_worker\G" | grep LAST_ERROR_MESSAGE LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'ANONYMOUS' at source log mysql-bin.000001, end_log_pos 9924; Error 'Either some of the authorization IDs in the AS clause are invalid or the current user lacks privileges to execute the statement.' on query. Default database: ''. Query: 'GRANT SELECT ON *.* TO 'u1'@'%' AS 'root2'@'localhost' WITH ROLE NONE' LAST_ERROR_MESSAGE: LAST_ERROR_MESSAGE: LAST_ERROR_MESSAGE: # Check binlog content: the GRANT is made with the AS of the dropped user, which explains the replication breakage. . master/sb_include $CLIENT_BASEDIR/bin/mysqlbinlog master/data/mysql-bin.000001 | grep -e DROP.USER -e GRANT | grep -e DROP.USER -A 1 DROP USER 'root2'@'localhost' GRANT SELECT ON *.* TO 'u1'@'%' AS 'root2'@'localhost' WITH ROLE NONE # For 8.4.3 (and 9.1.0). dbdeployer deploy multiple 8.4.3 -c partial_revokes=ON -c print_identified_with_as_hex=O ./n1 <<< "RESET BINARY LOGS AND GTIDS" ./n1 <<< "CREATE USER 'repl'@'%' IDENTIFIED BY 'password'" ./n1 -u root <<< "GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'" port=$(./n1 -N <<< "select @@global.port") sql="change replication source to SOURCE_HOST='127.0.0.1', SOURCE_PORT=$port, SOURCE_USER='repl', SOURCE_PASSWORD='password'" ./n3 <<< "$sql, SOURCE_SSL=1; start replica"; ln -s n1 m ln -s n3 s1 ln -s node1 master # Then same as above.