아니 시간도 반올림이 된다고?

안녕하세요. 렌딧 대출플랫폼 팀의 Soo입니다. 오늘은 이전에 겪었던 MySQL datetime 관련 이슈 중 흥미로운 부분이 있어 이렇게 블로그 글로 찾아오게 되었습니다. 그것은 바로!!!

MySQL datetime에 대한 반올림이 발생할 수 있다.

1

1) 전날 00:00:00 ~ 23:59:59까지 생성된 레코드를 대상으로 합니다.

2

2) 테이블은 unique key를 가진 칼럼이 있어, 배치 작업이 도는 중에는 중복된 key를 가진 레코드가 있을 수 없습니다.

다음 날이 검색에 포함된다???

그 뒤로도 같은 이슈가 자주는 아니지만 간헐적으로 발생했습니다. 그래서 이를 해결하고자 정확히 어떤 문제가 일어나고 있는지 먼저 찾아보기로 했습니다. 어떤 데이터들이 말썽을 부렸는지에 대한 로그가 남아있었기에 다행히 해당 키들은 손쉽게 뽑아낼 수 있었습니다.

문제를 되는 건들을 모아놓고 보니 전부 하나의 공통점이 있었습니다. 그것은 바로,

배치 작업일 당일 그것도 00시 경에 생성된 데이터

생성된 시점을 보자마자 어안이 벙벙했습니다. 분명 배치작업은 위에도 언급했듯이, 작업 전날 23:59:59초까지 생성된 데이터가 대상이었습니다. 그런데도 해당 건들이 작업 범위에 포함되어 있었습니다. 뭐지? 이상한데?라는 생각과 함께 무언가 생성시점과 관련이 있을 것이라는 생각이 들었습니다. 그래서 생성시점과 unique key 위반 사이의 상관관계를 간단하게 확인할 수 있는 테스트를 해보기로 했습니다. 먼저 createdAt 속성만 가진 엔티티를 만들고 이를 검색할 수 있는 API를 만들었습니다. 이때, 검색조건에 사용한 LocalDateTime 파라미터는 배치작업과 동일하게 주었습니다.

1

@Entity

2

@Table

3

class TestEntity(

4

5

    @Id

6

    @GeneratedValue

7

    var id: Long? = null,

8

9

    @Column(name = "created_at")

10

    var createdAt: LocalDateTime = LocalDateTime.now()

11

)

12

13

14

@RestController

15

@RequestMapping("/test")

16

