User Exists on the Replica and Not in the PrimaryHey folks,

This blog is straightforward but, at the same time, handy. At Percona, we know that the audience of our blogs is not limited to DBAs but includes a broader range of IT professionals, such as sysadmins, help desk employees, NOC operators, and developers as well.

Sometimes people (and also some DBAs, why not?) may accidentally and unintentionally execute supposedly innocuous commands which do not involve data manipulation, but can and do break the replication.

At Percona Managed Services, we see this type of situation more often than one would expect, believe me.

Suppose we have the following topology

PS8_Primary is a primary server, with Percona Server for MySQL 8.0.29 installed, in Read-Write mode

PS8_Primary is a replica server, with Percona Server for MySQL 8.0.29 installed, in Read-Only mode (but NOT Super Read Only!) and zero lag.

Below is a typical example of what I just mentioned. The user (whatever their role is) runs this command on the primary, and it works: the user is going to create the ‘foo‘ user with read-only privileges:

Then you realize that the following error occurs in the replica:

Replication is failing due to the user’s existence.

Now, you may be wondering: how could this have happened? Let’s explore a few possible options:

  • The replica was once the primary instance, and the ‘foo’ user was created with SET sql_log_bin=0, which is absurd but possible.
  • Person X explicitly created the users in the replica (which was not in Read-Only mode or Super Read-Only mode) so that they do not directly access the primary. Then person Y, without checking anything, tried to create (and managed to do so) the user.

And that’s what happened:

The user existed in the replica! It explains the error.

What’s done is done. How do we solve it?

  • We could skip the command if the replication is not based on GTID.
  • If the replica is based on GTID, skipping the command would be more complex but possible.
  • If the replication is based on GTID and at the same time uses multi-threaded replication, skipping the command would be more complex but possible.

The below links may help you:

How To Fix MySQL Replication After an Incompatible DDL Command

Repair MySQL 5.6 GTID replication by injecting empty transactions

We can also use the pt-slave-restart utility to sort out this error.  This is an example

Personally, I do not recommend any of the three methods or scenarios mentioned above.  I would prefer to do the following instead:

  • Access the replica, verify that the user exists and see what permissions are assigned.
  • Compare the permissions assigned in the replica with those that are trying to be assigned in the primary instance. We can use the pt-show-grants utility for this purpose:

Based on that, we can:

  • If the user and permissions match, we can stop the replication, DROP the user in the replica, and resume the replication.
  • If the user and the permissions DO NOT match, we need to discuss the situation with the user who executed the statement and verify what permissions they need (whether those of the replica or those of the primary or something in-between) and act accordingly.

Funnily enough, the opposite could happen: some people delete a user on the primary instance, and it doesn’t exist on the replica. In this case, it would be very similar.

Let’s prevent this. How? Using the EXISTS clause.

For the DROP USER case:

And now, as you can see, we don’t have any errors on the replica:

All resolved.

Below I will list most of the commands that are usually executed (in the first column), and how to make them failsafe (in the second column).

For MySQL

Instead of this

Use this

CREATE USER

CREATE USER [IF NOT EXISTS]

DROP USER

DROP USER [IF EXISTS]

CREATE ROLE

CREATE ROLE [IF NOT EXISTS]

DROP ROLE

DROP ROLE [IF EXISTS]

CREATE TABLE

CREATE [TEMPORARY] TABLE [IF NOT EXISTS]

DROP TABLE

DROP [TEMPORARY] TABLE [IF EXISTS]

CREATE VIEW

CREATE OR REPLACE

DROP VIEW

DROP VIEW [IF EXISTS]

CREATE PROCEDURE

CREATE PROCEDURE [IF NOT EXISTS]

DROP PROCEDURE

DROP {PROCEDURE | FUNCTION} [IF EXISTS]

CREATE FUNCTION

CREATE FUNCTION [IF NOT EXISTS]

DROP FUNCTION

DROP {PROCEDURE | FUNCTION} [IF EXISTS]

CREATE TRIGGER

CREATE TRIGGER [IF NOT EXISTS]

DROP TRIGGER

CREATE TRIGGER [IF NOT EXISTS]

CREATE EVENT

CREATE EVENT [IF NOT EXISTS]

DROP EVENT

DROP EVENT [IF EXISTS]

CREATE SCHEMA

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS]

DROP SCHEMA

DROP {DATABASE | SCHEMA} [IF EXISTS]

 

For MariaDB

(Surprisingly, it has not only the EXISTS clause, but REPLACE as well.)

Instead of this

Use this

CREATE USER

CREATE [OR REPLACE] USER [IF NOT EXISTS]

DROP USER 

DROP USER [IF EXISTS]

CREATE ROLE

CREATE [OR REPLACE] ROLE [IF NOT EXISTS]

DROP ROLE

DROP ROLE [IF EXISTS]

CREATE TABLE

CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS]

DROP TABLE

DROP [TEMPORARY] TABLE [IF EXISTS]

CREATE VIEW

CREATE [OR REPLACE][IF NOT EXISTS]

DROP VIEW

DROP VIEW [IF EXISTS]

CREATE PROCEDURE

CREATE [OR REPLACE] [IF NOT EXISTS]

DROP PROCEDURE

DROP {PROCEDURE | FUNCTION} [IF EXISTS]

CREATE FUNCTION

CREATE [OR REPLACE] [IF NOT EXISTS]

DROP FUNCTION

DROP FUNCTION [IF EXISTS]

CREATE TRIGGER

CREATE [OR REPLACE] [IF NOT EXISTS]

DROP TRIGGER

DROP TRIGGER [IF EXISTS]

CREATE EVENT

CREATE [OR REPLACE] [IF NOT EXISTS]

DROP EVENT

DROP EVENT [IF EXISTS]

CREATE SCHEMA

CREATE [OR REPLACE] {DATABASE | SCHEMA} [IF NOT EXISTS]

DROP SCHEMA

DROP {DATABASE | SCHEMA} [IF EXISTS]

CREATE INDEX

CREATE [OR REPLACE] INDEX [IF NOT EXISTS]

DROP INDEX

[IF EXISTS]

 

Conclusion

Next time, consider using these options in the DROP or CREATE commands. It will help ensure you’re doing the right thing for the primary database instance and don’t break the replication.

Be clever, be simple, but above all, be careful.

Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments