Announcement Announcement Module
Collapse
No announcement yet.
Running new job: "Duplicate entry" Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Running new job: "Duplicate entry"

    Hi,

    Just got started with Spring Batch 1.0.0.FINAL (using maven2).
    I store the job info in a MySQL db. I found the SQL files to create the tables from the source files (in SVN) and I used them to create all the tables and sequences.

    Running the first job is all good. Everything is fine.
    But when running the same exact job a second time I got:
    2008-04-09 21:18:53,960 [main] ERROR org.springframework.batch.core.launch.suppo
    rt.CommandLineJobRunner - Job Terminated in error:
    org.springframework.dao.DataIntegrityViolationExce ption: PreparedStatementCallba
    ck; SQL [INSERT into BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERS
    ION) values (?, ?, ?, ?)]; Duplicate entry '0' for key 1; nested exception is ja
    va.sql.SQLException: Duplicate entry '0' for key 1
    Caused by:
    java.sql.SQLException: Duplicate entry '0' for key 1
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.ja va:2926)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:15 71)
    ...
    I realized that for the first job, a job instance #0 has been inserted in the BATCH_JOB_INSTANCE table...and it looks like the second time it's trying to do that same exact thing (inserting with ID = 0).

    Am I missing something?
    What am I doing wrong?

    Please help.
    Thanks,
    Luc.

  • #2
    Can you post your configuration for the JobRepository? The only thing I can think of is the sequence giving you the same id twice, which doesn't seem possible.

    Comment


    • #3
      Here it is:
      <bean id="jobRepository" class="org.springframework.batch.core.repository.s upport.JobRepositoryFactoryBean">
      <property name="databaseType" value="mysql" />
      <property name="dataSource" ref="myDataSource" />
      </bean>
      In the sql files I found in SVN, it doesn't look like there was any sequence for Job Instance (that's mostly the reason why I'm so lost...don't understand how it works...)

      Thanks,
      Luc.

      Comment


      • #4
        The following is in the schema:

        Code:
        CREATE TABLE BATCH_STEP_EXECUTION_SEQ (ID BIGINT NOT NULL) type=MYISAM;
        INSERT INTO BATCH_STEP_EXECUTION_SEQ values(0);
        CREATE TABLE BATCH_JOB_EXECUTION_SEQ (ID BIGINT NOT NULL) type=MYISAM;
        INSERT INTO BATCH_JOB_EXECUTION_SEQ values(0);
        CREATE TABLE BATCH_JOB_SEQ (ID BIGINT NOT NULL) type=MYISAM;
        INSERT INTO BATCH_JOB_SEQ values(0);
        The factory bean just create a mysqlincrementer tied to those names. I'm assuming it uses a table because mysql doesn't support sequences.

        Comment


        • #5
          I do have all of those sequence tables but they are all empty (no rows).
          BATCH_JOB_INSTANCE, BATCH_JOB_EXECUTION and BATCH_STEP_EXECUTION have all 1 row. (I ran 1 job which succeeded and the second one fails)

          So one of this sequence table you mentioned must return an ID to insert in the BATCH_JOB_INSTANCE I assume right?
          Since all my sequence tables are empty, do you know what I might be missing?

          Thanks for your help lucasward,
          Luc.

          Comment


          • #6
            One possibility might be that you haven't set up transactions correctly, as discussed in chapter 4 of the reference documentation:

            http://static.springframework.org/sp...n.html#d0e3156

            (Section 4.3.2.2.1)

            Essentially, this needs to be in your application context:

            Code:
            <aop:config>
                <aop:advisor 
                    pointcut="execution(* org.springframework.batch.core..*Repository+.*(..))"
                <advice-ref="txAdvice" />
            </aop:config>
            
            <tx:advice id="txAdvice" transaction-manager="transactionManager">
                <tx:attributes>
                    <tx:method name="create*" propagation="REQUIRES_NEW" isolation="SERIALIZABLE" />
                    <tx:method name="*" />
                </tx:attributes>
            </tx:advice>
            I know that it seems a bit weird that this is required. There's actually an issue open against 1.1 to allow the JobRepositoryFactoryBean to set this up for you.

            Comment


            • #7
              Thanks lucasward. Took me a while to add all the aop thing but I just did and I still get the same result: "Duplicate entry".

              Here is my spring context:
              <?xml version="1.0" encoding="UTF-8"?>
              <beans xmlns="http://www.springframework.org/schema/beans"
              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
              xmlns:aop="http://www.springframework.org/schema/aop"
              xmlns:tx="http://www.springframework.org/schema/tx"
              xsi:schemaLocation="
              http://www.springframework.org/schema/beans http://www.springframework.org/schem...-beans-2.0.xsd
              http://www.springframework.org/schema/tx http://www.springframework.org/schem...ing-tx-2.0.xsd
              http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.0.xsd">
              <bean id="net.xeonsky.serenity.core.DataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
              <property name="driverClassName" value="com.mysql.jdbc.Driver" />
              <property name="url"
              value="jdbc:mysql://localhost:3306/serenity?zeroDateTimeBehavior=convertToNull" />
              <property name="username" value="serenity" />
              <property name="password" value="*****" />
              </bean>

              <bean id="net.xeonsky.serenity.jobs.JobRepository" class="org.springframework.batch.core.repository.s upport.JobRepositoryFactoryBean">
              <property name="databaseType" value="mysql" />
              <property name="dataSource" ref="net.xeonsky.serenity.core.DataSource" />
              </bean>

              <import resource="classpath:/net/xeonsky/serenity/jobs.xml" />

              <aop:config>
              <aop:advisor pointcut="execution(* org.springframework.batch.core..*Repository+.*(..) )"
              advice-ref="txAdvice" />
              </aop:config>

              <tx:advice id="txAdvice" transaction-manager="net.xeonsky.serenity.jobs.TxManager">
              <tx:attributes>
              <tx:method name="create*" propagation="REQUIRES_NEW" isolation="SERIALIZABLE" />
              <tx:method name="*" />
              </tx:attributes>
              </tx:advice>
              </beans>
              The jobs are defined here:
              <?xml version="1.0" encoding="UTF-8"?>
              <beans xmlns="http://www.springframework.org/schema/beans"
              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
              xmlns="http://www.springframework.org/schema/p"
              xmlns:aop="http://www.springframework.org/schema/aop"
              xsi:schemaLocation="http://www.springframework.org/schema/beans
              http://www.springframework.org/schem...-beans-2.0.xsd
              http://www.springframework.org/schema/aop
              http://www.springframework.org/schema/aop/spring-aop-2.0.xsd">

              <import resource="classpath:/net/xeonsky/serenity/dac.xml" />

              <bean id="net.xeonsky.serenity.jobs.AbstractJob" class="org.springframework.batch.core.job.SimpleJo b" abstract="true">
              <property name="jobRepository" ref="net.xeonsky.serenity.jobs.JobRepository" />
              <property name="restartable" value="true" />
              </bean>

              <bean id="net.xeonsky.serenity.jobs.TxManager" class="org.springframework.jdbc.datasource.DataSou rceTransactionManager">
              <property name="dataSource" ref="net.xeonsky.serenity.core.DataSource"/>
              </bean>

              <bean id="net.xeonsky.serenity.jobs.AbstractStep" class="org.springframework.batch.core.step.item.Si mpleStepFactoryBean" abstract="true">
              <property name="jobRepository" ref="net.xeonsky.serenity.jobs.JobRepository" />
              <property name="transactionManager" ref="net.xeonsky.serenity.jobs.TxManager" />
              </bean>

              <bean id="net.xeonsky.serenity.jobs.AbstractTaskletStep " class="org.springframework.batch.core.step.tasklet .TaskletStep" abstract="true">
              <property name="jobRepository" ref="net.xeonsky.serenity.jobs.JobRepository" />
              <property name="allowStartIfComplete" value="true" />
              </bean>

              <bean id="jobLauncher" class="org.springframework.batch.core.launch.suppo rt.SimpleJobLauncher">
              <property name="jobRepository" ref="net.xeonsky.serenity.jobs.JobRepository" />
              </bean>

              <bean id="net.xeonsky.serenity.jobs.SearchAndTrackJob" parent="net.xeonsky.serenity.jobs.AbstractJob">
              <property name="steps">
              <list>
              <bean parent="net.xeonsky.serenity.jobs.AbstractStep">
              <property name="itemReader" ref="net.xeonsky.serenity.jobs.SearchQueriesReader "/>
              <property name="itemWriter">
              <bean class="org.springframework.batch.item.transform.It emTransformerItemWriter">
              <property name="delegate" ref="net.xeonsky.serenity.jobs.SearchPositionTrack ingWriter"/>
              <property name="itemTransformer" ref="net.xeonsky.serenity.jobs.MyTransformer"/>
              </bean>
              </property>
              </bean>
              </list>
              </property>
              </bean>

              <bean id="net.xeonsky.serenity.jobs.RefreshReportsJob" parent="net.xeonsky.serenity.jobs.AbstractJob">
              <property name="steps">
              <list>
              <bean parent="net.xeonsky.serenity.jobs.AbstractTaskletS tep">
              <property name="tasklet" ref="net.xeonsky.serenity.jobs.RefreshReportsTaskl et"/>
              </bean>
              </list>
              </property>
              </bean>

              <bean id="net.xeonsky.serenity.jobs.RefreshReportsTaskle t" class="net.xeonsky.serenity.jobs.RefreshReportsTas klet">
              <property name="dataSource" ref="net.xeonsky.serenity.core.DataSource"/>
              </bean>

              <bean id="net.xeonsky.serenity.jobs.SearchQueriesReader "
              class="org.springframework.batch.item.database.Jdb cCursorItemReader">
              <property name="dataSource" ref="net.xeonsky.serenity.core.DataSource" />
              <property name="sql"
              value="SELECT id, value FROM searchqueries" />
              <property name="mapper">
              <bean
              class="net.xeonsky.serenity.core.dac.SearchQueryRo wMapper" />
              </property>
              </bean>

              <bean id="net.xeonsky.serenity.jobs.SearchPositionTracki ngWriter"
              class="net.xeonsky.serenity.jobs.SearchPositionTra ckingWriter">
              <property name="searchPositionTrackingDAC" ref="net.xeonsky.serenity.core.dac.SearchPositionT rackingDAC" />
              </bean>

              </beans>
              The dac.xml just defines the Data Access classes and MyTransformer (not important I think).
              Now I call the job this way:
              java -cp ".;target/serenity-jobs-0.0.1-jar-with-dependencies.jar" org.springframework.batch.core.launch.support.Comm andLineJobRunner ./etc/jobs_default.xml net.xeonsky.serenity.jobs.SearchAndTrackJob date=`date +%m-%d-%Y`
              Am I doing something wrong?

              Comment


              • #8
                Everything looks correct as far as I can tell.

                Although, when digging a bit deeper something else doesn't make sense to me. In your original post you mention that it puts 0 in as the key to the job instance table the first time, and then when you try and run the job again as a new instance, it tries to insert it with 0 again. I did some research into the MySQLMaxValueIncrementer that's used, and from looking at the code I can't see anyway that nextKey() could return anything other than 1 the first time around. I might be completely off, but that's something that seemed odd to me. Is there anyway you can debug through the code to see where the id is coming from, just to be sure?

                Comment


                • #9
                  Just a thought: what would happen if the incrementer for the job (jobIncrementer) was missing? Would it be a different error or would it use 0 by default?

                  I'll switch to debug and look at the logs see if I can find something in the meantime.

                  Comment


                  • #10
                    Couldn't find mention of any of the incrementers in the logs :-/
                    Here is what I found:
                    2008-04-11 11:32:22,242 [main] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL statement [SELECT JOB_INSTANCE_ID from BATCH_JOB_INSTANCE where JOB_NAME = ? and JOB_KEY = ?]
                    2008-04-11 11:32:22,242 [main] DEBUG org.springframework.transaction.support.Transactio nSynchronizationManager - Retrieved value [org.springframework.jdbc.datasource.ConnectionHold er@e0e515] for key [org.apache.commons.dbcp.BasicDataSource@1584807] bound to thread [main]
                    2008-04-11 11:32:22,242 [main] DEBUG org.springframework.transaction.support.Transactio nSynchronizationManager - Retrieved value [org.springframework.jdbc.datasource.ConnectionHold er@e0e515] for key [org.apache.commons.dbcp.BasicDataSource@1584807] bound to thread [main]
                    2008-04-11 11:32:22,242 [main] DEBUG org.springframework.jdbc.core.StatementCreatorUtil s - Setting SQL statement parameter value: column index 1, parameter value [net.xeonsky.serenity.jobs.SearchAndTrackJob], value class [java.lang.String], SQL type unknown
                    2008-04-11 11:32:22,242 [main] DEBUG org.springframework.jdbc.core.StatementCreatorUtil s - Setting SQL statement parameter value: column index 2, parameter value [date=04-11-2008;], value class [java.lang.String], SQL type unknown
                    2008-04-11 11:32:22,243 [main] DEBUG org.springframework.transaction.support.Transactio nSynchronizationManager - Retrieved value [org.springframework.jdbc.datasource.ConnectionHold er@e0e515] for key [org.apache.commons.dbcp.BasicDataSource@1584807] bound to thread [main]
                    2008-04-11 11:32:22,243 [main] DEBUG org.springframework.transaction.support.Transactio nSynchronizationManager - Retrieved value [org.springframework.jdbc.datasource.ConnectionHold er@e0e515] for key [org.apache.commons.dbcp.BasicDataSource@1584807] bound to thread [main]
                    2008-04-11 11:32:22,243 [main] DEBUG org.springframework.transaction.support.Transactio nSynchronizationManager - Retrieved value [org.springframework.jdbc.datasource.ConnectionHold er@e0e515] for key [org.apache.commons.dbcp.BasicDataSource@1584807] bound to thread [main]
                    2008-04-11 11:32:22,488 [main] DEBUG org.springframework.transaction.support.Transactio nSynchronizationManager - Retrieved value [org.springframework.jdbc.datasource.ConnectionHold er@e0e515] for key [org.apache.commons.dbcp.BasicDataSource@1584807] bound to thread [main]
                    2008-04-11 11:32:22,489 [main] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL update
                    2008-04-11 11:32:22,489 [main] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL statement [INSERT into BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION) values (?, ?, ?, ?)]
                    2008-04-11 11:32:22,489 [main] DEBUG org.springframework.transaction.support.Transactio nSynchronizationManager - Retrieved value [org.springframework.jdbc.datasource.ConnectionHold er@e0e515] for key [org.apache.commons.dbcp.BasicDataSource@1584807] bound to thread [main]
                    2008-04-11 11:32:22,519 [main] DEBUG org.springframework.transaction.support.Transactio nSynchronizationManager - Retrieved value [org.springframework.jdbc.datasource.ConnectionHold er@e0e515] for key [org.apache.commons.dbcp.BasicDataSource@1584807] bound to thread [main]
                    2008-04-11 11:32:22,519 [main] DEBUG org.springframework.jdbc.core.StatementCreatorUtil s - Setting SQL statement parameter value: column index 1, parameter value [0], value class [java.lang.Long], SQL type 4
                    2008-04-11 11:32:22,519 [main] DEBUG org.springframework.jdbc.core.StatementCreatorUtil s - Setting SQL statement parameter value: column index 2, parameter value [net.xeonsky.serenity.jobs.SearchAndTrackJob], value class [java.lang.String], SQL type 12
                    2008-04-11 11:32:22,519 [main] DEBUG org.springframework.jdbc.core.StatementCreatorUtil s - Setting SQL statement parameter value: column index 3, parameter value [date=04-11-2008;], value class [java.lang.String], SQL type 12
                    2008-04-11 11:32:22,519 [main] DEBUG org.springframework.jdbc.core.StatementCreatorUtil s - Setting SQL statement parameter value: column index 4, parameter value [0], value class [java.lang.Integer], SQL type 4
                    2008-04-11 11:32:22,684 [main] DEBUG org.springframework.transaction.support.Transactio nSynchronizationManager - Retrieved value [org.springframework.jdbc.datasource.ConnectionHold er@e0e515] for key [org.apache.commons.dbcp.BasicDataSource@1584807] bound to thread [main]
                    I does look like the JOB-INSTANCE_ID is somehow set to 0 when inserting the data into the db.

                    Any idea how I can get more logs or more details?
                    Is there any debug messages from MySQLMaxValueIncrementer that I should be able to see?

                    Comment


                    • #11
                      You could use AOP to wrap the getKey method and log it that way. I don't see any use of logging in the class itself. Keep in mind that you don't have to use the FactoryBean to configure it. You could wire up the SimpleJobRepository yourself with the 3 DAOs you need and their incrementers, there's examples of this in the docs. However, even if you don't do that you can use aop to add logging. (You could also put a break point around getKey()).

                      Comment


                      • #12
                        Stupid error (as always).
                        My BATCH_JOB_SEQ table (used by MySQLMaxValueIncrementer) didn't have any rows.
                        MySQLMaxValueIncrementer is expecting 1 row, and updates it to simulate a sequence (and update with no rows won't throw any exceptions and "last_insert_id()" returns 0).

                        Thanks for your time and patience lucasward.

                        Comment

                        Working...
                        X