Duplicate PRIMARY KEY errors after changing auto_increment settings

[monty solomon]

We are getting duplicate primary key errors after changing the auto_increment settings.

We were inserting rows into tables in two (2) databases on separate servers and changed the auto_increment settings on the servers so one of them would use odd values and the other one would use even values for a migration where we didn't want any collisions.

The migration is complete and we changed the auto_increment settings back to the default values and are getting duplicate primary key errors for some INSERT statements.

[monty solomon (monty+launchpad)]

How to repeat:

Change the auto_increment settings to use even values and insert many rows into several tables.

SET GLOBAL auto_increment_increment = 2 ; SET GLOBAL auto_increment_offset = 2 ;

Change the auto_increment settings back to the standard values and insert rows into the tables.

SET GLOBAL auto_increment_increment = 1 ; SET GLOBAL auto_increment_offset = 1 ;

Observe duplicate primary key errors for some INSERT statements.

[monty solomon (monty+launchpad)]

mysql Ver 14.14 Distrib 5.6.25-73.1, for Linux (x86_64) using 6.0
5.6.25-73.1-log Percona Server (GPL), Release 73.1, Revision 07b797f

[monty solomon (monty+launchpad)]

I created upstream https://bugs.mysql.com/bug.php?id=81101

[monty solomon (monty+launchpad)]

An INSERT failed today complaining about a duplicate PRIMARY KEY for an entry that was added three (3) days ago.

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '1436' for key 'PRIMARY' [statement:"INSERT INTO subscription_items

The entry with the id 1436 was created at 160416 1:50:56 Z and the failure was today at 160419 11:53:16 Z.

[monty solomon (monty+launchpad)]

It appears that the auto increment is working better after a server restart.

Something may be in a weird state after the dynamic changes.

[Laurynas Biveinis (laurynas-biveinis)]

The upstream bug was marked as a duplicate of https://bugs.mysql.com/bug.php?id=76872, which was closed as fixed in 5.6.35. Can you test?

[monty solomon (monty+launchpad)]

We are running 5.7 and haven't upgraded to 5.7.17 yet.

[monty solomon (monty+launchpad)]

This bug affects versions 5.6 and 5.7.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-3413