[MySQL] sql_mode로 알아보는 시스템 변수 permanent, runtime설정

2020. 3. 16. 09:54DB/MySQL

300x250
반응형

MySQL 서버에서는 운영에 필요한 구성설정에 이용되는 다양한 시스템 변수가 존재합니다.

MySQL 서버의 버전에 따라 각 시스템 변수는 기본값이 존재하고, 별도의 설정을 통해 값을 변경할 수 있습니다.

이번 시간에는 MySQL 시스템 변수중 sql_mode를 이용하여 시스템 변수를 설정하는 방법을 알아보도록 할 것입니다.


  1. sql_mode 란?
  2. sql_mode 설정 - 1. permanent
    2-1)
    my.cnf 파일 찾기
    2-2) my.cnf 파일에 sql_mode 설정 추가하기
    2-3) mysql client로 접속하여 현재 세션에 적용된 sql_mode 조회하기
    2-4) runtime 환경에서 바로 적용
    2-5) MySQL서버 재시작하기
  3. sql_mode 설정 - 2. runtime
    3-1) runtime 전역 설정
    3-2) runtime 세션 설정
  4. 정리
  5. 사용 예
    5-1) session에 sql_mode를 설정하기 전
    5-2) session에 sql_mode를 설정한 후
    5-3) session 종료 후 다시 접속한다면?

1. sql_mode 란?

sql_mode 는 MySQL에 저장될 데이터에 대한 유효성 검사(validation check) 범위를 설정하는 시스템 변수 입니다.

sql_mode에 활성화 하고자 하는 모드를 , 구분자로 구분하여 한줄로 추가합니다.

MySQL 버전별로 default sql_mode값이 다르며, sql_mode를 설정하여 데이터 유효성 검사 범위를 커스터마이징 할 수 있습니다.

현재 접속중인 세션에 적용되어있는 sql_mode를 조회하는 방법은 아래와 같습니다.

SELECT @@sql_mode;

sql_mode 설정 - 1. permanent

MySQL 8.0 에서는 기본적으로 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION 모드가 활성화 되어있습니다.

만일 sql_mode를 영구적으로 변경하고 싶다면
my.cnf(Windows OS에서는 my.ini) 파일에 sql_mode 속성을 추가하면 됩니다.

여기서 말하는 영구적 변경은 MySQL 서버의 재시작 또는 OS의 재시작 후에도 설정한 속성이 유지되는 것을 의미합니다.

2-1) my.cnf 파일 찾기

[jini@apple ~]$ sudo find / -name my.cnf
[sudo] password for jini:
/etc/my.cnf

find 명령어로 my.cnf 파일을 찾습니다.

C:\Users\jini>where /r C:\ my.ini
C:\ProgramData\MySQL\MySQL Server 5.7\my.ini

windows OS에서는 where 명령어로 my.ini를 찾습니다.
※ windows 용 MySQL에서는 버전에 따라 my.ini 파일이 없을수도 있습니다. 그럴 경우에는 MySQL이 설치된 디렉토리에 my.ini파일을 만들면 됩니다.


2-2) my.cnf 파일에 sql_mode 설정 추가하기

sudo vim /etc/my.cnf
[mysqld]

...

sql_mode="ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

[mysqld] 구간에 sql_mode 속성을 추가합니다.
활성화하고자 하는 모드는 ,로 구분하여 나열하여 쓰면 됩니다.

2-3) mysql client로 접속하여 현재 세션에 적용된 sql_mode 조회하기

mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+

mysql client로 접속하여 @@sql_mode를 조회했더니 my.cnf 파일에 설정했던 값이 아닌 default 설정값이 그대로 나오네요?

my.cnf로 설정한 정보는 MySQL 서버가 재시작된 후에 반영되는 정보이기 때문입니다.

2-4) runtime 환경에서 바로 적용

runtime 환경에서도 변경한 sql_mode를 적용하는 방법은 아래 2가지가 있다.

  1. MySQL 서버를 재시작
  2. @@global.sql_mode, @@session.sql_mode 설정

2-5) MySQL서버 재시작하기

[jini@apple ~]$ sudo systemctl restart mysqld
[jini@apple ~]$ mysql -u root -p
mysql> select @@sql_mode;
+---------------------------------------------------+
| @@sql_mode                                        |
+---------------------------------------------------+
| ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------+

mysqld 서비스를 재시작 한 후 다시 mysql client 접속을 합니다.
sql_mode를 조회했더니 이제는 my.cnf 에 설정한 값이 적용된것을 확인할 수 있었습니다.
(위의 sql_mode 설정값은 테스트를 위해 임의로 설정한 값이므로 my.cnf에서 sql_mode 설정정보를 제거하고 mysqld 서비스를 재시작 합시다.)

MySQL 서버를 재시작하면 my.cnf에 설정한 사항을 런타임 세션 및 글로벌 영영에서 적용할 수 있습니다. 그러나, MySQL 서버를 종료하면 안되는 상황이라면 어떻게 적용할 수 있을까요?
바로, 영구설정과 런타임 설정을 함께하는 것입니다!

두번째 방법인 @@global.sql_mode, @@session.sql_mode 설정 부분은 아래에서 알아보도록 하겠습니다.


3. sql_mode 설정 - 2. runtime

3-1) runtime 전역 설정

@@global은 MySQL 서버의 runtime 환경 전역을 의미합니다.

SET @@global.sql_mode="NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

SET @@global.sql_mode=로 MySQL 서버의 runtime 환경 전역에 설정합니다.
runtime이 끝나기 전까지(= MySQL 서버가 재시작 되기 전까지) 설정정보가 유효합니다.

runtime 환경에 설정하는 것이기 때문에 MySQL 서버가 재시작할 경우
my.cnf에 설정된 값(my.cnf에 별도의 설정이 없을 경우에는 MySQL Server 버전별 default값)으로 변경됩니다.

3-2) runtime 세션 설정

@@session은 client가 접속한 세션 영역입니다.

SET @@session.sql_mode="NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
SET @@sql_mode="NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

SET @@session.sql_mode=으로 세션영역에 sql_mode를 설정할 수 있으며, session은 생략 가능합니다.
@@session.sql_mode의 default 값은 @@global.sql_mode에 설정된 값입니다.(my.cnf에 설정된 값이 아닌 runtime-global에 설정된 값)

세션 시작 시 global영역에 설정된 sql_mode를 가져와 세션영역에 적재하며,
세션 중에 변경된 global설정은 자동으로 변경되지는 않습니다(※ 주의)

sql_mode 설정을 현재 접속중인 세션중에만 유효하도록 설정하고 싶다면 @@session.sql_mode에 설정하면 됩니다.


4. 정리

Q : MySQL 서버를 재시작하지 않으면서 영구적으로 설정하고 싶다면?

my.cnf 설정, @@global.sql_mode, @@sql_mode 설정

Q : MySQL 서버 재시작 전까지만 설정하고 싶다면? (runtime)

@@global.sql_mode, @@sql_mode

Q : 현재 접속중인 session에서만 이용할거라면?

@@sql_mode


5. 사용 예시

5-1) session에 sql_mode를 설정하기 전

mysql> select @@global.sql_mode;
+---------------------------------------------------------------------------------------------+
| @@global.sql_mode                                                                           |
+---------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select @@session.sql_mode;
+---------------------------------------------------------------------------------------------+
| @@session.sql_mode                                                                          |
+---------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO test(type, name, create_dt) VALUES('0', 'sol', '0000-00-00');
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00' for column 'create_dt' at row 1

MySQL 서버의 @@global.sql_mode가 STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION로 설정되어 있습니다.(ln 1~7)

MySQL client가 접속하여 얻은 session의 sql_mode은 아직 별도의 설정을 하지 않았기 때문에 global.sql_mode와 같은 결과를 출력합니다.(ln 9~15)

세션의 sql_mode에 STRICT_TRANS_TABLES와 NO_ZERO_DATE 모드가 활성화 되어있기 때문에 날짜 컬럼에 0이 들어갈 수 없습니다.(ln 17-18)

5-2) session에 sql_mode를 설정한 후

mysql> set @@session.sql_mode='NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@global.sql_mode;
+---------------------------------------------------------------------------------------------+
| @@global.sql_mode                                                                           |
+---------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select @@session.sql_mode;
+------------------------------------------------------------+
| @@session.sql_mode                                         |
+------------------------------------------------------------+
| NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO test(type, name, create_dt) VALUES('0', 'sol', '0000-00-00');
Query OK, 1 row affected (0.00 sec)

세션의 sql_mode에서 NO_ZERO_DATE와 STRICT_TRANS_TABLES를 비활성화 합니다.(ln 1~2)

session 영역에서는 sql_mode가 설정되어있으며, global영역에서는 변동사항이 없는 것을 확인할 수 있습니다.(ln 4~18)

datetime에 0을 넣어도 에러 출력없이 insert됩니다.(ln 20~21)

mysql접속을 종료한 후 다시 로그인하여 @@session.sql_mode값을 조회할 경우 @@global.sql_mode로 설정된 값이 다시 출력됩니다.

5-3) session 종료 후 다시 접속한다면?

세션이 종료된다는 말은 현재 접속중인 MySQL Command line Client를 종료하거나, MySQL Workbench CE와 같은 툴을 끄면서 접속을 끄는것을 의미한다.
2)번 과정에서 변경했던 sql_mode가 그대로 나올까?

mysql> exit
Bye
[jini@apple ~]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement

mysql> select @@session.sql_mode;
+---------------------------------------------------------------------------------------------+
| @@session.sql_mode                                                                          |
+---------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

session을 종료하고 다시 접속할 경우 MySQL 서버에서는 @@global.sql_mode에 설정된 값을 다시 @@session.sql_mode에 설정합니다.


+++

  • set MySQL Server System Variables
300x250
반응형