0. 개요
재직하는 회사에서 새로 들어온 분석 프로젝트를 검토하기 위해
고객사에서 의뢰한 데이터셋 덤프를 요청하였다.
문제는 고객사는 MariaDB 기반이었고 당사는 MySQL을 사용한다는 것이었는데,
MariaDB가 MySQL의 fork에서 시작한 만큼 웬만하면 되겠지! 라는 심정으로 그냥 덤프를 떠 봤다.
mysql -h [우리회사서버] -u [계정명] -p SAMPLE_DATA_DB<~/Downloads/sample_data_fulldump.sql
적당히 돌아가다가 아래 같은 이슈가 두 개 떴다.
[ERROR 1101] BLOB, TEXT, GEOMETRY or JSON column 'ATTR' can't have a default value
[ERROR 1178] The storage engine for the table doesn't support native partitioning
그래서 구글링 해서 해결하는 과정을 포스팅하려 한다.
1. ERROR 1101
(1) 에러의 원인
고객사가 제공한 DB는 과거 Oracle을 사용했으며, 추후 MariaDB로 변환하는 과정을 거쳤다.
MariaDB는 empty string 과 NULL 값을 다르게 인식한다.
또, MariaDB는 TEXT, JSON 열에서 NULL 을 무시할 수 있고, default value를 설정할 수 있다. 일반적으로 default value는 empty string 혹은 NULL 을 사용한다.
한편 MySQL은 default value를 설정할 수 없다. -> 보통은 NOT NULL 을 사용한다.
즉, 고객사가 Oracle에서 MariaDB로 데이터를 이관할 때 TEXT 의 default value 를 "" 로 설정하였는데, 이를 MySQL에서 받아들이지 못한 것이다.
고객사에 default value를 NULL로 하여 다시 덤프를 떠달라고 할 수도 있지만... 자체적인 해결법을 찾아보기로 했다.
(2) 해결법
위 사태는 STRICT_TRANS_TABLES 라는 sql_mode 설정을 해제하여 해결할 수 있다.
sql_mode 설정을 바꾸는 법은 세 가지가 있다.
1) 영구 수정 : /etc/my.conf 수정
리눅스와 맥은 /etc/my.conf 를,
윈도우는 C:\ProgramData\MySQL\MySQL Server x.x\my.ini 를 수정하면 된다.
리눅스 기준으로 설명하자면
a. mysql client 에 접속해서 다음 명령어로 현재 sql_mode 변수의 값을 살펴보고, 복사해서 백업해놓는다.
SELECT @@sql_mode;
b. vi로 /etc/my.conf 를 열어서(생성해서) 다음과 같이 sql_mode 변수를 수정하여 저장한다.
[mysqld]
...
sql_mode="ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
c. mysql 서버를 재실행한다.
2) 런타임 수정 : @@session.sql_mode 수정
mysql 서버에 클라이언트로 접속해서 수정하는 방법.
해당 sql 서버 재시작 전 까지만 유효하다.
재시작시 my.conf를 불러와서 설정하기 때문이다.
a. 전역 수정
모든 유저들에게 해당하도록 수정하는 방법이다.
@@global.sql_mode 를 수정하면 된다.
SET @@global.sql_mode="ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"b. 세션 수정
세션 수정은 접속해있는 client가 접속종료하기 전 까지만 유효하다.
SET @@session.sql_mode="ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
2. ERROR 1178
Database Engine(Storage Engine) 이 달라서 나타나는 오류다.
MySQL은 5.5버전을 기점으로 MyISAM에서 InnoDB로 기본 엔진을 변경하였다.
(1) MyISAM
에전 MySQL은 MyISAM을 Storage Engine으로 사용하였다. MyISAM은 블로그나 게시판성 시스템과 같이 Write보다 Read가 많은 시스템에 적합하다. 다시 설명하자면 Single-append, Multi-read 인 경우이다. 이 때는 myisampack을 이용하여 Read-only용 압축 테이블을 유지할 수 있어 디스크 용량 측면에서도 유리하다.
(2) InnoDB
InnoDB는 Innobase -> Oracle 의 기본 Storage Engine 이고, MySQL 5.5 이후 기본적용 되어있다. Transaction-safe 한 특성을 가지고 있으며 Undo, Tablespace 등 Oracle의 개념을 차용하여 구현된 것들이 있다.
앞서 말한 Tablespace 를 사용하여 동시처리에 효과적인 구조를 가지고 있으나, 메모리와 디스크를 많이 사용하고 복구 절차가 복잡하며 dead lock 가능성이 높다.
(3) 에러의 원인
MyISAM 은 Partitioning을 지원하지 않는다.
아래 명령어로 테이블의 엔진을 볼 수 있다.
SHOW TABLE STATUS
확인 결과 고객사에서 제공한 테이블 중 일부가 MyISAM을 사용하는 것으로 나타났다.
문제는 해당 dump 파일이 CREATE TABLE 까지 포함하고 있는데,
그 CREATE 의 옵션 중 ENGINE=MyISAM이 포함되어있다는 것이다.
MySQL 공식 문서는 두 가지 방식을 제안하고있는데, 다음과 같다.
ALTER TABLE ... REMOVE PARTITIONING # drop partitioning
ALTER TABLE ... ENGINE=INNODB # convert to InnoDB
고객사에서 제공한 dump.sql 파일을 일부 읽어와 (GB단위이므로 절대 전체를 읽어선 안된다!)
CREATE TABLE () ENGINE=MYISAM 부분 밑에
ALTER TABLE ... ENGINE=INNODB 를 추가로 기재해주었다.
나는 서버팀이 아닌데 왜 이걸 하고있는지 모르겠다.
끗.