Issue Details (XML | Word | Printable)

Key: BATCH-528
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Trivial Trivial
Assignee: Robert Kasanicky
Reporter: Alex Eagle
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Spring Batch

Inserting non-date JobParameter fails on MySQL

Created: 28/Mar/08 04:53 PM   Updated: 07/Aug/08 10:07 AM
Component/s: Core
Affects Version/s: 1.0.0.rc1
Fix Version/s: 1.0.1

Time Tracking:
Original Estimate: 0.04d
Original Estimate - 0.04d
Remaining Estimate: 0.04d
Remaining Estimate - 0.04d
Time Spent: Not Specified
Remaining Estimate - 0.04d

Environment:
MySQL 5.0.51a
java version "1.5.0_13"
MacOS 10.5.2 Intel


 Description  « Hide
I ran the schema creation script schema-mysql.sql found in the spring-batch-core-1.0.0.rc1.jar.
It creates a table for the job parameters:
{code:sql}
CREATE TABLE BATCH_JOB_PARAMS (
JOB_INSTANCE_ID BIGINT NOT NULL ,
    TYPE_CD VARCHAR(6) NOT NULL ,
KEY_NAME VARCHAR(100) NOT NULL ,
STRING_VAL VARCHAR(250) ,
DATE_VAL TIMESTAMP NULL,
LONG_VAL BIGINT ,
DOUBLE_VAL DOUBLE PRECISION,
constraint JOB_INSTANCE_PARAMS_FK foreign key (JOB_INSTANCE_ID)
references BATCH_JOB_INSTANCE(JOB_INSTANCE_ID)
) type=InnoDB;
{code}

However, when starting a job with a single long parameter, the query fails. A java.sql.Timestamp with value 0L is created in JdbcJobInstanceDao:146 and inserted into the DATE_VAL column, which fails:
{noformat}
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [INSERT into BATCH_JOB_PARAMS(JOB_INSTANCE_ID, KEY_NAME, TYPE_CD, STRING_VAL, DATE_VAL, LONG_VAL, DOUBLE_VAL) values (?, ?, ?, ?, ?, ?, ?)]; SQL state [01004]; error code [0]; Data truncation: Incorrect datetime value: '1969-12-31 19:00:00' for column 'DATE_VAL' at row 1

Query being executed when exception was thrown:

com.mysql.jdbc.ServerPreparedStatement[6] - INSERT into BATCH_JOB_PARAMS(JOB_INSTANCE_ID, KEY_NAME, TYPE_CD, STRING_VAL, DATE_VAL, LONG_VAL, DOUBLE_VAL) values (1, 'batchSize', 'LONG', '', '1969-12-31 19:00:00', 1, 0.0); nested exception is java.sql.SQLException: Data truncation: Incorrect datetime value: '1969-12-31 19:00:00' for column 'DATE_VAL' at row 1
        at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:121)
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:582)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:767)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:825)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:829)
        at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.insertParameter(JdbcJobInstanceDao.java:155)
        at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.insertJobParameters(JdbcJobInstanceDao.java:109)
        at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.createJobInstance(JdbcJobInstanceDao.java:71)
        at org.springframework.batch.core.repository.support.SimpleJobRepository.createJobExecution(SimpleJobRepository.java:179)
        at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:83)
{noformat}

Apparently MySQL has an issue with a timestamp in 1969:
{noformat}
mysql> create table timestamper ( t timestamp null );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into timestamper(t) values ('1969-12-31 19:00:00');
ERROR 1292 (22007): Incorrect datetime value: '1969-12-31 19:00:00' for column 't' at row 1
mysql> insert into timestamper(t) values ('1980-12-31 19:00:00');
Query OK, 1 row affected (0.00 sec)
{noformat}

As a workaround, I changed the column type of DATE_VAL to datetime rather than timestamp. I think datetime may be the more appropriate type for this column in mysql.

 All   Comments   Work Log   Change History   FishEye   Builds      Sort Order: Ascending order - Click to sort in descending order
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"?

Alex Eagle added a comment - 29/Mar/08 09:51 AM
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?

Dave Syer added a comment - 29/Mar/08 09:57 AM
Ironically I think the reason that we insert '0' is that someone told us the MySQL would not allow null values in a TIMESTAMP. Does it help if to define the column as DATETIME instead of TIMESTAMP?

Alex Eagle added a comment - 29/Mar/08 10:00 AM
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.

Alex Eagle added a comment - 29/Mar/08 10:12 AM
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.

Dave Syer added a comment - 29/Mar/08 10:35 AM
OK, I'll make it DATETIME instead of TIMESTAMP.

Alex Eagle added a comment - 29/Mar/08 11:45 AM
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.

Robert Kasanicky added a comment - 31/Mar/08 08:45 AM
TIMESTAMP externalized as property with DATETIME value for MySQL

Dave Syer added a comment - 07/Aug/08 10:07 AM
Assume closed as resolved and released