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.
