Replication 5.7.37 to 8.0.28 failing because of Replication Privilege Check

MySQL & MariaDB Percona Server for MySQL 8.0
1

Hello,

Thought I’d try again. I have a 4 node Group Replication - Primary is 5.7.37+, 2 x 5.7.37 Read Replicas and a new 8.0.28 Read Only Replica.

If a procedure utilizes TEMP tables and executes a DROP TEMPORARY TABLE IF EXISTS the 8.0.28 Read Replica errors out.

Slave I/O for channel 'group_replication_applier': The queue event failed for channel 'group_replication_applier' as an invalid event according to REQUIRE_ROW_FORMAT was found. Error_code: MY-013507

This is traced to a “new” security feature of 8.0 regarding “Replication Privilege Checks” and the solution seems pretty simple straight out of the document.

mysql> SET sql_log_bin = 0;
mysql> CREATE USER 'priv_repl'@'%.example.com' IDENTIFIED BY 'password' REQUIRE SSL;
mysql> GRANT REPLICATION_APPLIER ON *.* TO 'priv_repl'@'%.example.com';
mysql> SET sql_log_bin = 1;
mysql> STOP GROUP_REPLICATION;
mysql> CHANGE REPLICATION SOURCE TO PRIVILEGE_CHECKS_USER = 'priv_repl'@'%.example.com'
FOR CHANNEL 'group_replication_recovery';
mysql> FLUSH PRIVILEGES;
mysql> START GROUP_REPLICATION;

But after I do the above and start the Group Rep it fails with:
[ERROR] [MY-011526] [Repl] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-5303619, b0cf4d2d-845a-11ec-a038-005056ad8d00:1 > Group transactions: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-5303955'

I even tried the above GRANTing the PRIV to the user with a version of the DB prior to the DROP TEMPORARY TABLE Statement being executed.

Catch 22. How can I get this GRANT on the 8.0 Replica to work without skewing the BINLOG so it can proper recover properly?

Is there a way to just turn off Privilege Check using the my.cnf?

Any help would be great!

DD

2 Likes
2

Replication Rule #1: Never make changes directly on a replica.

You need to execute the CREATE and GRANT on the PRIMARY and let that replicate to the Secondaries. Then you can run the other DCLs on the replica.

Strange that the sql_log_bin=0 still recorded the CREATE USER and GRANT.

1 Like
3

My thoughts exactly and would love to make all changes on the Primary. But because the REPLICATION_APPLIER grant is v8 specific, I can’t make the change on the 5.7.37 Primary Node.
(Primary 5.7.37 with 2 x 5.7.37 Read Replicas. I added a 8.0.28 replica which is the one I am having issues with).

Do you know of a way around this?

1 Like
4

By default any FLUSH statements are recorded to the binary log. I think that is your problem. That statement should not be needed. The privileges should take effect immediately.

1 Like
5

You can also just inject a phantom transaction to the primary to “sync” them.

SET GTID_NEXT='b0cf4d2d-845a-11ec-a038-005056ad8d00:1';
BEGIN; COMMIT;
SET GTID_NEXT=AUTOMATIC;

Then start replication on the secondary.

1 Like
6

igroene - Thanks. I’ll check this out in next couple of days. Didn’t think about this one.
matthewb - Thanks as well.

So far I did find another work around, basically a less elegant but more brute-force just to get the darn thing working. I basically Grant ALL Privileges on the Replication User, which replicated to the 2 other 5.7 nodes. I then took a pecona backup and built a v8 node and it is now up and running in GROUP_REPLICATION and the user on this node has the REPLICATION_APPLIER grant and the source is using PRIVILEGE_CHECKS_USER now.

However, now I’m back to my original issue that the above grant was suppose to solve. Running a Stored Procedure with a DROP TEMP TABLE DDL causes the new node to fail with the invalid event according to REQUIRE_ROW_FORMAT was found. Error_code: MY-013507

Per Documentation if BIN LOG set to ROW and using Group Replication and with the above settings, it shouldn’t even be replicating CREATE or DROP TEMPORARY TABLE statements to READ Replicas.

Any tips or further documentation I can be referred to?

Thank You!

DD

1 Like