MySQL Bugs: #39920: MySQL cannot deal with Leap Second expression in string literal.

Bug #39920	MySQL cannot deal with Leap Second expression in string literal.
Submitted:	8 Oct 2008 7:07	Modified:	11 Dec 2008 20:45
Reporter:	Meiji KIMURA	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.0.x	OS:	Linux
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	timezone

Description:
MySQL supports "Leap Second" after setting these.

(1) Enable "Leap second" of OS's function.
(2) Use "mysql_tzinfo_to_sql" to set data for leap seconds into time_zone_leap_second table. (http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html)

After these settings, datetime column can be inserted  "Leap Second" (e.g. '2009-01-01 08:59:60' in Japan) by datetime functions. But string literal '2009-01-01 08:59:60' still cannot be inserted.

I think that '2009-01-01 08:59:60' can be inserted even if it's a string literal.

How to repeat:
MySQL supports "Leap Second" after setting these.

(1) Enable "Leap second" of OS's function.
(2) Use "mysql_tzinfo_to_sql" to set data for leap seconds into time_zone_leap_second table. (http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html)

After that, I could confirm that a return value of datetime functions could be inserted a "Leap second" as below.

[Sample]
mysql> create table test5(col1 datetime, col2 timestamp);

I made a table as above, and set OS's clock around "Leap Second".
(In Japn, it is '2009-01-01 08:59:60')
After that I issued multiple INSERTs as below.

mysql> insert into test5(col1) values(now());

The result is here.
mysql> select * from test5;
+---------------------+---------------------+
| col1 | col2 |
+---------------------+---------------------+
| 2009-01-01 08:59:59 | 2009-01-01 08:59:59 |
| 2009-01-01 08:59:60 | 2009-01-01 08:59:60 |
| 2009-01-01 09:00:00 | 2009-01-01 09:00:00 |
| 2009-01-01 09:00:01 | 2009-01-01 09:00:01 |
+---------------------+---------------------+

'2009-01-01 09:00:00' could be inserted.

But when I spcified a values '2009-01-01 08:59:60' as string literal, it could not be inserted.

mysql> insert into test5(col1) value ('2009/01/01 8:59:60');
Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'col1' at row 1 |
+---------+------+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test5;
+---------------------+---------------------+
| col1 | col2 |
+---------------------+---------------------+
| 2009-01-01 08:59:59 | 2009-01-01 08:59:59 |
| 2009-01-01 08:59:60 | 2009-01-01 08:59:60 |
| 2009-01-01 09:00:00 | 2009-01-01 09:00:00 |
| 2009-01-01 09:00:01 | 2009-01-01 09:00:01 |
| 0000-00-00 00:00:00 | 2009-01-01 09:10:04 |
+---------------------+---------------------+

Suggested fix:
MySQL should be deal with '2009-01-01 08:59:60' under "Leap Second" is enabled. Even if it's a string literal.

Hi!

Please note that as a workaround for dump/restore problem one can use --tz-utc of mysqldump.

'--tz-utc' can affect to timestamp type column only.
It cannot affect to datetime type column.

Then it is not a workaround for this case.

Bug #40610 is related to this bug

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/59241

2717 Georgi Kodinov	2008-11-19
      Bug #39920: MySQL cannot deal with Leap Second expression in string literal.
      
      Updated MySQL to react correctly on the 2008's UTC leap second.
      MySQL functions that return the OS current time, like e.g. CURDATE(), NOW() etc
      will return :59:59 instead of :59:60.
      As a result the reader will receive :59:59 for two consecutive seconds during the
      leap second.

Seem that returning :59 for the leap second is a common solution : http://kbase.redhat.com/faq/FAQ_103_13406.shtm

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/60233

2717 Georgi Kodinov	2008-11-29
      Bug #39920: MySQL cannot deal with Leap Second expression in string literal.
            
      Updated MySQL to react correctly on the 2008's UTC leap second.
      MySQL functions that return the OS current time, like e.g. CURDATE(), NOW() etc
      will return :59:59 instead of :59:60.
      As a result the reader will receive :59:59 for two consecutive seconds during the
      leap second.
      Added a test case to demonstrate this.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/60268

