Bug #24528 information_schema.COLUMNS lacks info on ON UPDATE default of TIMESTAMPs

• Submitted: 23 Nov 2006 0:17
• Modified: 28 Nov 2009 15:16
• Reporter: Roland Bouman
• Status: Can't repeat
• Category: MySQL Server: Information schema
• Severity: S4 (Feature request)
• Version: 5.1.12
• OS: Any (NA)
• CPU Architecture: Any
• Tags: COLUMN_DEFAULT, columns, EXTRA, infrormation_schema, ON UPDATE, timestamp

[23 Nov 2006 0:17] Roland Bouman

Description:
The columns system view in the information schema has a COLUMN_DEFAULT column to provide information on the default value for columns. There is also an EXTRA column to provide extension attributes (such as AUTO_INCREMENT)

However, neither of these provide information on the declaration of TIMESTAMP columns as mentioned in http://dev.mysql.com/doc/refman/5.1/en/timestamp-4-1.html 

Cases like this:

With no DEFAULT clause and with an ON UPDATE CURRENT_TIMESTAMP clause, the column has a default of 0 and is automatically updated.

and this:

With a DEFAULT CURRENT_TIMESTAMP  clause and no ON UPDATE clause, the column has the current timestamp for its default value but is not automatically updated.

are indiscernable. 

Because the behaviour for the resulting columns is so different, it seems reasonable to expect the information_schema to contain information regarding this matter. 

How to repeat:
mysql> create table t(ts timestamp default '0000-00-00 00:00:00');
Query OK, 0 rows affected (0.00 sec)

mysql> select table_name, column_name, column_default, extra from information_schema.columns where table_schema = schema() and table_name = 't';
+------------+-------------+---------------------+-------+
| table_name | column_name | column_default      | extra |
+------------+-------------+---------------------+-------+
| t          | ts          | 0000-00-00 00:00:00 |       | 
+------------+-------------+---------------------+-------+
1 row in set (0.00 sec)

mysql> drop table t;                                                                                                                            
Query OK, 0 rows affected (0.00 sec)

mysql> create table t(ts timestamp on update current_timestamp);                                                                                
Query OK, 0 rows affected (0.00 sec)

mysql> select table_name, column_name, column_default, extra from information_schema.columns where table_schema = schema() and table_name = 't';
+------------+-------------+---------------------+-------+
| table_name | column_name | column_default      | extra |
+------------+-------------+---------------------+-------+
| t          | ts          | 0000-00-00 00:00:00 |       | 
+------------+-------------+---------------------+-------+
1 row in set (0.00 sec)

Suggested fix:
Use the extra column to capture the ON UPDATE CURRENT TIMESTAMP logic. AFAICS it is only used to flag auto_increment columns, so it should be available for this purpose

[28 Nov 2009 15:16] Valeriy Kravchuk

This is already implemented as far as I can see:

77-52-12-228:5.1 openxs$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.42-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table t(ts timestamp default '0000-00-00 00:00:00');
Query OK, 0 rows affected (0.06 sec)

mysql> select table_name, column_name, column_default, extra from
    -> information_schema.columns where table_schema = schema() and table_name = 't';
+------------+-------------+---------------------+-------+
| table_name | column_name | column_default      | extra |
+------------+-------------+---------------------+-------+
| t          | ts          | 0000-00-00 00:00:00 |       |
+------------+-------------+---------------------+-------+
1 row in set (0.00 sec)

mysql> drop table t;
Query OK, 0 rows affected (0.00 sec)

mysql>  create table t(ts timestamp on update current_timestamp);    
Query OK, 0 rows affected (0.05 sec)

mysql> select table_name, column_name, column_default, extra from information_schema.columns where table_schema = schema() and table_name = 't';
+------------+-------------+---------------------+-----------------------------+
| table_name | column_name | column_default      | extra                       |
+------------+-------------+---------------------+-----------------------------+
| t          | ts          | 0000-00-00 00:00:00 | on update CURRENT_TIMESTAMP |
+------------+-------------+---------------------+-----------------------------+
1 row in set (0.00 sec)