class TestController(

17

    private val testEntityRepository: TestEntityRepository

18

){

19

    @PostMapping

20

    fun save() = testEntityRepository.save(TestEntity())

21

22

    @GetMapping

23

    fun get() = run {

24

        val refDt = LocalDate.of(2021, 11, 12)

25

        val fromDt = refDt.atStartOfDay() // 기준일의 가장 첫 시간 00:00:00 ~

26

        val toDt = refDt.atTime(LocalTime.MAX) // 기준일의 가장 마지막 시간 23:59:59 ~

27

28

        testEntityRepository.findByCreatedAtBetweenOrderByCreatedAt(

29

            fromDt = fromDt,

30

            toDt = toDt

31

        )

32

    }

그리고 데이터는 아래와 같이 10건을 저장한 뒤, 마지막 레코드만 생성 시간을 조절해주었습니다.

이후 포스트맨을 사용해서 해당 API로 요청을 날려보니!!!

분명 검색 날짜는 12일로 지정을 했는데, 검색결과는 다음날인 13일데이터도 포함하고 있었습니다.

Fractional Seconds

현상을 파악했으니, 이제는 진짜 원인을 찾을 차례였습니다. 다행히도 생각보다 손쉽게(?) 관련 내용을 MySQL 공식문서에서 찾을 수 있었습니다.

1

MySQL has fractional seconds support for TIME, DATETIME, and TIMESTAMP values, 

2

with up to microseconds (6 digits) precision

출처: https://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html

초 이하 단위, 즉 밀리 초(ms) 부터의 시간을 fractional seconds라고 부르는 것을 알 수 있었고, 좀 더 찾아보니 더 나은 시간 정밀도를 보장하기 위해서 MySQL 5.6부터 지원하기 시작했다고 나와있었습니다. 물론 무한히 보장해주는 것이 아니라, 보장 범위는 위에 적혀있는 것처럼 6자리(= 마이크로 초)까지 이며, datetime 타입 뒤 괄호에 숫자를 붙여 datetime(6) 이런 형식으로 fractional seconds를 지정할 수 있습니다. 문제는 좋은 의도를 가지고 추가된 이 fractional seconds가 의도치 않은 결과를 낼 수 있다는 점입니다.

날짜가 반올림 된다!

fractional seconds를 MySQL이 어떤식으로 처리하는지 살펴보기 위해, 간단히 테이블을 생성해보겠습니다. 각 컬럼들은 같은 datetime 류의 타입이지만 각자 다른 정밀도를 지정해주었습니다. (마지막 컬럼은 datetime 타입 종류 중 하나인 timestamp에도 같은 처리가 적용된다는 것을 확인하기 위해 추가했습니다.)

1

CREATE TABLE `table_t` (

2

    `a` datetime DEFAULT NULL,

3

    `b` datetime(3) DEFAULT NULL,

4

    `c` datetime(6) DEFAULT NULL,

5

    `d` timestamp DEFAULT NULL,

6

)

그리고 동일한 데이터를 각각의 컬럼에 추가합니다.

1

INSERT INTO `table_t` VALUES (

2

    '2016-05-05 23:59:59.999',

3

    '2016-05-05 23:59:59.999',

4

    '2016-05-05 23:59:59.999',

5

    '2016-05-05 23:59:59.999',

6

);

결과는 어떻게 되었을까요? (생각해보니 이미 소제목에 답이 있군요.)

fractional seconds를 지정하지 않은 부분은 반올림이 되어 다음날이 되어 버렸고, 지정한 부분은 지정한 수만큼 자릿수를 보장해 줍니다. (저장할 fractional seconds가 더 적은 자릿수를 가지고 있을 경우 0으로 채워주는 것을 확인할 수 있습니다.)

LocalTime.MAX

위에서 fractional seconds에 의해 날짜가 반올림 될 수 있다는 것을 확인했습니다. 그렇다면, 배치 작업에서 무엇이 반올림을 일으키는 주범이었을까요? 바로 LocalTime.MAX입니다. 이 상수는 해당 날짜의 가장 마지막 순간을 표현하기 위해 자바 LocalTime 클래스에 미리 정의되어 있습니다. 그런데 문제는 이 값이 바로 fractional seconds를 9자리만큼 가지고 있었다는 점입니다. 9자리는 MySQL이 최대로 지원하는 6자리를 초과하기 때문에 결국 다음날로 반올림이 되고 맙니다.

맨 위에서 당일 생성된 데이터를 검색하기 위해 사용했던 코드가 기억이 나시나요?

1

 testEntityRepository.findByCreatedAtBetweenOrderByCreatedAt(

2

    fromDt = fromDt,

3

    toDt = toDt

4

)

위의 코드였는데요. 이때, toDt에 들어가는 값도 바로 LocalTime.MAX를 활용해 만들어진 LocalDateTime 값이었습니다. 이러한 이유 때문에 당일 데이터를 검색하려고 의도했음에도 결과적으로는 다음날 데이터까지 포함되어버리는 문제가 발생한 것입니다. 실제로, LocalDate.atTime(LocalTime.MAX).truncateTo( .. )를 활용해 fractional seconds를 잘라줄 수 있었는데요. 테이블이 지원하는 만큼 잘라서 보내면 날짜 반올림은 일어나지 않았습니다.

해결책을 함께 논의해 봅시다.

이제 의도치 않은 LocalTime.MAX의 반올림 문제에 대한 근본적인 해결책을 마련하기 위해 다음 챕터 회의 시간에 확인한 내용들을 공유한 뒤, 해결책을 논의하는 시간을 가졌습니다. 사실 많은 곳에서 LocalTime.MAX를 23:59:59로 가정하고 사용하고 있었기 때문에 이를 서둘러 해결해야 했습니다.

일단 저희가 가진 요구사항을 분석해보니 다음과 같았습니다.

1

(1) 소수점없이 기록되던 기존 DB 컬럼에 소수점을 추가기록 할 수는 없다.

2

(2) 필요한 경우에는 fractional seconds를 사용하고 싶다.

사실 23:59:59가 당일의 마지막이라는 의미로 사용되는 곳은 소스코드 상에서만이 아니었습니다. 개발팀 외적으로도 이미 널리 사용되고 있었는데요. 예를 들면, 타 팀에서 보고서를 작성하거나, 데이터를 뽑아 내 필요한 자료를 만들 때, redash 등을 활용한 DB 쿼리 시에도 당일의 마지막과 관련된 표현은 항상 fractional seconds가 없는 23:59:59를 활용한 created_at BETWEEN 00:00:00 AND 23:59:59의 형태로 사용되고 있었습니다. 때문에 DB datetime 컬럼이 fractional seconds를 지원하게 수정한 뒤, LocalTime.MAX를 일부 잘라서 보내는 등의 해결책은 적용이 불가능했습니다. 중간에 끼어든 fractioanl seconds 때문에 23:59:59.xxx의 형태의 값이 DB에 들어가게 됬을 때, 해당 데이터들이 누락되거나 하는 등 다른 예상치 못한 더 큰 문제들이 발생할 수 있었습니다.

사실, 요구사항을 맞추기 위한 가장 손쉬운 방법은 DB 혹은 어플리케이션 단에서 일단 모든 fractional seconds를 절삭하는 것이었습니다. 그러나 긴 시간의 논의 끝에 이 부분이 말처럼 그렇게 쉽지 않다는 것을 깨닫게 되었습니다.

1. DB 단에서 절삭?
결론부터 말하자면 불가능했습니다. DB단에서 해결한다는 의미는 컬럼이 지원하는 범위를 초과한 fractional seconds 값이 들어왔을 때, 기본 처리 정책인 반올림을 버림으로 변경한다는 의미인데, 현재 버전에서는 이 동작에 대한 수정이 불가능 했습니다. 버전업을 하면 기본동작을 변경할 수 있다는 정보를 찾았으나 지금 당장 하기에는 고려할 사항이 많았습니다.

2. 어플리케이션 단에서 절삭?
어플리케이션 단에서의 문제해결을 위해서는 실수로라도 DB로 fractional seconds가 전달되지 않도록 fractional seconds가 만들어지는 모든 지점을 찾아 제거를 해줘야 했습니다. 그렇지 않으면 언제 어디서든 의도치 않은 날짜 반올림이 발생할 수 있었습니다. 그래서 가장 먼저 fractional seconds가 생성될만한 지점을 파악했고, 다음의 세 지점을 뽑아낼 수 있었습니다.

1

(1) LocalTime.MIN

2

(2) LocalTime.MAX

3

(3) LocalDateTime.now()

일단 (1)번은 값이 00:00:00으로 논외였습니다. 문제는 (2), (3)번이었습니다. 일단 LocalTime.MAX, LocalDateTime.now()를 사용하고 있는 부분을 모두 잘라줘야 했습니다. 공수가 꽤 들겠지만 LenditDateTime 이라는 클래스를 만든 뒤 fractional seconds가 없는 23:59:59, now()를 반환하는 메소드를 두는 방식으로 문제를 해결할 수 있었습니다. 이 방식의 장점은, 우리의 두 번째 요구사항인 필요할 때는 fractional seconds를 사용하고 싶다도 추가로 충족시켜줄 수 있을 것으로 보였다는 점입니다. LenditDateTime 클래스 내부에 with/without fractional seconds 형태의 두 가지 now() 메소드를 정의해주면 되기 때문이었습니다.

그러나, 안타깝게도 이 부분도 그렇게 호락호락하지 않았습니다. 기존의 LocalDateTime.now(), LocalTime.MAX를 사용하지 못하도록 강제하거나 혹시나 사용했을 때 에러를 내뿜게 하는 등 이를 막을 수 있는 수단의 부재가 문제였습니다. 서로 LenditDateTime만을 사용하도록 정한다고 하더라도, 언제 어디서든 누군가는 무의식적으로 기존 방식을 사용할 수 있었습니다. lint 레벨에서 제약을 둘 수 있지 않을까하는 추가적인 의견이 나왔지만 설정자체부터 녹록치 않았습니다.

여기까지 어찌저찌 해결했다고 하더라도, 여전히 외부에서 의도치 못하게 fractional seconds가 끼어들 가능성이 남아있다는 점도 문제였습니다. 저희의 서비스 특성상 외부와 전문 통신을 할 경우가 많은데요. 전문을 주고 받으면서 자연스럽게 그 안에 담겨 있는 시간/날짜 관련 값들도 활용을 하게 됩니다. 이때, 해당 값들이 fractional seconds를 갖고 있고, 이 부분이 쿼리 파라미터 등에 할당되어 DB로 날아가게 된다면 언제든지 날짜 반올림 문제가 발생할 수 있는 여지가 있었습니다. 그리고 이러한 형태의 문제가 발생했을 때, 이 부분을 캐치할 수 있을 지의 여부조차 불투명했습니다. 결국, 충분한 시간과 노력을 들이고도 궁극적으로는 완전한 문제해결이 불가능하다는 것이 가장 큰 문제점이었습니다.

다른 해결책은 없을까요? 있습니다!! 바로 JPA입니다.

사실, 챕터회의 시간에 제가 테스트 했던 내용을 공유하면서 MySQL general_log에 찍힌 실제 쿼리도 함께 살펴봤었는데요. 이러한 쿼리가 찍혀있었습니다.

SELECT id, created_at FROM test_entity WHERE created_at BETWEEN '2021-11-12 00:00:00' AND '2021-11-13 00:00'

요 쿼리를 살펴보면서, 제가 이야기한 잘못된 가설은

1

로그에 이러한 쿼리가 있는데,

2

어플리케이션 단에는 LocalTime.MAX의 실제 값인 23:59:59.999999999가 유지되고 있는 걸로 보아,

3

DB 단에서 쿼리 실행 후 옵티마이저에 의해 반올림되는 것이 아닐까요?

였습니다. 그리고 Ben 께서는 이게 오히려 어플리케이션 단에서 어떤 처리가 이루어지고 있음을 증명하는게 아닌가하는 의문을 표시하셨습니다. 그래서 이 부분에 대해서 어플리케이션 단에서도 fractional seconds에 대해서 별도의 처리가 있는지 회의시간이 끝나고 추가로 더 살펴보았습니다. 그리고 JPA 레벨에서 또 다른 해결책을 발견할 수 있었는데요. DB단에 앞서 이미 JPA 단에서 fractional seconds를 처리하고 있었기 때문입니다. 그렇기에 어떤 해결책이었는지를 살펴보기에 앞서 JPA가 fractional seconds를 어떻게 처리되는지 먼저 확인해보도록 하겠습니다.

1. 네, 사실, DB 단에서만 반올림이 일어나는 것이 아니었습니다.

DB에서 datetime 반올림이 발생하는 것도 맞습니다. 그런데, JPA 파라미터 바인딩 부분을 좀 더 파고들어가보니 이미 JPA에 의해 어플리케이션 단에서 당일 23:59:59.999999999가 다음날 00:00:00으로 반올림 된 후 파라미터로 바인딩되는 부분을 확인할 수 있었습니다. 해당 내용을 자세히 살펴보기 위해 JPA 내부, 그 중에서도 파라미터가 실제로 바인딩 되는 부분을 조금 더 들여다보겠습니다.

JPA가 필요한 쿼리를 먼저 prepared statement 형태로 생성해주고, 뒤이어 파라미터를 바인딩 하는데요. 이때 활용되는 부분은 QueryParameterSetter 클래스입니다. 실제로 디버깅을 찍어보면, setParameter 메소드 내부에서 먼저 값을 추출해 내는 것을 확인할 수 있습니다.

여기를 시작점으로 쭉 타고 들어가면 TimestampTypeDescriptor.getBinder으로 들어가게 됩니다. 이쪽 부분이 호출될 때, 이미 typeDescriptor = LocalDateTimeJavaDescriptor로 할당이 되어있습니다. 이 typeDescriptor와 value인 2021-11-12 23:59:59.99999999을 활용해 timestamp를 먼저 생성합니다.

다음으로는 조건에 맞는 st.setTimestamp 메소드가 호출이 되는데요. 쭉 따라가보면 ClientPreparedStatement.setTimestamp -> AbstractQueryBinding.setTimestamp로 이어집니다.

이제 AbstractQueryBinding.setTimestamp 메소드를 살펴보면, 가장 먼저 fractLen = -1이 되고, 조건을 비교하면서 맞는 메소드를 호출합니다. 별도의 설정이 되어있지 않은 상태라면 JPA가 fractional seconds를 보내도록 하는 것이 기본동작이기 때문에 if, else if 문을 모두 통과하고 그 다음 setTimestamp를 호출합니다.

여기서는 x(= timestamp) 값을 기준으로 조건을 가르는데, x는 보시다시피 값이 있으므로 아래로 내려가서 bindTimestamp로 갑니다.

bindTimestamp로 들어오면 가장먼저 하는 일이 fractionalLength를 확인하는 것입니다. 이 값이 위에서 봤던 fractLen입니다. 그리고 그 값은 -1로 할당되었기 때문에, 조건에 따라 6으로 바뀝니다. 이제 Timeutil.adjustNanosPrecision가 호출될 차례인데요. 드디어 반올림 매직이 일어나는 곳에 도달하게 되었습니다.

날짜 반올림 매직은 다음과 같이 일어납니다.

1) fsp = fractionalLen = fractionalLength = 6 = 문제 없이 맨 위의 체크조건은 통과
2) tail 값을 계산, 10^3 = 1000.0(double 이므로)
3) Math.round(res.getNanos() / tail) * (int) tail
(serverRoundFracSecs의 기본값은 true이므로)

