MySQL Workbench에서 Error Code: 2013 에러 해결하기

문제 발생 상황

MySQL을 사용하여 UPDATE 쿼리를 실행하는 도중에, 다음과 같은 오류가 발생

UPDATE
	movies
SET
	director_id =
    (SELECT director_id FROM directors WHERE name = movies.director)

Error Code: 2013. Lost connection to MySQL server during query

데이터 양도 많고 서브쿼리를 포함하고 있어 해당 쿼리가 실행되는데 시간이 걸려 기다리고 있었다. 그러던 중 MySQ 서버와의 연결이 끊어졌다는 에러가 발생했으며 다시 비밀번호를 입력해 workbench에 접속해야 했다. 그럼에도 불구하고 데이터는 정상적으로 업데이트된 것으로 보였다.

문제의 원인

원인이 될 수 있는 것으로는 다음의 두 가지가 있다.

1. 긴 쿼리 실행 시간에 비해 짧은 타임 아웃 시간

데이터의 양이 많거나 서브쿼리와 같은 처리 시간이 긴 구문을 포함하고 있을 때 이를 처리하는데 오랜 시간이 걸린다. 서버의 연결 타임아웃(wait_timeout, interactive_timeout) 값이 짧게 설정되어 있다면 실행 시간이 긴 쿼리를 처리할 때 연결 타임 아웃이 발생할 수 있다.

2. 네트워크 문제

서버와의 네트워크 연결이 불안정하거나 일시적인 문제로 인해 연결이 끊어졌을 수 있다.

해결 방안

1. 타임아웃 설정 변경

1-1. 일시적 변경

MySQL 서버의 타임아웃 시간을 늘려서 긴 쿼리 실행을 허용한다.
쿼리를 통해 간단히 해결할 수 있으며 단위는 초(sec)이다.
아래는 8시간으로 설정한 예시이다.

SET GLOBAL wait_timeout = 28800;
SET GLOBAL interactive_timeout = 28800;

SET GLOBAL 쿼리는 현재 MySQL 세션에 대해 변경된 설정이 적용되며, 서버가 종료되거나 재시작하면 기본값으로 돌아간다.

1-2. 영구적 변경

MySQL 서버의 타임아웃 설정을 영구적으로 변경하기 위해서 MySQL 설정 파일을 수정한 뒤 서버를 재시작 해야한다.
MySQL 설정 파일(my.cnf 또는 my.ini)에 들어가 파일의 [mysqld] 섹션에 아래와 같이 추가 또는 수정한다.

[mysqld]
wait_timeout = 28800
interactive_timeout = 28800

이후 서버를 재시작한다.
윈도우는 아래의 명령어를 사용한다.

net stop mysql
net start mysql

2. 업데이트 나눠 하기

한 번에 방대한 양의 업데이트를 진행하기보다는 여러 번에 걸쳐 데이터를 업데이트 한다.

결론

Error Code: 2013. Lost connection to MySQL server during query 오류는 주로 쿼리 실행 시간이 길어지면서 발생한다. 이를 해결하기 위해서는 MySQL의 타임아웃 값을 늘리거나 쿼리를 최적화하여 성능을 개선하거나, 쿼리 실행을 나눠 진행한다.

나의 경우 Duration값이 30.016 sec 이었던 것으로 보아 타임아웃 값이 30으로 짧게 설정되어 있어 이 에러가 발생한 것으로 보인다. 이후에 타임아웃 값을 600으로 늘린 뒤 다시 쿼리를 실행하니 정상적으로 실행되었다.

하지만 처음 타임아웃이 발생한 뒤 데이터를 확인해보니 업데이트가 되어있었다. 혹시나 데이터 손실이 있거나 일부 레코드가 제대로 처리되지 않았을 수도 있지 않을까하여 후속 확인 작업을 통해 점검해봤지만 정상적으로 처리가 되어 있었다.

그래도 이와 같은 문제를 방지하려면, 쿼리 실행 시간과 서버의 설정을 적절히 관리해야겠다.