TIMESTAMP columns allow values up to 61 for seconds

Bug #597778 reported by Patrick Crews
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Drizzle
Fix Released
Medium
Andrew Hutchings
7.0
Fix Released
Medium
Andrew Hutchings

Bug Description

TIMESTAMP columns can take values with seconds up to 61 (will fail if > 61).
The value is converted:
INSERT INTO t1 (b) VALUES('2000-01-01 12:00:61'); -> insert_value: "2000-01-01 12:01:01"

Test case:
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, b TIMESTAMP);
# This INSERT should fail with ERROR 1685 (bad UNIX TIMESTAMP)
INSERT INTO t1 (b) VALUES('2000-01-01 12:00:61');
SELECT * FROM t1;

DROP TABLE t1;

Related branches

Changed in drizzle:
status: New → Confirmed
importance: Undecided → Medium
Revision history for this message
ziminq (ziminq) wrote :

In drizzled/temporal.cc:1357

1357 bool DateTime::is_valid() const
1358 {
1359 return (_years >= DRIZZLE_MIN_YEARS_SQL && _years <= DRIZZLE_MAX_YEARS_SQL)
1360 && (_months >= 1 && _months <= 12)
1361 && (_days >= 1 && _days <= days_in_gregorian_year_month(_years, _months))
1362 && (_hours <= 23)
1363 && (_minutes <= 59)
1364 && (_seconds <= 61); /* Leap second... */
1365 }

it seems it's legal to be 61 seconds in case there is a leap second. any background on this?

backtrace:

(gdb) bt
#0 drizzled::DateTime::is_valid (this=0x6400000000) at drizzled/temporal.cc:1357
#1 0x0000000000840d5b in drizzled::Timestamp::is_valid (this=0x7f15d42579b0)
    at drizzled/temporal.cc:1370
#2 0x0000000000843186 in drizzled::DateTime::from_string (this=0x7f15d42579b0,
    from=0x3ac47c0 "2000-01-01 12:00:61", from_len=19) at drizzled/temporal.cc:161
#3 0x00000000005dc964 in drizzled::Field_timestamp::store (this=0x3acd558,
    from=0x3ac47c0 "2000-01-01 12:00:61", len=19) at drizzled/field/timestamp.cc:167
#4 0x0000000000628bd2 in drizzled::Item::save_str_value_in_field (this=0x3ac47d8,
    field=0x3acd558, result=0x3ac47e8) at drizzled/item.cc:261
#5 0x000000000066296a in drizzled::Item_string::save_in_field (this=0x3ac47d8,
    field=0x3acd558) at drizzled/item/string.cc:174
#6 0x000000000071b0ba in drizzled::fill_record (session=0x3abf1e0, fields=@0x3ac01a8,
    values=@0x3ac4710, ignore_errors=false) at drizzled/sql_base.cc:4378
#7 0x000000000072c0db in drizzled::mysql_insert (session=0x3abf1e0,
    table_list=0x3ac43e8, fields=@0x3ac01a8, values_list=@0x3ac01f0,
    update_fields=@0x3ac01d8, update_values=@0x3ac01c0, duplic=drizzled::DUP_ERROR,
    ignore=false) at drizzled/sql_insert.cc:358
#8 0x0000000000789c3c in drizzled::statement::Insert::execute (this=0x3ac7b00)
    at drizzled/statement/insert.cc:57
#9 0x000000000073ad69 in mysql_execute_command (session=0x3abf1e0)
    at drizzled/sql_parse.cc:478
#10 0x000000000073ae7d in drizzled::mysql_parse (session=0x3abf1e0,
    inBuf=0x3ac9a38 "insert into t1 (a, b) values (4, '2000-01-01 12:00:61')", length=55)
    at drizzled/sql_parse.cc:736
#11 0x000000000073b346 in drizzled::dispatch_command (command=drizzled::COM_QUERY,
    session=0x3abf1e0,
    packet=0x3abb1d1 "insert into t1 (a, b) values (4, '2000-01-01 12:00:61')",
---Type <return> to continue, or q <return> to quit---
    packet_length=55) at drizzled/sql_parse.cc:223
#12 0x0000000000705d38 in drizzled::Session::executeStatement (this=0x3abf1e0)
    at drizzled/session.cc:646
#13 0x0000000000706484 in drizzled::Session::run (this=0x3abf1e0)
    at drizzled/session.cc:505
#14 0x00007f15e7b97b50 in MultiThreadScheduler::runSession (this=0x32a0960,
    session=0x3abf1e0) at ./plugin/multi_thread/multi_thread.h:67
