[질문] inno DB 의 서버 재시작 시 auto_increment 리셋 보정 프로시저 및 트리 1 5 4,588

by 김성진 [MySQL] 자동증가 auto_increment innoDB [2013.09.17 17:35:10]


솔루션 특성상 AUTO_INCREMENT 가 있는 테이블에 DELETE가 발생합니다.

innoDB는 dbms 재 시작시 지워진 최대 AUTO_INCREMENT 값이 날라갑니다.

따라서 아래와 같은 AUTO_INCREMENT 가 있는 테이블에 트리거를 생성하고

트리거가 취합 한 정보를 추후 서버 재 시작시 보정하는 프로시저를 생성하고자 합니다.


근데 잘 안되네요;; ㅠ.ㅠ.


/*
트리거 생성 정보

DELIMITER $$

DROP TRIGGER IF EXISTS `TRG_agentcollectionpolicy_INSERT`$$

CREATE

    TRIGGER `TRG_agentcollectionpolicy_INSERT` AFTER INSERT ON `agentcollectionpolicy`

    FOR EACH ROW BEGIN

         UPDATE ac_tables

         SET ac_value = CASE WHEN NEW.MngNo >= ac_value THEN NEW.MngNo ELSE ac_value END

         WHERE ac_table = 'agentcollectionpolicy';

END;

$$

 

DELIMITER ;

*/

-- dbms information_schema에서 현재의 agentcollectionpolicy 테이블의 다음 AUTO_INCREMENT 값을 조사

SELECT table_name, `AUTO_INCREMENT` FROM information_schema.TABLES WHERE table_schema='NH70DB' AND table_name = 'agentcollectionpolicy';

-- table_name     AUTO_INCREMENT

-- agentcollectionpolicy   2035

 

-- 트리거로 저장한 AUTO_INCREMENT 최종 저장 값 확인

SELECT * FROM ac_tables WHERE ac_table = 'agentcollectionpolicy';

-- ac_table       ac_value

-- agentcollectionpolicy   2034

 

-- 현재 agentcollectionpolicy 테이블의 AUTO_INCREMENT 값 확인

SELECT *  FROM agentcollectionpolicy WHERE mngno >= 2025;

-- MngNo Name     Notice   StartTime         EndTime  Cycle    UserPolicy        Status

-- 2034  test2    \N       \N       \N       \N       \N       \N

 

-- 현재 agentcollectionpolicy 테이블의 AUTO_INCREMENT 정보 삭제

DELETE FROM agentcollectionpolicy WHERE mngno >= 2025;

 

-- 현재 agentcollectionpolicy 테이블의 AUTO_INCREMENT 정보 삭제 확인

SELECT *  FROM agentcollectionpolicy WHERE mngno >= 2025;

-- 0 row(s) returned

 

-- dbms information_schema에서 현재의 agentcollectionpolicy 테이블의 다음 AUTO_INCREMENT 값을 재 조사

SELECT table_name, `AUTO_INCREMENT` ai FROM information_schema.TABLES WHERE table_schema='NH70DB' AND table_name = 'agentcollectionpolicy';

-- table_name     AUTO_INCREMENT

-- agentcollectionpolicy   2035

 

