[ISSUE] MySQL dump/load시 sql_mode 이슈

배경

지속적으로 MySQL 버전 업 작업을 위해 여러 테스트중, 우선 동일 버전의 데이터를 다른 로컬 DB서버로 dump/load하는 중인데..

 

이슈

Task 'MySQL restore' started at Tue Jan 07 13:22:16 KST 2025
ERROR 1067 (42000) at line 12372: Invalid default value for 'date_created'

Task 'MySQL restore' finished at Tue Jan 07 13:41:47 KST 2025
2025-01-07 13:41:47.160 - IO error: Process failed (exit code = 1). See error log.
2025-01-07 13:41:47.160 - java.io.IOException: Process failed (exit code = 1). See error log.
	at org.jkiss.dbeaver.tasks.nativetool.AbstractNativeToolHandler.validateErrorCode(AbstractNativeToolHandler.java:263)
	at org.jkiss.dbeaver.tasks.nativetool.AbstractNativeToolHandler.executeProcess(AbstractNativeToolHandler.java:241)
	at org.jkiss.dbeaver.tasks.nativetool.AbstractNativeToolHandler.doExecute(AbstractNativeToolHandler.java:283)
	at org.jkiss.dbeaver.ext.mysql.tasks.MySQLNativeToolHandler.doExecute(MySQLNativeToolHandler.java:47)
	at org.jkiss.dbeaver.tasks.nativetool.AbstractNativeToolHandler.lambda$0(AbstractNativeToolHandler.java:87)
	at org.jkiss.dbeaver.runtime.RunnableContextDelegate.lambda$0(RunnableContextDelegate.java:39)
	at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:122)

 

깜찍하게도 요런 에러가 떴다. 시간을 보면 알겠지만 20분을 기다리게해놓코😭

이유를 찾아보니,,,

 

 

원인

https://stackoverflow.com/questions/36882149/error-1067-42000-invalid-default-value-for-created-at?utm_source=chatgpt.com

ERROR 1067 (42000): Invalid default value for 'created_at'

예 여기 저와 동일한 문제를 겪으신 분이 계시는군요.

 

 

MySQL 5.7 버전(현재 내가 사용중인 버전임)의 error 정의문서를 보면

error 1067은 테이블 생성 또는 수정 시 유효하지 않은 기본값이 지정되었을 때 발생하는데,

주로 DATE 또는 DATETIME 타입 컬럼에 기본값으로 '0000-00-00' 또는 '0000-00-00 00:00:00'과 같은

잘못된 날짜 값을 설정하려 할 때 나타난다.

그니까 뭐가 어쨌든 날짜엔 날짜가 들어가야지 0은 안된다..?

 

 

그럼 기 DB에서는 문제없던 데이터가, 왜 뉴 DB에서는 안되는가?

이런 부분은 같은 버전의 DB라고 해도 SQL 모드의 설정에 따라 달라질 수 있다.

결론적으로는 뉴 DB의 sql_mode에 설정된 NO_ZERO_IN_DATE 와 NO_ZERO_DATE 설정으로 인해 생긴 오류였다.

 

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-important.

MySQL :: MySQL 5.7 Reference Manual :: 5.1.10 Server SQL Modes

 

sql 모드 상세설명 ref : https://priming.tistory.com/117

 

 

 

해결

우선 현재 DB의 sql mode에 대해 파악한다.

SELECT @@sql_mode;

 

기DB : IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

 

뉴DB : 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

 

 

암흑의 원인들을 제거함과 동시에 혹시 모를 다른 문제들을 방지하기위해

그냥 기DB와 동일한 sql_mode로 수정한다.

set SESSION sql_mode = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

set GLOBAL sql_mode = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

 

현재 session에만 sql_mode를 적용하려면 SESSION을,

앞으로 연결될 모든 뉴 세션에도 sql_mode를 적용하려면 GLOBAL을 쓰면 되는데

나는 지금도 앞으로도 영원히 쓸거라서 둘 다 설정했다.

 

그리고 my.ini도 수정함

sql-mode="IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

 

파일위치 : C:\ProgramData\MySQL\MySQL Server\my.ini