#15 0x00007f15e7b95fa2 in session_thread (arg=0x3abf1e0)
    at plugin/multi_thread/multi_thread.cc:43
#16 0x00000033d84073da in start_thread () from /lib64/libpthread.so.0
#17 0x00000033d78e62bd in clone () from /lib64/libc.so.6

Revision history for this message
Hartmut Holzgraefe (hartmut-php) wrote :

The value should be 60, not 61? As far as i understand there can be multiple leap seconds per year (as we had 1972), but not on the same day ...

Extra checks that *could* be added if _seconds==60:

* leap seconds can only happen in the last minute of an UTC day
* leap seconds usually happen on Jun 30th or Dec 31st (and so far exclusively have)
** English Wikipedia says theoretically they could be inserted at the end of any month
** German Wikipedia says prefered dates are in June or December, alternative dates are end of March or September, so impiles they could happen at the end of a quarter only

Revision history for this message
Patrick Crews (patrick-crews) wrote : Re: [Bug 597778] Re: TIMESTAMP columns allow values up to 61 for seconds

I'm thinking the value should be 60 as well (not 61):
Modern Unix time is based strictly on
UTC<http://en.wikipedia.org/wiki/Coordinated_Universal_Time>.
UTC counts time using SI <http://en.wikipedia.org/wiki/SI> seconds, and
breaks up the span of time into days. UTC days are almost always 86 400 s
long, but due to "leap seconds <http://en.wikipedia.org/wiki/Leap_seconds>"
are occasionally 86 401 s and could be 86 399 s long (though the latter
option has never been used as of January 2010)

On Sun, Jul 4, 2010 at 3:59 AM, Hartmut Holzgraefe <email address hidden> wrote:

> The value should be 60, not 61? As far as i understand there can be
> multiple leap seconds per year (as we had 1972), but not on the same day
> ...
>
> Extra checks that *could* be added if _seconds==60:
>
> * leap seconds can only happen in the last minute of an UTC day
> * leap seconds usually happen on Jun 30th or Dec 31st (and so far
> exclusively have)
> ** English Wikipedia says theoretically they could be inserted at the end
> of any month
> ** German Wikipedia says prefered dates are in June or December,
> alternative dates are end of March or September, so impiles they could
> happen at the end of a quarter only
>
> --
> TIMESTAMP columns allow values up to 61 for seconds
> https://bugs.launchpad.net/bugs/597778
> You received this bug notification because you are a direct subscriber
> of the bug.
>
> Status in A Lightweight SQL Database for Cloud and Web: Confirmed
>
> Bug description:
> TIMESTAMP columns can take values with seconds up to 61 (will fail if >
> 61).
> The value is converted:
> INSERT INTO t1 (b) VALUES('2000-01-01 12:00:61'); -> insert_value:
> "2000-01-01 12:01:01"
>
> Test case:
> --disable_warnings
> DROP TABLE IF EXISTS t1;
> --enable_warnings
>
> CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, b TIMESTAMP);
> # This INSERT should fail with ERROR 1685 (bad UNIX TIMESTAMP)
> INSERT INTO t1 (b) VALUES('2000-01-01 12:00:61');
> SELECT * FROM t1;
>
> DROP TABLE t1;
>
> To unsubscribe from this bug, go to:
> https://bugs.launchpad.net/drizzle/+bug/597778/+subscribe
>

Revision history for this message
Andrew Hutchings (linuxjedi) wrote :

The IERS will only insert a leap second at the last second of a calendar quarter (as previously stated, this is usually the last second of June or December. I believe the SQL standard allows for (but doesn't define) 2 leap seconds in this period (2 leap seconds has never happened in one go yet though). Different databases implement this in different ways. Oracle and MSSQL I believe ignore it completely. DB2 uses 24:00:00 instead (probably a bad idea).

My suggestion is to make the rule stricter: Allow 2 leap seconds but only on the last second of a calendar quarter.

Changed in drizzle:
assignee: nobody → Andrew Hutchings (linuxjedi)
Revision history for this message
Andrew Hutchings (linuxjedi) wrote :

Although the actual bug here is that seconds '60' and '61' are not stored correctly. So I guess either we keep them and fix it or drop them.

Revision history for this message
Andrew Hutchings (linuxjedi) wrote :

And the reason is you can't store leap seconds in a timestamp field (since it is a timestemp, doh!). So this needs to be fixed.

Changed in drizzle:
status: Confirmed → In Progress
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers