한 줄로
로그인 실패 키는 「DB가 같다고 보는 두 값은 소문자로 바꿔도 같다」는 가정에 기대고 있었다. 허용 문자 68개로 만들 수 있는 모든 쌍(2,278쌍)을 COLLATE utf8mb4_0900_ai_ci로 비교했고, 로컬 MySQL 9.6과 dev MySQL 8.4.8 모두 불일치 0, 무시되는 문자 0이 나왔다. 운영 DB(Aurora 8.0)는 접속 시간 초과로 확인하지 못했다. 검사 도중 두 가지가 드러났다. 구 DB의 정렬 규칙이 utf8mb4_general_ci라는 것, 그리고 설계 문서에 「없음」으로 적힌 유니크 키가 dev에 있다는 것이다.
정렬 규칙(collation)이란
문자셋(charset)은 문자를 어떤 바이트로 저장할지 정한다. 정렬 규칙(collation)은 문자열을 비교·정렬하는 방식이고, 두 문자열을 같은 값으로 볼지도 여기서 정해진다.
이름 끝의 _ci는 case-insensitive(대소문자 무시), _ai는 accent-insensitive(악센트 무시)라는 뜻이다. 그래서 utf8mb4_0900_ai_ci에서는 A와 a를 같은 값으로 본다.
문자셋이 똑같이 utf8mb4여도 정렬 규칙은 여러 가지다. MySQL 8.0부터 기본값인 utf8mb4_0900_ai_ci와 그 전부터 써 온 utf8mb4_general_ci가 대표적이다. 둘은 문자열 끝 공백을 다르게 다룬다. general_ci는 비교할 때 끝 공백을 무시하고(PAD SPACE), 0900_ai_ci는 끝 공백도 글자로 센다(NO PAD).
왜 확인해야 했나
회사 프로젝트에서 로그인 실패 처리를 설계하는 중이었다. 로그인 실패 키는 「DB가 같다고 보는 값은 소문자로 바꿔도 같다」는 가정 위에 서 있다. 이 가정이 깨지면 DB에서는 한 계정으로 보는 입력이 키에서는 둘로 갈라진다.
그래서 먼저 문서를 손봤다.
- 이 가정을 설계 문서 §14 구현 체크리스트에 항목으로 넣었다.
- §6.5에 근거 문장이 거꾸로 적혀 있어 바로잡았다.
- 설계 리뷰 R42에서는 codex와 Claude 모두 「문제 없음」이라고 했다.
체크리스트 항목은 실제 DB에서 돌려 보고 닫았다.
검사 방법
- 모든 쌍 비교: 허용 문자 68개로 만들 수 있는 모든 쌍, 즉 68×67÷2 = 2,278쌍을 비교한다. DB의 같음 판정과 소문자 변환 뒤의 같음 판정이 어긋나는 쌍이 하나라도 있는지 본다.
- 무시되는 문자 확인: 비교에서 통째로 무시되는 문자가 있는지도 본다. 그런 문자가 있으면 그 문자를 끼운 문자열이 원래 문자열과 같다고 판정된다.
-
정렬 규칙 명시: 비교할 때마다
COLLATE utf8mb4_0900_ai_ci를 붙인다. 열이나 접속의 기본 정렬 규칙과 상관없이 같은 규칙으로 비교하기 위해서다. -
대조군:
é와 제어문자를 넣어, 문제가 있는 문자를 검사가 실제로 잡아내는지 확인한다.
SQL은 collation-check.sql 파일 하나에 모았다.
결과
| 환경 | 버전 | 결과 |
|---|---|---|
| 로컬 | MySQL 9.6 | 불일치 0, 무시 문자 0. 대조군(é, 제어문자)은 정상적으로 잡힘 |
| dev | MySQL 8.4.8 | 로컬과 같은 결과 → §14 항목을 [x]로 변경 |
| 운영 | Aurora 8.0 |
ERROR 2003 ... (60) 연결 시간 초과로 확인 못 함 |
dev에는 AWS Secrets Manager에 저장된 dev 접속 정보로 붙었다. 운영은 같은 방법으로 접속하려다 시간 초과가 났다. 검사 하나 때문에 운영 보안 그룹을 바꾸지는 않았다. 대신 체크리스트에 「운영 전환 때 다시 검사」 항목을 추가했다.
구 DB는 utf8mb4_general_ci였다
검사하는 김에 dev의 구 스키마도 설계와 대조했다.
- 문자셋은 utf8mb4로, 설계와 같다.
- 정렬 규칙은
utf8mb4_general_ci였다. 테이블 73개 중 67개가 그렇고,user·admin테이블의 문자열 열은 전부 그렇다.
이 결과에서 데이터 이전 SQL을 짤 때 지킬 규칙이 하나 나온다. 구 열과 신 열을 비교하는 곳에는 COLLATE를 붙여야 한다. 비교 없이 값을 옮기기만 하는 INSERT ... SELECT는 정렬 규칙이 달라도 오류가 나지 않는다.
중복 판정이 바뀌는지도 따져 봤다. 앞에서 말했듯 두 정렬 규칙의 차이는 끝 공백 처리 하나다. 그런데 공백은 입력 규칙에서 이미 막혀 있다. 그래서 이전 전에 하는 중복 점검 결과는 어느 정렬 규칙으로 비교해도 같다.
문서에는 없던 유니크 키
설계 문서에는 관리자 ID 유니크 키가 「없음」으로 적혀 있었다. 그런데 dev에는 admin_uk_id가 있었다.
운영은 접속하지 못해서 같은 키가 있는지 모른다. 그래서 §8.2를 「dev에는 있지만 운영은 확인하지 못했으므로 중복을 센다」로 고쳤다. 운영에 키가 있다고 가정하고 중복 점검을 빼는 대신, 미확인으로 남겨 두고 중복을 세는 쪽을 택했다.
남은 것
- 운영 DB 재검사. 운영 전환 때 같은 SQL을 다시 돌린다.
- 설계 승인, 설계 아티팩트 재게시, 설계 티켓 갱신은 아직 하지 않았다.