| Bug #40131 | Assigning '1970-01-01 00:00:00' UTC to a timestamp field results in error | ||
|---|---|---|---|
| Submitted: | 18 Oct 2008 19:20 | Modified: | 20 Oct 2008 18:57 |
| Reporter: | Pavel Bazanov | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 6.0.6-alpha-community | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | from_unixtime, timestamp, unix_timestamp | ||
[19 Oct 2008 8:30]
Sveta Smirnova
Thank you for the report. I can not repeat described behavior. Additionally version 6.0.2 is old. Please upgrade to current version 6.0.6, try with it and if problem still exists provide output of SELECT @@SQL_MODE;
[20 Oct 2008 14:32]
Pavel Bazanov
The problem still exists. The output of SELECT @@SQL_MODE is: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[20 Oct 2008 18:57]
Sveta Smirnova
Thank you for the feedback. You have STRICT_TRANS_TABLES SQL mode value. According to http://dev.mysql.com/doc/refman/6.0/en/server-sql-mode.html: "STRICT_TRANS_TABLES If a value could not be inserted as given into a transactional table, abort the statement. For a non-transactional table, abort the statement if the value occurs in a single-row statement or the first row of a multiple-row statement. More detail is given later in this section. " So error instead of warning is expected: mysql> set sql_mode='STRICT_TRANS_TABLES'; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE test_table SET TS = FROM_UNIXTIME(UNIX_TIMESTAMP(TS)); ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 03:00:00' for column 'TS' at row 1 mysql> set sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE test_table SET TS = FROM_UNIXTIME(UNIX_TIMESTAMP(TS)); Query OK, 0 rows affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 1 mysql> show warnings; +---------+------+------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------+ | Warning | 1264 | Out of range value adjusted for column 'TS' at row 1 | +---------+------+------------------------------------------------------+ 1 row in set (0.00 sec)
Description: Hello, The best way to describe the bug is the following code: CREATE TABLE test_table (TS TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'); INSERT INTO test_table SET TS = 0; # TS IS NOW '0000-00-00 00:00:00' UPDATE test_table SET TS = FROM_UNIXTIME(UNIX_TIMESTAMP(TS)); # ERROR HERE I expected that after the last query, TS value would stay the same: '0000-00-00 00:00:00', but instead I get the following error: Incorrect datetime value: '1970-01-01 04:00:00' for column 'TS' at row 1 (my timezone is GMT+4) So, I think this is an unexpected behavior and has to be changed. How to repeat: CREATE TABLE test_table (TS TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'); INSERT INTO test_table SET TS = 0; # TS IS NOW '0000-00-00 00:00:00' UPDATE test_table SET TS = FROM_UNIXTIME(UNIX_TIMESTAMP(TS)); # ERROR HERE Suggested fix: I see no reasons for the error in this example, so I think that assigning '1970-01-01 00:00:00' UTC should result in setting timestamp value to "zero" timestamp value ('0000-00-00 00:00:00').