[MySQL] SQL_MODE | Eclipse에서 Toad Plugin 사용시에 세션의 SQL_MODE를 서버의 SQL_MODE와 동일하게 적용

 MySQL에는 SQL의 문법과 데이터의 유효성을 검사하는 방법을 SQL_MODE라는 옵션변수에 저장을 한다. 해당 값은 my.ini , my.cnf 에서 설정이 가능하며 MysQL 5.6 부터는 기본이 'STRICT_TRANS_TABLES' 이 적용된다. 아래는 MySQL 5.6의 my.ini파일의 내용중 SQL_MODE가 기본 설정되어 있는 부분의 내용이다.

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

 SQL_MODE의 종류와 자세한 설명은 아래의 링크에서 확인 할 수 있다.

• MySQL 5.0 Manual : 5.2.5 서버 SQL 모드
• MySQL 5.1 Manual : 5.2.6. SQL 모드
  
• MySQL 5.6 Manual : Server SQL Modes

 지금 영문 매뉴얼을 확인하니 5.6.6 이후 버전은 SQL_MODE가 'NO_ENGINE_SUBSTITUTION' 가 기본이며 이전 버전은 SQL_MODE의 설정이 비어있다고 하였으나... 위에서 이야기한대로 5.6의 실제 설정파일과 처음 기동시에 SQL_MODE는 위와 같은 값을 기본으로 설정 하고 있었다.

 SQL_MODE 는 서버의 재시작 없이도 글로벌 변수를 변경하여 접속하는 클라이언트에 대해 해당 모드에서 작업이 가능할수 있게 한다. 또한 클라이언트의 세션 단위에서 모드를 변경하여 SQL 작업을 수행 할 수 있다. 해당모드는 아래와 같은 방법으로 확인이 가능하다.

SELECT @@GLOBAL.SQL_MODE, @@SESSION.SQL_MODE;

 보통의 클라이언트 툴(Toad, MySQL Workbench...)등은 MySQL 접속시에 해당 SQL_MODE를 서버의 환경을 그대로 사용하는데 Eclipse Toad Plugin의 경우 Java의 jdbc driver을 사용하는 이유로 MySQL서버와 연결을 한후 세션의 SQL_MODE를 강제로 'STRICT_TRANS_TABLES' 로 변경한 후 SQL 작업을 수행한다.

 일반 SQL의 경우 큰 문제는 없을 수 있으나 Procedure 생성시에 약간의 문제가 발생한다.  일단 아래를 실행하여 보면 대충 짐작이 가능할 것이다.

SELECT A.ROUTINE_NAME, A.SQL_MODE, A.*
FROM INFORMATION_SCHEMA.ROUTINES A
WHERE A.ROUTINE_SCHEMA = '데이터베이스이름'

 MySQL의 Procedure 생성시에 서버의 SQL_MODE가 아닌 클라이언트 세션의 SQL_MODE로 Procedure로 자동 생성되어 들어가는 듯 하다. 이 때문에 원래의 MySQL Database가 'STRICT_TRANS_TABLES' 가 아닌경우 이전 작성된 Procedure를 재 컴파일하여 Procedure를 생성하게 되면 기존에 루즈하게 사용하며 해당 Procedure를 사용하던 소스는 모두 에러를 뱉어 내게 될 것이다.

 글의 제목은 SQL_MODE의 설명은 아니었으나 얘기 하다 그쪽으로 치우쳤는데....... 아무튼 Eclipse Toad Plugin으로 MySQL과 연결하여 SQL 작업을 할때 해당 부분을 해결 하기 위해선 Toad Perspective 에서 Connection 생성시에 CustomConnection String 으로 생성하여 아래 이미지에서 보는 옵션을 주어 SQL_MODE의 변환을 막을 수 있다. 해당 옵션에 대해선 다음기회에...

jdbc:mysql://localhost:3306/test?jdbcCompliantTruncation=false