[MySQL] Alter Table 실행 도중 발생한 Connection Lost 에러 해결

[MySQL] Alter Table 실행 도중 발생한 Connection Lost 에러 해결

들어가며

post 테이블의 컬럼 viewCount 를 따로 postViewCount 테이블로 분리하기 위해 viewCount 컬럼을 post 테이블에서 삭제하고자 했습니다.

그러나, 쿼리가 현재 실행 중이라는 로딩 상태만 보여주다 결국 Error Code: 2013. Lost connection to MySQL server during query 에러가 발생했습니다.

Operation failed: There was an error while applying the SQL script to the database.
ERROR 2013: Lost connection to MySQL server during query
SQL Statement:
ALTER TABLE ...

다음 에러를 한 번도 만난 적이 없던 터라 당황했지만 결국 해결할 수 있었습니다.

다음 번에 이 에러를 만났을 때 금방 해결할 수 있도록 이렇게 기록으로 남깁니다.

이 에러의 일반적인 원인

다음 에러가 발생하는 원인은 보통 현재 실행 중인 쿼리의 수행 시간이 너무 오래 걸려서 서버에서 끊어버린 것이라 했습니다.

그러나 현재 개발 중인 서비스는 규모도 소규모일 뿐만 아니라 실행한 쿼리의 수행 시간이 결코 오래 걸리지 않는 쿼리였습니다.

내가 예상한 원인

아무래도 다른 사용자 혹은 트랜잭션이 post 테이블에 있는 데이터에 접근한 상태여서 post 테이블의 일부 Row 에 대한 Lock 이 걸려 내가 실행한 쿼리(Post 테이블의 컬럼 삭제)가 대기 중이지 않을까? 라는 생각이 들었습니다.

현재 SQL에서 비정상으로 처리된 프로세스가 있는지 확인

SHOW FULL processlist 쿼리로 현재 SQL 에서 비정상으로 처리된 프로세스가 있는지 확인해보았습니다.

SHOW FULL processlist

확인한 결과 쿼리를 막고 있는 프로세스가 없었습니다.

참고) 만약 쿼리를 막고 있는 프로세스가 있었다면 이 ID를 직접 kill 할 수 있는 MySQL 명령어로 kill 해 줍니다.

kill [Id];

TRANSACTION(트랜잭션) 확인하기

여전히 에러가 발생했는데, processlist에는 쿼리를 막는 프로세스가 없다는 것입니다.

그렇다면 혹시 이전에 실행되었던 트랜잭션이 정상적으로 종료되지는 않아 이것이 현재 실행 중인 쿼리를 막고 있지는 않을까? 라는 생각이 들었습니다.

비정상적으로 처리된 트랜잭션을 확인하기 위해 다음 MySQL 명령어를 실행했습니다.

SELECT * FROM information_schema.INNODB_TRX;

확인 결과는 다음과 같습니다.

제가 서버 코드에서 무언가 트랜잭션을 만들었고, 해당 트랜잭션이 정상적으로 종료되지 않았기 때문에 발생된 것임을 확인했습니다.

조회된 테이블에서 try_mysql_thread_id 를 확인합니다. 이 id 값은 mysql 의 프로세스 id 입니다.

해당 프로세스 목록 확인하기 위해 다시 SHOW FULL processlist 명령어를 실행해보았습니다.

Id: 13522
User: []
Host: []
db: development
Command: Sleep

Id: 13483
User: []
Host: []
db: development
Command: Sleep

Id: 13482
User: []
Host: []
db: development
Command: Sleep

위와 같이 정체를 몰랐던 프로세스의 id 를 확인할 수 있게 되었습니다.

비정상 종료된 트랜잭션이 실행된 프로세스 종료

비정상 종료된 트랜잭션이 실행된 프로세스 ID 에 대해서 kill 을 해줍니다.

kill 13522;
kill 13483;
kill 13482;

이 과정을 통해 비로소 Alter Table 이 정상적으로 실행될 수 있었습니다.

참고) 트랜잭션과 ALTER TABLE 과의 관계

트랜잭션과 ALTER TABLE과의 관계는 다음과 같습니다.

트랜잭션은 어떠한 일련의 처리를 위한 논리적 단위입니다. 하나의 트랜잭션이 여러 테이블을 거쳐 작성되는 것입니다.

문제는 이 트랜잭션이 처리되는 동안, 즉 종료 전까지는 테이블의 변형이 없어야만 한다는 것입니다.

트랜잭션이 처리 중인데 갑자기 테이블의 구조가 바뀌는 것은 상식적으로 불가한 일임을 잘 아실 것입니다.

그러므로 ALTER TABLE 쿼리를 실행할 때, 현재 트랜잭션이 없는 경우인데 결과가 나온다면 비정상을 의심해봐야 합니다.

참조

https://livetodaykono.tistory.com/80