MySQL - Unable to Convert Primary Key to Auto Increment

Question

I have a MySQL table where the Primary Key (int) was set by the software to be equal to the Primary Key (auto incremented) of the main table. Because of a change in design I now need the field to auto increment. When I tried to ALTER TABLE it came back with the following:-

ERROR 1062: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'
SQL Statement:
ALTER TABLE `Clinic`.`ClientSupplementary` CHANGE COLUMN `idClientSupplementary` `idClientSupplementary` INT(11) NOT NULL AUTO_INCREMENT

ERROR: Error when running failback script. Details follow.

ERROR 1050: Table 'ClientSupplementary' already exists

This has me totally puzzled as '1' is definitely unique as are all the other Primary Key values. Where should I be looking?

Answer 1

Your post intrigued me. There is a "workaround" here, but I was curious, so I performed the following tests. You might like to try what worked for me below before trying the workaround. If there are FOREIGN KEYs, and potential conflicts (duplicates and/or KEY violations), then this won't work. It may be the reason for your error.

I created a table (bill) as follows.

mysql> CREATE TABLE bill(fred INT, paul VARCHAR(10));
Query OK, 0 rows affected (0.31 sec)

Then inserted some sample values.

mysql> INSERT INTO bill VALUES(1, 'adsfd');
    Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO bill VALUES(2, 'adsfd');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO bill VALUES(3, 'xxxx');
Query OK, 1 row affected (0.05 sec)

I then performed a SHOW CREATE TABLE My_Table\G (the \G gives clear output that can be cut and pasted).

mysql> SHOW CREATE TABLE bill\G
*************************** 1. row ***************************
       Table: bill
Create Table: CREATE TABLE `bill` (
  `fred` int(11) DEFAULT NULL,
  `paul` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Just to see the results of my INSERTs

mysql> SELECT * FROM bill;
+------+-------+
| fred | paul  |
+------+-------+
|    1 | adsfd |
|    2 | adsfd |
|    3 | xxxx  |
+------+-------+
3 rows in set (0.00 sec)
mysql> 

Now, issue your ALTER TABLE statement.

ALTER TABLE bill MODIFY COLUMN fred INT AUTO_INCREMENT NOT NULL PRIMARY KEY;

Recheck the definition of your table.

mysql> SHOW CREATE TABLE bill\G
*************************** 1. row ***************************
       Table: bill
Create Table: CREATE TABLE `bill` (
  `fred` int(11) NOT NULL AUTO_INCREMENT,
  `paul` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`fred`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

And the field fred is indeed now a PRIMARY KEY and also an AUTO_INCREMENT.

And to check that it's working.

mysql> INSERT INTO bill (paul) VALUES('yyyy');
Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM bill;
+------+-------+
| fred | paul  |
+------+-------+
|    1 | adsfd |
|    2 | adsfd |
|    3 | xxxx  |
|    4 | yyyy  |
+------+-------+
4 rows in set (0.00 sec)

So, it's also AUTO_INCREMENTing correctly. I'm not sure why your original statement gave the strange ALTER TABLE causes auto_increment resequencing error - maybe a bug of some sort? While performing these tests, I made a number of small errors - MySQL appears to be very fussy about the exact order of steps and the particular syntax of the ALTER TABLE statement.

Answer 2

Assuming you have no references to fred elsewhere, this may be the 'safe' way:

ALTER TABLE bill
    DROP COLUMN fred,
    ADD  COLUMN fred INT AUTO_INCREMENT NOT NULL PRIMARY KEY;

Answer 3

Whist there were a number of useful comments and answers as mentioned in a previous comment of mine the problem was caused a rougue entry which would have corresoned to a PK of zero!. Deleting this solved the problem...

How it got there is the issue...