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.