Help us understand the problem. What is going on with this article?

embulk-input-jdbcのMySQLプラグインで9時間時間がずれる

More than 1 year has passed since last update.

embulk-input-jdbcのMySQLプラグインを利用してデータ同期すると、DATETIME型の時間が9時間ずれる現象が起きたので、その解決方法を紹介します。

環境

  • MySQL 5.6.27
  • Embulk 0.8.18
  • embulk-input-jdbc 0.8.2

修正前

修正前のsample.yml

sample.yml
in:
  type: mysql
  host: localhost
  user: user
  password: password
  database: sample
  table: sample
  select: "*"
  default_timezone: "Asia/Tokyo"
  column_options:
    create_time: {type: string, timestamp_format: "%Y-%m-%d %H:%M:%S%:z"}
    update_time: {type: string, timestamp_format: "%Y-%m-%d %H:%M:%S%:z"}


out:
  .
  .
  .

わかりやすいように、時間をAsia/TokyoでString型に変換しています。

mysql

mysql> SHOW VARIABLES LIKE '%time_zone%';
+------------------+------------+
| Variable_name    | Value      |
+------------------+------------+
| system_time_zone | UTC        |
| time_zone        | Asia/Tokyo |
+------------------+------------+

mysql> select * from sample;
+------+----------+---------------------+---------------------+
|  id  | name     | create_time         | update_time         |
+------+----------+---------------------+---------------------+
|    1 | sample1  | 2013-05-27 10:46:08 | 2013-05-28 21:33:01 |
|    2 | sample2  | 2013-05-27 10:46:08 | 2013-05-28 21:33:01 |
|    3 | sample3  | 2013-05-27 10:46:08 | 2013-05-28 21:33:01 |
|    4 | sample4  | 2013-05-27 10:46:08 | 2013-05-28 21:33:01 |
|    5 | sample5  | 2013-05-27 10:46:08 | 2013-05-28 21:33:01 |
|    6 | sample6  | 2013-05-27 10:46:08 | 2013-05-28 21:33:01 |
|    7 | sample7  | 2013-05-27 10:46:08 | 2013-05-28 21:33:01 |
|    8 | sample8  | 2013-05-27 10:46:08 | 2013-05-28 21:33:01 |         
|    9 | sample9  | 2013-05-27 10:46:08 | 2013-05-28 21:33:01 |        
|   10 | sample10 | 2013-05-27 10:46:08 | 2013-05-28 21:33:01 |     
+------+----------+---------------------+---------------------+

以上はAsia/Tokyo時間を表しています。

embulk preview

$ embulk preview sample.yml
2017-04-21 07:14:15.937 +0000: Embulk v0.8.18
2017-04-21 07:14:16.944 +0000 [INFO] (0001:preview): Loaded plugin embulk-input-mysql (0.8.2)
2017-04-21 07:14:16.977 +0000 [INFO] (0001:preview): Fetch size is 10000. Using server-side prepared statement.
2017-04-21 07:14:17.262 +0000 [INFO] (0001:preview): Fetch size is 10000. Using server-side prepared statement.
2017-04-21 07:14:17.269 +0000 [INFO] (0001:preview): SQL: SELECT * FROM `sample`
2017-04-21 07:14:17.283 +0000 [INFO] (0001:preview): > 0.01 seconds
+---------+-------------+---------------------------+---------------------------+
| id:long | name:string | create_time:timestamp     | update_time:timestamp     |
+---------+-------------+---------------------------+---------------------------+
|       1 | sample1     | 2013-05-27 19:46:08+09:00 | 2013-05-29 06:33:01+09:00 |
|       2 | sample2     | 2013-05-27 19:46:08+09:00 | 2013-05-29 06:33:01+09:00 |
|       3 | sample3     | 2013-05-27 19:46:08+09:00 | 2013-05-29 06:33:01+09:00 |
|       4 | sample4     | 2013-05-27 19:46:08+09:00 | 2013-05-29 06:33:01+09:00 |
|       5 | sample5     | 2013-05-27 19:46:08+09:00 | 2013-05-29 06:33:01+09:00 |
|       6 | sample6     | 2013-05-27 19:46:08+09:00 | 2013-05-29 06:33:01+09:00 |
|       7 | sample7     | 2013-05-27 19:46:08+09:00 | 2013-05-29 06:33:01+09:00 |
|       8 | sample8     | 2013-05-27 19:46:08+09:00 | 2013-05-29 06:33:01+09:00 |
|       9 | sample9     | 2013-05-27 19:46:08+09:00 | 2013-05-29 06:33:01+09:00 |
|      10 | sample10    | 2013-05-27 19:46:08+09:00 | 2013-05-29 06:33:01+09:00 |
+---------+-------------+---------------------------+---------------------------+

