Why does MySQL flag a duplicate key where none exists?

Question

I'm working on adding an auto-increment field to a large table.

The field is already populated (roughly 1-500M) and new inserts are manually incrementing correctly. To verify, I ran a query to show any duplicates:

mysql> SELECT new_id, COUNT(*) AS count FROM my_table GROUP BY new_id HAVING count > 1;
# No Results

And when I manually look at the latest records I see what looks like an auto-incrementing BIGINT(20) field. And no NULL's exist in that column.

Yet when I try to change the index to a UNIQUE INDEX (from non-unique index, but with unique values), it triggers a DUPLICATE KEY error. And the duplicate key is always MAX(new_id)+1, so a SELECT for the offending duplicate always returns no results.

Here's what I'm running:

mysql> SELECT MAX(new_id) FROM my_table;
+-------------+
| MAX(new_id) |
| 512345678 |
+-------------+
1 row in set (0.00 sec)
mysql> ALTER TABLE my_table
 DROP INDEX `idx_new_id`,
 ADD UNIQUE INDEX `idx_new_id ` USING BTREE (`new_id `);
ERROR 1062 (23000): Duplicate entry '512345679' for key 'idx_new_id'

But why? This record doesn't even exist! The MAX is 1 less than this and hasn't changed since before the query. So I prove it...

mysql> SELECT MAX(new_id) FROM my_table;
+-------------+
| MAX(new_id) |
| 512345678 |
+-------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM my_table WHERE new_id = 512345679;
# No results

Why did MySQL flag 512345679 as a duplicate if record 512345679 doesn't even exist?

I am so far unsuccessful in duplicating the problem from scratch. (db-fiddle)

Can you point me where to look next?

Ultimately I'm headed toward changing the primary key from another field to this one. But am trying to prevent downtime by pre-populating the unique ids (already done). If I am successful with this step I'll switch this new_id field to the PRIMARY KEY and AUTOINCREMENT it.

---

Here's the SHOW CREATE for my_table:

CREATE TABLE `my_table` (
 `new_id` bigint(20) NOT NULL,
 `id` bigint(20) NOT NULL,
 `username` varchar(80) NOT NULL,
 `name` varchar(100) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_name` (`name`),
 KEY `idx_username` (`username `),
 KEY `idx_new_id` (`new_id`) USING BTREE,
 KEY `idx_id` (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

• Philᵀᴹ: What's the real ID number? I refuse to believe it's 512345678 ... Too much of a coincidence. The reason I ask is that you have probably hit a datatype limit
• Ryan: Right @PhilTM. Of course it's not really a 5 followed by 12345678, but it's also not a datatype limit issue. The datatype is BIGINT(20) and I'm manually incrementing just fine. Also, in order to debug the above, I stopped all write queries from reaching the table. For kicks, here's the actual id: 559328724. Wait, now it's 559328923. ;)
• thatsaru: Can you post results from show create table my_table.
• RolandoMySQLDBA: What version of MySQL were you using when you got that error ??? Please run SELECT VERSION()
• Ryan: @RolandoMySQLDBA 5.7.13. It was in the tags, but I hope that helps.

Answer 1

I tried running your code in MySQL 5.7.12 for MacOS. I can't seem to duplicate this, but I found something interesting.

SQL-Fiddle has MySQL 5.7.17. You got the error in MySQL 5.7.13. Was anything with regard to dropping and adding a secondary index fixed between those versions ? I believe it was. Please note the Release Notes for 5.7.14, bulletpoint 21 under the subheading Bugs Fixed:

InnoDB: An assertion was raised during rollback of an ALTER TABLE operation that dropped and added a secondary index. (Bug #22005726)

Question: What is a secondary index ? According to the MySQL Documentation for Clustered and Secondary Indexes right under the heading How Secondary Indexes Relate to the Clustered Index:

All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.

This show that a UNIQUE INDEX is just a secondary index. A secondary index would have a copy of the PRIMARY KEY attached to every index entry.

My assertion is that a bug was introduced in 5.7.13 that did not exist in 5.7.12 when it comes to dropping and creating an index on the same column(s) at the same time.

Look back at your ALTER TABLE command

mysql> ALTER TABLE my_table
 DROP INDEX `idx_new_id`,
 ADD UNIQUE INDEX `idx_new_id` USING BTREE (`new_id `);

You dropped an index with a specific index name (idx_new_id) and with a specific column list (just one column, new_id) and then said add a new index with the same name and the same column list but make it the index UNIQUE. The net effect is that the index was never really dropped and the UNIQUE attribute was simply attached to the already existing non-unique index. Then, under the hood, mysqld attempted to load data into the table which is already populated and an index that's still populated, thus creating a duplicate key error. Please note I am only asserting this. I would have to look at source code for 5.7.13 to see if this is indeed occurring.

Notwithstanding, 5.7.12 and 5.7.14 cannot reproduce this problem. This indicates that this strange condition was addressed when making 5.7.14.

In light of all my conjecture, here are my suggestions:

SUGGESTIONS

SUGGESTION #1 : Upgrade to 5.7.14 or above

SUGGESTION #2 : Change the ALTER TABLE to use a different index name

mysql> ALTER TABLE my_table
 DROP INDEX `idx_new_id`,
 ADD UNIQUE INDEX `uniq_idx_new_id` USING BTREE (`new_id`);

SUGGESTION #3 : Do 2 ALTER TABLE commands

mysql> ALTER TABLE my_table DROP INDEX `idx_new_id`;
mysql> ALTER TABLE my_table ADD UNIQUE INDEX `uniq_idx_new_id` USING BTREE (`new_id`);

GIVE IT A TRY !!!

• Ryan: Trying out your suggestions now. Thanks so much for working on this.
• Ryan: Suggestion #2 did not work. Trying #3 now. The drop worked instantly (as expected, but I'm hoping the add unique index still works within an acceptable time.