Bug #96609 Corrupted timestamp data on alter
Submitted: 21 Aug 2019 19:14 Modified: 22 Aug 2019 9:46
Reporter: Christopher Shumake Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:5.7.23, 5.7.27 OS:Debian
Assigned to: CPU Architecture:Any
Tags: DST, timestamp, timezone

[21 Aug 2019 19:14] Christopher Shumake
Description:
Timestamps are converted when the column is rewritten as part of an alter and using the A/LA timezone

(This is the second in a series of bugs to decompose the parts from #93199)

How to repeat:
username@hostname [dbname]>set session time_zone = '+0:00';
Query OK, 0 rows affected (0.00 sec)

username@hostname [dbname]>create table bar (user_id int unsigned not null, ts timestamp not null, data char(1) not null, primary key (user_id, ts));
Query OK, 0 rows affected (0.02 sec)

username@hostname [dbname]>insert into bar values (1, from_unixtime(1478421000), 'a'), (1, from_unixtime(1478421000 + 3600), 'b');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

username@hostname [dbname]>select user_id, ts, unix_timestamp(ts), data from bar;
+---------+---------------------+--------------------+------+
| user_id | ts                  | unix_timestamp(ts) | data |
+---------+---------------------+--------------------+------+
|       1 | 2016-11-06 08:30:00 |         1478421000 | a    |
|       1 | 2016-11-06 09:30:00 |         1478424600 | b    |
+---------+---------------------+--------------------+------+
2 rows in set (0.00 sec)

Demonstration 1:
(rewriting the column as part of an alter does a second TZ conversion, possibly based on the datetime output of the timestamp)

username@hostname [dbname]>set session time_zone = 'America/Los_Angeles';
Query OK, 0 rows affected (0.00 sec)

username@hostname [dbname]>alter table bar drop primary key;
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

username@hostname [dbname]>select user_id, ts, unix_timestamp(ts), data from bar;
+---------+---------------------+--------------------+------+
| user_id | ts                  | unix_timestamp(ts) | data |
+---------+---------------------+--------------------+------+
|       1 | 2016-11-06 01:30:00 |         1478421000 | a    |
|       1 | 2016-11-06 01:30:00 |         1478421000 | b    |
+---------+---------------------+--------------------+------+
2 rows in set (0.01 sec)

--> row 'b' was converted a second time.

Demonstration 2:
(not all alters trigger the problem)

username@hostname [dbname]>set session time_zone = '+0:00';
Query OK, 0 rows affected (0.00 sec)

username@hostname [dbname]>create table bar (user_id int unsigned not null, ts timestamp not null, data char(1) not null);
Query OK, 0 rows affected (0.02 sec)

username@hostname [dbname]>insert into bar values (1, from_unixtime(1478421000), 'a'), (1, from_unixtime(1478421000 + 3600), 'b');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

username@hostname [dbname]>select user_id, ts, unix_timestamp(ts), data from bar;
+---------+---------------------+--------------------+------+
| user_id | ts                  | unix_timestamp(ts) | data |
+---------+---------------------+--------------------+------+
|       1 | 2016-11-06 08:30:00 |         1478421000 | a    |
|       1 | 2016-11-06 09:30:00 |         1478424600 | b    |
+---------+---------------------+--------------------+------+
2 rows in set (0.00 sec)

username@hostname [dbname]>set session time_zone = 'America/Los_Angeles';
Query OK, 0 rows affected (0.00 sec)

username@hostname [dbname]>alter table bar engine=innodb;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

username@hostname [dbname]>select user_id, ts, unix_timestamp(ts), data from bar;
+---------+---------------------+--------------------+------+
| user_id | ts                  | unix_timestamp(ts) | data |
+---------+---------------------+--------------------+------+
|       1 | 2016-11-06 01:30:00 |         1478421000 | a    |
|       1 | 2016-11-06 01:30:00 |         1478424600 | b    |
+---------+---------------------+--------------------+------+
2 rows in set (0.00 sec)

--> row 'b' was not converted again

Suggested fix:
Stop transparent conversions of timestamps on write.
[21 Aug 2019 19:17] Christopher Shumake
Note this is about daylight savings time conversions
[22 Aug 2019 9:46] MySQL Verification Team
Hello Christopher,

Thank you for the report and test case.
Verified as described with 5.7.27 build.

regards,
Umesh