/* usp_collect_max_reset_ac 선언된 프로시저 소스

DROP PROCEDURE IF EXISTS usp_collect_max_reset_ac;

DELIMITER //

CREATE PROCEDURE usp_collect_max_reset_ac()

BEGIN

 

DECLARE done INT DEFAULT FALSE;

DECLARE stmt_dml TEXT DEFAULT NULL;

DECLARE stmt_ddl TEXT DEFAULT NULL;

 

DECLARE update_query CURSOR FOR

         SELECT CONCAT('UPDATE ac_tables tgt

         JOIN (SELECT ''',table_name,''' AS src_table, MAX(',column_name,') AS src_max FROM ',table_name,') src

         SET ac_value = CASE WHEN src.src_max > tgt.ac_value  THEN src.src_max ELSE tgt.ac_value END

         WHERE tgt.ac_table = src.src_table; ') AS update_queries

         FROM information_schema.COLUMNS a

         WHERE a.table_schema='NH70DB' AND a.extra = 'AUTO_INCREMENT'

         ORDER BY table_name;

 

DECLARE reset_query CURSOR FOR

         SELECT CONCAT('ALTER TABLE `nh70db`.`',ac_table,'` AUTO_INCREMENT = ',ac_value+1,'; ') AS reset_queries

         FROM ac_tables;

 

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

 

-- 서버 재 시작 시 information_schema의 정보 수집이 늦을 수 있다고 판단되어 3초가 딜레이 후 시작

SET @sleep = SLEEP(3);

 

OPEN update_query;

 

update_loop: LOOP

         FETCH update_query INTO stmt_dml;

 

         IF done THEN

                  LEAVE update_loop;

         END IF;

        

         SET @stmt = stmt_dml;

        

         -- IF @stmt LIKE '%agentcollectionpolicy%' THEN

         --       SELECT dml FROM (SELECT @stmt AS dml) A WHERE dml LIKE '%agentcollectionpolicy%';

         -- END IF;

 

         PREPARE stmt FROM @stmt;

         EXECUTE stmt;

         DEALLOCATE PREPARE stmt;

 

END LOOP;

 

CLOSE update_query;

 

SET done = FALSE;

 

OPEN reset_query;

 

reset_loop: LOOP

         FETCH reset_query INTO stmt_ddl;

 

         IF done THEN

                  LEAVE reset_loop;

         END IF;

 

         SET @stmt2 = stmt_ddl;

 

         IF @stmt2 LIKE '%agentcollectionpolicy%' THEN

                  SELECT ddl FROM (SELECT @stmt2 AS ddl) A WHERE ddl LIKE '%agentcollectionpolicy%';

         END IF;

 

         PREPARE stmt FROM @stmt;

         EXECUTE stmt;

         -- DEALLOCATE PREPARE stmt;

 

END LOOP;

 

CLOSE reset_query;

 

END

//

DELIMITER ;

*/

 

-- ===========

-- dbms 재 시작

-- ===========

 

-- 트리거로 저장한 AUTO_INCREMENT 최종 저장 값 dbms 재 시작 후 확인

SELECT * FROM ac_tables WHERE ac_table = 'agentcollectionpolicy';

-- ac_table       ac_value

-- agentcollectionpolicy   2034

 

CALL usp_collect_max_reset_ac();

-- ddl

-- ALTER TABLE `nh70db`.`agentcollectionpolicy` AUTO_INCREMENT = 2035;

-- ALTER TABLE  문장이 실행됨을 확인

 

-- agentcollectionpolicy 테이블에 데이터 입력

INSERT INTO agentcollectionpolicy(NAME) VALUE ('test2');

-- 1 row(s) affected

 

-- !!! CALL usp_collect_max_reset_ac(); 했는데 ALTER TABLE `nh70db`.`agentcollectionpolicy` AUTO_INCREMENT = 2035; 실행 했다는데;; 2025!! 미챠브러!!!

SELECT *  FROM agentcollectionpolicy WHERE mngno >= 2025;

-- MngNo Name     Notice   StartTime         EndTime  Cycle    UserPolicy        Status

-- 2025  test2    \N       \N       \N       \N       \N       \N

 

-- 헐 안바꼈네;; 뭐가 잘 못 된거지??

SELECT table_name, `AUTO_INCREMENT` ai FROM information_schema.TABLES WHERE table_schema='NH70DB' AND table_name = 'agentcollectionpolicy';

-- table_name     AUTO_INCREMENT

-- agentcollectionpolicy   2026

 

by 김성진 [2013.09.17 17:36:16]
무었이 잘 못 된 것인지 몰르겠어요 ㅠ.ㅠ

고수님들의 조언 부탁드립니다.

