timestamp column default value bug in multiple time zones #9064

victorggsimida · 2019-01-15T07:11:31Z

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: 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 |

The text was updated successfully, but these errors were encountered:

XuHuaiyu · 2019-01-15T07:21:52Z

@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?

victorggsimida · 2019-01-15T07:32:24Z

@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

XuHuaiyu · 2019-01-15T09:11:44Z

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?

victorggsimida · 2019-01-15T10:54:01Z

@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 · 2019-01-17T02:03:03Z

@XuHuaiyu is there any progress in this issue?

XuHuaiyu · 2019-01-17T02:08:48Z

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?

What's the timezone of your system?

victorggsimida · 2019-01-17T02:24:30Z

@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

XuHuaiyu · 2019-01-17T02:31:19Z

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

victorggsimida · 2019-01-17T02:33:25Z

mysql>

XuHuaiyu · 2019-01-17T02:44:06Z

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 · 2019-01-17T02:45:08Z

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)

victorggsimida · 2019-01-17T02:46:02Z

@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

XuHuaiyu · 2019-01-17T02:51:27Z

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

victorggsimida · 2019-01-17T02:59:24Z

@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 · 2019-01-17T02:59:59Z

@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>

crazycs520 · 2019-01-17T03:21:48Z

@victorggsimida , It looks like this bug: #7724.
When you create the index?

victorggsimida · 2019-01-17T03:23:51Z

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);

crazycs520 · 2019-01-17T03:32:42Z

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

crazycs520 · 2019-01-17T06:54:29Z

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;

XuHuaiyu · 2019-01-17T07:05:45Z

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

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

victorggsimida · 2019-01-17T07:34:07Z

@XuHuaiyu

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

ghost · 2020-07-15T00:43:29Z

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)

XuHuaiyu added the type/bug The issue is confirmed as a bug. label Jan 17, 2019

XuHuaiyu changed the title ~~timestamp incorrect value with max~~ different values of table and index for timestamp column Jan 17, 2019

XuHuaiyu assigned crazycs520 Jan 17, 2019

crazycs520 mentioned this issue Jan 18, 2019

* fix timestamp default value bug in multiple time zones. #9115

Merged

crazycs520 changed the title ~~different values of table and index for timestamp column~~ timestamp column default value bug in multiple time zones Jan 29, 2019

ghost closed this as completed Jul 15, 2020

This issue was closed.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

timestamp column default value bug in multiple time zones #9064

timestamp column default value bug in multiple time zones #9064

victorggsimida commented Jan 15, 2019

XuHuaiyu commented Jan 15, 2019

victorggsimida commented Jan 15, 2019

XuHuaiyu commented Jan 15, 2019

victorggsimida commented Jan 15, 2019

victorggsimida commented Jan 17, 2019

XuHuaiyu commented Jan 17, 2019

victorggsimida commented Jan 17, 2019

XuHuaiyu commented Jan 17, 2019

victorggsimida commented Jan 17, 2019

XuHuaiyu commented Jan 17, 2019

XuHuaiyu commented Jan 17, 2019 •

edited

Loading

victorggsimida commented Jan 17, 2019

XuHuaiyu commented Jan 17, 2019

victorggsimida commented Jan 17, 2019

victorggsimida commented Jan 17, 2019

crazycs520 commented Jan 17, 2019

victorggsimida commented Jan 17, 2019

crazycs520 commented Jan 17, 2019

crazycs520 commented Jan 17, 2019 •

edited

Loading

XuHuaiyu commented Jan 17, 2019

victorggsimida commented Jan 17, 2019 •

edited

Loading

ghost commented Jul 15, 2020

timestamp column default value bug in multiple time zones #9064

timestamp column default value bug in multiple time zones #9064

Comments

victorggsimida commented Jan 15, 2019

Bug Report

XuHuaiyu commented Jan 15, 2019

victorggsimida commented Jan 15, 2019

XuHuaiyu commented Jan 15, 2019

victorggsimida commented Jan 15, 2019

victorggsimida commented Jan 17, 2019

XuHuaiyu commented Jan 17, 2019

victorggsimida commented Jan 17, 2019

XuHuaiyu commented Jan 17, 2019

victorggsimida commented Jan 17, 2019

XuHuaiyu commented Jan 17, 2019

XuHuaiyu commented Jan 17, 2019 • edited Loading

victorggsimida commented Jan 17, 2019

XuHuaiyu commented Jan 17, 2019

victorggsimida commented Jan 17, 2019

victorggsimida commented Jan 17, 2019

crazycs520 commented Jan 17, 2019

victorggsimida commented Jan 17, 2019

crazycs520 commented Jan 17, 2019

crazycs520 commented Jan 17, 2019 • edited Loading

prepare

Then

XuHuaiyu commented Jan 17, 2019

victorggsimida commented Jan 17, 2019 • edited Loading

ghost commented Jul 15, 2020

XuHuaiyu commented Jan 17, 2019 •

edited

Loading

crazycs520 commented Jan 17, 2019 •

edited

Loading

victorggsimida commented Jan 17, 2019 •

edited

Loading