1

1. res.getNanos() = fractional seconds = 999999999 

2

2. 999999999 / 1000.0 = 999999.999 

3

(int가 아닌 double로 나누므로 소수점 보존)

4

3. Math.round 후 1000000

5

4. 1000000 * tail = 1000000000 =  nanos(최종값)

1

1. nanos / 1000000000 = 0

2

2. res.getTime() + 1000 = 다음날 00:00:00.999

2. 그렇다면 JPA의 동작은 바꿀 수 있을까요?

네 물론 바꿀 수 있었습니다. 이미 위 코드 사진들을 보면서 눈치를 채신 분들도 있겠지만 이 조건들 중 하나만 false로 변경할 수 있다면 가능합니다. 잠시 위에서 봤던 코드를 다시 한 번 가져오겠습니다.

여기서 bindTimestamp로 넘어가기 전에 조건을 먼저 체크하는데, serverSupportsFracSecs() 혹은 sendFractionalSeconds 둘 중에 하나만 false라면 truncateFractionalSeconds가 실행됩니다.




그리고 truncateFractionalSeconds 메소드의 내부를 보면!!!!! fractionalSeconds 부분을 강제로 0으로 셋팅해주는 것을 확인할 수 있습니다.

Silver Bullet? Of Course Not…

두 값 중에 false로 셋팅하기 더 편한 값은 sendFractionalSeconds였습니다. serverSupportsFracSecs()는 하이버네이트 세션 설정과 관련이 있었는데, 이미 세션관련 설정이 별도로 적용되어 있는 부분이 있기도 하고, 해당 값을 바꾸는 부분이 꽤나 까다로웠습니다. 반면에, sendFractionalSeconds는 값의 변경이 매우 간단했습니다. 단지,

