MySQL 트러블슈팅 회고 (feat. 대소문자 구분)

업무 중 겪었던
찾기 까다로웠던 이슈의 트러블슈팅 과정을 정리합니다.

MySQL에서 문자열 대소문자가 비교되지 않는 문제에 대한 트러블슈팅 과정을 다룹니다.
비슷한 이슈 처리를 위해 보시는 경우 해결법 섹션부터 보셔도 무방합니다.

이슈 발생

n분 주기로 특정 데이터를 외부 데이터와 동기화해주는 로직이 있었습니다.
그런데 데이터 하나가 외부 데이터와 차이가 큰 상태임에도 동기화가 안 되고 있는 것이 문제였습니다.

동기화 로직에서 하는 일
1. 외부 데이터 동기화
외부 데이터와 구분자(ex. key)를 비교하여, 같을 시 해당 외부 데이터와 같도록 업데이트
2. deprecated 된 기존 데이터 삭제
기존 데이터의 key값이 외부 데이터 목록에 존재하지 않을 시, 해당 기존 데이터 삭제 처리

앞으로 위 로직을 칭할 때 각각 1번 로직, 2번 로직이라고 칭하겠습니다.

코드로 최대한 보기 좋게 정리하자면 아래와 같은 형태입니다.
각 라인마다 실행되는 쿼리는 바로 윗라인 주석으로 작성해두었습니다.

# 1. 외부 데이터 동기화
for external_row in external_rows:
	# SELECT * FROM ORIGINAL WHERE key = external_row['key']
	original_row = db.select(where = (original_row['key'] == external_row['key']))
		
	if original_row != None:
	
		# UPDATE ORIGINAL SET ... WHERE key = external_row['key']
		original_row = db.update(external_row)

---

# 2. deprecated된 기존 데이터 삭제
external_row_keys = external_rows['key'].to_set()

# SELECT * FROM ORIGINAL WHERE deletable = true
deletable_original_rows = db.select(where = (deletable = true))

for original_row in deletable_original_rows:
	if original_row['key'] not in external_row_keys:
	
		# UPDATE ORIGINAL SET deleted_at = now() WHERE key = original_row['key']
		original_row['deleted_at'] = now()

일단 운영 중 문제가 없어야 하니, 수동으로 데이터를 업데이트해서 해결해두고
근본적인 원인을 찾고자 로그를 분석하기 시작했습니다.

접근법

두 가지 가설
1. 기존-외부 데이터 간 key 값이 서로 달라서, 동기화 기준에 맞지 않아 1번 로직을 타지 않았다.
2. 업데이트 중 무언가 에러가 나서 동기화가 안 됐다.

그래서 기존 데이터 로그와, 외부 데이터의 로그를 비교했습니다.

분석 결과

original_key = "100ML water please"
external_key = "100ml water please"

외부 데이터의 로그를 봤을 때, 문자열이 다르므로 1번 가설에 해당이 되는 것 같았습니다.
이 경우엔 original에 해당하는 row 삭제 후, external_key에 해당하는 row를 새로 만들어서 insert하는 식으로 처리하고 있었습니다.

그럼 original은 삭제, external은 insert가 돼서 결국 external쪽 데이터로만 남아야 정상입니다만...
original 쪽 데이터로 계속 남아있는게 문제였습니다.

에러 로그를 확인해보았습니다.

java.lang.IllegalStateException: Multiple representations of the same entity

JPA 에러 중 하나인데,
한 트랜잭션 내에서 row를 업데이트 시도할 때, 같은 id를 가졌지만 서로 다른 hashcode를 가진 인스턴스들이 있는 경우 발생합니다.
즉 업데이트할 때 봐야할 참조 데이터가 여러개라 어느 쪽으로 업데이트할지 모르겠다는 의미와 비슷합니다.

같은 row를 서로 다른 값으로 여러번 업데이트하려고 한다는 뜻인데, 이해가 되지 않았습니다.

위에서 보여드린 예시 코드에서 UPDATE문을 실행하는 곳은 단 두 곳 뿐입니다.
이게 둘 다 실행되어야 말이 되는것 같은데, 논리적으로 생각했을 땐 불가능합니다.
1번 로직에선 외부 데이터에 값이 있는지를 비교하고, 삭제 로직에선 외부 데이터에 값이 없는지를 확인합니다.
즉 둘 다 실행되려면 값이 외부 데이터 중에 있으면서도 없다는게 되는데 말이 안 됐습니다.

그래서 결국 로컬에 데이터를 똑같이 맞추고, 브레이크포인트 찍어가며 원인을 분석했습니다.

찾았다!

정리하자면, 문제되는 데이터가 1번과 2번 로직을 동시에 타고 있었던게 맞습니다.
즉 외부 데이터 동기화도 시도하고, 기존 데이터 삭제도 시도했으니

업데이트와 삭제가 동시에 시도되고 있었다… 입니다.

