[MySQL] auto_increment duplicate entry for key 1

평소와 동일하게 Auto_Incremental 컬럼이 포함된 테이블에 Insert 를 할 때 다음과 같은 에러가 발생하는 경우가 생겼다.

ERROR 1062 (23000): Duplicate entry '11522886' for key 1

흠...스크립트는 아래와 같다.

CREATE TABLE `TimeLimitItems` (

  `ID` int(10) NOT NULL auto_increment,

  `World` enum('DRAC','GENT') NOT NULL default 'DRAC',

  `OwnerID` char(20) NOT NULL,

  `ItemClass` smallint(10) NOT NULL default '0',

  `ItemID` int(10) NOT NULL default '0',

  `LimitDateTime` datetime NOT NULL default '2003-04-04 12:00:00',

  `Status` tinyint(3) NOT NULL default '0',

  PRIMARY KEY  (`ID`),

  KEY `TimeLimitItems_m1` (`OwnerID`),

  KEY `TimeLimitItems_m2` (`ItemClass`,`ItemID`)

) AUTO_INCREMENT=11522885 ;

auto_increment 값이 위의 값과 다른 것을 확인할 수 있다.

즉, Duplicate 가 생길 수 없는 것이다.

혹시나 하여 해당 ID 값을 조회 했지만 해당 하는 값이 존재하지 않는 것으로 나왔다.

여러가지 검색을 통해 해결 방법을 나열해 봤다.

하지만 내가 해결한 방법은 간단하게 테이블 repair 를 진행한 것이다.

먼저 내가 해결한 방법이다.

repair table TimeLimitItems;

아래 방법들로 진행을 하고자 노력했지만 해결되지 않았고..테이블에 대해 문제가 생겼을 것으로 판단하여

복구를 진행한 것 뿐이다.

이것이 해결한 방법이다. (운이 좋았나....)

다음은 검색을 통한 방법 공유다.

---

[출처] http://ktdsoss.tistory.com/389

MySQL을 AUTO_INCREMENT 기능은 기본 키 값을 증가의 자동 시퀀스를 생성 할 수있는 가장 쉬운 방법입니다.

 너무 사용하기가 매우 쉽습니다 :

create table t (
    i integer auto_increment,
    other_field varchar(100),
    primary key(id)
);

 AUTO_INCREMENT를 사용할 때 염두에 두어야 할 몇 가지 기능.

AUTO_INCREMENT는 결코 테이블에 이미 존재한 미만의 값을 제공하지는 않습니다. 

명시 적으로 더 높은 값으로 ID를 추가 할 경우, AUTO_INCREMENT에 의해 생성되는 다음 값은 하나 더 높은 값일 것입니다. 

이 말은 간격이 있다는 것을 의미합니다. 

이것은 어떤 이유로 필드의 가능한 최대 값이 삽입되어있는 경우, 다음과 같은 삽입이 실패 할 것을 의미합니다 :

mysql> insert into t values (2147483647);
Query OK, 1 row affected (0.05 sec)
mysql> insert into t values ();
ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'

 AUTO_INCREMENT counter를 재 설정 하는 방법

값은 테이블과 연관된 카운터에 의해 생성되고, 카운터는 증가값을 가집니다. 

그 경우를 제외하고 때때로 당신은 오래된 값으로 카운터를 재설정 할 수 있습니다.

alter table t auto_increment = 0;

당신은 자신이 DELETE로 테스트 테이블을 비우는 후이 일을 찾을 경우, TRUNCATE 명령문을 다음 번 사용해보십시오. 

그것은 모든 데이터를 삭제하고 자동 증가 카운터를 재설정합니다.

기록을 삭제하는 경우, AUTO_INCREMENT 는 서버 재시작으로 다시 점프할 수 있다.

InnoDB의 스토리지 엔진에서는 자동 증분 카운터는 디스크가 아닌 메모리에 저장됩니다.

 이 자동 증가 카운터가 MySQL을 다시 시작할 때 열이 최대 값으로 초기화되는 것을 의미합니다.

이것은 또 다른에서 삭제 된 데이터를 보관하는 하나의 테이블을 사용하는 응용 프로그램에 문제가 될 수 있습니다. : 갑자기 ID가 반복되는 경우

테이블은 하나의 AUTO_INCREMENT 값을 가지고, 그 값은 key가 된다.

자동 증가 컬럼은 기본 키가 될 필요가 없습니다;