url:jdbc:mysql:// ~ ?sendFractionalSeconds=false

출처: https://kwonnam.pe.kr/wiki/database/mysql/5.6

그런데 문제는, 해당 설정은 JDBC Driver 차원에서 전역적으로 strict하게 적용된다는 부분이었습니다. 즉, 이 설정 값을 false로 바꾸면 앞으로 쿼리를 DB로 날릴때, 파라미터에 datetime 관련 타입의 fractional seconds를 아예 사용할 수 없게 된다는 부분이 걸렸습니다.

이외에도 추가적인 주의사항이 공식문서에 명시되어 있었습니다.

1

sendFractionalSeconds

2

3

If set to “false”, the fractional seconds will always be truncated 

4

before sending any data to the server.

5

This option applies only to prepared statements, callable statements or updatable result sets.

6

Default Value true Since Version 5.1.37

바로 prepared statements, callable statements, 그리고 updatable result sets에만 해당 설정이 적용된다는 점이었습니다. 쿼리를 직접 사용하는 경우는 적용이 안된다는 의미인데, 소스코드 상에서 Native Query를 사용하는 부분이 꽤 있어서 이 옵션이 안전하게 적용될 수 있을까 하는 의문이 들었습니다.

추가논의 및 최종결정

다행히 Native Query를 사용했을 때, sendFractionalSeconds=false옵션이 적용되는 부분은 크게 문제가 없었는데요. 아래의 쿼리를 가지고 테스트해 보았습니다.

