[MySQL] DDL문이 실행되지 않을 때 ( SHOW FULL PROCESSLIST / KILL PROCESS)

문제상황 

회사 사수가 DB 테이블에 외래키를 설정하는데 아무리 기다려도 ALTER TABLE 쿼리가 실행되지 않는 문제가 발생했다.

이 외에도 테이블 명 수정, 칼럼 명 수정, 외래키 지정, TRUNCATE 등 모든 DDL 쿼리문이 실행되지 않았다.

 

문제원인

문제 원인은 해당 테이블에 Transactional한 process가 끝나지 않아서 발생하는 문제였다.

참고한 자료는 다음과 같다.

mysql bug report

mysql 공식 문서

 

문제 해결을 위해서는 변경하려는 테이블에 완료되지 않은 Transaction을 정리해야 할 것으로 보인다.

 

SHOW FULL PROCESSLIST

우선 완료되지 않은 process 목록을 보기 위해 아래의 쿼리문을 실행해 보자

SHOW FULL PROCESSLIST;

 

그러면 아래와 같은 목록을 확인할 수 있다 ( Chat GPT 로 생성한 예시 데이터 입니다. )

위의 표와 같이 실행 중인 process의 목록을 확인할 수 있다.

이 때 Id 3 process를 확인해 보면 time 이 62이고 state가 Locked 인 상태이다.
해당 process처럼 아직 끝나지 않은 Transaction이 존재하는 경우 DDL 문이 동작하지 않는다.

 

왜 발생했는지 모를 이 process를 강제로 종료시켜보자.

 

KILL PROCESS

원하지 않는 process를 강제로 종료시키는 방법은 간단하다.

 

KILL :PROCESSID;
ex) KILL 3;

 

위와 같이 종료시키고자 하는 PROCESS ID 를 KILL 뒤에 입력해 주면 된다.

 

그런 뒤에 다시 한 번 SHOW FULL PROCESSLIST를 실행해 보자.

 

3번 Process가 사라진 것을 확인할 수 있을 것이다.
테이블에 걸려 있던 종료되지 않은 Process들을 모두 강제 종료 시켰으니 다시 한 번 처음의 문제였던 DDL 문을 실행해 보자.

 

문제 해결

Rename, FK 생성, Index 생성 등 모든 DDL문이 정상 동작하는 것을 확인할 수 있었다 !

잘 기억하자 ! DDL 문은 테이블의 모든 Transaction이 종료된 뒤에 실행된다 !