| Bug #37438 | multiple TIMESTAMP columns | ||
|---|---|---|---|
| Submitted: | 17 Jun 2008 1:44 | Modified: | 30 Jul 2008 23:27 |
| Reporter: | Sarah Sproehnle | Email Updates: | |
| Status: | Closed | ||
| Category: | Server: Docs | Severity: | S3 (Non-critical) |
| Version: | 5.1.25 | OS: | Microsoft Windows |
| Assigned to: | Paul DuBois | Target Version: | |
| Tags: | timestamp | ||
| Triage: | D4 (Minor) | ||
[17 Jun 2008 5:09]
Valeriy Kravchuk
Thank you for a problem report. Sorry, but I can not repeat the behaviour described:
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.1.25-rc-community-debug MySQL Community Server - Debug (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> drop table t;
Query OK, 0 rows affected (0.14 sec)
mysql> CREATE TABLE t (
-> ts1 TIMESTAMP DEFAULT 0,
-> ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
-> ON UPDATE CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.24 sec)
Is it a new installation or you had upgraded from 5.0.x?
[17 Jun 2008 14:24]
Susanne Ebrecht
Sarah, I can reproduce it but it's not a bug. I will set it to verified because you are right, this could be better documented. The problem here is that you try to give data type timestamp the default value 0. but 0 is an integer and not a timestamp. By using strict mode of course there is a check if the data type will match. In strict mode its only possible to give a default like: NULL, CURRENT_TIMESTAMP, YYYY-MM-DD HH:MM:SS When you have a column: i integer default 'A' Then the system also don't know that you expect that 'A' is 65. Strict mode is more strict.
[30 Jul 2008 23:13]
Paul DuBois
Point of clarification: I don't think it's TRADITIONAL mode per se that causes this restriction. It's the NO_ZERO_DATE mode, which happens to be one of the modes enabled by TRADITIONAL.
[30 Jul 2008 23:27]
Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products. The examples that use DEFAULT 0 will not work if the NO_ZERO_DATE SQL mode is enabled because that mode causes zero date values (specified as 0, '0000-00-00, or '0000-00-00 00:00:00') to be rejected. Be aware that the TRADITIONAL SQL mode includes NO_ZERO_DATE.
Description: This table definition gives an error (ERROR 1067: Invalid default value for ts1). It worked in previous versions and is taken straight from the refman timestamp documentation: CREATE TABLE t ( ts1 TIMESTAMP DEFAULT 0, ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); How to repeat: try to create this table: CREATE TABLE t ( ts1 TIMESTAMP DEFAULT 0, ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);