Replication 5.7.37 to 8.0.28 failing because of Replication Privilege Check


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'@'' IDENTIFIED BY 'password' REQUIRE SSL;
mysql> GRANT REPLICATION_APPLIER ON *.* TO 'priv_repl'@'';
mysql> SET sql_log_bin = 1;
FOR CHANNEL 'group_replication_recovery';

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!



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

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

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

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

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

Then start replication on the secondary.

1 Like

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!


1 Like