Spring Batch Admin M2 with Oracle/2.0 SB Schema

[Dave Syer]

The queries that you posted form the log weren't trying to do that though. The first one (the job execution) just has "where ROWNUM=20" - it only wants one record. The second has "where ROWNUM<=1000" (just the first 1000). They still look OK to me (but I still can't test them properly).

[sams]

Sorry, yes, you are right. It is still incorrectly done even to return a single row. Rownum doesn't work as it's being used (it assigns the row numbers to the results themselves so you can't actually ask for rownum = 50 - unless you include rownum in the inner query). You can compare rownum, however as it'll check that as it's building results. It makes sense if you think about it (however obtuse) - it will make the rownum test (in the absolute case rownum = 50) and it will fail, hence you'll never get/generate any rows (if you use absolute). However, if you use <= some number, it'll succeed until it tries to generate a row past your range.

The problem query again:

SELECT * FROM (SELECT E.JOB_EXECUTION_ID AS SORT_KEY FROM BATCH_JOB_EXECUTION E, BATCH_JOB_INSTANCE I WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID ORDER BY E.JOB_EXECUTION_ID DESC) where rownum = 20;

One possible fix (include rownum in the inner query):

SELECT SORT_KEY FROM (SELECT E.JOB_EXECUTION_ID AS SORT_KEY, rownum rn FROM BATCH_JOB_EXECUTION E, BATCH_JOB_INSTANCE I WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID and rownum <= 20 ORDER BY E.JOB_EXECUTION_ID DESC) WHERE rn = 20;

Option 2 - Use min() over over the results to snag the sort key:

 SELECT min(sort_key)
  FROM (select E.JOB_EXECUTION_ID AS SORT_KEY FROM BATCH_JOB_EXECUTION E, BATCH_JOB_INSTANCE I 
WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID ORDER BY E.JOB_EXECUTION_ID DESC)  
 WHERE ROWNUM <= 20;

[sams]

I had to do some damage to the OraclePagingQueryProvider class to fix this - luckily the admin code is using the factory directly so I could circumvent that and just create my hacked Oracle paging provider. The rownum fix was slightly more involved since Oracle will generate rownums then sort. Here's the query I ended up with to fix the job execution paging:

 SELECT SORT_KEY FROM 
   (SELECT a.*, rownum rn 
     from (select E.JOB_EXECUTION_ID AS SORT_KEY FROM BATCH_JOB_EXECUTION E, BATCH_JOB_INSTANCE I 
     WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID ORDER BY E.JOB_EXECUTION_ID DESC) a) WHERE rn = 20;

My hack to the OraclePagingQueryProvider to get this sql was quite bad (since I tried to use the SqlPagingQueryUtils... you can only imagine). I still have the other two issues, but I believe they're both freemarker template issues. I will see if I can get to the bottom of it.

[sams]

I fixed the two template issues. I've never used Freemarker, so I just did my best to hack it as well. The first problem was in execution.ftl. The exitDescription is normally null (so it seems) on this path: stepExecutionInfo.stepExecution.exitStatus.exitDes cription so I just changed it to use the toString of exitStatus which includes the exitDescription if it exists (line 84):

${stepExecutionInfo.stepExecution.exitStatus}

vs.

${stepExecutionInfo.stepExecution.exitStatus.exitD escription}

The output ends up looking like this, which is fine for my purposes:
Exit Message exitCode=COMPLETED;exitDescription=null

The other issue was in jobs.ftl. It was using a variable defined in the rows loop to manufacture the link for the next/previous. That link included the job name. I modified it to make a new url (line 33) that was just to the /jobs resource (once again, no Freemarker skills so there's probably a better way to do this!):

<ul class="controlLinks">
   <#assign base_job_url><@spring.url relativeUrl="${servletPath}/jobs"/></#assign>
   <li>Rows: ${startJob}-${endJob} of ${totalJobs}</li> 
   <#if nextJob??><li><a href="${base_job_url}?startJob=${nextJob}&pageSize=${pageSize!20}">Next</a></li></#if>
   <#if previousJob??><li><a href="${base_job_url}?startJob=${previousJob}&pageSize=${pageSize!20}">Previous</a></li></#if>
   <li>Page Size: ${pageSize!20}</li>
</ul>

[Dave Syer]

The first problem was in execution.ftl. The exitDescription is normally null (so it seems) on this path: stepExecutionInfo.stepExecution.exitStatus.exitDes cription

That's an Oracle problem too (empty strings come back from the JDBC driver as null). We will fix it some other way.

The other issue was in jobs.ftl. It was using a variable defined in the rows loop to manufacture the link for the next/previous. That link included the job name.

Nice catch, thanks. I don't think I ever had more than 20 jobs in a test.

I'll also have alook at the ROWNUM queries. Can you raise a JIRA for that, and then you will know when it is fixed?

[piya]

I try to run the Spring-Batch-Admin and use the Oracle 10.2 and Spring Batch 2.1.1.

I access this url "http://localhost.../batch/jobs/executions" and it's fine. When I try to access next page "http://localhost.../batch/jobs/execu...20&pageSize=20", I got an error as shown below.

17:15:51,402 INFO  [STDOUT] 17:15:51,402 DEBUG http-localhost%2F127.0.0.1-81-1 JdbcTemplate:434 - Executing SQL query [SELECT COUNT(1) from BATCH_JOB_EXECUTION]
17:15:51,559 INFO  [STDOUT] 17:15:51,559 DEBUG http-localhost%2F127.0.0.1-81-1 JdbcTemplate:434 - Executing SQL query [SELECT * FROM (SELECT E.JOB_EXECUTION_ID AS SORT_KEY, ROWNUM as TMP_ROW_NUM FROM BATCH_JOB_EXECUTION E, BATCH_JOB_INSTANCE I WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID ORDER BY E.JOB_EXECUTION_ID DESC) WHERE TMP_ROW_NUM = 20]
17:15:52,934 ERROR [[Batch Servlet]] Servlet.service() for servlet Batch Servlet threw exception
org.springframework.jdbc.IncorrectResultSetColumnCountException: Incorrect column count: expected 1, actual 2
	at org.springframework.jdbc.core.SingleColumnRowMapper.mapRow(SingleColumnRowMapper.java:87)
	at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:92)
	at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:1)
	at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:445)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:395)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:455)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:463)
	at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:471)
	at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:476)
	at org.springframework.jdbc.core.JdbcTemplate.queryForLong(JdbcTemplate.java:480)
	at org.springframework.jdbc.core.simple.SimpleJdbcTemplate.queryForLong(SimpleJdbcTemplate.java:127)
	at org.springframework.batch.admin.service.JdbcSearchableJobExecutionDao.getJobExecutions(JdbcSearchableJobExecutionDao.java:190)
	at org.springframework.batch.admin.service.SimpleJobService.listJobExecutions(SimpleJobService.java:220)
	at org.springframework.batch.admin.web.JobExecutionController.list(JobExecutionController.java:156)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:174)
	at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:421)
	at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:409)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:771)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:716)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644)
	at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:549)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:77)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.springframework.web.filter.ShallowEtagHeaderFilter.doFilterInternal(ShallowEtagHeaderFilter.java:57)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:235)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
	at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:190)
	at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:92)
	at org.jboss.web.tomcat.security.SecurityContextEstablishmentValve.process(SecurityContextEstablishmentValve.java:126)
	at org.jboss.web.tomcat.security.SecurityContextEstablishmentValve.invoke(SecurityContextEstablishmentValve.java:70)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
	at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:158)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:330)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:829)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:598)
	at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
	at java.lang.Thread.run(Unknown Source)

Anyone could help or suggest me?
Thank you very much

[Dave Syer]

Stupid question: do you actually have more than 20 JobExecutions? If so maybe there is a bug in the logic that works out how many pages there are. The only other explanation I can think of is that you have a background process that deletes JobExecutions.