이슈 소개
회사에서 개발하는 서비스에서는 매달 1일에 그 달이 생일인 유저에게 쿠폰을 발급해주고 있다. 예를 들어, 3월 1일이 되면 3월 1일 00:00:00부터 3월 31일 23:59:59까지 사용할 수 있는 쿠폰을 발급한다.
// * 쿠폰 지급
const lastDay = LocalDateTime.now().toLocalDate().lengthOfMonth();
const userCoupon: UserCoupon = this.userCouponRepository.create();
userCoupon.coupon = birthCoupon;
userCoupon.user = user[i];
userCoupon.expireDate = DateService.convertLocalDateTime2JsDate(
LocalDateTime.now()
.withDayOfMonth(lastDay)
.withHour(23)
.withMinute(59)
.withSecond(59)
);
await this.userCouponRepository.save(userCoupon);위 코드를 수행하고 DB 데이터를 확인해보면 대부분의 expireDate 데이터는 의도한대로 말일 23:59:59로 잘 생성되어 있었다. 하지만 일부 데이터는 다음달 1일 00:00:00로 설정되어 있는 것을 발견했다.
위 데이터를 확인해보면 createdAt은 밀리 초 이하만 상이할 뿐 같은 날 같은 시간에 수행된 것을 확인할 수 있다. 반면 expireDate의 데이터를 살펴보면 어떤 데이터는 2023-12-31 23:59:59로 잘 들어가 있지만 일부 데이터는 2024-01-01 00:00:00이라는 의도하지 않은 데이터가 들어가 있는 것을 확인할 수 있다.
왜 이런 문제가 발생했을까?
이슈의 원인(DATETIME 시간의 반올림 이슈)
이런 현상은 MySQL DATETIME 타입의 밀리 초 이하 데이터의 반올림 처리 때문이다. 데이터를 다시 살펴보면 createdAt 컬럼의 초 단위 다음 소수점의 첫째 자리가 5가 넘어가는 시점부터 expireDate가 다음 날로 넘어간 것을 확인할 수 있다. 즉 반올림 처리가 되었다는 의미다.
MySQL 공식문서에 의하면 MySQL 5.7 버전부터 DATETIME 타입을 비롯한 시간 타입은 Fractional Seconds(fsp)라는 밀리 초에 대한 정밀도를 설정할 수 있다고 한다. fsp값을 설정하면 해당 자릿수까지 밀리 초 데이터를 지원한다는 의미이다. 테이블 생성 시 컬럼 명세에 fsp 설정을 추가할 수 있으며 0~6 사이 설정이 가능하다. 여기서 주의할 점은 따로 설정해주지 않을 경우 default value는 0이라는 점이다.
위 이미지에도 각 컬럼의 데이터 타입을 살펴보면 createdAt이나 updatedAt의 경우 TIMESTAMP(6)이라는 fsp가 여섯 자리로 설정되어 있다. 반면, expireDate는 DATETIME 타입이지만 fsp가 설정되어 있지 않다.
# fsp를 사용한 컬럼이 포함된 테이블 생성
CREATE TABLE fractest( c1 TIME(2), c2 DATETIME(2), c3 TIMESTAMP(2) );
# 시간 데이터 추가
INSERT INTO fractest VALUES
('17:51:04.777', '2018-09-08 17:51:04.777', '2018-09-08 17:51:04.777');
# 데이터 결과
mysql> SELECT * FROM fractest;
+-------------+------------------------+------------------------+
| c1 | c2 | c3 |
+-------------+------------------------+------------------------+
| 17:51:04.78 | 2018-09-08 17:51:04.78 | 2018-09-08 17:51:04.78 |
+-------------+------------------------+------------------------+위 공식문서의 예제를 살펴보면 각각의 c1, c2, c3 컬럼은 모두 (2)라는 fsp 값이 설정되어 있다. 이후 밀리 초가 3 자리인 시간 데이터를 추가했는데, 결과는 셋째 자리에서 둘째 자리로 반올림 처리된 시간 데이터가 들어간 것을 확인할 수 있다. 즉 fsp 값에 설정된 자릿 수 만큼의 밀리 초 데이터 저장을 지원하며 지원 범위를 벗어나는 경우, 지원하는 자릿 수까지 반올림 처리를 하는 것이다!
정리하면 문제가 발생했던 회사 DB expireDate 컬럼의 타입은 DATETIME이었지만, 별도의 fsp 값이 설정되어 있지 않았다. 그래서 default value인 0으로 설정되었고, fsp가 0이므로 밀리 초를 지원하지 않아서 소수점 첫째 자리에서 반올림이 적용되었던 것이다.
해결 방법
1. DATETIME 타입에 fsp 설정하기
문제가 발생했던 expireDate 컬럼의 데이터 타입에 fsp를 설정해주는 방법이다. 가장 근본적이고 이상적인 해결책이라고 생각하지만, 약 180만 개에 달하는 데이터를 모두 마이그레이션 해줘야 하는 가장 무거운 작업이기도 하다.
2. millisecond를 절삭하기
MySQL JDBC Driver 차원에서 지원하는 sendFractionalSeconds=true|false 프로퍼티 설정을 통해서 밀리 초 이하 데이터를 절삭하여 전송할 수 있다. fsp 설정을 false로 하면 DATETIME(0)에서도 밀리 초 이하 데이터의 전송을 막을 수 있어 결론적으로 밀리 초의 반올림 현상을 방지할 수 있다.
3. 코드 상에서 조작하기
서비스 코드 단에서 밀리 초의 반올림을 원천부터 차단하는 방법이다. 기존 코드에서는 expireDate의 시간 데이터를 생성할 때 Hour, Minute, Second 까지만 데이터를 설정할 뿐 그 이하의 데이터에 대해서는 설정해주지 않았다. 그래서 실제로 해당 코드가 실행될 때의 밀리 초가 삽입되었고, 반올림 현상이 일어나게 한 원인이 되었던 것이다.
DATETIME의 반올림 문제를 해결하기 위한 다양한 방법이 있겠지만, SW 개발은 항상 한정된 자원 속에서 최적의 결과를 찾아 적용하는 것을 요구 받는다.
이 문제를 해결하기 위해 3번 방법을 택했다. 가장 큰 이유는 코드 상에서 간단하게 조작할 수 있어 빠른 대응 및 서비스 배포가 가능했기 때문이다. 해당 이슈가 발생했던 시점이 2월 말일이었는데, 당장 다음 날인 3월 00시가 되는 순간 해당 코드가 다시 수행되어야 하는 상황이어서 빠른 대응이 다른 무엇보다 가장 중요한 상황이었다.
// * 쿠폰 지급
const lastDay = LocalDateTime.now().toLocalDate().lengthOfMonth();
const userCoupon: UserCoupon = this.userCouponRepository.create();
userCoupon.coupon = birthCoupon;
userCoupon.user = user[i];
userCoupon.expireDate = DateService.convertLocalDateTime2JsDate(
LocalDateTime.now()
.withDayOfMonth(lastDay)
.withHour(23)
.withMinute(59)
.withSecond(59)
.withNano(0), **// ! nano seconds에 대한 설정 추가**
);
await this.userCouponRepository.save(userCoupon);시간 라이브러리로 js-joda를 이용하고 있는데, 해당 라이브러리에서는 시간을 설정할 때 간편하게 withHour(23)과 같은 형태로 설정이 가능하다. 문제가 발생했던 expireDate 데이터 생성 코드 부분에 withNano(0)이라는 옵션을 추가해 주어 반올림이 일어날 밀리 초 데이터를 아예 만들지 않게 했다.
SELECT * FROM UserCoupons uc ORDER BY uc.expireDate DESC 쿼리문 실행 결과, 이후 실행된 데이터에서는 의도한 대로 23:59:59 라는 데이터가 잘 들어가 있는 것을 확인할 수 있다!
참고
