MySQL Bugs: #2539: Setting default value for timestamp column type doesn't work

Bug #2539	Setting default value for timestamp column type doesn't work
Submitted:	28 Jan 2004 0:31	Modified:	30 Jan 2004 8:31
Reporter:	Predrag Malicevic	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.17	OS:	Linux (Linux)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
There seems to be a problem with setting default values for the timestamp
column type in version 4.0.17. You can't create a table with a timestamp column and specify a default value no matter what format you use (YYYY-MM-DD HH:MM:SS, YYYYMMDDHHMMSS and smaller). Version 4.0.16 works normally.

How to repeat:
mysql> create table test (t timestamp default '2001-01-01 00:00:00' not null);
ERROR 1067: Invalid default value for 't'

Hi!

It is not sensible to set default value for first TIMESTAMP column in the table
since this field of this type in any case will actually have NOW() as default
value and will be auto-updated if row containing this field is modified and 
this field is not explicitly set.

(Please look into documentation for more info on this issue: http://www.mysql.com/doc/en/DATETIME.html)

But it is perfectly sensible to have some default value for columns other than 
first, like in the following statement:
 create table testts 
   (ts1 timestamp, ts2 timestamp default '2003-01-01 00:00:00');

So I'm verifying your bug.

Actually, I stumbled on this problem while adding a table with multiple timestamp columns, so the problem occurs in that case too. So, generally, the timestamp column doesn't allow a default value to be set in any case.

Also, versions <= 4.0.16 don't complain if you try to set a default value to the first timestamp column. I've seen some apps with table definitions that set default values to first timestamp column. If people try to use these apps with 4.0.17, they won't be able to create tables without removing the DEFAULT clauses.

Hi, Predrag! 

Your argument is strong. It was bad idea to introduce such change in stable tree. But because of exactly same reason (4.0 is stable) the perfect fix of this problem (to allow working default values for first columns and add special standard compatible syntax for auto-updated timestamps or at least give a warning about ignoring default value for first column) can be introduced only in one of development trees.

So now we will allow default values for timestamp columns, but will silently ignore such value for first column.

ChangeSet 1.1683.6.1 2004/01/30 15:13:19 dlenev@mysql.com
  Fix for bugs #1885, #2464, #2539. Proper handling of default
  values for TIMESTAMP columns.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

We experience the same problem, having multiple TIMESTAMP(14) fields (actually two).
I've tried to change the second field to NOT NULL but it always goes back to NULL and the default value is set to '00000000000000'.
The bad thing that we're hosting an application and it worked before; now our provider has apparently upgraded to 4.0.18-standard so we've got ourself a big problem 'cause every table has two TIMESTAMPS one is for initial date, another is for all updates of the record.

I have two timestamp column,how to give default?