솔루션 특성상 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