[Error] DB Event 실행 후 적용 안됨

이 글은 DB Event를 적용하면서 직면했던 문제와 해결 방법입니다.

 

DB에서 Trigger와 Event를 사용하는 도중 Event 실행 후 쿼리가 테이블에 적용이 안 되는 현상이었습니다.

CREATE DEFINER=`user`@`localhost` EVENT `deletepush_00hour`
    ON SCHEDULE
        EVERY 1 DAY STARTS '2023-05-09'
    ON COMPLETION PRESERVE
    ENABLE
    COMMENT ''
    DO BEGIN
    DELETE FROM XXX;
    ...
    ...
END

위 SQL은 HeidiSQL에서 생성 user 권한으로 생성한 Event입니다.

 

위의 이벤트는 테이블을 초기화하는 일을 하는데 Event가 발생하고 테이블이 초기화되지 않았습니다.

 

자세히 보면 CREATE 문 ON COMPLETION을 보면 완료 후 PRESERVE 

 

"즉 상태를 보존한다. 해당 이번트를 적용하지 않겠다."라고 정의되어 있다는 것을 확인했고

 

PRESERVE를 NOT PRESERVE로 수정해주면 됩니다.

 

❗이렇게 쉽게 끝났으면 헤매지 않았을 것입니다.

 

Event를 생성 후 수정이 안되었고, MySQL 공식문서를 찾아봤습니다.

 

MySQL 공식문서 저장 객체 접근 제어

프로시저, 함수, 트리거, 이벤트 (이런 것들을 저장 프로그램이라 하는구나)이 친구들은 실행에 적용할 수 있는 권한은 DEFINER 속성에 의해 제어할 수 있다고 합니다. 이때 살짝 감을 잡았습니다. user 권한에 루트권한이 없어서 그런가?

 

아래에 DEFINER 속성에 대해 더 알아봤습니다.

 

MySQL 공식 문서 DEFINER 속성

아하 저장 프로그램 설정을 변경하는 권한은 SYSTEM_USER이여야 한답니다.

 

SYSTEM_USER에 대해 찾아봤습니다.

MySQL 공식 문서 SYSTEM_USER

그렇군요!. SYSTEM_USER는 MySQL 버전 8.0.16에서 추가되었다는군요.(저는 MySQL 8.0.31)

 

따끈따끈한 친구여서 검색을 해도 안 나왔나 봅니다. 공식 문서에 따르면

 

SYSTEM_USER는 권한을 가진 시스템 사용자와, 권한이 없는 일반 사용자로 나누어집니다.

 

이후 아래에 시스템 사용자와 일반 사용자에 대한 권한 이야기가 나옵니다.

 

요약하자면 일반 사용자는 시스템 사용자가 만든 개체를 수정하거나 삭제할 수 없다고 합니다.

 

여기서 다시 ROOT권한으로 DB에 접속해서 PRESERVE를 NOT PRESERVE로 수정할 수 있었습니다.

 

CREATE DEFINER=`root`@`localhost` EVENT `deletepush_00hour`
    ON SCHEDULE
        EVERY 1 DAY STARTS '2023-05-09'
    ON COMPLETION NOT PRESERVE
    ENABLE
    COMMENT ''
    DO BEGIN
    DELETE FROM XXX;
    ...
    ...
END

 

이후 이벤트가 잘 적용하는 것을 했습니다.