Tidb: timestamp column default value bug in multiple time zones

Created on 15 Jan 2019 · 22Comments · Source: pingcap/tidb

Bug Report

Please answer these questions before submitting your issue. Thanks!

What did you do?
If possible, provide a recipe for reproducing the error.

the privious version : 08.14 rc version

currecnt version: 2019-01-03 rc version

What did you expect to see?

now=2019-01-05 15:06

mysql> alter table Account add column marstime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
Query OK, 0 rows affected (1.02 sec)

mysql> alter table Account add index(marstime);
^@^@^@^@^@^@^@Query OK, 0 rows affected (7 min 3.18 sec)

mysql> select max(marstime) From Account;
+---------------------+
| max(marstime) |
+---------------------+
| 2019-01-15 22:54:33 |
+---------------------+
1 row in set (0.38 sec)

mysql> select min(marstime) From Account;
+---------------------+
| min(marstime) |
+---------------------+
| 2019-01-15 14:57:46 |
+---------------------+
1 row in set (0.26 sec)

Account schema:

CREATE TABLE Account (
asdfid int(11) NOT NULL AUTO_INCREMENT,
nsdfafsdme varchar(60) NOT NULL DEFAULT '',
daisdfn varchar(60) NOT NULL DEFAULT '',
fsag int(11) DEFAULT '0',
unt int(11) DEFAULT '0',
uib int(11) NOT NULL DEFAULT '0',
pot int(11) DEFAULT '0',
blom int(11) DEFAULT '0',
im int(10) UNSIGNED DEFAULT '0',
bce int(11) DEFAULT '0',
newe datetime DEFAULT NULL,
pad varchar(16) DEFAULT NULL,
marstime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (asdfid),
UNIQUE KEY n (nsdfafsdme,daisdfn),
UNIQUE KEY d (daisdfn,nsdfafsdme),
KEY marstime (marstime)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=22938908
1 row in set (0.03 sec)

Account data count: 2kw+, and the data is imported by myloader in privious tidb_version;;

I do the same thing in : tidb( 2018-08-14 rc version) and (2019-01-03 version)
is stil the same

What did you see instead?

when I calculate marstime with max, it return the impossible data.

the value is in the future

What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

| Release Version: v2.0.11
Git Commit Hash: 83889a5d821f40412be1af2ad2f2393737f517fd
Git Branch: release-2.0
UTC Build Time: 2019-01-03 09:27:12
GoVersion: go version go1.11.2 linux/amd64
TiKV Min Version: 2.0.0-rc.4.1 |

typbug

Source

victorggsimida

Most helpful comment

I reproduce this bug in tidb, pd, tikv release 2.1.
bellow is tidb version.

Release Version: v2.1.2-7-g08f56d318                                     
Git Commit Hash: 08f56d31857c75e9187f48a558dc9455105783b3               
Git Branch: release-2.1                                                  
UTC Build Time: 2019-01-16 04:29:17

prepare

use test.
create table t (a int);
insert into t1 values (2);

Then

Create 2 sessions, both execute use test first.
then：
session 1:

set global time_zone = '+02:00';

session 2:

alter table t1 add column b timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
alter table t1 add index(b);

Then, use follow sql to check:

select b from t1;
select b from t1 use index(b);
admin check table t1;

crazycs520 on 17 Jan 2019

👍2

All 22 comments

@victorggsimida Thanks for your feedback.
1.

the privious version : 08.14 rc version
currecnt version: 2019-01-03 rc version

May you provide us the githash of these two tidb-server binary?

> I do the same thing in : tidb( 2018-08-14 rc version) and (2019-01-03 version)
is stil the same

What does still the same mean?

now=2019-01-05 15:06

What does this mean?

> mysql> select max(marstime) From Account;
+---------------------+
| max(marstime) |
+---------------------+
| 2019-01-15 22:54:33 |
+---------------------+
1 row in set (0.38 sec)
mysql> select min(marstime) From Account;
+---------------------+
| min(marstime) |
+---------------------+
| 2019-01-15 14:57:46 |
+---------------------+
1 row in set (0.26 sec)

Are these the results of MySQL or TiDB?

XuHuaiyu on 15 Jan 2019

@XuHuaiyu
1.
Release Version: 2.0.11
Git Commit Hash: c666277d3565164a00451b95666012cb04274d93
Git Commit Branch: release-2.0
UTC Build Time: 2019-01-03 09:29:52
Rust Version: 1.27.0-nightly (48fa6f963 2018-04-05)

privious version：
tidb-server v2.0.6

the same: I execute the same command in this two version and the result is the same.

Today: I upgrade the cluster from V2.0.6 to 2.0.11.
I test in these two versions

now means the time I execute the command. For showing the imporssible timestamp value '2019-01-15 22:54:33'

4.These result are in TiDB.
The max values of 'marstime' column is impossible

victorggsimida on 15 Jan 2019

OK, @victorggsimida
I have 2 more questions:

What's the result of select @@time_zone in TiDB?
If it's SYSTEM, you may further check what's the timezone of your system.

I can not find this git commit hash c666277d3565164a00451b95666012cb04274d93 in branch release-2.0. Is it a temporary version?

XuHuaiyu on 15 Jan 2019

@XuHuaiyu

time_zone variables:

before upgrade today, I remember that I have set the time_zone to shanghai to avoid the problem of glibc timestamp transfer lock.

Because I have a problem of using too much system cpu where I select many data which the data include timestamp type.

After upgrade: I roll update the cluster and the time_zone is as below shows
mysql> select @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| SYSTEM |
+-------------+
1 row in set (0.00 sec)

version info:
I use the wget: wget http://download.pingcap.org/tidb-v2.0.11-linux-amd64.tar.gz to download the binary.

tidb_version = v2.0.11

Hope my answer can help u.

victorggsimida on 15 Jan 2019

@XuHuaiyu is there any progress in this issue?

victorggsimida on 17 Jan 2019

Sorry for late reply, I did not notice your comment before.

before upgrade today, I remember that I have set the time_zone to shanghai

Do you mean you changed your timezone to shanghai in tidb server? If so, how did you do that?

2.
What's the timezone of your system?

XuHuaiyu on 17 Jan 2019

@XuHuaiyu

yes. before upgrade the cluster. I set the timezone.
I can't precisely remember the command.
But I remember the link I reference
试试 SET GLOBAL time_zone = 'Asia/Shanghai';

reference: https://github.com/pingcap/docs-cn/blob/master/sql/time-zone.md

timezone of my system:
utc 8

victorggsimida on 17 Jan 2019

what's the result of
explain select min(marstime) From Account;

XuHuaiyu on 17 Jan 2019

mysql>

victorggsimida on 17 Jan 2019

So your operation process is as follows?

SET GLOBAL time_zone = 'Asia/Shanghai'; // I'm not really sure whether you really set the GLOBAL timezone.
upgrade tidb cluster to v2.0.11
run alter table Account add column marstime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
run alter table Account add index(marstime);

XuHuaiyu on 17 Jan 2019

And please run the following sqls:
select min(marstime) From Account ignore index(marstime)
explain select min(marstime) From Account ignore index(marstime)
select max(marstime) From Account ignore index(marstime)
explain select max(marstime) From Account ignore index(marstime)

XuHuaiyu on 17 Jan 2019

@XuHuaiyu

I am sure. Because I exit session. and relogin and check the variable

tidb version now:::
What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?
| Release Version: v2.0.11
Git Commit Hash: 83889a5
Git Branch: release-2.0
UTC Build Time: 2019-01-03 09:27:12
GoVersion: go version go1.11.2 linux/amd64
TiKV Min Version: 2.0.0-rc.4.1 |

step 3,4 already try it .

it is not oK

victorggsimida on 17 Jan 2019

Yep.
I meant that: Did the 4 steps the same as what you did 2 days ago?

XuHuaiyu on 17 Jan 2019

@XuHuaiyu
mysql> select min(marstime) From Account ignore index(marstime)
-> ;
+---------------------+
| min(marstime) |
+---------------------+
| 2019-01-17 10:49:10 |
+---------------------+
1 row in set (8.73 sec)

mysql> select max(marstime) From Account ignore index(marstime);
+---------------------+
| max(marstime) |
+---------------------+
| 2019-01-17 10:49:10 |
+---------------------+
1 row in set (5.45 sec)

mysql>

victorggsimida on 17 Jan 2019

@XuHuaiyu
mysql> select min(marstime) From Account;
+---------------------+
| min(marstime) |
+---------------------+
| 2019-01-17 18:49:10 |
+---------------------+
1 row in set (0.08 sec)

mysql> select max(marstime) From Account;
+---------------------+
| max(marstime) |
+---------------------+
| 2019-01-17 18:49:10 |
+---------------------+
1 row in set (0.59 sec)

mysql>

victorggsimida on 17 Jan 2019

@victorggsimida , It looks like this bug: https://github.com/pingcap/tidb/pull/7724.
When you create the index?

crazycs520 on 17 Jan 2019

I just now rerun the sql

run alter table Account add column marstime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
run alter table Account add index(marstime);

victorggsimida on 17 Jan 2019

@victorggsimida , add column and create index in v2.0.11?

crazycs520 on 17 Jan 2019

I reproduce this bug in tidb, pd, tikv release 2.1.
bellow is tidb version.

Release Version: v2.1.2-7-g08f56d318                                     
Git Commit Hash: 08f56d31857c75e9187f48a558dc9455105783b3               
Git Branch: release-2.1                                                  
UTC Build Time: 2019-01-16 04:29:17

prepare

use test.
create table t (a int);
insert into t1 values (2);

Then

Create 2 sessions, both execute use test first.
then：
session 1:

set global time_zone = '+02:00';

session 2:

alter table t1 add column b timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
alter table t1 add index(b);

Then, use follow sql to check:

select b from t1;
select b from t1 use index(b);
admin check table t1;

crazycs520 on 17 Jan 2019

👍2

@victorggsimida
Thanks for your report, we'll fix it soon.

Feel free to let us know if you need any further help.

XuHuaiyu on 17 Jan 2019

@XuHuaiyu

ok when u fix it,pls let me know. I will upgrade it

victorggsimida on 17 Jan 2019

👍1

I can confirm that this bug has been fixed in mater. I am going to close this issue now, but please feel free to re-open if you have any further questions. Thanks!

..

mysql> select b from t1;
+---------------------+
| b                   |
+---------------------+
| 2020-07-14 18:42:19 |
+---------------------+
1 row in set (0.00 sec)

mysql> select b from t1 use index(b);
+---------------------+
| b                   |
+---------------------+
| 2020-07-14 18:42:19 |
+---------------------+
1 row in set (0.00 sec)

mysql> admin check table t1;
Query OK, 0 rows affected (0.02 sec)