| 항목 | 버전 |
|---|
| 이전 버전 | MySQL 5.7.44 |
| ... | dtb_csv (CSV 출력 정의) |
| 발생 위치 | 상품 CSV / 주문 CSV / 회원 CSV 출력 |
MySQL 5.7에서 8.4로 버전을 올렸을 뿐, 애플리케이션 코드는 단 한 줄도 변경하지 않았음에도 불구하고 CSV의 열 순서가 바뀌는 현상을 겪었습니다. 데이터 자체는 정확하고 건수도 일치하지만, 실행할 때마다 열의 배열만 달라집니다. 이는 마이그레이션 검증에서 흔히 발생하는 '건수 일치 여부' 체크로는 절대 감지할 수 없는 종류의 파괴입니다.
본 기사에서는 원인 특정부터 감지 및 재정렬(re-sequencing), 롤백(rollback)까지 실제 사용한 SQL과 PHP 코드를 포함하여 정리합니다.
EC-CUBE 4.3의 CSV 출력은 dtb_csv 테이블을 이용하며, 이 테이블의 한 행이 'CSV의 한 열'에 대응합니다. 각 행은 sort_no (정렬 번호)를 가지고 있으며, 출력 시에는 이 번호가 오름차순으로 정렬되어 열로 나열됩니다. 즉, 열 순서의 근거는 오직 sort_no 하나입니다.
그런데 확장 플러그인이 항목을 추가할 때, 기존 최대값 + 1을 사용하지 않고 고정 값(혹은 기존과 동일한 값)을 sort_no에 설정하는 경우가 있었습니다. 그 결과, csv_type (CSV 종류)별로 sort_no가 중복된 상태가 되었습니다.
-- 중복 예시: csv_type = 'product' 내에서 sort_no = 30이 2건 발생
SELECT csv_id, csv_type, field_name, sort_no
FROM dtb_csv
...
+--------+-----------+------------------+---------+
| csv_id | csv_type | field_name | sort_no |
+--------+-----------+------------------+---------+
...
MySQL 5.7에서는 ORDER BY sort_no와 같이 동일한 값이 존재하는 정렬의 경우, 사양상 그룹 내 순서는 보장되지 않습니다. 하지만 실제로는 테이블 스캔 순서(대부분 기본 키 순)로 반환되는 경우가 많았고, 결과적으로 '등록된 순서'처럼 보이게 했습니다. 애플리케이션 측도 이를 전제로 동작하고 있었습니다.
MySQL 8.0 이후부터는 옵티마이저와 정렬 알고리즘이 변경되면서, 동일 값 그룹 내의 순서는 실행 계획(execution plan), 통계 정보(statistics), 정렬 버퍼 크기 등에 따라 변동합니다. 그 결과, 어떤 실행에서는 역순, 어떤 실행에서는 정순이라는 재현성조차 불안정한 상태가 되었습니다.
| 관점 | MySQL 5.7 | MySQL 8.4 |
|---|
동일 값 sort_no의 순서 | 사양상 불확실 (사실상 PK 순이 되기 쉬움) | 사양상 불확실, 실행 계획에 따라 변동 |
| 재현성 | 거의 안정적으로 '등록 순'을 따름 | 실행마다 달라질 수 있음 |
| 원인 파악 용이성 | 문제가 표면화되지 않음 | 간헐적으로 열 순서가 깨짐 |
| 검증 시 감지 가능 여부 | 건수 일치만으로는 불가능 | 건수 일치만으로는 불가능 |
CSV를 가져오는 시스템은 헤더 행을 보지 않고 열의 위치로 항목을 판단하는 경우가 많습니다. 열 순서가 바뀌면, price 위치에 stock이 와 있더라도, 가져오는 쪽은 '3번째 열은 가격'으로 처리를 계속합니다. 오류가 나지는 않지만, 값만 어긋난 상태로 재고나 주문이 등록됩니다. 이것이 가장 까다로운 부분입니다.
대처 방침을 간단히 3단계로 나누었습니다.
- CSV 종류별로
sort_no의 중복을 감지한다.
- 중복이 발생한 종류에 한해,
(sort_no, csv_id) 순서로 1부터 재정렬한다.
- 1 트랜잭션 내에서 재정렬 전후의 유효 열 순서를 비교하고, 다르면 롤백 처리한다. 두 번째 시도에서는 아무것도 하지 않는 **멱등(Idempotent)**한 구조를 만든다.
우선적으로 '어떤 CSV 종류가 깨져 있는지'를 파악합니다. 여기서 중요한 것은 유효한 열 (enabled = 1)만 대상으로 삼는 것입니다. 비활성화된 열이 동일 값을 가지고 있어도 출력에는 영향을 주지 않지만, 나중에 활성화되는 순간 문제가 발생할 수 있습니다. 감지는 유효/비유효 양쪽을 모두 확인하는 것이 안전합니다.
-- CSV 종류별로 sort_no가 중복된 것을 감지
SELECT csv_type, sort_no, COUNT(*) AS dup_count,
GROUP_CONCAT(csv_id ORDER BY csv_id) AS csv_ids
...
재정렬(Reassignment)은 ROW_NUMBER()를 사용하여 (sort_no, csv_id) 순서로 1부터 부여하기만 하면 됩니다. 다만 동일 테이블을 서브쿼리에서 참조하며 UPDATE할 경우 ERROR 1093이 발생할 수 있으므로, 임시 테이블을 사용하는 것이 좋습니다. 이는 5.7 버전의 검증 환경에서도 동일한 SQL로 동작할 수 있어, 마이그레이션 전후에 절차를 공통화할 수 있다는 장점도 있습니다.
-- 대상 csv_type만 임시 테이블로 옮겨서 번호를 매깁니다.
CREATE TEMPORARY TABLE tmp_csv_sort AS
SELECT csv_id,
...
ORDER BY sort_no, csv_id의 두 번째 키에 csv_id를 반드시 포함하는 것이 핵심입니다. 이 부분을 생략하면 '중복을 해소하기 위한 번호 부여'가 다시 비결정적 순서(indeterminate order)가 되어 같은 사고를 재현하게 됩니다.
재정렬은 '망가진 상태를 고치는' 작업이므로, 적용하기 전에 반드시 이전 및 이후의 열 순서를 비교하여 의도하지 않은 변화가 있다면 롤백해야 합니다. 또한 마이그레이션 스크립트는 몇 번 실행될지 알 수 없으므로, 멱등(Idempotent)하게 만듭니다.
<?php
$pdo->beginTransaction();
...
여기서 핵심은 비교 대상을 sort_no 자체가 아니라 field_name의 배열 순서로 삼는 것입니다. 번호 재할당은 번호가 바뀌는 것을 목적으로 하므로, 번호를 비교하는 것은 의미가 없습니다. '사용자 관점에서 본 열의 순서가 변하지 않았는지'를 검증해야 합니다.
이번 사안을 계기로 버전 업그레이드 전 확인 항목 맨 앞에 '순서 결정 근거가 유일한지'를 추가했습니다. 데이터가 올바르더라도, 순서 보장이 바뀌면 깨지는 부분이 있기 때문입니다.
| 우선순위 | 체크 항목 | 확인 방법 | 이유 |
|---|
| 1 | 순서 결정 근거가 유일한지 | GROUP BY ... HAVING COUNT(*) > 1 | 순서 보장 변화로 가장 먼저 깨짐 |
| 2 | 건수 일치 여부 | 마이그레이션 전후의 COUNT(*) 비교 | 누락/중복 감지 |
| 3 | 체크섬 일치 여부 | CHECKSUM TABLE / 전체 열 해시 | 값 파괴 감지 |
| 4 | 실행 계획 변화 | EXPLAIN 전후 비교 | 성능 저하 예조 |
| 5 | 문자 비교 순서(Collation) | utf8mb4_0900_ai_ci로의 변경 영향 | 검색 결과 차이점 |
| | | |
'건수 일치 검증과 순서 결정 근거의 유일성 중 무엇을 먼저 확인할 것인가?'라는 질문에는, 저는 순서 결정 근거의 유일성을 먼저 확인하겠습니다. 건수가 맞아도 순서가 망가져 있다면, 가져오는 쪽에서 값이 틀어진 채로 정상 종료해 버릴 수 있기 때문입니다. 건수 불일치는 아직 감지할 수 있지만, 열 순서 불일치는 감지하기 어렵습니다.
.ORDER BY에 타이브레이크 키를 빠뜨린 ORDER BY sort_no만 있는 부분은 모두 ORDER BY sort_no, csv_id로 수정해야 합니다. 애플리케이션 측의 SQL도 마찬가지입니다. -
. 5.7부터 기본 활성화되었지만, 8.4에서도 유효합니다. ONLY_FULL_GROUP_BY
SELECT csv_type, field_name ... GROUP BY csv_type와 같은 부실한 쿼리가 마이그레이션 후에 에러가 발생할 수 있습니다. -
. 동일 테이블을 서브쿼리에서 참조하는 UPDATE는 피하고, 임시 테이블이나 파생 테이블을 사용해야 합니다. ERROR 1093
인증 플러그인 변경. mysql_native_password에서 caching_sha2_password로 바뀌면서, 오래된 PDO/드라이버로는 연결할 수 없는 경우가 있습니다. -
비교 순서(Collation) 기본값 변경. utf8mb4_0900_ai_ci가 되면서, ORDER BY name의 결과가 5.7과 달라질 수 있습니다. 이 역시 '조용히 변하는' 유형입니다.
Q. 5.7에서는 지금까지 동작했는데, 왜 8.4에서 갑자기 깨지는 건가요?
A. 동일 값 그룹 내의 순서는 5.7에서도 사양상 불확실합니다(indeterminate). 우연히 기본 키 순서로 반환되었을 뿐이고, 8.0 이후 옵티마이저 변경으로 그 '우연'이 성립하지 않게 된 것이 정확한 이해입니다. 버그가 아니라, 미정의 동작에 의존했던 부분이 노출된 형태입니다.
Q. sort_no에 UNIQUE 제약 조건을 걸면 재발하지 않을까요?
A. 재발은 막을 수 있지만, 기존 데이터의 중복을 먼저 해소해야 합니다. 또한
(csv_type, sort_no)
복합 UNIQUE(Composite UNIQUE)로 지정하지 않으면 의미가 없습니다. 중장기적으로는 제약 조건 추가를 권장합니다.
Q. 재작업(振り直し) 과정에서 열 순서가 변경될 위험은 없나요?
A. (sort_no, csv_id)로 번호를 부여하기 때문에, 중복된 경우에만 순서가 확정됩니다. 절차 3의 해시 비교를 통해 field_name의 배열이 변경되었다면 Rollback을 수행하므로 안전하게 처리할 수 있습니다.
Q. 운영 환경(Production) 적용 전에 무엇을 확인해야 하나요?
A. 스테이징(Staging) 환경에서 동일한 SQL을 실행하고, 출력 CSV 파일의 헤더 행을 마이그레이션 전후로 diff(비교)해 보세요. 열의 개수와 열 이름의 배열 순서가 완벽하게 일치하면 합격입니다.
- 열 순서의 근거가
sort_no처럼 단일 컬럼으로 중복될 수 있는 설계는 MySQL 버전 업그레이드 시 조용히 깨질 수 있습니다. 대책은 '감지 → 중복된 부분만 재작업(振り直し) → 트랜잭션 내에서 순서를 비교하여 Rollback'의 3단계 과정이 필요합니다.
- 마이그레이션 스크립트는 Idempotent(멱등성)해야 합니다. 비교는 번호가 아닌 사용자 관점에서의 열 배열 순서로 진행해야 합니다. 마이그레이션 전 체크는 '순서 결정 근거가 유일한지'를 건수 일치 여부보다 먼저 확인해야 합니다.
데이터가 정확하다는 것과, 순서가 보장된다는 것은 별개의 문제입니다. 버전 업그레이드로 후자가 변경될 가능성을 체크리스트의 최상단에 배치하는 것이 안전합니다.
BENTEN Web Works — 업무 자동화 및 시스템 개발 프리랜서 엔지니어입니다. GAS / Python / RPA를 활용한 업무 자동화, 웹 제작 및 시스템 개발 상담을 받고 있습니다.
'이런 것도 자동화할 수 있을까?'라는 질문만으로도 편하게 문의해 주세요.
👉 BENTEN Web Works — 상세 정보/문의는 여기를 클릭하세요
🐦 X(구 트위터) — 일일 지식을 발신하고 있습니다.