by l2monkeys [2013.09.24 15:25:55]

위 방법에 맞추서 제가 테스트 해본결과는 

mysql> select table_name , `AUTO_INCREMENT`  from information_schema.tables ;

   test                                         |             10 |
 
 mysql> alter table test.test  auto_increment = 12 ;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> show create table test.test\G ;
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `i` int(11) NOT NULL AUTO_INCREMENT,
  `j` char(1) DEFAULT NULL,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 <= 정상적으로 12로 바뀜 확인 
1 row in set (0.00 sec)

mysql> insert into test.test(j) values('6') ;
Query OK, 1 row affected (0.01 sec)
mysql> select * from test.test ;
+----+------+
| i  | j    |
+----+------+
|  7 | 2    |
|  8 | 3    |
|  9 | 4    |
| 12 | 6    |
+----+------+
4 rows in set (0.00 sec)

데이터 역시 12부터 들어가는것 확인  문제 없음 
결론 : 테스트 방법에는 문제가 없어 보입니다. 
다만 아래 프로시저  실행하고 나서  아래처럼 AUTO_INCREMENT=12 값이 정상적으로 바뀌었는지 
확인 해보시기 바랍니다. 

 select table_name , `AUTO_INCREMENT`  from information_schema.tables ;
 test                                         |             12 |
+----------------------------------------------+----------------+


show create table test.test\G ;
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `i` int(11) NOT NULL AUTO_INCREMENT,
  `j` char(1) DEFAULT NULL,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


CALL
 usp_collect_max_reset_ac();

-- ddl

-- ALTER TABLE `nh70db`.`agentcollectionpolicy` AUTO_INCREMENT = 2035;

-- ALTER TABLE  문장이 실행됨을 확인


by 김성진 [2013.09.24 18:23:56]
감사합니다.

MySQL innoDB에서 서버가 재 시작 되었을때 문제가 생깁니다.;;;

test 테이블에 auto_increment 컬럼에 1-12까지 넣은 후
(이때 ac_tables 에서는 트리거 때문에 12번까지 업데이트 됨)
12번을 지운 후 서버 재시작 후

CALL usp_collect_max_reset_ac();를 실행해서
auto_increment 변경  alter 문이 실행이 비정상으로 안됩니다.;;

저도 참 아이러니 한 결과 라고 생각됩니다.
제가 쓰는 버전에 버그가 잇는가요?

[root@static bin]# mysql --version
mysql  Ver 14.14 Distrib 5.1.66, for redhat-linux-gnu (x86_64) using readline 5.1
입니다.
mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.6.12                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.6.12-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | linux-glibc2.5               |
+-------------------------+------------------------------+
7 rows in set (0.01 sec)

입니다.;;

by l2monkeys [2013.09.25 14:29:09]
버전하고 상관은 없구요 mysql 은 auto_increment 값을 메모리에 가지고 있다가 서버가 다운시 초기화 되고 이때 메모리에 다시 올려질때는  해당 컬럼의 가장 높은 숫자 MAX(auto_increment)로 다시 세팅되게 됩니다.
재시작시에 값이 다시 초기화 되는거지요 
중간의 값을 삭제시에는 문제가 되지 않으나 현재의 MAX(auto_increment) 값을 삭제 하게 되면
재시작시에 위값을 다시 불러올경우는 삭제된 값이 다시 들어 가게 됩니다.

위 값을 변경이 필요하다면 재시작 한후에 위 auto_increment 값을 변경해야 합니다. 


by 김성진 [2013.10.01 14:19:42]
그래서 위의 프로시저를 만든것이거든요 ㅠ.ㅠ

근데 mysql 서버가 재시작 되자마자 위의 프로시저를 실행하면

ac_table의 값을 바탕으로 자동으로 AUTO_INCREMNET 값을 재설정

하게 만들었는데;;

이상하게 안되네요;;

안되는 현상을 시간순에 따라서 적은 것이 위의 게시물 입니다.;;
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입