그것은에 어떤 종류의 인덱스가 있다는 것을 충분합니다 심지어 고유 인덱스 일 필요는 없습니다.

MyISAM과 BDB 스토리지 엔진에서는 다중 열 인덱스에 보조 컬럼과 같이 자동 증가 열을 가질 수 있으며, 다음 자동 증가는 각 그룹에 대해 1에서 시작합니다

create temporary table t (
    v varchar(100), 
    i integer(2) auto_increment, 
    primary key(v, i)
) engine=myisam;
insert into t (v) values ('a'),('a'),('b'),('b'),('b');
select * from t;
+---+---+
| v | i |
+---+---+
| a | 1 |
| a | 2 |
| b | 1 |
| b | 2 |
| b | 3 |
+---+---+

BY LEE JI EUN

또 다른 방법이다.

---

[출처] http://jason-heo.github.io/mysql/2014/03/05/manage-dup-key2.html

MySQL 중복 키 관리 방법 (INSERT 시 중복 키 관리 방법 (INSERT IGNORE, REPLACE INTO, ON DUPLICATE UPDATE)

안내

본 문서는 블로그의 운영자인 본인이 Stackoverflow에 올린 답변을 정리한 글입니다. Stackoverflow에 올린 답변 중 한국에 있는 다른 개발자들에게도 도움이 될만한 Q&A를 보기 쉽게 정리했습니다. 가능한 경우는 SQLFiddle에 샘플 데이터도 같이 올려서 실습도 해 볼 수 있도록 하였습니다. 또한 전체 Q&A를 묶어서 PDF 파일로도 배포하고 있습니다. 방문해 주시는 많은 분들에게 도움이 되었으면 좋겠습니다.

Stackoverflow URL

http://stackoverflow.com/questions/20342518/on-duplicate-key-update-value-inserting-same-values-twice/20342598

질문

다음과 같이 INSERT 구문을 사용 중이다.

INSERT INTO person VALUES(NULL, 15, 'James', 'Barkely')
    ON DUPLICATE KEY UPDATE academy_id = VALUES(academy_id);

중복된 값을 여러 번 INSERT한 뒤에 SELECT를 해 보면 중복된 값이 저장되어 있다.

mysql> SELECT * FROM person;
+----+------------+------------+-----------+
| id | academy_id | first_name | last_name |
+----+------------+------------+-----------+
|  1 |         15 | James      | Barkely   |
|  2 |         15 | Cynthia    | Smith     |
|  3 |         15 | James      | Barkely   |
|  4 |         15 | Cynthia    | Smith     |
|  5 |         15 | James      | Barkely   |
+----+------------+------------+-----------+
5 rows in set (0.00 sec)

무엇이 잘못된 것인가?

답변

중복 키 관리를 위해서는 중복 방지를 할 컬럼이 PRIMARY KEY이거나 UNIQUE INDEX이어야 한다. 질문자의 경우 first_name과 last_name의 조합을 이용하여 중복 관리를 하려는 것 같다. 따라서다음과 같이 last_name, last_name을 PRIMARY KEY로 설정하거나,

ALTER TABLE person ADD PRIMARY KEY (first_name, last_name) UNIQUE INDEX를 추가해야 한다.

ALTER TABLE person ADD UNIQUE INDEX (first_name, last_name)

INSERT 시 중복 키 관리를 위한 방법에는 다음과 같이 3가지가 있다.

1. INSERT IGNORE
2. REPLACE INTO …
3. INSERT INTO … ON DUPLICATE UPDATE

중복 키 관리는 본 책의 앞 부분에서 잠시 언급되었는데 각각의 특징을 좀 더 자세히 알아보도록 하자.

앞의 person 테이블을 다음과 같이 생성한 뒤에 테스트를 진행하였다.

CREATE TABLE person
(
  id INT NOT NULL AUTO_INCREMENT,
  academy_id INT,
  first_name VARCHAR(20),
  last_name VARCHAR(20),
  PRIMARY KEY (id),
  UNIQUE INDEX (first_name, last_name)
);

INSERT IGNORE

`INSERT IGNORE`는 중복 키 에러가 발생했을 때 신규로 입력되는 레코드를 무시하는 단순한 방법이다. 다음의 예를 보면 중복 키 에러가 발생했을 때 INSERT 구문 자체는 오류가 발생하지 않고, 대신'0 row affected'가 출력된 것을 볼 수 있다.

mysql> INSERT IGNORE INTO person VALUES (NULL, 15, 'James', 'Barkely');
Query OK, 1 row affected (0.00 sec)
 
mysql> INSERT IGNORE INTO person VALUES (NULL, 15, 'Cynthia', 'Smith');
Query OK, 1 row affected (0.00 sec)
 
mysql> INSERT IGNORE INTO person VALUES (NULL, 15, 'James', 'Barkely');
Query OK, 0 rows affected (0.00 sec)
 
mysql> INSERT IGNORE INTO person VALUES (NULL, 15, 'Cynthia', 'Smith');
Query OK, 0 rows affected (0.00 sec)
 
mysql> INSERT IGNORE INTO person VALUES (NULL, 15, 'James', 'Barkely');
Query OK, 0 rows affected (0.00 sec)

당연히 SELECT의 결과는 2건만 존재한다.

mysql> SELECT * FROM person;
+----+------------+------------+-----------+
| id | academy_id | first_name | last_name |
+----+------------+------------+-----------+
|  1 |         15 | James      | Barkely   |
|  2 |         15 | Cynthia    | Smith     |
+----+------------+------------+-----------+
2 rows in set (0.00 sec)

AUTO_INCREMENT 컬럼의 값이 1, 2인 것에 주목하라

REPLACE INTO

"REPLACE INTO"는 "INSERT INTO" 구문에서 INSERT를 REPLACE로 바꾼 구문이다. 사용 방법은 "INSERT INTO"와 완벽히 동일하다.

mysql> REPLACE INTO person VALUES (NULL, 15, 'James', 'Barkely');
Query OK, 1 row affected (0.00 sec)
 
mysql> REPLACE INTO person VALUES (NULL, 15, 'Cynthia', 'Smith');
Query OK, 1 row affected (0.00 sec)
 
mysql> REPLACE INTO person VALUES (NULL, 15, 'James', 'Barkely');
Query OK, 2 rows affected (0.00 sec)
 
mysql> REPLACE INTO person VALUES (NULL, 15, 'Cynthia', 'Smith');
Query OK, 2 rows affected (0.00 sec)
 
mysql> REPLACE INTO person VALUES (NULL, 15, 'James', 'Barkely');
Query OK, 2 rows affected (0.00 sec)

REPLACE INTO의 결과는 INSERT IGNORE와 다르게 중복 키 오류 발생 시 ‘2 rows affected’가 출력되었다. SELECT 결과는 다음과 같다.

mysql> SELECT * FROM person;
+----+------------+------------+-----------+
| id | academy_id | first_name | last_name |
+----+------------+------------+-----------+
|  4 |         15 | Cynthia    | Smith     |
|  5 |         15 | James      | Barkely   |
+----+------------+------------+-----------+
2 rows in set (0.00 sec)

id가 4, 5로 변하였다. 이를 ‘2 rows affected’와 함께 종합적으로 판단한다면 “REPLACE INTO”는 중복 키 오류 발생 시 기존 레코드를 삭제하고 새로운 레코드를 입력한 것이다. 그래서 ‘2 rows affected’가 출력되었다. 1건은 DELETE, 1건은 INSERT로 보면 되고, 새로운 레코드가 입력되면서 AUTO_INCREMENT 컬럼의 값이 매번 새롭게 발급되었다.

AUTO_INCREMENT는 흔히 레코드를 식별할 수 있는 id로 사용되는데 이 값이 변경될 수 있으므로 “REPLACE INTO”는 그다지 좋은 방법이 아니다.

ON DUPLICATE UPDATE

ON DUPLICATE UPDATE는 중복 키 오류 발생 시 사용자가 원하는 값을 직접 설정할 수 있다는 장점이 있다. 우선 기본적인 사용 방법을 보자.

mysql> INSERT INTO person VALUES (NULL, 15, 'James', 'Barkely')
    ->     ON DUPLICATE KEY UPDATE academy_id = VALUES(academy_id);
Query OK, 1 row affected (0.00 sec)
 
mysql>
mysql> INSERT INTO person VALUES (NULL, 15, 'Cynthia', 'Smith')
    ->     ON DUPLICATE KEY UPDATE academy_id = VALUES(academy_id);
Query OK, 1 row affected (0.00 sec)
 
mysql>
mysql> INSERT INTO person VALUES (NULL, 15, 'James', 'Barkely')
    ->     ON DUPLICATE KEY UPDATE academy_id = VALUES(academy_id);
Query OK, 0 rows affected (0.00 sec)
 
mysql>
mysql> INSERT INTO person VALUES (NULL, 15, 'Cynthia', 'Smith')
    ->     ON DUPLICATE KEY UPDATE academy_id = VALUES(academy_id);
Query OK, 0 rows affected (0.00 sec)
 
mysql>
mysql> INSERT INTO person VALUES (NULL, 15, 'James', 'Barkely')
    ->     ON DUPLICATE KEY UPDATE academy_id = VALUES(academy_id);
Query OK, 0 rows affected (0.00 sec)

INSERT 결과만 보면 “INSERT IGNORE”와 동일하다.

mysql> SELECT * FROM person;
+----+------------+------------+-----------+
| id | academy_id | first_name | last_name |
+----+------------+------------+-----------+
|  1 |         15 | James      | Barkely   |
|  2 |         15 | Cynthia    | Smith     |
+----+------------+------------+-----------+
2 rows in set (0.00 sec)

SELECT 결과를 보니, 중복 키 오류 발생 시 기존 레코드는 그대로 남아 있는 것 같다. 즉, id 값이 변경되지 않았다. 그렇다면 “ON DUPLICATE UPDATE”는 “INSERT IGNORE” 대비 장점은 없을까?아니다. 복잡하고 어려운 대신에 중복 키 오류 발생 시 사용자가 원하는 행동을 지정할 수 있다는 장점이 있다. 예를 위해 person 테이블 구조를 다음과 같이 변경했다.

CREATE TABLE person
(
  id INT NOT NULL AUTO_INCREMENT,
  academy_id INT,
  first_name VARCHAR(20),
  last_name VARCHAR(20),
  insert_cnt INT,
  PRIMARY KEY (id),
  UNIQUE INDEX (first_name, last_name)
);

그런 뒤 다음과 같은 INSERT 구문을 실행하였다.

mysql> INSERT INTO person VALUES (NULL, 15, 'James', 'Barkely', 1)
    ->     ON DUPLICATE KEY UPDATE insert_cnt = insert_cnt + 1;
Query OK, 1 row affected (0.00 sec)
 
mysql>
mysql> INSERT INTO person VALUES (NULL, 15, 'Cynthia', 'Smith', 1)
    ->     ON DUPLICATE KEY UPDATE insert_cnt = insert_cnt + 1;
Query OK, 1 row affected (0.00 sec)
 
mysql>
mysql> INSERT INTO person VALUES (NULL, 15, 'James', 'Barkely', 1)
    ->     ON DUPLICATE KEY UPDATE insert_cnt = insert_cnt + 1;
Query OK, 2 rows affected (0.00 sec)
 
mysql>
mysql> INSERT INTO person VALUES (NULL, 15, 'Cynthia', 'Smith', 1)
    ->     ON DUPLICATE KEY UPDATE insert_cnt = insert_cnt + 1;
Query OK, 2 rows affected (0.00 sec)
 
mysql>
mysql> INSERT INTO person VALUES (NULL, 15, 'James', 'Barkely', 1)
    ->     ON DUPLICATE KEY UPDATE insert_cnt = insert_cnt + 1;

Query OK, 2 rows affected (0.00 sec) SELECT를 해 보면 insert_cnt에는 해당 중복 값이 몇 번 INSERT 시도가 되었는지 기록되어 있을 것이다.

mysql> SELECT * FROM person;
+----+------------+------------+-----------+------------+
| id | academy_id | first_name | last_name | insert_cnt |
+----+------------+------------+-----------+------------+
|  1 |         15 | James      | Barkely   |          3 |
|  2 |         15 | Cynthia    | Smith     |          2 |
+----+------------+------------+-----------+------------+
2 rows in set (0.00 sec)

이 외에도 다양한 용도로 활용될 수 있다.

주의해야 할 점은 INSERT 구문에 주어진 값으로 UPDATE하고자 할 때는 항상 “VALUES(column)”과 같이 VALUES()로 감싸야 한다는 점이다. 만약 다음과 같이 VALUES() 없이 사용한다면 기존에존재하는 레코드의 컬럼 값을 의미하게 된다.

INSERT INTO person VALUES (NULL, 15, 'James', 'Barkely')
    ON DUPLICATE KEY UPDATE academy_id = academy_id;

앞과 같이 사용했을 때, 기존 person 테이블에 존재하는 ‘James Barkely’의 academy_id가 13이었다면, INSERT 후에도 academy_id는 여전히 13이다.

요약

• INSERT IGNORE
• REPLACE INTO …
• INSERT INTO … ON DUPLICATE UPDATE