로그 관련

매일 돌아갈 이벤트 스케쥴러에 등록될 로그 프로시저

CREATE DEFINER=`권한 유저`@`%` PROCEDURE `sys_log_table_create`()
proc_label:BEGIN
 
    declare $target_max_month int;
    declare $cnt int;
           
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- default exception handler
        ROLLBACK;
        COMMIT;
        SET @@SQL_SAFE_UPDATES=1;
        RESIGNAL;        
    END;
 
    if not exists(SELECT 1
                    FROM information_schema.TABLES
                    WHERE TABLE_SCHEMA = 'fom_kr_log' and TABLE_NAME like 'user_game_log_20%') then
        begin
            set $target_max_month = 6;
            set $cnt = 0;
 
            #전 달부터 생성한다. 그 이전 데이터은 신경쓰지 않는다. 필요한 경우 수동 생성
            call sub_sys_log_table_create(date_format(DATE_ADD(utc_timestamp, interval - 1 month) , '%Y%m'));
            
            create_loop:LOOP 
               IF ($cnt = $target_max_month) THEN
                  LEAVE create_loop;
               END IF;  
               
               call sub_sys_log_table_create(date_format(DATE_ADD(utc_timestamp, interval + $cnt month) , '%Y%m'));
               
               SET $cnt = $cnt + 1;
            END LOOP;
        end;
    else
        begin
            declare $last_month int;
            set $last_month = (SELECT replace(`table_name`, 'user_game_log_', '')
                                FROM information_schema.TABLES
                                WHERE TABLE_SCHEMA = 'fom_kr_log' and   TABLE_NAME like 'user_game_log%'
                                order by `CREATE_TIME` desc
                                limit 1);
            
            set $target_max_month = 5;
 
            if( EXTRACT(YEAR_MONTH FROM DATE_ADD(utc_timestamp, interval + $target_max_month  month)) > $last_month) then
                begin
                    call sub_sys_log_table_create(date_format(DATE_ADD(utc_timestamp, interval + $target_max_month month) , '%Y%m'));
                end;
            end if;
        end;
    end if;
END

이벤트 스케쥴러에 의해 돌아가는 프로시저에 의해 실행된 프로시저

CREATE DEFINER=`fom_service`@`%` PROCEDURE `sub_sys_log_table_create`(in $target_date int)
proc_label:BEGIN
    declare $user_game_log varchar(200);
    declare $user_storage_slot_log varchar(200);
    #declare @create_user_game_log_query_string varchar(500);
    #declare @create_user_storage_slot_log_query_string varchar(500);
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- default exception handler
        ROLLBACK;
        COMMIT;
        SET @@SQL_SAFE_UPDATES=1;
        RESIGNAL;        
    END;
 
    SET $user_game_log := CONCAT("user_game_log_", $target_date);
    SET @create_user_game_log_query_string = CONCAT('
        CREATE TABLE IF NOT EXISTS ', $user_game_log,' (
          `idx` bigint(20) NOT NULL AUTO_INCREMENT,
        ... 추가 컬럼
          PRIMARY KEY (`idx`),
          KEY `server_key` (`server_key`),
          KEY `user_key+transaction_serial` (`user_key`,`transaction_serial`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
    ');
    
     SET $user_storage_slot_log := CONCAT("user_storage_slot_log_", $target_date);
    
     SET @create_user_storage_slot_log_query_string = CONCAT('
        CREATE TABLE IF NOT EXISTS ', $user_storage_slot_log,' (
          `idx` bigint(20) NOT NULL AUTO_INCREMENT,
        ... 추가 
          PRIMARY KEY (`idx`),
          KEY `server_key` (`server_key`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
    ');
START TRANSACTION;
 
    PREPARE create_backup_table_repare FROM @create_user_game_log_query_string;
    EXECUTE create_backup_table_repare;
    DEALLOCATE PREPARE create_backup_table_repare;
 
    PREPARE create_backup_table_repare2 FROM @create_user_storage_slot_log_query_string;
    EXECUTE create_backup_table_repare2;
    DEALLOCATE PREPARE create_backup_table_repare2;
 
    set @create_user_game_log_query_string = null;
    set @create_user_storage_slot_log_query_string = null;
COMMIT;
    set @create_user_game_log_query_string = null;
    set @create_user_storage_slot_log_query_string = null;
END