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 1:31	Modified:	30 Jan 2004 9:31
Reporter:	Predrag Malicevic
Status:	Closed
Category:	Server	Severity:	S3 (Non-critical)
Version:	4.0.17	OS:	Linux (Linux)
Assigned to:	Dmitri Lenev	Target Version:

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?