Bug #12707 The ON UPDATE and DEFAULT constructs need an Enahncement
Submitted: 21 Aug 2005 23:33 Modified: 4 Feb 2009 10:05
Reporter: Dale Harris Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: DDL Severity:S4 (Feature request)
Version:4.0, 5.1 OS:Windows (Windows and Linux)
Assigned to: CPU Architecture:Any

[21 Aug 2005 23:33] Dale Harris
I Suggest the following Enyhancements to MySQL.

The	Create Table ... Column ...
constructs need to be enhanced to allow other time functions,
than just the 

Consider the following two programming problems
that would be solved better with this enhancement to MySQL.

Problem 1)

I want to have Databases in different countries,
and thus in different time zones.
I want to be able to select records from a table in DB2,
based on a time greater than records from a table in DB1.

Since the time zone may differ, what I really want to do is:

  	CREATE TABLE timeclock (
     		id Integer UNSIGNED NOT NULL DEFAULT 0,
      		ts TimeStamp 
      		PRIMARY KEY (id)
     	) TYPE=MyISAM;

Unfortunately, I have to rather use CURRENT_TIMESTAMP
and keep another DB table of time zone differences,
in order to make sure the code works right.

Sure, I could have the calling code allways set the timestamp.
However, it is a far better programming practice
to have the DB always set the correct default value,
rather than depending on all the calling code in all
of the diverse places to never forget to enter the correct "default value".

For Data Corruption prevention, it would be far better
to have the DB code enforce the rules that:
1) on insert, use a default of Now in Universal time.
2) ON update, use a default of Now in Universal time.

is present, and that is good enough within one time zone.
But, allowing UNIX_TIMESTAMP would help those with diverse time zones.

Problem 2)

I want to create a Table with a timestamp row that indicates 
the Time the row was last checked for a given condition.
Thus, the value must be TIME ZERO on insert or update.
ie, if the row changes, then it must be checked again.

What I want to be able to say is:
	CREATE TABLE timeclock (
        	id Integer UNSIGNED NOT NULL DEFAULT 0,
        	ts TimeStamp ON UPDATE 0 DEFAULT 0,
        	PRIMARY KEY (id)
      	) TYPE=MyISAM;
The problem is that it seems that for the ON UPDATE construct,
MySQL only allows one argument, namely: CURRENT_TIME.
So, again the code must set the "default value" to zero time
on insert and update, which is not a safe programming practice.
Again, for data corruption prevention,
it is far better for DB code to handle defaults,
rather than depending on all the programmers
writing all the lines of code in diverse modules, 
to never forget to set the "default" value.

The ON UPDATE construct to MySQL was a great idea.
Extending it would make it even more helpful.

How to repeat:
Try entering in the SQL statements given.
The language currently does not allow these extensions
to the ON UPDATE and DEFAULT constructs
for a TimeStamp field.

Suggested fix:

for a TimeStamp field
to not only allow CURRENT_TIME
but also allow both:
    0  (ie, '0001-01-01')
[6 Nov 2007 15:30] Reggie Burnett
This is not a Connector/Net bug.  Moving to the Server/DDL category
[4 Feb 2009 10:05] Susanne Ebrecht
Many thanks for writing a feature request.

Feature request here is using functions as default values.

Our developers will discuss this.
[19 Jan 2010 9:41] Valeriy Kravchuk
Bug #50426 was marked as a duplicate of this one.