Set AUTO_INCREMENT on a column with missing values

Question

I have a table called users in my database. It has a field id of type int. As of now this is manually incremented for every user that registers and some intermediate values are missing because of deleted user accounts. I cannot change the user id of other registered users . I tried to change this column to AUTO_INCREMENT using this statement

ALTER TABLE `userinfo` CHANGE `id` `id` BIGINT(20) NOT NULL AUTO_INCREMENT;

But I got the following error

1062 - ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'

I only have around 200 users in my table. So I wanted to start AUTO_INCREMENT from 201. I executed the following statements

ALTER TABLE `userinfo` AUTO_INCREMENT=201;
ALTER TABLE `userinfo` CHANGE `id` `id` INT(10) NOT NULL AUTO_INCREMENT;

But still I encounter the same issue.

@Barmar The answer for the question you are referring to works if there no missing values in the column I have tried whats given there but it does not work. I get the same error. — , Apr 08 '17 at 08:50
See http://stackoverflow.com/questions/5402949/mysql-cant-make-column-auto-increment — Barmar, Apr 08 '17 at 08:52

Ramin Darvishov · Answer 1 · 2017-04-08T09:36:02.897

2

You can just drop auto_increment then set auto_increment, do not need recreate primary key

ALTER TABLE `userinfo` CHANGE `id` `id` int NOT NULL;
ALTER TABLE `userinfo` AUTO_INCREMENT=9;
ALTER TABLE `userinfo` CHANGE `id` `id` int NOT NULL AUTO_INCREMENT;

edited Apr 08 '17 at 09:36

answered Apr 08 '17 at 09:23

Ramin Darvishov

1,043
1
15
30

score 1 · Answer 2 · answered Apr 08 '17 at 08:54

1

Try resetting the column to primary key, like this:

ALTER TABLE `userinfo` CHANGE `id` `id` INT(10) PRIMARY KEY AUTO_INCREMENT;

answered Apr 08 '17 at 08:54

SaraFlower

755
1
8
15

wchiquito · Accepted Answer · 2017-04-08T11:40:46.783

If you don't have duplicates (id), you should not have problems:

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.17    |
+-----------+
1 row in set (0.00 sec)

mysql> DROP TABLE IF EXISTS `userinfo`;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE IF NOT EXISTS `userinfo` (
    ->   `id` INT(11) NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `userinfo`
    ->   (`id`)
    -> VALUES
    ->   (1),
    ->   (2),
    ->   (3),
    ->   -- (1),
    ->   (10),
    ->   (11),
    ->   (15),
    ->   (20),
    ->   (182),
    ->   (191);
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `userinfo`
    ->   CHANGE `id` `id` BIGINT(20) NOT NULL
    ->   PRIMARY KEY AUTO_INCREMENT;
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `userinfo` AUTO_INCREMENT = 201;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE `userinfo`\G
*************************** 1. row ***************************
       Table: userinfo
Create Table: CREATE TABLE `userinfo` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

In another case:

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.17    |
+-----------+
1 row in set (0.00 sec)

mysql> DROP TABLE IF EXISTS `userinfo`;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE IF NOT EXISTS `userinfo` (
    ->   `id` INT(11) NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `userinfo`
    ->   (`id`)
    -> VALUES
    ->   (1),
    ->   (2),
    ->   (3),
    ->   (1), -- Duplicate
    ->   (10),
    ->   (11),
    ->   (15),
    ->   (20),
    ->   (182),
    ->   (191);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `userinfo`
    ->   CHANGE `id` `id` BIGINT(20) NOT NULL
    ->   PRIMARY KEY AUTO_INCREMENT;
ERROR 1062 (23000): ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'

this is wrong in my experience (mysql 5.7.28) the resequencing fails when there are gaps in the inserted values if the insert is a bulk insert statement - not sure if it matters that the rest of the schema is more complex, but my experience seems counter to this example. I think I have found a solution however, see my answer. — user6096790, Dec 02 '19 at 15:00
@user6096790: I don't understand exactly the problem you mention. See a complete example that may be helpful [dbfiddle](https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=be678ac7b8c0737500cac61528d4fedc). — wchiquito, Dec 07 '19 at 16:09
in my case, I was trying to import mysqldump data from a table whose primary key field has missing sequential values. The import failed with the above mentioned error even though, of course, there were no duplicates in the source primary key field. So your example did not fit my experience as the resequencing error was occurring even when there were not duplicate values. Basically, my experience does not fit your statement that "If you don't have duplicates (id), you should not have problems". — user6096790, Dec 08 '19 at 21:41

score 0 · Answer 4 · answered Dec 02 '19 at 15:22

I was having this same problem/error (MySql 5.7.28) with bulk inserts of data that was missing primary key/auto_increment values - even though there were no duplicates. I wanted to preserve the primary key and needed to preserve the original field values also as resequencing would have caused data-integrity problems in the system.

My solution was to remove the auto_increment and then primary key as suggested above:

ALTER TABLE `userinfo` CHANGE `id` `id` int NOT NULL;
ALTER TABLE `userinfo` DROP PRIMARY KEY;

Then run my bulk insert statement without resequencing problems.

Then restore the primary key and original values using the following steps:

renaming the original PK/auto-increment field which now has the desired values in it
creating a new PK/auto-increment field with the original field name
setting the auto-increment value to one more than the max value of the renamed field
copying the original values into the new PK/auto-increment field
dropping the renamed field (now you have the original schema with PK/auto-increment field with original gaps in the sequence as desired.

Here is the sql for the first two steps:

ALTER TABLE `userinfo` CHANGE `id` `orig_id` INT;
ALTER TAble `userinfo` add id int auto_increment not null primary key;

Then use the following query result:

select max(orig_id) + 1 from `userinfo`;

to set the new auto-increment, before updating the new PK/auto-increment values:

ALTER TABLE `userinfo` AUTO_INCREMENT=201;
UPDATE `userinfo` set id = orig_id;
ALTER TABLE `userinfo` drop column orig_id;

and now I've got the original table values copied exactly as wanted. I hope this helps others save time.

Set AUTO_INCREMENT on a column with missing values

4 Answers4