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:
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

[19 Dec 2011 17:13] Jesse Barnum
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.
[27 Jan 2012 21:27] Sveta Smirnova
test case

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

[27 Jan 2012 21:29] Sveta Smirnova
Thank you for the report.

getVersionColumns is supposed to provide this information, but it does not work with current c/J. Verified as described.
[16 Mar 2012 7:53] Tonci Grgin
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".
[16 Mar 2012 15:18] Jesse Barnum
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.
[28 Jun 2012 9:33] Alexander Soklakov
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.
[28 Jun 2012 12:11] Jesse Barnum
Wonderful, thank you Alexander!
[2 Aug 2012 21:09] John Russell
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.