このように見事にちょうど9時間ずれています。

解決方法

原因はプラグインが自動でMySQLのtimezoneを取得しないことにあります。そのせいで、JDBCがAsia/Tokyo時間をUTCとして読み取り、9時間ずれる結果となっています。そこで、JDBCにオプションを渡してやる必要があります。以下のようにymlを変更することで、JDBCにtimezoneを教えることができます。

修正後のsample.yml

in:
  type: mysql
  host: localhost
  user: user
  password: password
  database: sample
  table: sample
  select: "*"
+ options: {useLegacyDatetimeCode: false, serverTimezone: Asia/Tokyo}
  default_timezone: "Asia/Tokyo"
  column_options:
    create_time: {type: string, timestamp_format: "%Y-%m-%d %H:%M:%S%:z"}
    update_time: {type: string, timestamp_format: "%Y-%m-%d %H:%M:%S%:z"}


out:
  .
  .
  .

以上のように、options:にhash形式でJDBCオプションを渡すことができます。
serverTimezoneに関しては環境ごとに変える必要があります。

+ options: {useTimezone: true, serverTimezone: Asia/Tokyo}

とすることでも解決できますが、古いオプションなので、上の方を使うようにしたほうが良いです。

修正後

$ embulk preview sample.yml
2017-04-21 07:21:09.930 +0000: Embulk v0.8.18
2017-04-21 07:21:10.972 +0000 [INFO] (0001:preview): Loaded plugin embulk-input-mysql (0.8.2)
2017-04-21 07:21:11.012 +0000 [INFO] (0001:preview): Fetch size is 10000. Using server-side prepared statement.
2017-04-21 07:21:11.261 +0000 [INFO] (0001:preview): Fetch size is 10000. Using server-side prepared statement.
2017-04-21 07:21:11.270 +0000 [INFO] (0001:preview): SQL: SELECT * FROM `sample`
2017-04-21 07:21:11.276 +0000 [INFO] (0001:preview): > 0.00 seconds
+---------+-------------+---------------------------+---------------------------+
| id:long | name:string | create_time:timestamp     | update_time:timestamp     |
+---------+-------------+---------------------------+---------------------------+
|       1 | sample1     | 2013-05-27 10:46:08+09:00 | 2013-05-28 21:33:01+09:00 |
|       2 | sample2     | 2013-05-27 10:46:08+09:00 | 2013-05-28 21:33:01+09:00 |
|       3 | sample3     | 2013-05-27 10:46:08+09:00 | 2013-05-28 21:33:01+09:00 |
|       4 | sample4     | 2013-05-27 10:46:08+09:00 | 2013-05-28 21:33:01+09:00 |
|       5 | sample5     | 2013-05-27 10:46:08+09:00 | 2013-05-28 21:33:01+09:00 |
|       6 | sample6     | 2013-05-27 10:46:08+09:00 | 2013-05-28 21:33:01+09:00 |
|       7 | sample7     | 2013-05-27 10:46:08+09:00 | 2013-05-28 21:33:01+09:00 |
|       8 | sample8     | 2013-05-27 10:46:08+09:00 | 2013-05-28 21:33:01+09:00 |
|       9 | sample9     | 2013-05-27 10:46:08+09:00 | 2013-05-28 21:33:01+09:00 |
|      10 | sample10    | 2013-05-27 10:46:08+09:00 | 2013-05-28 21:33:01+09:00 |
+---------+-------------+---------------------------+---------------------------+

以上のように正常に時間を取得できました!

まとめ

embulk-input-jdbcはその名前の通り、JDBC依存なのでオプションに注意しましょう。また、各プラグインのオプションを一通り眺めると解決に繋がりそうですね。

参考

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした