3

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?

  • Add the SHOW CREATE TABLE Clinic.ClientSupplementary; output in the question. – ypercubeᵀᴹ Aug 12 '15 at 16:02
  • 2
    You have a row with value 0 or negative. right? – ypercubeᵀᴹ Aug 12 '15 at 16:04
  • It seems like you have a duplicate value for idClientSupplementary. – oNare Aug 12 '15 at 16:38
  • Are there FOREIGN KEYs on your ClientSupplementary table? It sure looks like is. Could you also post the complete ALTER TABLE statement? – Vérace Aug 12 '15 at 16:50
  • 1
    @ypercube Deleting the meaningless row with a PK of 0 allowed me to set it to AI - problem solved. – MichaelJohn Aug 13 '15 at 8:34
2

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.

  • please see my comment in the question. MySQL is indeed fussy about syntax and often does not give meaningful error messages! Upvote given. – MichaelJohn Aug 12 '15 at 16:59
  • @Vérace - Since you started with 1,2,3, we can't tell if the ALTER renumbered them (to 1,2,3) or not. – Rick James Aug 17 '15 at 21:14
  • I tried -- numbers > 0 are preserved; 0 and NULL are given new (high) numbers -- but high for the moment. Hence, if 0 turns into 1 and then you try to insert 1, you get dup key. – Rick James Aug 17 '15 at 21:21
  • Exactly what happened... – MichaelJohn Aug 19 '15 at 17:08
0

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;
0

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...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.