tidb-lightning alters the values of timestamp columns

[ngocson2vn]

Bug Report

1. What did you do? If possible, provide a recipe for reproducing the error. Using the tidb-lightning tool to restore a full backup data.

   • In the full backup data, there is a table that has timestamp columns like this:

     CREATE TABLE `users` (
     `id` bigint(20) NOT NULL,
     `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
     `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     ...
     )

   • Set all servers (TiDB, PD, TiKV, Lightning, ...)' timezone to UTC

   • In the script deploy/scripts/start_lightning.sh, set timezone to Asia/Tokyo

     
     #!/bin/bash
     set -e
     ulimit -n 1000000
     cd "/home/ec2-user/deploy" || exit 1
     mkdir -p status

   export RUST_BACKTRACE=1

   export TZ=Asia/Tokyo

   echo -n 'sync ... ' stat=$(time sync) echo ok echo $stat

   nohup ./bin/tidb-lightning -config ./conf/tidb-lightning.toml &> log/tidb_lightning_stderr.log &

   echo $! > "status/tidb-lightning.pid"

• Start the tidb-lightning tool

  $ cd deploy/
  $ scripts/start_lightning.sh

2. What did you expect to see? The tidb-lightning tool should respect the original data (in the full backup), import the original data as it is.

3. What did you see instead? The tidb-lightning tool has altered the values of timestamp columns. For examples,

   Original data (from the full backup):

   $ head -2 ./xxxxx.users.000000001.sql
   INSERT INTO `users` VALUES
   (123456789123456789,'2019-09-03 12:31:02','2019-09-03 12:35:18',...)

   Imported data:

   > select id, created_at, updated_at from users where id = 123456789123456789;
   +--------------------+---------------------+---------------------+
   | id                 | created_at          | updated_at          |
   +--------------------+---------------------+---------------------+
   | 123456789123456789 | 2019-09-03 03:31:02 | 2019-09-03 03:35:18 |
   +--------------------+---------------------+---------------------+

   So the tidb-lightning tool has altered the values of columns created_at and updated_at. The original values have been subtracted by 9 hours.

4. Versions of the cluster

   • TiDB-Lightning version (run tidb-lightning -V):

     Release Version: v4.0.9
     Git Commit Hash: 56bc32daad19b9dff10104c55300292de959fde3
     Git Branch: heads/refs/tags/v4.0.9
     UTC Build Time: 2020-12-19 04:48:01
     Go Version: go version go1.13 linux/amd64

   • TiKV-Importer version (run tikv-importer -V)

     Didn't use

   • TiKV version (run tikv-server -V):

     TiKV
     Release Version:   4.0.10
     Edition:           Community
     Git Commit Hash:   2ea4e608509150f8110b16d6e8af39284ca6c93a
     Git Commit Branch: heads/refs/tags/v4.0.10
     UTC Build Time:    2021-01-15 03:16:35
     Rust Version:      rustc 1.42.0-nightly (0de96d37f 2019-12-19)
     Enable Features:   jemalloc mem-profiling portable sse protobuf-codec
     Profile:           dist_release

   • TiDB cluster version (execute SELECT tidb_version(); in a MySQL client):

     +---------------------+
     | version()           |
     +---------------------+
     | 5.7.25-TiDB-v4.0.10 |
     +---------------------+

   • Other interesting information (system version, hardware config, etc):

     > show variables like '%time_zone%';
     +------------------+--------+
     | Variable_name    | Value  |
     +------------------+--------+
     | system_time_zone | UTC    |
     | time_zone        | SYSTEM |
     +------------------+--------+
     
     $ cat /etc/os-release
     NAME="Amazon Linux"
     VERSION="2"
     ID="amzn"
     ID_LIKE="centos rhel fedora"
     VERSION_ID="2"
     PRETTY_NAME="Amazon Linux 2"
     ANSI_COLOR="0;33"
     CPE_NAME="cpe:2.3:o:amazon:amazon_linux:2"
     HOME_URL="https://amazonlinux.com/"

5. Operation logs

   • Please upload tidb-lightning.log for TiDB-Lightning if possible
   • Please upload tikv-importer.log from TiKV-Importer if possible
   • Other interesting logs

6. Configuration of the cluster and the task

   • tidb-lightning.toml for TiDB-Lightning if possible

     
     # lightning Configuration

   [lightning] file = "/home/tidb/deploy/log/tidb_lightning.log" index-concurrency = 2 io-concurrency = 5 level = "info" max-backups = 14 max-days = 28 max-size = 128 pprof-port = 8289 table-concurrency = 6

   [checkpoint] enable = true schema = "tidb_lightning_checkpoint" driver = "file"

   [tikv-importer] backend = "local" sorted-kv-dir = "/home/tidb/deploy/sorted-kv-dir"

   [mydumper] data-source-dir = "/home/tidb/deploy/mydumper/scheduled-backup-20210120-044816" no-schema = false read-block-size = 65536

   [tidb] build-stats-concurrency = 20 checksum-table-concurrency = 16 distsql-scan-concurrency = 100 host = "TIDB_HOST" index-serial-scan-concurrency = 20 log-level = "error" password = "xxxxx" port = 4000 status-port = 10080 user = "root" pd-addr = "PD_HOST:2379"

   [post-restore] analyze = true checksum = true

   [cron] log-progress = "5m" switch-mode = "5m"

   
   - `inventory.ini` if deployed by Ansible

7. Screenshot/exported-PDF of Grafana dashboard or metrics' graph in Prometheus for TiDB-Lightning if possible

[3pointer]

Two reasons cause this problem.

1. Lightning and TiDB cluster have different TZ
2. TiDB cluster's default variable time_zone is SYSTEM

Because we already set time_zone to SYSTEM in session, Lightning will use the local system TZ which is Asia/Tokyo.

To solve this problem quickly. you can set Lightning the same TZ with the TiDB cluster. Next, We can fix it by set Lightning session TZ to TiDB cluster's system_time_zone when TiDB cluster's time_zone is SYSTEM.

Thanks for the reports!