How To Fix MySQL Replication After an Incompatible DDL Command

How To Fix MySQL Replication After an Incompatible DDL Command

PREVIOUS POST
NEXT POST

fix MySQL replication after incompatible DDLMySQL supports replicating to a slave that is one release higher. This allows us to easily upgrade our MySQL setup to a new version, by promoting the slave and pointing the application to it. However, though unsupported, there are times when the MySQL version of slave deployed is one release lower. In this scenario, if your application has been performing much better on an older version of MySQL, you would like to have a convenient option to downgrade. You can simply promote the slave to get the old performance back.

The MySQL manual says that ROW based replication can be used to replicate to a lower version, provided that no DDLs replicated are incompatible with the slave. One such incompatible command is ALTER USER which is a new feature in MySQL 5.7 and not available on 5.6. :

Executing that command would break replication. Here is an example of a broken slave in non-GTID replication:

Skipping the statement does not resume replication:

Fixing non-GTID replication

When you check slave status, replication still isn’t fixed. To fix it, you must manually skip to the next binary log position. The current binary log (Relay_Master_Log_File) and position (Exec_Master_Log_Pos) executed are mysql-bin.000002 and 36174373 respectively. We can use mysqlbinlog on the master to determine the next position:

Based on the output above, the next binary log position is 36174621. To fix the slave, run:

Verify if the slave threads are now running by executing SHOW SLAVE STATUS\G

To make the slave consistent with the master, execute the compatible query on the slave.

Done.

GTID replication

For GTID replication, in addition to injecting an empty transaction for the offending statement, you’ll need skip it by using the non-GTID solution provided above. Once running, flip it back to GTID.

Here’s an example of a broken GTID slave:

Since the last position executed is 7403, so you’ll need to create an empty transaction for the offending sequence 7404.

Note: If you have MTS enabled, you can also get the offending GTID coordinates from Last_SQL_Error of SHOW SLAVE STATUS\G

The next step is to find the next binary log position. The current binary log(Relay_Master_Log_File) and position(Exec_Master_Log_Pos) executed are mysql-bin.000003 and 12468343 respectively. We can again use mysqlbinlog  on the master to determine the next position:

The next binary log position is 36174621. To fix the slave, run:

Notice that I added MASTER_AUTO_POSITION=0 above to disable GTID replication for now. You can run SHOW SLAVE STATUS\G to determine that MySQL is running fine:

Since it’s running fine you can now revert back to GTID replication:

Finally, to make the slave consistent with the master, execute the compatible query on the slave.

Summary

In this article, I’ve shared how to fix replication when it breaks due to an incompatible command being replicated to the slave. In fact, I’ve only identified ALTER USER as an incompatible command for 5.6. If there are other incompatible commands, please share them in the comment section. Thanks in advance.

Update: 
I filed a bug at https://bugs.mysql.com/bug.php?id=92629 to verify if the errors I’ve encountered here is a bug or undocumented behavior.

PREVIOUS POST
NEXT POST

Share this post

Comment (1)

  • Subhajit Reply

    Great article. Thank you!

    October 1, 2018 at 9:10 am

Leave a Reply