Spring Batch
  1. Spring Batch
  2. BATCH-528

Inserting non-date JobParameter fails on MySQL

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Trivial Trivial
    • Resolution: Fixed
    • Affects Version/s: 1.0.0.rc1
    • Fix Version/s: 1.0.1
    • Component/s: Core
    • Labels:
      None
    • Environment:
      MySQL 5.0.51a
      java version "1.5.0_13"
      MacOS 10.5.2 Intel

      Description

      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:

      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;
      

      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:

      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)
      

      Apparently MySQL has an issue with a timestamp in 1969:

      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)
      

      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.

        Activity

        Hide
        Dave Syer added a comment -

        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"?

        Show
        Dave Syer added a comment - 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"?
        Hide
        Alex Eagle added a comment -

        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)

        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.

        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

        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.

        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?

        Show
        Alex Eagle added a comment - 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 ) 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. 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 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. 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?
        Hide
        Dave Syer added a comment -

        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?

        Show
        Dave Syer added a comment - 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?
        Hide
        Alex Eagle added a comment -

        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.

        Show
        Alex Eagle added a comment - 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.
        Hide
        Alex Eagle added a comment -

        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:

        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).

        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.

        Show
        Alex Eagle added a comment - 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: 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). 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.
        Hide
        Dave Syer added a comment -

        OK, I'll make it DATETIME instead of TIMESTAMP.

        Show
        Dave Syer added a comment - OK, I'll make it DATETIME instead of TIMESTAMP.
        Hide
        Alex Eagle added a comment -

        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.

        Show
        Alex Eagle added a comment - 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.
        Hide
        Robert Kasanicky added a comment -

        TIMESTAMP externalized as property with DATETIME value for MySQL

        Show
        Robert Kasanicky added a comment - TIMESTAMP externalized as property with DATETIME value for MySQL
        Hide
        Dave Syer added a comment -

        Assume closed as resolved and released

        Show
        Dave Syer added a comment - Assume closed as resolved and released

          People

          • Assignee:
            Robert Kasanicky
            Reporter:
            Alex Eagle
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Time Tracking

              Estimated:
              Original Estimate - 20m
              20m
              Remaining:
              Remaining Estimate - 20m
              20m
              Logged:
              Time Spent - Not Specified
              Not Specified