13

Replication on my slave database (MySQL 5.7.12) stopped working suddenly. When I run SHOW SLAVE STATUS\G I see the following error:

Last_Errno: 1396
Last_Error: Error 'Operation CREATE USER failed for 'user'@'ip'' on query. Default database: ''. Query: 'CREATE USER 'user'@'ip' IDENTIFIED WITH 'mysql_native_password' AS '*password''

I had recently created 2 new read-only users on both my master and my slave so I'm certain it's because of this.

I also see this in the status:

Slave_IO_Running: Yes
Slave_SQL_Running: No

Any suggestions on how to fix this?

Thank you.

tptcat
  • 247
  • 1
  • 2
  • 7

1 Answers1

20

TL;DR

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; 
START SLAVE;

Now the slightly longer version:

if you have manually created the user on your slave first and on your master second, the CREATE USER command executed on the master has been replicated to the slave. Attempted subsequent execution of the statement failed, because the user was already present. So my suggestions to fix "this":

  • Tell the replication engine to skip the statement and move on
  • Do not mess with slaves in a replication setup in the future
  • Make sure to read the official documentation whenever doing things you haven't done before
tptcat
  • 247
  • 1
  • 2
  • 7
the-wabbit
  • 40,737
  • 13
  • 111
  • 174
  • Thank you. This did it for me. The only thing I added is `STOP SLAVE;` before I ran the `SQL_SLAVE_SKIP_COUNTER`. – tptcat Sep 11 '17 at 11:25
  • I got an error `sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON` So I first performed `stop slave`, then `set global gtid_mode=ON_PERMISSIVE;` then `start slave` then waited till it caught up, and finally `set global gtid_mode=ON;` – Mike S Oct 05 '22 at 13:05