오류 발생 상황

데이터 삽입 과정에서 다음과 같은 예외가 발생했다:

INSERT INTO logs (log_id, message) VALUES (0, 'system boot');
-- ERROR: Duplicate entry '0' for key 'PRIMARY'

이후 해당 컬럼에 AUTO_INCREMENT 속성을 부여하려 했으나, 추가 오류가 발생했다:

ALTER TABLE logs MODIFY log_id INT AUTO_INCREMENT;
-- ERROR: 1062 - ALTER TABLE causes auto increment resequencing,
-- resulting in duplicate entry '1' for key 'PRIMARY'

원인 분석

문제는 두 가지 측면에서 발생한다. 첫째, 기존 레코드에 0이나 음수가 이미 존재하는 상태에서 MySQL의 자동 증가 메커니즘이 양수 1부터 값을 할당하려 하면 충돌이 일어난다. 둘째, AUTO_INCREMENT를 기존 테이블에 적용할 때 MySQL이 내부적으로 시퀀스를 재구성하면서 기존 데이터와 새로운 자동 증가 값 사이에 중복이 발생할 수 있다.

해결 절차

1단계: 현재 데이터 점검

-- 비정상적인 식별자 조회
SELECT log_id FROM logs WHERE log_id <= 0;

-- 중복 식별자 확인
SELECT log_id, COUNT(*) AS cnt 
FROM logs 
GROUP BY log_id 
HAVING cnt > 1;

2단계: 충돌 데이터 정제

비즈니스 로직에 따라 다음 중 하나를 선택한다:

-- 방법 A: 비정상 레코드 제거
DELETE FROM logs WHERE log_id <= 0;

-- 방법 B: 식별자 재배치
SET @sequence = 0;
UPDATE logs 
SET log_id = (@sequence := @sequence + 1) 
ORDER BY created_at;

3단계: 자동 증가 속성 제거

ALTER TABLE logs MODIFY log_id INT;

4단계: 자동 증가 기준점 설정

-- 현재 최대값보다 큰 값으로 설정
SELECT MAX(log_id) + 100 FROM logs;

ALTER TABLE logs AUTO_INCREMENT = 1100;

5단계: 자동 증가 속성 재적용

ALTER TABLE logs MODIFY log_id INT AUTO_INCREMENT;

안전 장치 마련

재발 방지를 위해 컬럼 제약을 강화하고, 삽입 검증 로직을 추가한다:

-- 음수 및 0 방지
ALTER TABLE logs MODIFY log_id INT UNSIGNED AUTO_INCREMENT;

-- 삽입 테스트
INSERT INTO logs (message) VALUES ('validation check');
SELECT LAST_INSERT_ID();

운영 환경에서는 NO_AUTO_VALUE_ON_ZERO SQL 모드 설정 여부도 점검해야 한다. 해당 모드가 활성화되면 0을 명시적으로 삽입할 때 자동 증가 값이 할당되지 않아 의도치 않은 중복이 발생할 수 있다.