1

1. with @Query

2

@Query(

3

    value = "SELECT id, created_at FROM test_entity WHERE created_at BETWEEN :fromDt AND :toDt", 

4

    nativeQuery = true

5

)

6

fun findByCreatedAtBetweenOrderByCreatedAt(fromDt: LocalDateTime, toDt: LocalDateTime): List<TestEntity>

7

8

9

2. with em.createNativeQuery

10

fun getTestEntities(): List<*> {

11

    val nativeSql = "SELECT id, created_at FROM test_entity WHERE created_at BETWEEN :fromDt AND :toDt"

12

13

    val refDt = LocalDate.of(2021, 10, 12)

14

    val fromDt = refDt.atStartOfDay()

15

    val toDt = refDt.atTime(LocalTime.MAX)

16

17

    val res = entityManager.createNativeQuery(nativeSql, TestEntity::class.java)

18

        .setParameter("fromDt", fromDt)

19

        .setParameter("toDt", toDt)

20

21

    return res.resultList

22

}

두 쿼리 모두 중간에 JPA가 끼어있어서, JPA가 SQL을 생성할 때 자체적으로 prepared statements를 먼저 만들어주고 이후 파라미터를 바인딩하는 형태로 쿼리 생성이 이루어졌습니다. 그래서인지 위 설정이 잘 적용되어, 날짜가 반올림되는 현상은 발생하지 않았습니다.

