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

[26 Jul 2007 16:44] Jim Winstead
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.
[26 Jul 2007 16:44] Jim Winstead
Ideally, this should be available from a MYSQL_FIELD fetched using mysql_fetch_field(), not just mysql_list_fields().
[26 Jul 2007 17:39] MySQL Verification Team
Thank you for the bug report.
[12 Sep 2007 12:23] Bugs System
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.
[2 Nov 2007 14:18] Bugs System
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.
[13 Nov 2007 9:31] Bugs System
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.
[15 Nov 2007 8:18] Bugs System
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)
[16 Nov 2007 9:33] Bugs System
Pushed into 5.1.23-rc
[16 Nov 2007 9:36] Bugs System
Pushed into 6.0.4-alpha
[15 Mar 2008 20:26] Jon Stephens
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.