최근에 서버 이전을 진행하게 되었습니다. 흔히 볼 수 있는 그누보드5 로 Nginx + PHP5.6 + MariaDB 을 사용하는 녀석입니다.
간단히 될 거라고 생각했고 어느정도 예상대로 큰 문제 없이 이전하였습니다.
기존 서버와 신규 서버는 모두 Ubuntu 18.04 64bit 였는데요.
일단 DB 이전과 PHP 웹소스 이전, 업로드 파일 및 도메인 이전까지 순조롭게 되었습니다.
사이트도 잘 열리고..
그런데 다음날 API 연동이 안된다는 겁니다. ㅜㅜ
웹서버 로그를 확인해보니 GET 은 이상이 없는데.. POST 에서 에러 500을 띄우고 있었습니다.
에러나는 부분을 찾아보니 API 에서만 사용하는 stored function 에서 나는 오류더군요.
증상은 다음과 같습니다.
(HY000/1364): Field '<필드명>' doesn't have a default value해당 필드가 NOT NULL 인데 기본값이 지정되지 않아서 오류가 발생한 겁니다.
일반적인 개발 상황이라면 큰 문제가 없는 오류입니다. 해당 필드에 기본값을 설정하던가 아니면 insert 문장에 해당 필드를 값을 명시적으로 지정해 주면 끝나는 문제니까요.
하지만 동일한 환경설정으로 서버 이전한 상황에서 이런 에러는 나올수 없는 부분인데요..
일단 구글에서 해당 에러를 검색하면 SQL MODE 를 확인하고 STRICT 설정을 빼고 변경하라고 나옵니다.
- 먼저 SQL MODE 를 확인합니다.
# mysql -uroot -p
MariaDB [db]> SELECT @@GLOBAL.sql_mode;
+--------------------------------------------+
| @@GLOBAL.sql_mode |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)
결과를 보면 STRICT_TRANS_TABLES 이 있으니 이것을 제거하고 다음과같이 모드값을 바꿔주면 됩니다.
MariaDB [(none)]> SET @@GLOBAL.sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SELECT @@GLOBAL.sql_mode;
+--------------------------------------------+
| @@GLOBAL.sql_mode |
+--------------------------------------------+
| NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)
이렇게 하면 문제가 해결된다고 합니다. 하지만 제 DB서버는 이미 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 로 SQL MODE 가 설정되어 있었습니다.
슬슬 짜증이 올라옵니다. ㅜㅜ
실제 위와같이 해보고 API 테스트를 해봐도 같은 증상이 발생하더군요. 결국 울며겨자먹기로 테이블 필드 설정에 기본값 ” 을 넣어보기 시작했습니다. 일단 하나 수정하니 또 다음 필드.. 그거 수정하니 또 다음 필드.. 에러나는 모든 필드에 기본값 ” 을 설정하였습니다.
이제는 되겠지 하고 테스트 하는 순간 못보던 오류가 또 출력됩니다.
ERROR 1366 (22007): Incorrect integer value: '' for column '' at row 1빡침 최고조 단계입니다. (아오!)
결국 기본값 문제가 아니라는 것을 깨닫고 다시 SQL MODE 관련해서 검색을 더해봤습니다. 위 로그로 검색해보고요.
결국 MariaDB 공식 설명서에 다음과 같은 문구를 발견했습니다.
SQL_MODE and Stored Programs
Stored programs and views always use the SQL_MODE that was active when they were created. This means that users can safely change session or global SQL_MODE; the stored programs they use will still work as usual.
It is possible to change session SQL_MODE within a stored program. In this case, the new SQL_MODE will be in effect only in the body of the current stored program. If it calls some stored procedures, they will not be affected by the change.
Some Information Schema tables (such as ROUTINES) and SHOW CREATE statements such as SHOW CREATE PROCEDURE show the SQL_MODE used by the stored programs.
https://mariadb.com/kb/en/sql-mode/#sql_mode-and-stored-programs
위 굵게 처리한 문장의 설명과 같이 stored program 즉 stored procedure/function 의 경우 생성될 당시의 SQL_MODE 를 사용한다는 것입니다. 결론은 전역설정의 SQL_MODE 를 사용하지 않는다는 의미인데요. 아무래도 DB 복원할때 뭔가 잘못된 가 같습니다. 예전처럼 function 들만 따로 백업받아서 복원할것을 귀찮아서 데이터랑 한번에 백업/복원해버렸거든요. (실제로 루틴만 백업 했을때는 SQL_MODE 가 주석으로 지정되는 것을 볼 수 있습니다.)
이제 문제되는 function 의 생성코드를 확인해 보겠습니다.
MariaDB [(none)]> show create function `<함수명>`\G;
*************************** 1. row ***************************
Function: <함수명>
sql_mode: IGNORE_SPACE,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Create Function: CREATE ...(중략)...
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)
ERROR: No query specified
드디어 찾았습니다. 함수 생성시 sql_mode 에 STRICT_TRANS_TABLES 가 들어가서 그동안 문제가 되었던 거였습니다. ㅜㅜ
이제 SQL_MODE 값을 바꿔주면 되는데요.
아직 FUNCTION 을 바꾸는 방법을 알지 못해서 급한대로 다음과 같이 SET 명령어를 DECLARE 구문 이후에 추가해서 함수 내에서 SQL_MODE 바꾸도록 해서 해결했습니다.
CREATE DEFINER=`...` FUNCTION <함수명> ...
BEGIN
DECLARE ...;
DECLARE ...;
DECLARE ...;
SET @@SQL_MODE = REPLACE(@@SQL_MODE, 'STRICT_TRANS_TABLES', '');
INSERT ...
END이제 API 테스트를 해보면 정상적으로 됩니다. (야호!)
제대로 하결하기 위해 함수 수정이 아닌 삭제후 재등록 방식이 간단합니다.
함수들만 백업하기
$ mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt DB명 > 백업파일.sql백업 파일을 열어보면 아래와 같은 구문이 계속 나올겁니다.
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'IGNORE_SPACE,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER ...
각 함수 생성 구문 위에 있는 sql_mode 값에서 STRICT_TRANS_TABLES 를 제거합니다.
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'IGNORE_SPACE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER ...파일을 저장하고 프로시져나 함수들을 모두 삭제 후 수정한 백업파일을 복원시키면 됩니다. (삭제시 cli 에서는 함수 하니씩 삭제해야 하다보니 gui (DBeaver 애용중)에서 선택해 삭제해버렸습니다. ^^; )
$ mysql -u사용자명 -p DB명 < 백업파일.sql
무작정 sql_mode 를 ” 빈칸으로 변경하니 또 안되더군요. 결국 STRICT_TRANS_TABLES 만 제거하니 잘 동작하였습니다. 더 좋은 방법이 있다면 댓글로 알려주시면 감사하겠습니다. ㅜㅜ
결론
stored procedure 와 stored function 및 view 들은 실행시 전역 설정의 SQL_MODE 를 사용하지 않고 생성시 SQL_MODE 를 따라가므로 백업 및 복원시 주의해야 합니다.
SQL MODE 에 STRICT_TRANS_TABLES 가 추가된 경우 (MariaDB 10.2.4 부터는 기본적으로 들어갑니다.) INSERT 시 자동으로 기본값이 지정되거나 너무긴 문자열 처리, 수치형필드에 수치값 문자열 대입등이 있을 때 에러가 발생합니다. (With strict mode not set (default in version <= MariaDB 10.2.3), MariaDB will automatically adjust invalid values, for example, truncating strings that are too long, or adjusting numeric values that are out of range, and produce a warning.)
참고
https://mariadb.com/kb/en/sql-mode/#sql_mode-and-stored-programs
답글 남기기
댓글을 달기 위해서는 로그인해야합니다.