그러나 JPA 에러로 실제 처리까지 이어지진 않았으니, 기존 데이터로 쭉 남아있었던겁니다.
그런데 왜 동시에 시도되고 있었을까요 ...?

대소문자 비교

핵심은 1번과 2번 로직의 대소문자 비교 방식에 차이가 있었습니다.

아래는 각 로직 내 key 값을 비교하는 부분입니다.

# 1. 외부 데이터 동기화
# SELECT * FROM ORIGINAL WHERE key = external_row['key']
original_row = db.select(where = (original_row['key'] == external_row['key']))
---
# 2. deprecated된 기존 데이터 삭제
if original_row['key'] not in external_row_keys:

차이점은 문자열을 비교하는 부분입니다.
1번은 문자열 비교를 DB 쿼리로 했고, 2번은 코드로 했습니다.

혹시나 싶어서 DB에 저 쿼리를 직접 입력해보았습니다.

SELECT * FROM ORIGINAL WHERE key='100ML water please'

결과는…

id	key
…	100ml water please

여기서 깨달았습니다.

MySQL에서 문자열 대소문자 비교가 안 되고 있구나…
정확히는 key 컬럼이 VARCHAR형이라서 그렇습니다.
찾아보니 대소문자 구분용 문자열 타입은 따로 있었습니다. (VARBINARY 타입 등)

즉 1번 로직과 2번 로직을 같이 탔던 이유는
1번에서는 문자열이 같은것으로 취급되었고, 2번은 다른 것으로 취급되었기 때문입니다.

해결법

1. BINARY 키워드

BINARY라는 대소문자 구분 키워드를 붙이면 됩니다.
일단은 아래와 같이 쿼리하게 해서 해결했습니다.

# AS-IS
SELECT * FROM WHERE key  = 'string';

# TO-BE
SELECT * FROM WHERE key = BINARY 'string';

주의:
값이 아니라 컬럼 쪽에 BINARY 를 붙여도 동일하나, 인덱스 보유한 컬럼일 경우 인덱스를 타기 어려워지기 때문에 권장하지 않습니다.

근데 이렇게 대소문자 필요할 때마다 하나하나 키워드 붙이는게 사실 번거롭습니다.
이런 키워드 안 붙여도 항상 대소문자 구분하게끔 할 순 없을까요?

2. DDL로 해결법

2-1. Collation 지정

ALTER TABLE ORIGINAL MODIFY `key` varchar(32) BINARY NOT NULL;

이렇게 DDL에 붙일 수도 있습니다.

이 경우 쿼리할 때 BINARY 여부를 신경쓰지 않아도 항상 대소문자 구분이 가능합니다.
하지만 이 경우 쿼리만으로 대소문자 구분이 될지 안 될지 알기 어려워집니다.

아래 커맨드로 확인해볼 수 있습니다.

SHOW FULL COLUMNS FROM ORIGINAL;

Field	Type	Collation
key	varchar(32)	utf8_bin

Collation 값으로 확인해보면 됩니다.
꼭 utf8_bin은 아닐 수도 있지만, 보통 뒤에 ci(case-insensitive) 자가 붙지 않으면 대소문자 구분을 하는 상태입니다.

• utf8_bin: 대소문자 구분
• utf8_general_ci: 대소문자 구분 X

2-2. BINARY 타입으로 지정

MySQL 문자열 타입에는 아래 두가지 분류가 있습니다.

• nonbinary 타입: 문자열 비교 시 collation을 활용하여 비교
  - CHAR, VARCHAR, TEXT
• binary 타입: 바이트 값 자체를 비교 (항상 대소문자 구분됨)
  - BINARY, VARBINARY, BLOB

즉 binary 타입으로 하면 항상 대소문자 구분을 할 수 있긴 합니다.

다만 이 해결법이 어려운 이유는

1. 한번 정한 타입을 바꾸는 것은 생각보다 큰 사이드 이펙트를 가지고 온다.
2. BLOB 형태로 저장되기에 조회 시 문자열이 무엇인지 확인하기 어렵다.

그래서 최종적으로는 BINARY 키워드를 쿼리에 붙이는 것만 적용했습니다.

배운 점

정말... 당시에 굉장히 찾기 까다로웠던 버그였습니다.
로컬에 데이터들 세팅해서 직접 디버깅한 다음에야 알아낼 수 있었으니까요...

DB와 코드 간 문자열 비교 방식이 다르다는게 핵심이었습니다.

문자열 비교 활용하는 로직을 짤 때, 사실 큰 고민 없이 항상 대소문자 구분 될거라고 생각하는 경향이 있었습니다.
이제는 약간 트라우마(?)처럼 남아서 한번 더 생각하게 될 것 같습니다.

참고

• https://dev.mysql.com/doc/refman/8.4/en/case-sensitivity.html
• https://tomining.tistory.com/m/179