MySQL TIMESTAMP autoupdates itself during commandline update

[jasonuithol]

Hi all,

This post is quite lengthy and I apologise for it. I am concerned about using the TIMESTAMP datatype in MySQL and was wanting a few questions answered about it.

---------

The TIMESTAMP datatype of MySQL will update itself to the current date/time when a normal INSERT/UPDATE statement is made to it from any boring old connection e.g. the mysql monitor.

NOTE: This only happens under two conditions

(a) The INSERT/UPDATE statement is not explicity providing a value for that column.

(b) It only happens to the _first_ TIMESTAMP column in the table ! Any other TIMESTAMP columns in the table are left alone ?!?!?!? Weird.





Seeing this in action, I figured I'd better use java.util.Date, since in JBossCMP it maps the table type as the DATETIME datatype.

However, when Jboss generates the new tables, it STILL uses TIMESTAMP. I notice that the mapping states that the mapped JDBC type is called TIMESTAMP.

Has anyone else had this strange mapping behaviour happen to them ?


Asumming that I am stuck with TIMESTAMP ( it seems from the Forums that other people are happy with it ... )

- has anyone had any problems with the TIMESTAMP datatype updating itself ? I see from the debug logs when jboss is in action that the SQL statements cover every field, so in theory an explicit value is being provided for the TIMESTAMP field, so MySQL won't autoupdate it.

Are there any cases when Jboss won't provide such a comprehensive INSERT/UPDATE statement ?

---------

Any advice would be appreciated.


Jason.

[tabo]

Hi Jason,

I've got the 2nd problem!!!
But I couldn't change to the DATETIME datatype, it doesn't log the hhmmss value.

How did u solve it eventually???

Thank you.

[dsundstrom]

I think you're a little confused (or I'm confused). The jdbc-type has nothing to do with the actual column type. It is used to get the JDBC driver to coerce the actual type passed in into something the database actually wants. TIMESTAMP is the only jdbc type (java.sql.Types) that supports both date and time data in the same column. The DATE and TIME types only set the date and time parts of a java.util.Date respectively.

I don't use mySQL so this may be a mySQL weirdism. If you find a better mapping, please post a bug report at SourceForge.

[crippendsl]

Just use this mapping for mySQL:


<java-type>java.util.Date</java-type>
<jdbc-type>TIMESTAMP</jdbc-type>
<sql-type>DATETIME</sql-type>


and that will do the job you want!

[madalin]

I got the same problem with Timestamp and I switched to java.util.Date. But when it is saving the datetime to database it is 1 hour off the original date!!!!, like it is something wrong with the machine....Both Jboss and mySQL are on the same machine...Any ideeas please?

Best Regards,
Madalin