MySQL Bugs: #63800: getVersionColumns() does not return timestamp fields; always empty

Bug #63800	getVersionColumns() does not return timestamp fields; always empty
Submitted:	19 Dec 2011 17:13	Modified:	2 Aug 2012 21:09
Reporter:	Jesse Barnum	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.18	OS:	MacOS
Assigned to:	Alexander Soklakov	CPU Architecture:	Any
Tags:	current_timestamp, getVersionColumns, ON UPDATE, timestamp

Description:
I need to detect which fields in a MySQL database have modification timestamps, and I don't see a way to do this using Connector/J. getVersionColumns() always returns an empty result set. In my test database, I have a field called 'modstamp', where the default value is CURRENT_TIMESTAMP and it is set with 'on update CURRENT_TIMESTAMP'.

I can see the CURRENT_TIMESTAMP default value in Connector/J by calling metadata.getColumns() and looking at column #13, COLUMN_DEF, which contains 'CURRENT_TIMESTAMP'. However, all this means is that the column is initially populated with a timestamp when the row is created. I need to know which columns have the 'on update CURRENT_TIMESTAMP' set, so that I can be sure that this column is updated every time the row is changed.

This seems to clearly be the purpose of getVersionColumns(). From the javadoc:
"Retrieves a description of a table's columns that are automatically updated when any value in a row is updated"

Shouldn't modification timestamps be returned by this method, instead of an empty result?

How to repeat:
Create a table with a modification timestamp on it. Call getVersionColumns() on that table, and it will return an empty result set.

Suggested fix:
Fix getVersionColumns() to return timestamp columns that are updated every time a row is changed.

test case

Attachment: bug63800.java (text/x-java), 1.29 KiB.

Thank you for the report.

getVersionColumns is supposed to provide this information, but it does not work with current c/J. Verified as described.

Hi Jesse.

I am thinking of your report for quite some time now and I do not see it's feasible to fix right now. Not until there is such a feature in server that will allow me to relay on needed info. You see, such flag can be removed from the column on the fly, via simple ALTER command. Thus, I can't really relay on it.

Setting to "To be fixed later".

That doesn't seem like a good reason to not fix this. Every piece of metadata, such as the column names and types, can be changed using the ALTER command, but the driver still returns the column names and types.

I think it's only important that the information which the driver returns is accurate at the moment the call is made.

Hi Jesse.

You are right, we have just a dummy getVersionColumns() method. There is enough info from server to implement it in proper way. The patch is ready and we plan to include it in 5.1.22 release.

Wonderful, thank you Alexander!

Added to changelog for 5.1.22: 

Implemented the getVersionColumns() method, which formerly always
returned an empty result set. Now this method returns the timestamp
columns that are updated every time a row is changed.