Error 1062 Alter Table Causes Auto_increment Resequencing
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings duplicate entry '1' for key 'primary' and policies of this site About Us Learn more about Stack
#1062 - Duplicate Entry '0' For Key 'primary'
Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs create table auto_increment Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them;
Mysql Set Auto_increment
it only takes a minute: Sign up MySql can't make column auto_increment up vote 20 down vote favorite 10 I have a table "Bestelling" with 4 columns: "Id" (PK), "KlantId", "Datum", "BestellingsTypeId", now I want to make the column Id auto_increment, however, when I try to do that, I get this error: ERROR 1062: ALTER TABLE causes auto_increment resequencing, resulting reset auto increment mysql in duplicate entry '1' for key 'PRIMARY' SQL Statement: ALTER TABLE `aafest`.`aafest_bestelling` CHANGE COLUMN `Id` `Id` INT(11) NOT NULL AUTO_INCREMENT ERROR: Error when running failback script. Details follow. ERROR 1046: No database selected SQL Statement: CREATE TABLE `aafest_bestelling` ( `Id` int(11) NOT NULL, `KlantId` int(11) DEFAULT NULL, `Datum` date DEFAULT NULL, `BestellingstypeId` int(11) DEFAULT NULL, PRIMARY KEY (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Anyone got an idea? mysql sql mysql-error-1062 share|improve this question edited Apr 26 '11 at 3:30 OMG Ponies 198k36356415 asked Mar 23 '11 at 9:12 Sander Declerck 67041231 add a comment| 7 Answers 7 active oldest votes up vote 10 down vote accepted Edit: Don't know exactly how that would be caused, but I do have a workaround. First, create a new table like the old one: CREATE TABLE aafest_bestelling_new LIKE aafest_bestelling; Then change the column ALTER TABLE `aafest`.`aafest_bestelling_new` CHANGE COLUMN `Id` `Id` INT(11) NOT NULL AUTO_INCREMENT Dump in the new data: INSERT INTO aafest_bestelling_new (KlantId, Datum, BestellingTypeId) SELECT KlantId, Datum, BestellingTypeId FROM aafest_bestelling; Move the tables: RENAME TABLE aafest_bestelling TO aafest_bestelling_old, aafest_bestelling_new TO aafest_bestelling
FunctionsPL/SQLORA ERRORBugsScriptsToolsMySQLLinuxOtherContact Set auto_increment on existing table (MySQL) Set Auto Increment in existing table where the table have data mysql> select * from employee; +---------------------+-----------------------+----------------------------------+-------------+ | ID | FULLNAME | EMAIL | PHONENUMBER | +---------------------+-----------------------+----------------------------------+-------------+ | 1 | James Bond | james.bond@xyz.com | http://stackoverflow.com/questions/5402949/mysql-cant-make-column-auto-increment 46700000007 | | 2 | Mediline | mediline@xyz.com | 46724312313 | +---------------------+-----------------------+----------------------------------+-------------+ In this example case we need to set auto_increment value 3 ALTER TABLE employee AUTO_INCREMENT = 3; ALTER http://www.nazmulhuda.info/set-auto_crement-on-existing-table-mysql TABLE employee MODIFY ID int AUTO_INCREMENT; If you have value 0 on the column where you want to set auto_increment then you will get the following error: ERROR 1062 (23000): ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY' mysql> select * from employee; +---------------------+-----------------------+----------------------------------+-------------+ | ID | FULLNAME | EMAIL | PHONENUMBER | +---------------------+-----------------------+----------------------------------+-------------+ | 0 | Test | testtes@xyz.com | 123456 | | 1 | James Bond | cemilbedran.yildirim@xyz.com | 46700000007 | | 2 | Mediline | mediline@xyz.com | 46724312313 | +---------------------+-----------------------+
Milestone Drizzle Edit Fix Released High Stewart Smith Edit Drizzle aloha "aloha" You need to log in to change this bug's status. Affecting: Drizzle Filed here by: Aarti Pai When: 2009-01-07 Confirmed: 2009-02-01 Assigned: 2009-05-13 Started work: https://bugs.launchpad.net/bugs/314567 2009-05-15 Completed: 2009-06-30 Target Distribution Baltix BOSS Juju Charms Collection Elbuntu Guadalinex Guadalinex Edu Kiwi Linux nUbuntu PLD Linux Tilix tuXlab Ubuntu Ubuntu Linaro Evaluation Build Ubuntu RTM Package (Find…) Project (Find…) Status Importance Milestone Fix Released High Drizzle aloha Assigned to Me Stewart Smith (stewart) Comment on this change (optional) Email me about changes to this bug report Also affects project (?) Also affects distribution/package Nominate for series Bug Description duplicate entry broken.auto_increment [ fail ] drizzletest: At line 200: query 'alter table t1 modify a bigint not null auto_increment primary key' failed: 1062: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY' The result from queries just before the failure was: < snip > 1 2 3 4 5 drop table t1; create table t1 (a bigint); insert into t1 values (1), (2), (3), (0), (0); alter table for key 'primary' t1 modify a bigint not null auto_increment primary key; select * from t1; a 1 2 3 4 5 drop table t1; create table t1 (a bigint); insert into t1 values (0), (1), (2), (3); alter table t1 modify a bigint not null auto_increment primary key; Add tags Tag help Padraig O'Sullivan (posulliv) wrote on 2009-02-01: #1 Confirmed on trunk: TEST RESULT TIME (ms) ------------------------------------------------------- main.auto_increment [ fail ] drizzletest: At line 190: query 'alter table t1 modify a bigint not null auto_increment primary key' failed: 1146: Table 'test.t1' doesn't exist The result from queries just before the failure was: < snip > select * from t1; a 1 2 3 4 5 drop table t1; create table t1 (a bigint); insert into t1 values (1), (2), (3), (0), (0); alter table t1 modify a bigint not null auto_increment primary key; select * from t1; a 1 2 3 4 5 drop table t1; alter table t1 modify a bigint not null auto_increment primary key; It seems like you are already aware of this since this bug is referenced in the auto_increment.test test case: # Bug314567 - ALTER TABLE causes auto_increment resequencing, # resulting in duplicate entry since sql_mode=NO_AUTO_VALUE_ON_ZERO #is not supported. #alter table t1 modify a bigint not null auto_increment p