MySQL Bugs: #30081: Can't distinguish between auto-set TIMESTAMP and auto-updated TIMESTAMP

Bug #30081	Can't distinguish between auto-set TIMESTAMP and auto-updated TIMESTAMP
Submitted:	26 Jul 2007 16:44	Modified:	15 Mar 2008 20:26
Reporter:	Jim Winstead	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0	OS:	Any
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any
Tags:	DEFAULT, MYSQL_FIELD, ON UPDATE, SQLSpecialColumns, timestamp

Description:
It is not possible to tell, from the contents of the MYSQL_FIELD structure, whether a TIMESTAMP field will be set to CURRENT_TIMESTAMP on UPDATE. Connector/ODBC needs to be able to determine this in order to properly return results for the SQLSpecialColumns() ODBC function.

How to repeat:
CREATE TABLE t1 (a TIMESTAMP DEFAULT CURRENT_TIMESTAMP, b TIMESTAMP);

CREATE TABLE t2 (a TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, b TIMESTAMP);

In results from both tables, TIMESTAMP_FLAG will be set on column 'a'. TIMESTAMP_FLAG is not set on the 'b' columns.

Using 'SHOW FIELDS', you can't tell the difference between column 'a' and 'b' in t2.

Suggested fix:
Add a new flag to MYSQL_FIELD::flags to indicate that a column is a timestamp column that will be updated automatically.

Ideally, this should be available from a MYSQL_FIELD fetched using mysql_fetch_field(), not just mysql_list_fields().

Thank you for the bug report.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/34087

ChangeSet@1.2505, 2007-09-12 16:18:15+00:00, evgen@moonbone.local +15 -0
  Bug#30081: "ON UPDATE CURRENT_TIMESTAMP" wasn't shown by the SHOW FIELDS
  command and reported to a client.
  
  The fact that a timestamp field will be set to NO on UPDATE wasn't shown 
  by the SHOW COMMAND and reported to a client through connectors. This led to
  problems in the ODBC connector and might lead to a user confusion.
  
  A new filed flag called NOW_ON_UPDATE_FLAG is added. 
  Constructors of the Field_timestamp set it when a field should be set to NOW
  on UPDATE.
  
  The get_schema_column_record function now reports whether a timestamp field
  will be set to NOW on UPDATE.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/36992

ChangeSet@1.2617, 2007-11-02 18:12:39+00:00, evgen@moonbone.local +17 -0
  Bug#30081: "ON UPDATE CURRENT_TIMESTAMP" wasn't shown by the SHOW FIELDS
  command and reported to a client.
  
  The fact that a timestamp field will be set to NO on UPDATE wasn't shown 
  by the SHOW COMMAND and reported to a client through connectors. This led to
  problems in the ODBC connector and might lead to a user confusion.
  
  A new filed flag called ON_UPDATE_NOW_FLAG is added. 
  Constructors of the Field_timestamp set it when a field should be set to NOW
  on UPDATE.
  
  The get_schema_column_record function now reports whether a timestamp field
  will be set to NOW on UPDATE.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/37644

ChangeSet@1.2617, 2007-11-13 13:24:48+00:00, evgen@moonbone.local +17 -0
  Bug#30081: "ON UPDATE CURRENT_TIMESTAMP" wasn't shown by the SHOW FIELDS
  command and reported to a client.
  
  The fact that a timestamp field will be set to NO on UPDATE wasn't shown 
  by the SHOW COMMAND and reported to a client through connectors. This led to
  problems in the ODBC connector and might lead to a user confusion.
  
  A new filed flag called ON_UPDATE_NOW_FLAG is added. 
  Constructors of the Field_timestamp set it when a field should be set to NOW
  on UPDATE.
  
  The get_schema_column_record function now reports whether a timestamp field
  will be set to NOW on UPDATE.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/37831

ChangeSet@1.2668, 2007-11-15 12:13:19+04:00, gluh@mysql.com +3 -0
  updated result file(Bug#30081)

Pushed into 5.1.23-rc

Pushed into 6.0.4-alpha

Documented in the 5.1.23 and 6.0.4 changelogs as follows:

        It was not possible for client applications to distinguish between
        auto-set auto-updated TIMESTAMP column values.

        To rectify this problem, a new ON_UPDATE_NOW_FLAG flag is set by
        Field_timestamp constructors whenever a column should be set to NOW on
        UPDATE, and the get_schema_column_record() function now reports whether
        a timestamp column is set to NOW on UPDATE. In addition, such columns
        now display on update CURRENT_TIMESTAMP in the Extra column in the
        output from SHOW COLUMNS.