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;
Best Answer
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
: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: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
commandYou 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 indexUNIQUE
. The net effect is that the index was never really dropped and theUNIQUE
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 nameSUGGESTION #3 : Do 2
ALTER TABLE
commandsGIVE IT A TRY !!!