[MySQL] ERROR 1118 (42000) : Row size too large (> 8126). Changing some columns to TEXT or BLOB my help. In current row format, BLOB prefix of 0 bytes is stored inline.

제목과 같은 에러가 발생한 경우 mysql이나 mariadb에서 어떤 특정 테이블의 전체 column의 크기를 합친게 mysql이나 mariadb에 설정된 테이블당 최대 크기를 넘은 상태를 의미한다. 정확히는 Engine이 innodb로 된 테이블의 최대 크기를 의미한다. 이럴때는 해결방안이 2가지가 있다.

 

해결방안 1

에러 로그에도 나와있는 방법으로, varchar로 선언되어있는 컬럼들 중에서 크기가 큰 컬럼의 데이터 타입을 TEXT나 BLOB으로 변경해주는 것이다. TEXT나 BLOB타입은 테이블 데이터에 저장되지 않고 별도의 공간에 분리되어 저장된다고 한다(?) 정확히 무슨 의미인지는 아직 잘 모르지만.. 이렇게 함으로써 해결할 수 있다.

 

해결방안 2

my.cnf(my.ini) 설정을 수정해준다.

[mysqld]

아래에 innodb에 대한 설정이 있을것이다. 이 아래에 다음과 같은 설정을 추가해주면 된다.

 

[mysqld]
innodb_strict_mode = 0

 

 

아마 innodb의 최대 크기를 잡을꺼냐 말꺼냐 말고도 다른 영향이 있겠지만 일단 저렇게 설정함으로써도 해결할 수 있다.

 

※ 참고로 윈도우는 설정파일명이 my.ini이고 리눅스에 설치된 경우에는 my.cnf로 되어있을것이다. 리눅스의 경우에는 아마 /etc/mysql 디렉토리 안에 my.cnf가 존재할것이고 윈도우는 mysql 접속한 후에 아래의 명렁어를 통해 datadir의 경로에 가면 나올것이다.

 

SHOW VARIABLES WHERE Variable_Name LIKE '%dir';

 

해결방안 3 * (2022. 3. 4. 추가)

설정 파일을 건드릴 수 없는 환경인 경우 연결된 connection session 안에서 설정할 수도 있다. session이라는 개념이 어색할 수도 있는데 간단하게 말해서 현재 연결중인 connection에서만 유지되는 설정이라고 생각하면 된다. 다음과 같이 설정할 수 있다.

 

-- 설정 방법 (set innodb_strict_mode)
SET innodb_strict_mode = 0;

-- 확인 방법 (check innodb_strict_mode)
SHOW variables = 'innodb_strict_mode';
or
SHOW variables like '%strict_mode';

 

주의사항

innodb_strict_mode를 0으로 설정한다는 것은 innodb 스토리지 엔진에 대한 엄격한 검증을 비활성화(OFF)한다는 것이고 이로 인해서 원래는 innodb에 만들어질 수 없는 테이블 또는 들어갈 수 없는 길이의 데이터를 넣을 수 있게 되는데 잠재적인 위험이 있다.

 

Row size too large 라는 에러 메시지를 보면 row 크기가 너무 크다는 것인데 이를 억지로 넣게되면 데이터는 짤려서 들어간다. 이 때문에 테이블을 만들어놓고 나중에 데이터가 짤려서 들어가서 문제가 생길 수도 있다. 또는 큰 데이터를 넣었는데 해당 데이터가 짤려서 들어갔기 때문에 조회가 안될 수도 있다. 이뿐만 아니라 unsigned int 데이터 타입인데 -1을 등록할 경우 알아서 0으로 바꿔서 등록된다. 이렇게 등록될 경우 나중에 어떤 에러나 오류로 돌아올지 모른다.

 

근본적인 해결방안

이에 대한 해결방안은 해당 테이블을 재설계하는것이다. 에러 메시지에 나와있는 대로 몇몇 컬럼을 text 나 blob 데이터 타입으로 바꿔서 해결할 수도 있지만 근본적인 해결책은 아니다. text나 blob으로 변경하는 것은 inline에 저장하지 않고 파일로 저장하겠다는 것인데 컬럼이 500개인 테이블이 있다고 가정했을 때 몇 백개의 컬럼을 text로 바꿔서 파일로 저장하게 되면 테이블을 생성하고 데이터를 등록하는 것에는 문제가 없을지 몰라도 쿼리 성능이 안나올 수도 있고 파일로 저장하기 때문에 나중에 어떤 이슈가 생길지도 불투명하고 인덱싱을 하고 있다면 인덱싱 시간이 엄청 오래 걸릴지도 모른다. 잠재적인 위험 요소가 많아지기 때문에 서비스 안정성을 위해서라도 해당 테이블을 재설계하는 것이 맞다고 생각한다.

예를들어, 재설계는 컬럼이 많은 경우 테이블을 적절히 분리하여 근본적으로 해결할 수 있다고 생각한다.