그래도 곧바로 위의 옵션을 적용하기에는 너무 극단적인 감이 있어서 추가적인 논의과정을 거쳤습니다. 그리고 논의 끝에,

1

1) 어차피 원래 DB에는 fractional seconds가 저장되지 않고 있다

2

2) LocalTime.atMax를 일일이 수정하는 것은 사소한 작업임에도 공수가 꽤나 많이 든다

3

3) 혹시나 정밀하게 시간을 비교할 일이 있을 때도, 애플리케이션 단에서는 기존처럼 fractional seconds를 사용할 수 있어서 큰 무리가 없을 것 같다

4

4) Native Query도 순수한 SQL만을 사용하는 것이 아니라 JPA를 통해 사용하는 것이므로 옵션이 적용되지 않을 위험이 없다

여전히 남은 과제 & 마치며

지금까지 datetime 타입의 fractional seconds가 MySQL, JPA에서 어떻게 처리되는지 조금 더 자세히 알아보았습니다. 뜻 밖의 예외 덕분에, 무언가에 대해 조사하고 심도있는 논의도 하면서 많은 것을 배울 수 있던 시간이었습니다. sendFractionalSeconds=false을 적용하고 나서 반올림 문제는 해결이 되었지만 사실 저희에겐 아직도 남은 과제가 있습니다. 왜냐하면 DB에 저장되어 있는 데이터들에 대해서 추후 fractional seconds 단위의 정교한 비교를 하고 싶다면, 결국 fractional seconds를 저장하는 등의 별도 처리가 필요하기 때문입니다.

개발이란 한정된 상황 그리고 자원 속에서 최선의 해결책을 찾아가는 과정이라고 표현되곤 합니다. sendFractionalSeconds=false라는 방안은 그 자체로는 물론 아쉬움이 있었지만 오늘의 환경에서는 최선책이었습니다. 상황은 끊임없이 변화합니다. 저는 이 말이 앞으로도 언제든 더 나은 해결책이 앞으로 언제든지 등장할 수 있음을 암시하는 말이라고도 생각합니다. 그렇기 때문에 저희는 여기서 멈추지 않고, 치열하게 생각하면서 앞으로도 더 나은 방안이 있는지 끊임없이 고민하겠습니다. 그리고 그 때 또 다른 후속글로 찾아뵙겠습니다. 긴 글 읽어주셔서 감사합니다!