MySQL Bugs: #81241: Column default value ON UPDATE not available in DatabaseMetaData#getColumns()

Bug #81241	Column default value ON UPDATE not available in DatabaseMetaData#getColumns()
Submitted:	29 Apr 2016 14:12	Modified:	2 May 2016 17:01
Reporter:	Stefan Thurnherr	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.38	OS:	Any
Assigned to:	Filipe Silva	CPU Architecture:	Any

Description:
When fetching the column metadata from a MySQL database (5.6 or 5.7) using Connector/J, any "ON UPDATE CREATED_TIMESTAMP" clause for column default value is not available in the resultSet returned by DatabaseMetaData#getColumns().

The "ON UPDATE ..." clause for column default value has been available since at least MySQL 5.5 - see here: http://dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html.

How to repeat:
Create the following table in db schema MY_SCHEMA a MySQL 5.6 database:

CREATE TABLE `my_table` (
  `updated_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_swedish_ci;

Then use the Connector/J to fetch the metadata for this table. You will get a resultSet containing the following data (first line is the column name in the resultSet, second line is the actual value indented by two spaces in the output here):
TABLE_CAT
  MY_SCHEMA
TABLE_SCHEM
  null
TABLE_NAME
  my_table
COLUMN_NAME
  updated_timestamp
DATA_TYPE
  93
TYPE_NAME
  TIMESTAMP
COLUMN_SIZE
  19
BUFFER_LENGTH
  65535
DECIMAL_DIGITS
  null
NUM_PREC_RADIX
  10
NULLABLE
  0
REMARKS
  
COLUMN_DEF
  CURRENT_TIMESTAMP
SQL_DATA_TYPE
  0
SQL_DATETIME_SUB
  0
CHAR_OCTET_LENGTH
  null
ORDINAL_POSITION
  4
IS_NULLABLE
  NO
SCOPE_CATALOG
  null
SCOPE_SCHEMA
  null
SCOPE_TABLE
  null
SOURCE_DATA_TYPE
  null
IS_AUTOINCREMENT
  NO
IS_GENERATEDCOLUMN
  NO

Hi Stefan,

Thank you for this bug report.

Actually the clause "ON UPDATE CREATED_TIMESTAMP" doesn't belong to the "DEFAULT" setting.

I'm setting this report as "Verified" because we may consider extending the ResultSet returned from DatabaseMetaData.getColumns() with an additional field that will contain this information.

While this doesn't happen, you can query directly the system table INFORMATION_SCHEMA.COLUMNS, where you'll find the column EXTRA containing this information.

I hope that helps.