Announcement Announcement Module
Collapse
No announcement yet.
jdbccursoritemreader issue Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • jdbccursoritemreader issue

    Hi,
    before i start the step i got listener to set the sql like something select * from b where b.i=01 and b.k=02. The data in the table should be around 100k.
    Sometime the error is fail to initialise the step which the error is the packet lost for communication to sql. The sql server i use is mysql. Beside, it is quite slow for me to initialise the step. Hwo to solve this kind of problem? By the way, there is one field to set the setFetchSize. If i set the fetch size to 100 will help? As i set it to 100 but still have problem. Issit before initialise the step, it will load all the data into the result set first? It seem like too much data and will hit java heap space error right if dump all the data inside?

  • #2
    It sounds like you have a network connection issue between your machine and the database you're using. Have you tried pinging to test response times?

    Comment


    • #3
      the database is install in the same PC. should be not network issue right?

      Comment


      • #4
        I try set ?useCursorFetch=true to the URL. but the error i Get like the below.
        Is it mysql error or wat? normally the doing the cursor is at server side right? not spring batch doing the cursor itself right? so when we use jdbccursor write "select * from b" will have to wait the sql server to return cursor? Because it take around 5 minutes hang there only the step start. Anyone have facing the similar problem? my table will have around 400k rows.

        Code:
        org.springframework.dao.InvalidDataAccessResourceUsageException: Unexpected cursor position change.
                at org.springframework.batch.item.database.JdbcCursorItemReader.verifyCursorPosition(JdbcCursorItemReader.java:384)
                at org.springframework.batch.item.database.JdbcCursorItemReader.doRead(JdbcCursorItemReader.java:423)
                at org.springframework.batch.item.support.AbstractBufferedItemReaderItemStream.read(AbstractBufferedItemReaderItemStream.java:92)
                at org.springframework.batch.item.support.DelegatingItemReader.read(DelegatingItemReader.java:61)
                at org.springframework.batch.core.step.item.BatchListenerFactoryHelper$1.read(BatchListenerFactoryHelper.java:67)
                at org.springframework.batch.core.step.item.SimpleItemHandler.doRead(SimpleItemHandler.java:88)
                at org.springframework.batch.core.step.item.SimpleItemHandler.read(SimpleItemHandler.java:80)
                at org.springframework.batch.core.step.item.SimpleItemHandler.handle(SimpleItemHandler.java:66)
                at org.springframework.batch.core.step.item.ItemOrientedStep$2.doInIteration(ItemOrientedStep.java:367)
                at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:346)
                at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:212)
                at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:143)
                at org.springframework.batch.core.step.item.ItemOrientedStep.processChunk(ItemOrientedStep.java:360)
                at org.springframework.batch.core.step.item.ItemOrientedStep$1.doInIteration(ItemOrientedStep.java:248)
                at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:346)
                at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:212)
                at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:143)
                at org.springframework.batch.core.step.item.ItemOrientedStep.doExecute(ItemOrientedStep.java:230)
                at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:172)
                at org.springframework.batch.core.job.SimpleJob.execute(SimpleJob.java:100)
                at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:86)
                at java.lang.Thread.run(Thread.java:619)
        22:11:07,685 ERROR [STDERR] Exception in thread "SimpleAsyncTaskExecutor-2"
        22:11:07,685 ERROR [STDERR] org.springframework.dao.InvalidDataAccessResourceUsageException: Unexpected cursor position change.
        22:11:07,686 ERROR [STDERR]     at org.springframework.batch.item.database.JdbcCursorItemReader.verifyCursorPosition(JdbcCursorItemReader.java:384)
        22:11:07,686 ERROR [STDERR]     at org.springframework.batch.item.database.JdbcCursorItemReader.doRead(JdbcCursorItemReader.java:423)
        22:11:07,686 ERROR [STDERR]     at org.springframework.batch.item.support.AbstractBufferedItemReaderItemStream.read(AbstractBufferedItemReaderItemStream.java:92)
        22:11:07,686 ERROR [STDERR]     at org.springframework.batch.item.support.DelegatingItemReader.read(DelegatingItemReader.java:61)
        22:11:07,686 ERROR [STDERR]     at org.springframework.batch.core.step.item.BatchListenerFactoryHelper$1.read(BatchListenerFactoryHelper.java:67)
        22:11:07,686 ERROR [STDERR]     at org.springframework.batch.core.step.item.SimpleItemHandler.doRead(SimpleItemHandler.java:88)
        22:11:07,687 ERROR [STDERR]     at org.springframework.batch.core.step.item.SimpleItemHandler.read(SimpleItemHandler.java:80)
        22:11:07,687 ERROR [STDERR]     at org.springframework.batch.core.step.item.SimpleItemHandler.handle(SimpleItemHandler.java:66)
        22:11:07,687 ERROR [STDERR]     at org.springframework.batch.core.step.item.ItemOrientedStep$2.doInIteration(ItemOrientedStep.java:367)
        22:11:07,687 ERROR [STDERR]     at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:346)
        22:11:07,687 ERROR [STDERR]     at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:212)
        22:11:07,687 ERROR [STDERR]     at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:143)
        22:11:07,688 ERROR [STDERR]     at org.springframework.batch.core.step.item.ItemOrientedStep.processChunk(ItemOrientedStep.java:360)
        22:11:07,688 ERROR [STDERR]     at org.springframework.batch.core.step.item.ItemOrientedStep$1.doInIteration(ItemOrientedStep.java:248)
        22:11:07,688 ERROR [STDERR]     at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:346)
        22:11:07,688 ERROR [STDERR]     at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:212)
        22:11:07,688 ERROR [STDERR]     at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:143)
        22:11:07,688 ERROR [STDERR]     at org.springframework.batch.core.step.item.ItemOrientedStep.doExecute(ItemOrientedStep.java:230)
        22:11:07,689 ERROR [STDERR]     at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:172)
        22:11:07,689 ERROR [STDERR]     at org.springframework.batch.core.job.SimpleJob.execute(SimpleJob.java:100)
        22:11:07,689 ERROR [STDERR]     at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:86)
        22:11:07,689 ERROR [STDERR]     at java.lang.Thread.run(Thread.java:619)

        Comment


        • #5
          If you're running the server on your machine with that much data, I suppose opening the cursor could be an issue, you could try opening it with normal sql statements to see how long it takes.

          Comment


          • #6
            ya! i have tried v normal prepared statement, then set fetch size for the prepared statement. It take quite long time also in order for the result set to start get value. Wonder is the mysql issue?

            I have try DB2 database as well. The case become worst as I launch two job same time asynchronous but keep getting deadlock when update spring table. Beside before start the the step include the jdbcCursorItemReader, hit the exception :
            Code:
             The driver did not support holdability.
            But from the documention, I found that the driver is support holdability. I also not sure. I try again with other database.

            Any idea how to solve the deadlock issue for DB2?

            Comment


            • #7
              Was this issue ever resolved?

              I'm working on getting Spring Batch working on WebLogic 9.2.0. The same job was working fine in Tomcat 6.0.18 using Spring provided jndi data sources. In Weblogic we're using container managed datasources and oracle.jdbc.xa.client.OracleXADataSource db drivers to access staging tables. The Spring Batch data source uses oracle.jdbc.OracleDriver.

              The batch job is trying to process about 8000 db records.

              This is the error:

              org.springframework.batch.core.step.skip.SkipLimit ExceededException: Skip limit of '10' exceeded
              at org.springframework.batch.core.step.skip.LimitChec kingItemSkipPolicy.shouldSkip(LimitCheckingItemSki pPolicy.java:123)
              at org.springframework.batch.core.step.item.FaultTole rantChunkProvider.read(FaultTolerantChunkProvider. java:51)
              at org.springframework.batch.core.step.item.SimpleChu nkProvider$1.doInIteration(SimpleChunkProvider.jav a:105)
              at org.springframework.batch.repeat.support.RepeatTem plate.getNextResult(RepeatTemplate.java:352)
              at org.springframework.batch.repeat.support.RepeatTem plate.executeInternal(RepeatTemplate.java:212)
              at org.springframework.batch.repeat.support.RepeatTem plate.iterate(RepeatTemplate.java:143)
              at org.springframework.batch.core.step.item.SimpleChu nkProvider.provide(SimpleChunkProvider.java:102)
              at org.springframework.batch.core.step.item.ChunkOrie ntedTasklet.execute(ChunkOrientedTasklet.java:65)
              at org.springframework.batch.core.step.tasklet.Taskle tStep$2.doInChunkContext(TaskletStep.java:264)
              at org.springframework.batch.core.scope.context.StepC ontextRepeatCallback.doInIteration(StepContextRepe atCallback.java:67)
              at org.springframework.batch.repeat.support.TaskExecu torRepeatTemplate$ExecutingRunnable.run(TaskExecut orRepeatTemplate.java:230)
              at java.lang.Thread.run(Thread.java:595)
              Caused by: org.springframework.dao.InvalidDataAccessResourceU sageException: Unexpected cursor position change.
              at org.springframework.batch.item.database.JdbcCursor ItemReader.verifyCursorPosition(JdbcCursorItemRead er.java:431)
              at org.springframework.batch.item.database.JdbcCursor ItemReader.doRead(JdbcCursorItemReader.java:478)
              at org.springframework.batch.item.support.AbstractIte mCountingItemStreamItemReader.read(AbstractItemCou ntingItemStreamItemReader.java:84)
              at org.springframework.batch.core.step.item.SimpleChu nkProvider.doRead(SimpleChunkProvider.java:89)
              at org.springframework.batch.core.step.item.FaultTole rantChunkProvider.read(FaultTolerantChunkProvider. java:47)
              ... 10 more

              Comment


              • #8
                Your skip limit was exceeded, you should set it higher. Unless 10 was set for some business reason, in which case failure is the right action.

                Comment


                • #9
                  You are trying to use a TaskExecutorRepeatTemplate with a cursor reader? Maybe that's the problem (two threads access the cursor simultaneously). Can you post the configuration and explain your use case a bit?

                  Comment


                  • #10
                    Fix or work-around?

                    I was able to get around the problem with the "Cursor Position Change" by changing the database driver for the Spring Batch and staging table datasources from OCI XA to just OCI. No problems at all after that. As a matter of fact we were never able to process more than 1500 records on Tomcat and flew through 10000 in WebLogic with no issues.

                    Comment


                    • #11
                      I'm interested in what you mean exactly by "never able to process more than 1500 records". And were you actually using a multi-threaded step to read from a cursor? It has no right to work (and is probably broken for restart, so be very careful).

                      Comment


                      • #12
                        I jumped the gun that the driver change fixed the problem. I went to a faster system and the problem came back at exactly the same step. I'm attaching the (sanitized) job config. Is there another class we should use instead of JdbcCursorItemReader?

                        Here is also a little bit more context of the batch job. The first step runs a process that populates a table. The second step reads that table and processes it's records in chunks. The rest of the steps read from the output of the previous step, execute a service and write to another table.
                        Last edited by joecarew; Apr 17th, 2009, 01:43 PM.

                        Comment


                        • #13
                          The problem is that you can't use a JdbcCursorItemReader with a multi-threaded task executor. Look at the difference between StagingItemReader and JdbcCursorItemReader to see why. You either need to make your first stagingInitializationReader step single-threaded or use a different (custom) reader.

                          Comment


                          • #14
                            Thanks for the suggestion. We created a custom multi-threaded reader, and that did the trick.

                            Comment


                            • #15
                              I have a similar problem

                              Hello
                              I'm using spring batch 1.1.4.RELEASE
                              I have the unexpected cursor position exception when I'm using the ThreadPoolTaskExecutor taskExecutor in my step. If I desable verifyCursorPosition, sometimes two or three threads insert the same record. How can'I use spring batch to solve this problem? Or I must make a synchronize on my cursor to be sur that only a single thread read the cursor?

                              Comment

                              Working...
                              X