본문 바로가기
Database/MYSQL

MySQL Strict mode 끄기/켜기

by 반화넬 2018. 5. 8.
반응형

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 |
+------+

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


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

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      |       |
+---------------+-------+

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


[mysqld]
sql_mode="NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES"


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



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"

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


mysql -u root -ppassword -e "SELECT @@sql_mode"


Laravel 의 strict mode

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

 여기를 클릭하여 펼치기...

#!/bin/bash
export MYSQL_CNF=$(cat <<CNF
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# strict SQL mode in MySQL 5.7 is default behaviour.
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
skip-character-set-client-handshake
collation-server=utf8mb4_unicode_ci
character-set-server=utf8mb4
#collation-server=utf8_unicode_ci
#character-set-server=utf8
max_allowed_packet = 256M
innodb_log_file_size=512M
transaction-isolation=READ-COMMITTED
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
CNF
)
mkdir -p /etc/docker/mysql.conf.d
echo "${MYSQL_CNF}" > /etc/docker/mysql.conf.d/mysqld.cnf
## run docker with "-volume=/etc/docker/mysql.conf.d:/etc/mysql/mysql.conf.d"
view raw mysql-conf.sh hosted with ❤ by GitHub



Ref


반응형

관련글

티스토리툴바