[MySQL] Debezium, datetime, Offset 적용 시 주의해야 할 점

[DolphaGo]

오늘의 삽질

• 카프카 커넥터로 MySQL binlog를 통해 CDC를 구현했었다.
• 문제는 데이터베이스 커넥션의 serverTimeZone 도 JST (Asia/Tokyo) 였고, DB parameter setting 도 JST로 되어있는데, CDC를 통해 뽑아낸 datetime은 값이 이상하게 나오는 것이었다?
  • 참고로, mysql의 datetime은 9999년도까지 지원, timestamp는 2038년도 까지 지원은 상식이지만, debizium 커넥터를 이용해 데이터를 뽑아내보면, datetime의 경우, epoch millis로 출력되고, timestamp의 경우 "yyyy-MM-dd'T'HH:mm:ss.ssZ" 와 같이 String 형태로 출력된다.
  • 또한, 다음과 같이 debizium mysql 커넥터로, avro 스키마 매핑시에, datetime 은 io.debezium.time.Timestamp 로 매핑해주도록 한다. 참고
  • 살짝 헷갈릴 수도 있는건 mysql에 timestamp 타입으로 되어있다면, debizium에선 ZonedDateTime을 써야한다. docs를 뒤적여보면 알 수 있다.

서론이 길었고 문제는 다음과 같다.

Request :2022-08-16'T'22:59:59+09:00 일 때, Database에는 JST 로 되어있기 때문에 2022-08-16 22:59:59 로 잘 보인다.

• 데이터베이스의 서버타임존에 따라서 보이는 값이 다를 뿐 시간의 절대량은 동일하다. (Offset 개념이 없는 Date를 생각하라.)
• 이 페이지에서, time값을 통해 현재 시간 및 UTC 값을 측정할 수 있다.
• 내가 겪은 문제는, 카프카 커넥트를 통해 담긴 CDC 데이터들에, OffsetDateTime으로 선언했던 필드의 값이 이상했던 것
  • 어떻게 이상했냐고? 위의 예제에서처럼 2022-08-16 22:59:59 (JST)를 기대했지만, 결과는 2022-08-17 08:59:59 (JST) 였던 것이다.
• 처음에는 DB 자체에 시간 절대값이 잘못 저장된 것은 아닌가 확인했었다.
  • 1. DB Connection 확인 (serverTime, zone)
  • 2. DB Configuration 확인 (serverTime, zone)
  • 3. Application 단에서 Offset을 핸들링하고 있는 것은 아닌지?
  • OffsetDateTime을 JPA Auditing 하기 위해서는 DateTimeProvider Bean이 필요했다.
  • 그래서 다음과 같이 Bean을 만들어두고, 사용하는 모듈에서 @EnableJpaAuditing(dateTimeProviderRef = "auditingDateTimeProvider") 과 같이 두고 사용했었다.

@Configuration
class PersistenceConfig {
    @Bean(name = ["auditingDateTimeProvider"])
    fun dateTimeProvider(): DateTimeProvider {
        return DateTimeProvider { Optional.of(OffsetDateTime.now()) }
    }
}

뭐 아무튼, 위의 문제들은 모두 아니었음.

그래서 확인한 방법은 다음과 같다.

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_unix-timestamp

select unix_timestamp(`OffsetDateTime으로 선언했던, 지금 관심사인 필드`) from table where id = ??

위의 쿼리로 알게된 unixtime으로 위에 적어둔 사이트에서 확인해보니, 내가 설계했던 그대로 값이 존재했다. 즉, 현재 DB zone은 JST가 맞았고, UTC로 변환하면, -9:00가 되어있었다. JST 커넥션으로 들어갔기 때문에 request(+09:00, JST가 적용된 시간)한 시간이 그대로 DB에 보이는 것이었다.

결론적으로, mysql에 저장된 시간의 절대값을 알아보는 unixtime을 알게되었는데, 이 값을 통해 확인하니, DB 저장에는 문제가 없음을 확신할 수 있었다...! 내일 카프카 커넥트를 좀 확인해봐야지. 카프카 커넥트 쪽에서 JST로 된 시간을 UTC로 인식한 뒤, 다시 Offset을 적용하여 +09:00 처리하여 뱉고 있는 듯 하다.

[DolphaGo]

오늘 추가 확인 결과, 다음과 같다.

이 부분 을 읽어보면, 다음과 같다.

그래서 Database의 system_time_zone을 변경하려고 시도했다. 커넥션에 serverTimeZone을 UTC로 지정했지만, 원하는 대로 동작하지 않았다. DataBase의 system_time_zone이 JST로 설정되어있었기 때문이다.

즉, DB에는 system_time_zone 필드와 time_zone 2개의 필드가 있는데, system_time_zone의 경우 세션 레벨에서 변경이 불가능하며, 서버의 재기동이 필요하지만, time_zone의 경우 세션 레벨에서 컨트롤이 가능하다.(클라이언트 측에서 timeZone을 지정하는 것)

DBA 분께서 내가 헷갈려하고 있는 부분을 정확히 설명해주셨다.

즉, Insert 될 때, datetime 필드는 당시 커넥션의 타임존의 설정을 받아 JST(+09:00)이 된 시간으로 들어가 있었고, CDC에서는 UTC based로 시간값을 읽게 되니, 이와 같은 이슈가 있던 것이다.

즉,정리하면 다음과 같다.

• DB : (time : JST based) Create -> CDC : (time : UTC based) Read

그래서, 최종적으로 Client에서 CDC 데이터를 읽고, JST로 Offset을 적용하면, DB에 적용된 시간보다 +09:00으로 보이는 것이었다.

DB의 타임존을 바꿀 것이 아니라면, 필드의 특성이니, Known issue로 간주하고, Client 측에서 처리를 해주도록 우회했다.