|
[
Permlink
| « Hide
]
Dave Syer added a comment - 29/Mar/08 07:09 AM
The DDL for MySQL changed since rc1. Can you try with the latest version or edit your file to say "DATE_VAL TIMESTAMP DEFAULT NULL"?
I re-ran the DDL from the schema-mysql contained in spring-batch-core-1.0.0.-20080325.010315-7.jar, and the problem is the same.
From the MySQL manual on date/time datatypes: (http://dev.mysql.com/doc/refman/5.0/en/datetime.html) {quote} Remember that although DATETIME, DATE, and TIMESTAMP values all can be specified using the same set of formats, the types do not all have the same range of values. For example, TIMESTAMP values cannot be earlier than 1970 or later than 2038. This means that a date such as '1968-01-01', while legal as a DATETIME or DATE value, is not valid as a TIMESTAMP value and is converted to 0. {quote} Although this says the value should be converted to zero, MySQL by default runs in a 'traditional' mode that is fairly strict. I had to change my MySQL options substantially, including setting the option 'ALLOW_INVALID_DATES', and removing both STRICT modes - this is also indicated in the manual: http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html {quote} Strict mode disallows invalid date values such as '2004-04-31'. It does not disallow dates with zero month or day parts such as '2004-04-00' or "zero" dates. To disallow these as well, enable the NO_ZERO_IN_DATE and NO_ZERO_DATE SQL modes in addition to strict mode. {quote} I don't think it's reasonable to expect users to change these database settings for the purpose of allowing what might be called "bad data" into this column. What is the reasoning behind inserting a value into the three unused columns of BATCH_JOB_PARAMS, rather than leaving it unspecified and picking up the default? Is it just to reuse the single CREATE_JOB_PARAMETERS query? To clarify: the 20080325.010315-7 build I used did not include the change you asked about, "DATE_VAL TIMESTAMP DEFAULT NULL"
However, a) With the old schema, MySQL changed it to "`DATE_VAL` timestamp NULL default NULL" as I can see from "show create table BATCH_JOB_PARAMS" b) After making the change and reloading the schema, "show create table..." looks the same and the bug is still there. Yes, my understanding is that the datetime type provides a good mapping for a java.sql.Timestamp object, despite the difference in naming.
The MySQL timestamp type holds similar data, but has a bunch of strange restrictions around how default values are declared, and how multiple timestamp columns behave in the same table. Here's a good quote: http://coding.derkeiler.com/Archive/Java/comp.lang.java.programmer/2008-03/msg01442.html: {quote} For any other RDBMS I'd agree with Lew, but MySQL has some brain damage in this department. In MySQLese, DATETIME is the type allowing '2008-03-17 00:03:14' for a large range of dates and times, with one- second resolution (corresponding to the SQL TIMESTAMP type), and TIMESTAMP is the MySQL mapping of its implementation's time_t type (on most unices, a 32-bit int representing seconds since 1970). MySQL TIMESTAMPs also have some constraints about how many of them you can have and in what order, within a table, which is completely inane and makes the type hard to use for general-purpose storage of timestamps -- MySQL very strongly assumes TIMESTAMP means "timetamp of the creation of this row". The MySQL java connector should have some documentation indicating how DATETIME columns map to JDBC types -- I wouldn't be surprised if the java.sql.Timestamp type was an appropriate mapping (along with getTimestamp/setTimestamp). {quote} Looking at the mysql jdbc driver docs (http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-type-conversions.html) and both DATETIME and TIMESTAMP are indicated to map to a java.sql.Timestamp. I think it would be best to use DATETIME throughout the schema-mysql.sql I did a search/replace in mine just now, and things are running ok for the small amount of Batch I'm using so far. That's cool, does it also mean that setting 0's into the unused fields in JdbcJobInstanceDao may be changed? It's trivial, but a shame if the only reason for it was a workaround for the TIMESTAMP columns.
TIMESTAMP externalized as property with DATETIME value for MySQL
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||