Persistence of autoinc fixed in MySQL 8.0

MySQL 8.0 autoinc persistence fixedThe release of MySQL 8.0 has brought a lot of bold implementations that touched on things that have been avoided before, such as added support for common table expressions and window functions. Another example is the change in how AUTO_INCREMENT (autoinc) sequences are persisted, and thus replicated.

This new implementation carries the fix for bug #73563 (Replace result in auto_increment value less or equal than max value in row-based), which we’ve only found about recently. The surprising part is that the use case we were analyzing is a somewhat common one; this must be affecting a good number of people out there.

Understanding the bug

The business logic of the use case is such the UNIQUE column found in a table whose id is managed by an AUTO_INCREMENT sequence needs to be updated, and this is done with a REPLACE operation:

“REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.”

So, what happens in practice in this particular case is a DELETE followed by an INSERT of the target row.

We will explore this scenario here in the context of an oversimplified currency converter application that uses USD as base reference:

Let’s add a trio of rows to this new table:

which gives us the following initial set:

Now we update the rate for Brazilian Reais using a REPLACE operation:

With currency being a UNIQUE field the row is fully replaced:

and thus the autoinc sequence is updated:

The problem is that the autoinc sequence is not updated in the replica as well:

Now, the moment we promote that replica as master and start writing to this table we’ll hit a duplicate key error:

Note that:

a) the transaction fails and the row is not replaced, however the autoinc sequence is incremented:

b) this problem only happens with row-based replication (binlog_format=ROW), where REPLACE in this case is logged as a row UPDATE:

With statement-based replication—or even mixed format—the REPLACE statement is replicated as is: it will trigger a DELETE+INSERT in the background on the replica and thus update the autoinc sequence in the same way it did on the master.

This example (tested with Percona Server versions 5.5.61, 5.6.36 and 5.7.22) helps illustrate the issue with autoinc sequences not being persisted as they should be with row-based replication. However, MySQL’s Worklog #6204 includes a couple of scarier scenarios involving the master itself, such as when the server crashes while a transaction is writing to a table similar to the one used in the example above. MySQL 8.0 remedies this bug.


There are a few possible workarounds to consider if this problem is impacting you and if neither upgrading to the 8 series nor resorting to statement-based or mixed replication format are viable options.

We’ll be discussing three of them here: one that resorts around the execution of checks before a failover (to detect and fix autoinc inconsistencies in replicas), another that requires a review of all REPLACE statements like the one from our example and adapt it as to include the id field, thus avoiding the bug, and finally one that requires changing the schema of affected tables in such a way that the target field is made the Primary Key of the table while id (autoinc) is converted into a UNIQUE key.

a) Detect and fix

The less intrusive of the workarounds we conceived for the problem at hand in terms of query and schema changes is to run a check for each of the tables that might be facing this issue in a replica before we promote it as master in a failover scenario:

If the table does not pass the test, like ours didn’t at first (just before we attempted a REPLACE after we failed over to the replica), then update autoinc accordingly. The full routine (check + update of autoinc) could be made into a single stored procedure:

It doesn’t allow for as clean a failover as we would like but it can be helpful if you’re stuck with MySQL<8.0 and binlog_format=ROW and cannot make changes to your queries or schema.

b) Include Primary Key in REPLACE statements

If we had explicitly included the id (Primary Key) in the REPLACE operation from our example it would have also been replicated as a DELETE+INSERT even when binlog_format=ROW:

We could point out that we are doing it wrong by not having the id included in the REPLACE statement in the first place; the reason for not doing so would be mostly related to avoiding an extra lookup for each replace (to obtain the id for the currency we want to update). On the other hand, what if your business logic do expects the id to change at each REPLACE ? You should have such requirement in mind when considering this workaround as it is effectively a functional change to what we had initially.

c) Make the target field the Primary Key and keep autoinc as a UNIQUE key

If we make currency the Primary Key of our table and id a UNIQUE key instead:

the same REPLACE operation will be replicated as a DELETE+INSERT too:

Of course, the same would be true if we had just removed id entirely from the table and kept currency as the Primary Key. This would work in our particular test example but that won’t always be the case. Please note though that if you do keep id on the table you must make it a UNIQUE key: this workaround is based on the fact that this key becomes a second unique constraint, which triggers a different code path to log a replace operation. Had we made it a simple, non-unique key instead that wouldn’t be the case.

If you have any comments or suggestions about the issue addressed in this post, the workarounds we propose, or even a different view of the problem you would like to share please leave a comment in the section below.

Co-Author: Trey Raymond

Trey RaymondTrey Raymond is a Sr. Database Engineer for Oath Inc. (née Yahoo!), specializing in MySQL. Since 2010, he has worked to build the company’s database platform and supporting team into industry leaders.

While a performance guru at heart, his experience and responsibilities range from hardware and capacity planning all through the stack to database tool and utility development.

He has a reputation for breaking things to learn something new.

Co-Author: Fernando Laudares

fernando laudaresFernando is a Senior Support Engineer with Percona. Fernando’s work experience includes the architecture, deployment and maintenance of IT infrastructures based on Linux, open source software and a layer of server virtualization. He’s now focusing on the universe of MySQL, MongoDB and PostgreSQL with a particular interest in understanding the intricacies of database systems, and contributes regularly to this blog. You can read his other articles here.

Share this post

Comments (3)

  • Federico Razzoli

    In my understanding, replacing REPLACE with DELETE + INSERT should also avoid the problem. Correct?
    If so, and if adding the DELETE is a problem for developers, DELETE could also be added in a trigger.

    October 9, 2018 at 7:15 am
    • Fernando Laudares Camargos

      Hi Federico,
      note that REPLACE acts in two ways: it will (a) add a row (INSERT) with the entry if it doesn’t exists already or (b) actually replace it (DELETE+INSERT) if it does. The use of REPLACE by the developer/DBA avoids the need for such check – that extra manual lookup I mentioned. Historically, it also made sense in the old days, when transactions weren’t available in MySQL (MyISAM): it’s like embedding the DELETE+INSERT in a single atomic operation.
      You’re right in that avoiding the use of REPLACE and going instead with a [DELETE+]INSERT is also a valid option (and a more portable one too!).

      October 11, 2018 at 5:30 pm
      • Federico Razzoli

        I am not a fan of REPLACE, because developers tend to use it without understanding details and drawbacks (AUTO_INCREMENT, table with multiple UNIQUE indexes, foreign keys, triggers, number of affected rows).
        This bug seems to me one more reason to ask developers to use DELETE + INSERT instead, or INSERT ON DUPLICATE KEY UPDATE.
        Thanks for confirming that the bug only affects the REPLACE statement.

        October 12, 2018 at 7:45 am

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.