Announcement Announcement Module
Collapse
No announcement yet.
Configuring the Spring Batch infrastructure in database - MySQL Data Truncation Error Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Configuring the Spring Batch infrastructure in database - MySQL Data Truncation Error

    I have implemented Spring Batch job repository implementation to store the job metadata in a database.

    Configuration of a persistent job repository

    Code:
    <beans xmlns="http://www.springframework.org/schema/beans"
        xmlns:batch="http://www.springframework.org/schema/batch" 
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="
            http://www.springframework.org/schema/beans 
            http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
            http://www.springframework.org/schema/batch 
            http://www.springframework.org/schema/batch/spring-batch-2.2.xsd">
    
        <!-- stored job-meta in memory --> 
    
        <batch:job-repository id="jobRepository"
        data-source="dataSource"
        transaction-manager="transactionManager" />
    
        <bean id="transactionManager"
            class="org.springframework.jdbc.datasource.DataSourceTransactionManager" >
            <property name="dataSource" ref="dataSource"/>
            </bean>
    
        <bean id="jobLauncher"
            class="org.springframework.batch.core.launch.support.SimpleJobLauncher">
            <property name="jobRepository" ref="jobRepository" />
        </bean>
    
    </beans>

    database.xml is as folows :-

    Code:
    <beans xmlns="http://www.springframework.org/schema/beans"
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                xmlns:jdbc="http://www.springframework.org/schema/jdbc"
                xsi:schemaLocation="http://www.springframework.org/schema/beans
                                                        http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
                                                        http://www.springframework.org/schema/jdbc
                                                        http://www.springframework.org/schema/jdbc/spring-jdbc.xsd">
    
        <!-- connect to database -->
        <bean id="dataSource"
            class="org.springframework.jdbc.datasource.DriverManagerDataSource">
            <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
            <property name="url" value="jdbc:mysql://localhost:3306/sod_sustenance" />
            <property name="username" value="root"></property>
            <property name="password" value="root"></property>
        </bean>
    
        <bean id="oracleDataSource"
            class="org.springframework.jdbc.datasource.DriverManagerDataSource">
            <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"></property>
            <property name="url" value="jdbc:oracle:thin:@internal.com:1533:vis" />
            <property name="username" value="system"></property>
            <property name="password" value="manager"></property>
        </bean>
    
        <!-- <bean id="transactionManager"
            class="org.springframework.batch.support.transaction.ResourcelessTransactionManager" /> -->
    
        <!-- create job-meta tables automatically -->
        <jdbc:initialize-database data-source="dataSource">
            <jdbc:script location="org/springframework/batch/core/schema-drop-mysql.sql" />
            <jdbc:script location="org/springframework/batch/core/schema-mysql.sql" />
        </jdbc:initialize-database>
    
    
    </beans>
    after i run the Application , the tables are created in MySQL. However i get the following error :-

    Code:
    org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT into BATCH_JOB_EXECUTION(JOB_EXECUTION_ID, JOB_INSTANCE_ID, START_TIME, END_TIME, STATUS, EXIT_CODE, EXIT_MESSAGE, VERSION, CREATE_TIME, LAST_UPDATED) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; Data truncation: Incorrect datetime value: '' for column 'CREATE_TIME' at row 1; nested exception is com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '' for column 'CREATE_TIME' at row 1
        at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:603)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:812)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:868)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:872)
        at org.springframework.batch.core.repository.dao.JdbcJobExecutionDao.saveJobExecution(JdbcJobExecutionDao.java:155)
        at org.springframework.batch.core.repository.support.SimpleJobRepository.createJobExecution(SimpleJobRepository.java:145)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:601)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.batch.core.repository.support.AbstractJobRepositoryFactoryBean$1.invoke(AbstractJobRepositoryFactoryBean.java:172)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
        at com.sun.proxy.$Proxy0.createJobExecution(Unknown Source)
        at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:124)
        at com.sodconflict.App.run(App.java:43)
        at com.sodconflict.App.main(App.java:24)
    Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '' for column 'CREATE_TIME' at row 1
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2973)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
        at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1129)
        at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:681)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1368)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1283)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1268)
        at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:818)
        at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:812)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587)
        ... 21 more
    Due to this error, the values could not be inserted and the jobs metadata are not inserted.

    Just an Update - The following table structure is created in MySQL :-

    Code:
    CREATE TABLE `batch_job_execution` (
      `JOB_EXECUTION_ID` bigint(20) NOT NULL,
      `VERSION` bigint(20) DEFAULT NULL,
      `JOB_INSTANCE_ID` bigint(20) NOT NULL,
      `CREATE_TIME` date DEFAULT NULL,
      `START_TIME` datetime DEFAULT NULL,
      `END_TIME` datetime DEFAULT NULL,
      `STATUS` varchar(10) DEFAULT NULL,
      `EXIT_CODE` varchar(100) DEFAULT NULL,
      `EXIT_MESSAGE` varchar(2500) DEFAULT NULL,
      `LAST_UPDATED` datetime DEFAULT NULL,
      PRIMARY KEY (`JOB_EXECUTION_ID`),
      KEY `JOB_INST_EXEC_FK` (`JOB_INSTANCE_ID`),
      CONSTRAINT `JOB_INST_EXEC_FK` FOREIGN KEY (`JOB_INSTANCE_ID`) REFERENCES `batch_job_instance` (`JOB_INSTANCE_ID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    Kindly help
Working...
X