Description:
Hi,
when granting SET_USER_ID on a 8.0 primary, it breaks replication on a 8.4.0 replica. See How to repeat for details.
From what I understand, replication from 8.0.37 to 8.4.0 should be supported. In MySQL 8.4 Reference Manual / Upgrading MySQL / Upgrade Paths [1], I can read below.
> Upgrade Path: From an LTS or Bugfix series to the next LTS series
> Path Examples: 8.0.37 to 8.4.x LTS
> In-place upgrade, logical dump and load, and replication
[1]: https://dev.mysql.com/doc/refman/8.4/en/upgrade-paths.html
So from what I understand, such breakage should not happen as SET_USER_ID is not deprecated in 8.0.
From what I see, the SET_USER_ID GRANT was deprecated in 8.2.0 and removed in 8.4.0, so it is probably the removal of this GRANT that breaks replication. IMHO, to allow replication compatibility between 8.0 and 8.4, SET_USER_ID should not have been removed in 8.4.0, it should have been removed in a later non-minor release. A solution could be to re-introduce this GRANT. Another solution could be to allow the "GRANT SET_USER_ID" syntax in 8.4, making this an alias for the replacement of SET_USER_ID (SET_ANY_DEFINER and ALLOW_NONEXISTENT_DEFINER).
Additionally, in How to repeat, we can see that a 8.2.0 replica does not break, but only gets the SET_USER_ID GRANT, not the SET_ANY_DEFINER and ALLOW_NONEXISTENT_DEFINER GRANTs. From what I understand, when upgrading from 8.0 to 8.2.0, users having SET_USER_ID should get SET_ANY_DEFINER and ALLOW_NONEXISTENT_DEFINER, so I would expect a GRANT SET_USER_ID in 8.2.0, especially replicating from 8.0, to also grant SET_ANY_DEFINER and ALLOW_NONEXISTENT_DEFINER. Please consider this if re-introducing SET_USER_ID in 8.4.
Note: even if this is a replication breakage, I set this a Category MySQL Server because the source of the breakage points in the direction of a "Server" incompatibility.
Thanks to Evan Elias comment in Bug#115187 that made me think of this: "And separately, also the SET_USER_ID privilege".
Many thanks for looking into this,
Jean-François Gagné
How to repeat:
dbdeployer deploy single 8.0.37 -c server_id=101 &
dbdeployer deploy single 8.2.0 -c server_id=102 &
dbdeployer deploy single 8.4.0 -c server_id=103 &
wait
./msb_8_0_37/use <<< "RESET MASTER"
./msb_8_0_37/use <<< "CREATE USER 'repl'@'%' IDENTIFIED BY 'password'"
./msb_8_0_37/use -u root <<< "GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'"
port=$(./msb_8_0_37/use -N <<< "select @@global.port")
sql="change replication source to SOURCE_HOST='127.0.0.1', SOURCE_PORT=$port"
sql="$sql, SOURCE_USER='repl', SOURCE_PASSWORD='password', SOURCE_SSL=1"
sql="$sql; start replica"
./msb_8_2_0/use <<< "$sql"
./msb_8_4_0/use <<< "$sql"
function check_db() { ./$1/use <<< "show databases" | grep $2 || echo "does not exist"; }
function check_rpl() { ./msb_8_0_37/use <<< "create database $1"; sleep 1; check_db msb_8_2_0 $1; check_db msb_8_4_0 $1; }
check_rpl test_jfg1
test_jfg1
test_jfg1
# At this point, replication from 8.0.37 to 8.4.0 works,
# let's try granting SET_USER_ID to 'repl'@'%'.
./msb_8_0_37/use -u root <<< "GRANT SET_USER_ID ON *.* TO 'repl'@'%'"
check_rpl test_jfg2
test_jfg2
does not exist
# From above, replication looks broken on 8.4.0, let's confirm why.
./msb_8_4_0/use <<< "show replica status\G" | grep Last_SQL_Error:
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 binlog.000001, end_log_pos 1103. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
./msb_8_4_0/use <<< "select WORKER_ID, LAST_ERROR_MESSAGE from performance_schema.replication_applier_status_by_worker\G"
*************************** 1. row ***************************
WORKER_ID: 1
LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'ANONYMOUS' at source log binlog.000001, end_log_pos 1103; Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use' on query. Default database: ''. Query: 'GRANT SET_USER_ID ON *.* TO 'repl'@'%''
*************************** 2. row ***************************
WORKER_ID: 2
LAST_ERROR_MESSAGE:
*************************** 3. row ***************************
WORKER_ID: 3
LAST_ERROR_MESSAGE:
*************************** 4. row ***************************
WORKER_ID: 4
LAST_ERROR_MESSAGE:
# So we effectively have a GRANT of SET_USER_ID in 8.0 breaking replication to 8.4.
# Out of suriosity, let's check the GRANTs.
./msb_8_0_37/use -N <<< "SHOW GRANTS for 'repl'@'%'"
GRANT REPLICATION SLAVE ON *.* TO `repl`@`%`
GRANT SET_USER_ID ON *.* TO `repl`@`%`
./msb_8_2_0/use -N <<< "SHOW GRANTS for 'repl'@'%'"
GRANT REPLICATION SLAVE ON *.* TO `repl`@`%`
GRANT SET_USER_ID ON *.* TO `repl`@`%`
./msb_8_4_0/use -N <<< "SHOW GRANTS for 'repl'@'%'"
GRANT REPLICATION SLAVE ON *.* TO `repl`@`%`
# In above, I am surprised that SET_ANY_DEFINER and ALLOW_NONEXISTENT_DEFINER are not set in the 8.2.0 replica.
Suggested fix:
Re-introduce the SET_USER_ID GRANT in a 8.4 minor release.
Or allow the "GRANT SET_USER_ID" in 8.4, making this an alias for the replacement of SET_USER_ID (SET_ANY_DEFINER and ALLOW_NONEXISTENT_DEFINER).
