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?
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
SHOW CREATE for
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;