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

여러 프로젝트 혹은 성능 테스트를 해보기 위해 dummy data를 넣는 과정에서 다음과 같은 에러를 마주하곤 할것입니다.

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

 

이 에러는 왜 생기는지, 그리고 어떻게 해결하는지 알려드리겠습니다.

---

에러 발생 이유

보통 해당 에러가 발생하는 이유는 클라이언트와 서버 간 연결이 작업 도중 끊어졌음을 의미합니다.

대규모 데이터 삽입 작업과 같은 상황에서 발생할 수 있습니다.

 

1. 쿼리 실행 시간이 너무 길어서 서버 연결 시간 초과

- MySQL 서버 설정에서 쿼리가 실행될 수 있는 최대 시간이 제한되어 있습니다.

- 많은 데이터를 삽입할 때, 작업 시간이 길어져 클라이언트가 서버와의 연결을 유지하지 못하고 종료될 수 있습니다.

 

2. max_allowed_packet 크기 초과

- MySQL 클라이언트와 서버 간 전송할 수 있는 최대 데이터 패킷 크기가 max_allowed_packet 설정에 의해 제한됩니다.

- 한 번에 삽입하려는 데이터 크기가 이 제한을 초과하면 연결이 끊어질 수 있습니다.

 

3. 네트워크 문제

- 클라이언트와 MySQL 서버 간의 네트워크 연결이 불안정하거나, 방화벽/네트워크 설정에 의해 연결이 끊어질 수 있습니다.

 

4. 서버 자원 부족

- 한 번에 대량의 데이터를 처리할 때 MySQL 서버의 메모리, CPU, 또는 디스크 I/O가 과부하되어 연결이 끊어질 수 있습니다.

 

해결방법

1. 데이터를 나누어 삽입하기

가령 100,000건의 데이터를 넣으려고 한다고 가정하겠습니다. 그럼 아래와 같이 한번에 넣게 하는것 보단, 1000개씩 100번 끊어서 넣는 방법으로 해결할 수 있습니다.

 

- 기존에 한번에 100,000건의 데이터를 넣는 코드

DELIMITER $$

CREATE PROCEDURE InsertUserData()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 100000 DO
        INSERT INTO user
        VALUES (NULL, CONCAT('USER_', i), FLOOR(1 + RAND() * 3));
        SET i = i + 1;
    END WHILE;
END$$

DELIMITER ;

CALL InsertUserData();

 

- 1000개씩 100번 끊어서 넣는 코드

DELIMITER $$

CREATE PROCEDURE InsertUserDataBatch()
BEGIN
    DECLARE i INT DEFAULT 1; -- 현재 사용자 ID
    DECLARE batch_size INT DEFAULT 1000; -- 한 번에 삽입할 데이터 개수
    DECLARE max_users INT DEFAULT 100000; -- 전체 사용자 수

    WHILE i <= max_users DO
        DECLARE j INT DEFAULT 0;

        START TRANSACTION;
        WHILE j < batch_size AND i <= max_users DO
            INSERT INTO user
            VALUES (NULL, CONCAT('USER_', i), FLOOR(1 + RAND() * 3));
            SET i = i + 1;
            SET j = j + 1;
        END WHILE;
        COMMIT;
    END WHILE;
END$$

DELIMITER ;

 

2. MySQL Session 설정 변경하기

workbench 설정에서 session 설정 변경을 통해서도 해당 오류를 해결할 수 있습니다.

 

MySQL Workbench -> Settings에 들어가서 아래와 같이 설정을 변경하면 됩니다.

 

DBMS connection keep-alive interval (in seconds) -> 36000

-> MySQL Workbench가 MySQL 서버와 연결이 끊어지지 않도록 주기적으로 keep-alive 메시지를 보내는 시간 간격

 

DBMS connection read timeout interval (in seconds) -> 36000

-> 서버로부터 응답을 기다리는 최대 시간

---

권장하는 방법은 첫 번째 방법입니다.

물론 두 번째 방법도 일시적으로 데이터를 삽입하는 동안 연결이 끊기는 문제를 어느 정도 방지할 수 있습니다.

하지만 완벽한 해결책은 아닐 수 있으니,

타임아웃 설정과 더불어 데이터 삽입을 배치 처리를 활용해서 하면 더 안전한 대규모 데이터 삽입을 할 수 있습니다...!!