균로그

🤯 문제 상황

운영에서 insert 쿼리에 timestamp 컬럼에 NULL 을 넣지만, not null 타입이라 cannot be null 에러가 발생했다.

언뜻보면 당연한 결과인 것 같지만, 인프라 이관 작업에서 발생한 이슈라는 점에서 봤을때 잘 동작하는 코드가 환경이 변해서 에러가 발생하는 상황인 것이다.

따라서 환경에 따른 문제인 것으로 보여서, 이를 힌트로 잡고 문제를 찾아 나갔다.

👨‍⚕️ 원인 분석

1. 인프라 이관 전 환경에서 에러 flow를 따라 동일하게 실행 (MySQL 5.7)
   → 정상 동작
2. 인프라 이관 후 환경에서 동일하게 실행 (MySQL 5.7)
   → 에러 발생
   

   INSERT INTO BSI_BENEFIT_BASES
   (
   	...
       , CREATION_DATE
       , LAST_UPDATE_DATE
   )
   VALUES
   (
   	...
       , CURRENT_TIMESTAMP()
       , null
   )

3. Local MySQL에서 insert 쿼리 동일하게 실행 (MySQL 8.0)
   → 동일하기 cannot be null 에러 발생

위 단계만으로는 원인을 알기 어려워 DBA 분께 문의 넣었다.

################ DBA와의 대화 넣기

DBA 답변
두 DB 상에서 explicit_defaults_for_timestamp 변수 값이 다르네요. 오류가 안나는 곳은 값이 OFF이고, 오류가 나는 곳은 ON입니다. mysql 서버 버전이 5.7.12 → 5.7.40으로 변경하며 변수 기본값이 다른 것 같습니다.
https://minsql.com/mysql/MySQL-explicit_defaults_for_timestamp/

라고 하시며 친절히 설명 블로그와 사진까지 보내주셨다.

정리하자면, 이전 환경(MySQL 5.7.12)에서는 OFF가 기본값으로 설정되어 있어서 NULL을 insert 하면 current timestamp가 입력된다.

이관된 후 환경(MySQL 5.7.40)에서는 기본값이 ON으로 설정되어 NULL을 똑같이 입력하더라도 에러가 발생하는 것이다.

여기서 한가지 의문이 생긴다.

왜 Local MySQL(8.0)에서는 안됐을까?

찾아보니, 5.7.40 버전과 동일하게 8.0에서는 기본값이 ON으로 설정되어 있다. [공식 문서]

MySQL 8.0 문서에서는 8.0부터 해당 변수의 default값이 변경 되었다고 나와있다. 아마 인프리 이관하면서 해당 변수 설정이 누락되지 않았나 예상한다.

🔏 해결방법

해결방법은 매우 간단하다. explicit_defaults_for_timestamp 변수 값을 OFF로 설정해 주거나, mysql 설정에 의존하지 않고 CURRENT_TIMESTAMP() 를 넣어주도록 쿼리를 수정하면 된다.

나는 DB 버전에 따른 기본값 설정을 따라가는 쿼리는 좋지 않다고 느꼈다. 시간이 지나면 DB 버전은 올리게 될 것이고, 버전 업그레이드에 따른 리스크를 안고 가고 싶지는 않았다. 따라서 쿼리에 default 값을 직접 명시해주는 방법을 선택하였다.

문제상황

회사에서 법+수사기관 관련된 요건이 들어왔다. 예상한대로 요건은 굉장히 복잡했고, entity 하나에 30개에 육박하는 필드를 가진 무시무시한 녀석이 탄생했다. 여기서 문제는 10개 이상의 필드에 대해서 대소문자 구분없는 검색 을 할 수 있도록 해달라는 것이였다. 나는 필수값이 없으니 index를 잡기도 어렵고, 심지어 대소문자 구분은 lower() 함수를 사용해서 모두 구분해야해서, index를 잡을수도 없다고 생각했다. 그래서 대소문자 구분없이 검색하는 것이 정말로 유용한 필드에서만 적용하는게 어떻겠냐고 제안했었다.

개발 완료 후 QA 중, "다른 필드는 안해주셔도 되는데 대소문자 구분없이 검색 가능하도록 모든 필드에 적용해주셨네요~" 라고 하셨다.

읭?? 나는 그런적이 없는데...? 해서 DB에 직접 sql을 쳐보니... 이게 웬걸!? 너무나 잘되었다...!
나는 또다시 나의 무지함에 감탄하며 어떻게 대소문자 구분없이 동작하는지 찾아보았다.

MySQL의 마법

mysql에서는 기본적으로 CHAR, VARCHAR, TEXT 자료형의 경우 대소문자 구분이 없다고 한다. 대소문자를 구분하고 싶으면 BINARY, VARBINARY, BLOB 자료형을 사용하거나, collation을 변경하라고 합니다. 관련 stackoverflow

음... 기본적으로 대소문자를 구분하지 않는다면, binary 데이터는 분명 값이 다를테니까 BINARY 자료형을 쓰라는거군! 여기까진 이해했다. 그럼 왜 mysql은 기본적으로 대소문자를 구분하지 않는것일까?

• Collation

mysql 공식문서를 확인해보면, CHAR, VARCHAR, TEXT 자료형은 검색 시 collation을 사용한다고 나와있다. collation이 뭔데?

mysql collation 공식문서에서, "collation은 character string을 비교, 정렬하는 rule의 집합이다" 라고 설명하고 있다.
mysql에서 collation 설정으로 인해 문자열 비교/정렬 기준이 정해지는 것이고, 이로 인해 대소문자 구분을 하지 않는거구나! 그렇다면 collation의 종류는 어떤게 있을까?

collation은 아래 SQL을 실행해보면 어떤 종류가 있는지 알 수 있다.

-- 모든 collation 확인
SHOW COLLATION;

-- utf8mb4의 charset의 collation 확인
SHOW COLLATION LIKE 'utf8mb4%';

이름이 너무 어려운데, 어떻게 구분하면 될까?

뒤에 _ci 가 붙는 애들은, case-insentive 즉 대소문자 구분하지 않는 얘들이다.
자세한건 나중에 더 찾아봐서 추가로 설명하겠다.

그럼 mysql에서는 어떤 설정이 기본일까?
mysql은 8.0부터는 charset은 utf8mb4, collation은 utf8mb4_0900_ai_ci 가 기본 설정이다. 그래서 내가 의도하지 않는 대소문자 구분 기능개발이 됐었던 것이다...ㅎ

여기서 궁금한점이 생겼다.
mysql이 비교 연산에 쓰이는 로직을 구분하고 있다는 것인데, 그럼 collation별 성능 차이가 존재하지 않을까? 어느정도 차이가 날까?

https://www.percona.com/blog/charset-and-collation-settings-impact-on-mysql-performance/
이 블로그를 확인하면 collation과 charset 별, 그리고 mysql 버전에 따른 성능 비교를 확인할 수 있다.

후기

팀원들에게 나의 삽질을 공유했다. 그 때 들어온 질문은 "실제로 모두 소문자가 기록되는건가요?" 였다. db에서 실제로 데이터를 조회해보면 대소문자 구분 있이 주기 때문에 실제 저장의 변화는 없고, 비교/정렬 등의 연산에만 사용되는 기준! 이라고 보면 될 것 같다. (소문자로 저장이 되었다면, db입장에서는 어떤게 대문자였는지 구분조차 하기 어려웠을 것이다)