java.sql.SQLException: Data truncation: Incorrect datetime value: '1969-12-31 19:00:00' for column 'DATE_VAL' at row 1

Spring JIRA | Alex Eagle | 9 years ago
  1. 0

    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.

    Spring JIRA | 9 years ago | Alex Eagle
    java.sql.SQLException: Data truncation: Incorrect datetime value: '1969-12-31 19:00:00' for column 'DATE_VAL' at row 1
  2. 0

    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.

    Spring JIRA | 9 years ago | Alex Eagle
    java.sql.SQLException: Data truncation: Incorrect datetime value: '1969-12-31 19:00:00' for column 'DATE_VAL' at row 1
  3. 0

    MySQL Bugs: #74271: Generate post-4.1 hash for mem users when old_passwords=1

    mysql.com | 1 year ago
    java.sql.SQLException: The password hash doesn't h ave the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    MySQL Bugs: #74271: Generate post-4.1 hash for mem users when old_passwords=1

    mysql.com | 1 year ago
    java.sql.SQLException: The password hash doesn't h ave the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.
  6. 0

    ORA-01461: can bind a LONG value only for inser... | Oracle Community

    oracle.com | 11 months ago
    java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column

    2 unregistered visitors
    Not finding the right solution?
    Take a tour to get the most out of Samebug.

    Tired of useless tips?

    Automated exception search integrated into your IDE

    Root Cause Analysis

    1. 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()
    2. Spring Framework
      JdbcTemplate.update
      1. org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:121)
      2. org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322)
      3. org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:582)
      4. org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:767)
      5. org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:825)
      6. org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:829)
      6 frames
    3. Spring Batch Core
      SimpleJobLauncher.run
      1. org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.insertParameter(JdbcJobInstanceDao.java:155)
      2. org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.insertJobParameters(JdbcJobInstanceDao.java:109)
      3. org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.createJobInstance(JdbcJobInstanceDao.java:71)
      4. org.springframework.batch.core.repository.support.SimpleJobRepository.createJobExecution(SimpleJobRepository.java:179)
      5. org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:83)
      5 frames