Last Updated on 4월 29, 2023 by Jade(정현호)
안녕하세요
이번 포스팅에서는 MySQL 버전 8.0.33 Fixed 에 추가된 AUTO_INCREMENT과 관련된 Bug105092 에 대해서 확인해보려고 합니다.
Bug105092
Bug105092에서 보고된 내용은 MySQL 8.0에서 테이블 생성 후 ALTER TABLE로 AUTO_INCREMENT 속성을 추가, 그 다음 AUTO_INCREMENT 값이 PK컬럼 MAX+1 이하로 설정될 수 있는 결함 입니다.
참고) Bug105092 외에 NonPublic Bug33419246도 동일한 내용입니다.
예제를 통해서 Bug의 내용을 확인 해보도록 하겠습니다.(버전 MySQL8.0.32)
-- 테이블 생성
create table `tb_test` (
`id` int not null primary key
) engine=innodb;
-- 데이터 입력 3건
insert into `tb_test`(`id`) values (1), (2), (3);
-- auto_increment가 아니었던 id컬럼을 auto_increment로 속성을 변경 합니다.
alter table `tb_test` modify `id` int not null auto_increment;
-- 테이블의 auto_increment 값을 변경(초기화)
alter table `tb_test` auto_increment=1;
위의 내용은 테이블 생성 및 데이터 입력 후 auto_increment 속성을 추가하여 컬럼 변경 하였고 그 다음에 auto_increment 값을 변경 하였습니다.
위에서 생성한 테이블 정보를 확인 해보도록 하겠습니다.
select table_name,auto_increment from information_schema.tables where table_name = 'tb_test'; +------------+----------------+ | TABLE_NAME | AUTO_INCREMENT | +------------+----------------+ | tb_test | 1 | <!!--- +------------+----------------+
alter table <테이블명> auto_increment=N 으로 변경시 id컬럼에 이미 값이 있기 때문에 그 값의 MAX+1 로 설정 되어야 합니다.
하지만 위에 볼 수 있듯이 기존에 입력된 값이 있었지만, auto_increment=1 명령어가 그대로 적용 되면서 auto_increment가 1로 된 것으로 확인 할 수 있습니다.
이제 새로운 데이터를 입력해보도록 하겠습니다.
insert into `tb_test` (`id`) values (null); <-- error: #1062 - duplicate entry '1' for key 'tb_test.PRIMARY' insert into `tb_test` (`id`) values (null); <-- error: #1062 - duplicate entry '2' for key 'tb_test.PRIMARY' insert into `tb_test` (`id`) values (null); <-- error: #1062 - duplicate entry '3' for key 'tb_test.PRIMARY' insert into `tb_test` (`id`) values (null); <-- 성공
위의 예제에서 처럼 기존에 입력된 데이터만큼 계속 Primary Key Duplicate 에러가 발생되게 되며, 기 입력된 3건 다음 4번째 입력 부터 정상적으로 입력되는 것을 확인 할 수 있습니다.
Bug105092에서 보고된 내용은 위의 내용과 같이 InnoDB 스토리지 엔진의 테이블에서 auto_increment 속성의 아닌 컬럼에서 auto_increment 속성으로 변경시에 auto_increment값이 MAX+1 이하로 설정되지 않아야 하지만 MAX+1 이하로 설정이 되는 현상 입니다.
동일한 내용을 MySQL 5.7.40 버전과 MySQL 8.0.33 버전에서 수행 해보도록 하겠습니다.
• MySQL 8.0.33
-- 8.0.33 버전입니다.
select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.33 |
+-----------+
create table `tb_test` (
`id` int not null primary key
) engine=innodb;
insert into `tb_test`(`id`) values (1), (2), (3);
alter table `tb_test` modify `id` int not null;
alter table `tb_test` auto_increment=1;
select table_name,auto_increment
from information_schema.tables
where table_name = 'tb_test';
+------------+----------------+
| TABLE_NAME | AUTO_INCREMENT |
+------------+----------------+
| tb_test | 4 |
+------------+----------------+
<!-8.0.32버전과 다르게
4로 인식되고 있음
insert into `tb_test` (`id`) values (null);
Query OK, 1 row affected (0.00 sec)
insert into `tb_test` (`id`) values (null);
Query OK, 1 row affected (0.00 sec)
insert into `tb_test` (`id`) values (null);
Query OK, 1 row affected (0.00 sec)
insert into `tb_test` (`id`) values (null);
Query OK, 1 row affected (0.00 sec)
select * from tb_test;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+----+
7 rows in set (0.00 sec)
• MySQL 5.7.40
-- 5.7.40 버전입니다.
mysql> select @@version;
+------------+
| @@version |
+------------+
| 5.7.40-log |
+------------+
create table `tb_test` (
`id` int not null primary key
) engine=innodb;
insert into `tb_test`(`id`) values (1), (2), (3);
alter table `tb_test` modify `id` int not null auto_increment;
alter table `tb_test` auto_increment=1;
select table_name,auto_increment
from information_schema.tables
where table_name = 'tb_test';
+------------+----------------+
| TABLE_NAME | AUTO_INCREMENT |
+------------+----------------+
| tb_test | 4 |
+------------+----------------+
<!-8.0.32버전과 다르게
4로 인식되고 있음
insert into `tb_test` (`id`) values (null);
Query OK, 1 row affected (0.00 sec)
insert into `tb_test` (`id`) values (null);
Query OK, 1 row affected (0.00 sec)
insert into `tb_test` (`id`) values (null);
Query OK, 1 row affected (0.00 sec)
insert into `tb_test` (`id`) values (null);
Query OK, 1 row affected (0.00 sec)
select * from tb_test;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+----+
7 rows in set (0.00 sec)
먼저 MySQL 5.7번에서는 발생되지 않는 이유는 MySQL 재시작시 auto_increment 값이 초기화 되는 부분에 대해서 MySQL 8.0에서의 개선사항과 연관되어 있기 때문입니다.
MySQL8.0에서 auto_increment 개선에 대한 내용은 아래 포스팅에서 정리하였습니다.
더 상세하고 깊은 내용은 Worklog 에서 확인 하실 수 있습니다.
위의 내용을 다시 간단하게 정리하면 MySQL 8.0 에서 재시작(또는 Crash에 의한 재시작)시 auto_increment count reset 되는 것을 개선하기 위해 다음과 같은 프로세스가 진행 됩니다.(모든 내용을 담고 있는 것은 아님)
MySQL 8.0에서 재시작시 auto_increment가 reset 되는 것에 대한 개선으로 dict_table_t에 autoinc_persisted, autoinc_persisted_mutex, autoinc_index 총 3개의 변수가 추가되었습니다.
autoinc_persisted는 redo 로그 또는 DDTableBuffer에 기록된 persisted autoinc counter 값 입니다.
이 값(autoinc_persisted)은 DDTableBuffer 에 카운터를 다시 쓰려고 할 때 주로 사용됩니다.
할당된 모든 카운터는 redo 로그에 기록하며 'autoinc'는 이 persisted(지속적인) 카운터 값의 다음 카운터값과 정확히 일치해야 합니다.
DDTableBuffer에 카운터를 다시 기록할 필요가 있을 때, redo 로그에 기록된 카운터와 일치하도록 유지하기 위해 이 카운터가 필요합니다.
왜냐면 'autoinc'에서 직접 쉽게 읽을 수 없기 때문입니다. 직접 읽기 위해서는 autoinc_lock이 필요하고, 데드락이 발생할 수 있기 때문입니다.
문제가 발생된 부분은 컬럼에 AUTO_INCREMENT 속성을 추가(컬럼 변경) 하는 부분입니다.
"ALTER TABLE <테이블명> MODIFY id INT NOT NULL AUTO_INCREMENT" 을 실행시 테이블의 기존 컬럼을 수정 하기 위해서, 중간 테이블을(intermediate table) 생성하고 원본 테이블의 내용을 모두 복사한 다음, 중간 테이블intermediate table의 이름을 변경하고 원본 테이블을 삭제합니다.
그런데 중간 테이블을(intermediate table)을 복사하는 동안, autoinc_persisted 변수를 업데이트하지 않고 건너뛰기 때문에 이 문제가 발생하게 되는 것 입니다.
autoinc_persisted 변수는 리두 로그에 지속되는 카운터 값을 저장하기 때문입니다.
해당 Alter Table 작업은 ALGORITHM=COPY 방식으로 수행 됩니다.( ALGORITHM=INPLACE 불가)
mysql> select @@version; +-----------+ | @@version | +-----------+ | 8.0.32 | +-----------+ mysql> alter table `tb_test` modify `id` int not null auto_increment, ALGORITHM=INPLACE,LOCK=NONE; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. <!!!--- INPLACE 불가, COPY 방식 사용
이 문제를 해결하기 위해 중간 테이블을(intermediate table)로 내용을 복사하는 과정에서 autoinc_persisted 변수를 업데이트 하는 것으로 변경되었습니다.
더 정확히는 중간 테이블을(intermediate table)과 temporary tables로 내용을 복사하는 과정이 생략(skip) 하는 로직에서 중간 테이블을(intermediate table)은 대상에서 제외되었습니다.
("CREATE TEMPORARY TABLE"은 이 변경 코드 따르지 않으며, 이 문제에 영향을 받지 않습니다.)
• 변경된 코드 내용
## MySQL 8.0.32
No need to log for temporary tables and intermediate tables */
if (!index->table->is_temporary() && !index->table->skip_alter_undo &&
------------------------------------------
## MySQL 8.0.33
No need to log for temporary tables */
if (!index->table->is_temporary() &&
ut_ad(!index->table->is_intrinsic());
DBUG_EXECUTE_IF("crash_create_after_autoinc_persisted_update",
DBUG_SUICIDE(););
이번 포스팅에서는 MySQL 8.0.33 버전 Fixed bug인 Bug105092/33419246 과 auto_increment에 대해서 확인해보았으며, 글을 여기에서 마무리 하도록 하겠습니다.
감사합니다.
Reference
Reference URL
• dev.mysql.com/news-8-0-33
• bugs.mysql.com/bug.php?id=105092
• Bug #33419246 - 4a691b6 · GitHub
연관된 다른 글
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
