Strict mode

MySQL 의 CHAR 나  VARCHAR 컬럼에 데이타를 입력할 경우 컬럼 사이즈보다 큰 데이타가 입력됐을때 당연히 DB 차원에서 insert 가 안 되는줄 알았다.

 

그런데 특정 서버에서는 사이즈보다 큰 부분이 truncate 되고 insert 되어 찾아보니 MySQL 서버의 sql_mode 라는 설정의 strict mode에 따라 데이타 검증을 수행하는데 5.5의 경우 기본적으로 이 모드가 꺼져 있다고 한다.

create table a(b varchar(4));
insert into a values('12345');
select * from a;
+------+
| b |
+------+
| 1234 |
+------+
SQL
SQL

이뿐만이 아니라 다음과 같이 unsigned 컬럼에 -1 이 입력되면 자동으로 0 으로 변환하는 만행을 저지른다.

CREATE TABLE unsigned_int (a int unsigned);
INSERT INTO unsigned_int (a) VALUES (-1);
SELECT * FROM unsigned_int;
+------+
| a    |
+------+
|    0 |
+------+
SQL
SQL

MySQL 버전마다 다르겠지만 현재 사용하고 있는 5.5.40 의 기본 sql_mode 설정은 '' 이며 (https://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_sql-mode)

이 경우 STRICT_ALL_TABLES, STRICT_TRANS_TABLES  가 꺼지며 CHAR, VARCHAR 의 크기를 초과하는 데이타 입력시 에러를 내지 않고 잘리게 된다.

 

개인적인 생각으로는 이런 데이타의 무결성을  app 에서 하는것 보다는 DBMS 에서 체크하는게 더 편하고 이를 위해 DBMS 를 쓰는 것이므로 비록 속도가 저하되더라도 STRICT_MODE 로 쓰는게 좋다고 보다.

그러면 STRICT MODE 로 설정을 변경해 보자.

 

현재 설정은 mysql 의 console 에서 확인할 수 있다.

mysql> show variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
Plain text
CODE

이제 /etc/my.cnf 에 다음 내용을 추가하고 MySQL 을 재구동하면 설정이 반영된다.

[mysqld]
sql_mode="NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES"
Plain text
CODE

 

경축! 아무것도 안하여 에스천사게임즈가 새로운 모습으로 재오픈 하였습니다.
어린이용이며, 설치가 필요없는 브라우저 게임입니다.
https://s1004games.com

STRICT MODE 로 전환되었다면 다음 SQL 문은 오류가 나고 정상적으로 동작하지 않는다.

mysql>  INSERT INTO unsigned_int (a) VALUES (-1);
ERROR 1264 (22003): Out of range value for column 'a' at row 1
mysql> insert into a values('12345');
ERROR 1406 (22001): Data too long for column 'b' at row 1
Plain text
CODE

 

 

MySQL 5.7 부터는 STRICT_MODE 가 기본 설정이라고 하니 별도의 설정 작업이 필요없다.

non strict mode 사용

기존 app 를 최신 MySQL 에서 구동해야 하는 경우 strict mode 에 맞게 수정할 시간이 부족하다면 어쩔수없이 strict mode 를 끄고 서서히 수정하는게 필요하다.

만약 5.7 을 사용한다면 명시적으로 sql_mode 에 다음과 같이 non strict mode 를 지정해야 한다.

[mysqld]
sql_mode="IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
Plain text
CODE

설정이 끝났으면 mysql 을 재구동하고 sql_mode 를 확인한다.

mysql -u root -ppassword -e "SELECT @@sql_mode"
Plain text
CODE

 

Laravel 의 strict mode

laravel framework 는 기본적으로 MySQL 의 strict mode 를 켜며 자세한 내용은 아래 gist 의 sql_mode 항목을 참고

 

Ref