The 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:
1 2 3 4 5 | CREATE TABLE exchange_rate ( id INT PRIMARY KEY AUTO_INCREMENT, currency VARCHAR(3) UNIQUE, rate FLOAT(5,3) ) ENGINE=InnoDB; |
Let’s add a trio of rows to this new table:
1 | INSERT INTO exchange_rate (currency,rate) VALUES ('EUR',0.854), ('GBP',0.767), ('BRL',4.107); |
which gives us the following initial set:
1 2 3 4 5 6 7 8 9 | master (test) > select * from exchange_rate; +----+----------+-------+ | id | currency | rate | +----+----------+-------+ | 1 | EUR | 0.854 | | 2 | GBP | 0.767 | | 3 | BRL | 4.107 | +----+----------+-------+ 3 rows in set (0.00 sec) |
Now we update the rate for Brazilian Reais using a REPLACE operation:
1 | REPLACE INTO exchange_rate SET currency='BRL', rate=4.500; |
With currency being a UNIQUE field the row is fully replaced:
1 2 3 4 5 6 7 8 9 | master (test) > select * from exchange_rate; +----+----------+-------+ | id | currency | rate | +----+----------+-------+ | 1 | EUR | 0.854 | | 2 | GBP | 0.767 | | 4 | BRL | 4.500 | +----+----------+-------+ 3 rows in set (0.00 sec) |
and thus the autoinc sequence is updated:
1 2 3 4 5 6 7 8 9 10 11 | master (test) > SHOW CREATE TABLE exchange_rate\G *************************** 1. row *************************** Table: exchange_rate Create Table: CREATE TABLE `exchange_rate` ( `id` int(11) NOT NULL AUTO_INCREMENT, `currency` varchar(3) DEFAULT NULL, `rate` float(5,3) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `currency` (`currency`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) |
The problem is that the autoinc sequence is not updated in the replica as well:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | slave1 (test) > select * from exchange_rate;show create table exchange_rate\G +----+----------+-------+ | id | currency | rate | +----+----------+-------+ | 1 | EUR | 0.854 | | 2 | GBP | 0.767 | | 4 | BRL | 4.500 | +----+----------+-------+ 3 rows in set (0.00 sec) *************************** 1. row *************************** Table: exchange_rate Create Table: CREATE TABLE `exchange_rate` ( `id` int(11) NOT NULL AUTO_INCREMENT, `currency` varchar(3) DEFAULT NULL, `rate` float(5,3) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `currency` (`currency`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) |
Now, the moment we promote that replica as master and start writing to this table we’ll hit a duplicate key error:
1 2 | slave1 (test) > REPLACE INTO exchange_rate SET currency='BRL', rate=4.600; ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY' |
Note that:
a) the transaction fails and the row is not replaced, however the autoinc sequence is incremented:
1 2 3 4 5 6 7 | slave1 (test) > SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE table_schema='test' AND table_name='exchange_rate'; +----------------+ | AUTO_INCREMENT | +----------------+ | 5 | +----------------+ 1 row in set (0.00 sec) |
b) this problem only happens with row-based replication (binlog_format=ROW), where REPLACE in this case is logged as a row UPDATE:
1 2 3 4 5 6 7 8 9 10 11 | # at 6129 #180829 18:29:55 server id 100 end_log_pos 5978 CRC32 0x88da50ba Update_rows: table id 117 flags: STMT_END_F ### UPDATE `test`.`exchange_rate` ### WHERE ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### @2='BRL' /* VARSTRING(3) meta=3 nullable=1 is_null=0 */ ### @3=4.107 /* FLOAT meta=4 nullable=1 is_null=0 */ ### SET ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ ### @2='BRL' /* VARSTRING(3) meta=3 nullable=1 is_null=0 */ ### @3=4.5 /* FLOAT meta=4 nullable=1 is_null=0 */ |
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.
Workarounds
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:
1 2 3 4 5 6 7 | slave1 (test) > SELECT ((SELECT MAX(id) FROM exchange_rate)>=(SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE table_schema='test' AND table_name='exchange_rate')) as `check`; +-------+ | check | +-------+ | 1 | +-------+ 1 row in set (0.00 sec) |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | DELIMITER // CREATE PROCEDURE CheckAndFixAutoinc() BEGIN DECLARE done TINYINT UNSIGNED DEFAULT 0; DECLARE tableschema VARCHAR(64); DECLARE tablename VARCHAR(64); DECLARE columnname VARCHAR(64); DECLARE cursor1 CURSOR FOR SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys') AND EXTRA LIKE '%auto_increment%'; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; OPEN cursor1; start_loop: LOOP IF done THEN LEAVE start_loop; END IF; FETCH cursor1 INTO tableschema, tablename, columnname; SET @get_autoinc = CONCAT('SELECT @check1 := ((SELECT MAX(', columnname, ') FROM ', tableschema, '.', tablename, ')>=(SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=\'', tableschema, '\' AND TABLE_NAME=\'', tablename, '\')) as `check`'); PREPARE stm FROM @get_autoinc; EXECUTE stm; DEALLOCATE PREPARE stm; IF @check1>0 THEN BEGIN SET @select_max_id = CONCAT('SELECT @max_id := MAX(', columnname, ')+1 FROM ', tableschema, '.', tablename); PREPARE select_max_id FROM @select_max_id; EXECUTE select_max_id; DEALLOCATE PREPARE select_max_id; SET @update_autoinc = CONCAT('ALTER TABLE ', tableschema, '.', tablename, ' AUTO_INCREMENT=', @max_id); PREPARE update_autoinc FROM @update_autoinc; EXECUTE update_autoinc; DEALLOCATE PREPARE update_autoinc; END; END IF; END LOOP start_loop; CLOSE cursor1; END// DELIMITER ; |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | master (test) > REPLACE INTO exchange_rate SET currency='BRL', rate=4.500, id=3; # at 16151 #180905 13:32:17 server id 100 end_log_pos 15986 CRC32 0x1d819ae9 Write_rows: table id 117 flags: STMT_END_F ### DELETE FROM `test`.`exchange_rate` ### WHERE ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### @2='BRL' /* VARSTRING(3) meta=3 nullable=1 is_null=0 */ ### @3=4.107 /* FLOAT meta=4 nullable=1 is_null=0 */ ### INSERT INTO `test`.`exchange_rate` ### SET ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### @2='BRL' /* VARSTRING(3) meta=3 nullable=1 is_null=0 */ ### @3=4.5 /* FLOAT meta=4 nullable=1 is_null=0 */ # at 16199 #180905 13:32:17 server id 100 end_log_pos 16017 CRC32 0xf11fed56 Xid = 184 COMMIT/*!*/; |
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:
1 2 3 4 5 | CREATE TABLE exchange_rate ( id INT UNIQUE AUTO_INCREMENT, currency VARCHAR(3) PRIMARY KEY, rate FLOAT(5,3) ) ENGINE=InnoDB; |
the same REPLACE operation will be replicated as a DELETE+INSERT too:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | # at 19390 #180905 14:03:56 server id 100 end_log_pos 19225 CRC32 0x7042dcd5 Write_rows: table id 131 flags: STMT_END_F ### DELETE FROM `test`.`exchange_rate` ### WHERE ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### @2='BRL' /* VARSTRING(3) meta=3 nullable=0 is_null=0 */ ### @3=4.107 /* FLOAT meta=4 nullable=1 is_null=0 */ ### INSERT INTO `test`.`exchange_rate` ### SET ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ ### @2='BRL' /* VARSTRING(3) meta=3 nullable=0 is_null=0 */ ### @3=4.5 /* FLOAT meta=4 nullable=1 is_null=0 */ # at 19438 #180905 14:03:56 server id 100 end_log_pos 19256 CRC32 0x79efc619 Xid = 218 COMMIT/*!*/; |
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 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 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.
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.
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!).
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.