Bug #47806 Incomplete syntax for TIMESTAMP column format
Submitted: 3 Oct 2009 14:44 Modified: 5 Oct 2009 15:34
Reporter: Martin von Gagern Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.0, 5.1, 5.4 OS:Any
Assigned to: Paul DuBois CPU Architecture:Any

[3 Oct 2009 14:44] Martin von Gagern
Description:
http://dev.mysql.com/doc/refman/5.4/en/create-table.html lists the syntax for the "CREATE TABLE" statement but doesn't include a production for the "ON UPDATE" part of a "TIMESTAMP" column, as described in http://dev.mysql.com/doc/refman/5.4/en/timestamp.html .

How to repeat:
CREATE TEMPORARY TABLE t (c TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP);
SHOW CREATE TABLE t;

Suggested fix:
Augment the production like this:

data_type:
  ...
  | TIMESTAMP [ ON UPDATE CURRENT_TIMESTAMP ]
  ...

The syntax above still doesn't account for the fact that the "DEFAULT" may come before the "UP UPDATE", as in my example. But I guess it's implicitely assumed that some reordering of column attributes is allowed, even if the grammar doesn't express this.
[3 Oct 2009 15:54] Valeriy Kravchuk
Thank you for the documentation request.
[5 Oct 2009 15:34] Paul DuBois
Yes, you're correct. The data type specifications on that page do not always contain the full syntax, as noted:

data_type represents the data type in a column definition. spatial_type represents a spatial data type. The data type syntax shown is representative only. For a full description of the syntax available for specifying column data types, as well as information about the properties of each type, see Chapter 10, Data Types, and Section 11.13, “Spatial Extensions”.