HOW TO SOLVE ERROR 1062 (23000) ?

HOW TO SOLVE ERROR 1062 (23000) ?


CAUSE
a)ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
b)ERROR 1062 (23000): ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'


SOLUTION
a)ALTER TABLE `animals` CHANGE `id` `id` MEDIUMINT UNSIGNED auto_increment; 
b)Primary key column values set 1 starting




1.create case table and insert into data


mysql> CREATE TABLE animals (
    ->      id MEDIUMINT NOT NULL AUTO_INCREMENT,
    ->      name CHAR(30) NOT NULL,
    ->      PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.04 sec)


mysql> INSERT INTO animals (name) VALUES
    ->     ('dog'),('cat'),('penguin'),
    ->     ('lax'),('whale'),('ostrich');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0


mysql> SELECT * FROM animals;
+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+
6 rows in set (0.00 sec)


 1.1 show create table statement
mysql> show create table animals ;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                   |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| animals | CREATE TABLE `animals` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT ,
  `name` char(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


 1.2 show next Auto_increment value
mysql> show table status like 'animals' \G
*************************** 1. row ***************************
           Name: animals
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 6
 Avg_row_length: 2730
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 7
    Create_time: 2016-03-24 11:19:03
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)


2.DDL and change id column,table definition is changed.


mysql> ALTER TABLE `animals` CHANGE `id` `id` INT; 
Query OK, 6 rows affected (0.09 sec)
Records: 6  Duplicates: 0  Warnings: 0


mysql> show create table animals ;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                          |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| animals | CREATE TABLE `animals` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` char(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


2.1 DDL alter and Auto_increment is change to NULL
mysql> show table status like 'animals' \G
*************************** 1. row ***************************
           Name: animals
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 6
 Avg_row_length: 2730
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
  Auto_increment: NULL
    Create_time: 2016-03-24 11:23:20
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)
mysql> INSERT INTO animals (name) VALUES('doudou01');
Query OK, 1 row affected (0.05 sec)


mysql> SELECT * FROM animals;
+----+----------+
| id | name     |
+----+----------+
|  0 | doudou01 |
|  1 | dog      |
|  2 | cat      |
|  3 | penguin  |
|  4 | lax      |
|  5 | whale    |
|  6 | ostrich  |
+----+----------+
7 rows in set (0.00 sec)


3.ERROR 1062 (23000) and solve problem using 'UNSIGNED'


mysql> INSERT INTO animals (name) VALUES('doudou02');
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
mysql> ALTER TABLE `animals` CHANGE `id` `id` MEDIUMINT UNSIGNED auto_increment; 
ERROR 1062 (23000): ALTER TABLE causes auto_increment resequencing , resulting in duplicate entry '1' for key 'PRIMARY'
## id=0 is not accord with auto_increment resequencing,and auto_increment default 1 .


mysql> select  LAST_INSERT_ID() ;
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)


mysql>  SELECT * FROM animals;
+----+----------+
| id | name     |
+----+----------+
|  0 | doudou01 |
|  1 | dog      |
|  2 | cat      |
|  3 | penguin  |
|  4 | lax      |
|  5 | whale    |
|  6 | ostrich  |
+----+----------+
7 rows in set (0.00 sec)


mysql> INSERT INTO animals (id,name) VALUES(7,'doudou01');
Query OK, 1 row affected (0.01 sec)


3.1 set id column is accord with auto_increment resequencing
mysql> delete from animals where id=0;
Query OK, 1 row affected (0.01 sec)


3.2 solve 'ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY''
mysql>   ALTER TABLE `animals` CHANGE `id` `id` MEDIUMINT UNSIGNED auto_increment; 
Query OK, 7 rows affected (0.16 sec)
Records: 7  Duplicates: 0  Warnings: 0


mysql> SELECT * FROM animals;
+----+----------+
| id | name     |
+----+----------+
|  1 | dog      |
|  2 | cat      |
|  3 | penguin  |
|  4 | lax      |
|  5 | whale    |
|  6 | ostrich  |
|  7 | doudou01 |
+----+----------+
7 rows in set (0.01 sec)


mysql> show create table animals ;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                            |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| animals | CREATE TABLE `animals` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> 
mysql> show table status like 'animals' \G
*************************** 1. row ***************************
           Name: animals
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 7
 Avg_row_length: 2340
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 8
    Create_time: 2016-03-24 11:29:52
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)
mysql> select  LAST_INSERT_ID() ;
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)


mysql> INSERT INTO animals (name) VALUES('doudou02');
Query OK, 1 row affected (0.00 sec)


mysql> select  LAST_INSERT_ID() ;
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                8 |
+------------------+
1 row in set (0.00 sec)


mysql> select * from animals ;
+----+----------+
| id | name     |
+----+----------+
|  1 | dog      |
|  2 | cat      |
|  3 | penguin  |
|  4 | lax      |
|  5 | whale    |
|  6 | ostrich  |
|  7 | doudou01 |
|  8 | doudou02 |
+----+----------+
8 rows in set (0.00 sec)


4.Problem is solved.


########################################################################################
版權所有，文章允許轉載，但必須以連結方式註明源地址，否則追究法律責任!【QQ交流群：53993419】
QQ：14040928 E-mail：dbadoudou@163.com
本文連結： http://blog.itpub.net/26442936/viewspace-2063150/
########################################################################################




--------------------------------------------------------------------------------------------------------------------------------------
5.case scripts


CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);
INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');
SELECT * FROM animals;
show create table animals ;
show table status like 'animals' \G
ALTER TABLE `animals` CHANGE `id` `id` INT; 
show create table animals ;
show table status like 'animals' \G
INSERT INTO animals (name) VALUES('doudou01');
INSERT INTO animals (name) VALUES('doudou02');
ALTER TABLE `animals` CHANGE `id` `id` MEDIUMINT UNSIGNED auto_increment; 
select  LAST_INSERT_ID() ;
SELECT * FROM animals;
show create table animals ;
show table status like 'animals' \G
INSERT INTO animals (id,name) VALUES(7,'doudou01');
delete from animals where id=0;
ALTER TABLE `animals` CHANGE `id` `id` MEDIUMINT UNSIGNED auto_increment; 
show create table animals ;
show table status like 'animals' \G
INSERT INTO animals (name) VALUES('doudou02');
select  LAST_INSERT_ID() ;
INSERT INTO animals (name) VALUES('doudou02');
SELECT * FROM animals;

--------------------------------------------------------------------------------------------------------------------------------------------------