Bug #76516 time interval arithmetic leads to exception on summer time change
Submitted: 28 Mar 2015 10:07 Modified: 25 Jul 2018 11:45
Reporter: Andrei Voronkov Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: DML Severity:S1 (Critical)
Version:5.5.41-0ubuntu0.14.04.1-log OS:Linux (Ubuntu 14.04)
Assigned to: CPU Architecture:Any
Tags: summer time change, time arithmetic

[28 Mar 2015 10:07] Andrei Voronkov
Description:
when @@sql_mode does not use ALLOW_INVALID_DATES, time arithmetic leads to an exception when the result false on the summer time change. 

How to repeat:
This is the value of @@sql_mode I have:

STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

If I call SELECT NOW(), MySQL returns '2015-03-28 10:37:42'.
If I call SELECT NOW() + INTERVAL 16 HOUR, MySQL returns '2015-03-29 02:37:42', which is a non-existing time. Thanks to the time change, '2015-03-29 01:59:00', changes to '2015-03-29 03:00:00'

The problem appears when I execute a prepared statement from the Perl DBD::mysql interface 

DBD::mysql::db do failed: Incorrect datetime value: '2015-03-29 02:32:15' for column 'expires' at row 1 at /......../lib/DBConnection.pm line 89. While executing query INSERT INTO tempdata(nonce,expires,value) VALUES (?,NOW()+INTERVAL ? SECOND,?) at /...../lib/DBConnection.pm line 98.

The tempdata table schema is 

CREATE TABLE `tempdata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nonce` char(16) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `expires` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `value` longblob NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=22364623 DEFAULT CHARSET=utf8

it is non-transactional on purpose, though the query is evaluated from a transaction

I cannot reproduce this bug when I did not use a prepared statement but used concrete values in VALUES()

My server reported hundreds of errors in a few minutes, when many records with an expiry time in 24 hours were added last night, when in 24 hours means a few minutes after the summer time change.
Restarting MySQL did not help. Setting @@sql_mode 'NO_ENGINE_SUBSTITUTION,ALLOW_INVALID_DATES' did help

I marked the bug severe since my Web service used daily by 12,000 users has become unusable for many users when the bug appeared (300+ errors in a few minutes only)

Suggested fix:
I have no idea, because the bug only appeared when executing a prepared statement, however the bug seems coming from MySQL (the error message is MySQL), also confirmed by SELECT NOW() + INTERVAL 16 HOUR, MySQL returning a wrong time string '2015-03-29 02:37:42'.

My guess was that MySQL uses epoch seconds for representing timestamps internally, so I do not understand why the wrong time string values appears at all and why insertion in a table results in a MySQL under some circumstances.

I wish I could help more.
[25 Jun 2018 11:45] MySQL Verification Team
Thia issue still presents with most recent server version?.
[26 Jul 2018 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".