2717 Georgi Kodinov	2008-12-01
      Bug #39920: MySQL cannot deal with Leap Second expression in string literal.
                  
      Updated MySQL time handling code to react correctly on UTC leap second additions 
      that can occur on 30 Jun/1 Jul and 31 Dec/1 Jan in the second after 23:59:59.
      MySQL functions that return the OS current time, like e.g. CURDATE(), NOW() etc
      will return :59:59 instead of :59:60.
      As a result the reader will receive :59:59 for two consecutive seconds during the
      leap second.
      This fix will not affect the values returned by UNIX_TIMESTAMP() for leap seconds.
      But note that with this change when converting the value returned by UNIX_TIMESTAMP()
      to broken down time the correction of leap seconds will still be applied.
      Note that this fix will make a difference *only* if the OS is specially configured
      to return leap seconds from the OS time calls.
      Added a test case to demonstrate the effect of the fix.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/60278

2717 Georgi Kodinov	2008-12-01
      Bug #39920: MySQL cannot deal with Leap Second expression in string literal.
                        
      Updated MySQL time handling code to react correctly on UTC leap second additions.
      MySQL functions that return the OS current time, like e.g. CURDATE(), NOW() etc
      will return :59:59 instead of :59:60 or 59:61.
      As a result the reader will receive :59:59 for 2 or 3 consecutive seconds 
      during the leap second.
      This fix will not affect the values returned by UNIX_TIMESTAMP() for leap seconds.
      But note that when converting the value returned by UNIX_TIMESTAMP() to broken 
      down time the correction of leap seconds will still be applied.
      Note that this fix will make a difference *only* if the OS is specially configured
      to return leap seconds from the OS time calls or when using a MySQL time zone 
      defintion that has leap seconds.
      Even after this change date/time literals (or other broken down time 
      representations) with leap seconds (ending on :59:60 or 59:61) will still be 
      considered illegal and discarded by the server with an error or 
      a warning depending on the sql mode.
      Added a test case to demonstrate the effect of the fix.

Patch approved with minor comments sent by e-mail.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/60291

2731 Georgi Kodinov	2008-12-01
      addendum to the fix for bug #39920 : post-merge test suite fixes

Pushed into 5.0.74  (revid:kgeorge@mysql.com-20081201154106-c310zzy5or043rqa) (version source revid:kgeorge@mysql.com-20081201154106-c310zzy5or043rqa) (pib:5)

Pushed into 5.1.31  (revid:kgeorge@mysql.com-20081201154106-c310zzy5or043rqa) (version source revid:kgeorge@mysql.com-20081201154351-wuy9mqel7dv0ie1c) (pib:5)

Pushed into 6.0.9-alpha  (revid:kgeorge@mysql.com-20081201154106-c310zzy5or043rqa) (version source revid:kgeorge@mysql.com-20081201160003-fe3wip6fv8hhmifq) (pib:5)

Noted in 5.0.74, 5.1.31, 6.0.9 changelogs.

If the operating system is configured to return leap seconds from OS
time calls or if the MySQL server uses a time zone definition that
has leap seconds, functions such as NOW() could return a value having
a time part that ends with :59:60 or :59:61. If such values are
inserted into a table, they would be dumped as is by mysqldump but
considered invalid when reloaded, leading to backup/restore problems.
    
Now leap second values are returned with a time part that ends with
:59:59. This means that a function such as NOW() can return the same
value for two or three consecutive seconds during the leap second. It
remains true that literal temporal values having a time part that
ends with :59:60 or :59:61 are considered invalid.

For additional details about leap-second handling, see 
http://dev.mysql.com/doc/refman/5.0/en/time-zone-leap-seconds.html.

Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)

Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)

Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)

Noted in 5.0.72sp1 changelog.