MySQL – Unable to Convert Primary Key to Auto Increment

MySQLmysql-workbenchprimary-key

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?

Best Answer

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.

Related Solutions

Mysql – Drop auto increment hack w/o alter table

I tried something similar just now

Here is MySQL for My PC

mysql> select * from information_schema.global_variables where variable_name='datadir' or variable_name like 'versio%';
+-------------------------+------------------------------+
| VARIABLE_NAME           | VARIABLE_VALUE               |
+-------------------------+------------------------------+
| VERSION_COMMENT         | MySQL Community Server (GPL) |
| VERSION                 | 5.5.12-log                   |
| VERSION_COMPILE_MACHINE | x86                          |
| DATADIR                 | C:\MySQL_5.5.12\data\        |
| VERSION_COMPILE_OS      | Win64                        |
+-------------------------+------------------------------+
5 rows in set (0.00 sec)

I will run this using MyISAM

Step 01) create a table called 'rolando'
Step 02) insert 'dominique' and 'diamond'
Step 03) copy the table structure to 'pamela'
Step 04) alter 'pamela' to not have auto_increment
Step 05) In DOS, copy rolando.MYD to pamela.MYD
Step 06) run REPAIR TABLE pamela; (Rebuild pamela.MYI)
Step 07) run SELECT COUNT(1) FROM pamela;
Step 08) run SHOW CREATE TABLE pamela\G
Step 09) run SELECT * FROM pamela;
Step 10) insert 'carlik' into pamela
Step 11) run SELECT * FROM pamela;

Let's see if these steps are kosher.

Here are Steps 1-4

mysql> drop table if exists rolando;
Query OK, 0 rows affected (0.02 sec)

mysql> drop table if exists pamela;
Query OK, 0 rows affected (0.00 sec)

mysql> create table rolando
    -> (
    ->     name varchar(20),
    ->     id int not null auto_increment,
    ->     primary key (id)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into rolando (name) values ('dominique'),('diamond');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from rolando;
+-----------+----+
| name      | id |
+-----------+----+
| dominique |  1 |
| diamond   |  2 |
+-----------+----+
2 rows in set (0.00 sec)

mysql> create table pamela like rolando;
Query OK, 0 rows affected (0.05 sec)

mysql> show create table rolando\G
*************************** 1. row ***************************
       Table: rolando
Create Table: CREATE TABLE `rolando` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table pamela\G
*************************** 1. row ***************************
       Table: pamela
Create Table: CREATE TABLE `pamela` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

mysql> alter table pamela modify id int(11) unsigned not null;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table pamela\G
*************************** 1. row ***************************
       Table: pamela
Create Table: CREATE TABLE `pamela` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

mysql>

Here is Step 6

C:\>copy C:\MySQL_5.5.12\data\test\rolando.MYD C:\MySQL_5.5.12\data\test\pamela.MYD
        1 file(s) copied.

C:\>

Here are the rest of the Steps starting at Step 7

mysql> repair table pamela;
+-------------+--------+----------+------------------------------------+
| Table       | Op     | Msg_type | Msg_text                           |
+-------------+--------+----------+------------------------------------+
| test.pamela | repair | warning  | Number of rows changed from 0 to 2 |
| test.pamela | repair | status   | OK                                 |
+-------------+--------+----------+------------------------------------+
2 rows in set (0.03 sec)

mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> insert into pamela (name,id) values ('carlik',3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from pamela;
+-----------+----+
| name      | id |
+-----------+----+
| dominique |  1 |
| diamond   |  2 |
| carlik    |  3 |
+-----------+----+
3 rows in set (0.00 sec)

mysql>

Dangerous game, isn't it ???

Guess what? Stuff like this is actually published in "High Performance MySQL : Optimization, Backups, Replication, and more", Pages 146-148 under the Subheading Speeding Up ALTER TABLE. Page 147 Paragraph 1 says:

The technique we are about to demonstrate is unsupported, undocumented, and may not work. Use it at your risk. We advise you to back up you data first!

I also had an earlier post when someone ask a similar question : Can I rename the values in a MySQL ENUM column in one query?

You got guts, @atxdba !!!

Mysql – Getting duplicate primary key for auto increment column

I reached the max possible value of an int column. Problem resolved by altering table and setting big int than int.

The signed range of MIDINT is –8388608 to 8388607. The unsigned range is 0 to 16777215

Reference: http://help.scibit.com/mascon/masconMySQL_Field_Types.html

Best Answer

Related Solutions

Mysql – Drop auto increment hack w/o alter table

Mysql – Getting duplicate primary key for auto increment column

Related Question