queryCusor() fails in streaming mode of MySQL Connector/J (fetchSize=Integer.MIN_VALUE)

[YouUWd]

MyBatis version

3.5.x

Database vendor and version

mysql mysql-connector-java-5.1.48

Test case or example project

    @Options(fetchSize = Integer.MIN_VALUE)
    @Select("SELECT * FROM BLOG")
    Cursor<Blog> cursorAllMysql();

Steps to reproduce

Expected result

data list

Actual result

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.sql.SQLException: No operations allowed after statement closed.
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT * FROM BLOG
### Cause: java.sql.SQLException: No operations allowed after statement closed.

cause by

commit-id: 8892890
org.apache.ibatis.executor.SimpleExecutor#doQueryCursor
line 74: stmt.closeOnCompletion();

[harawata]

Thank you for the report, @YouUWd !
Please post the full stack trace.

[YouUWd]

Thank you for the report, @YouUWd !
Please post the full stack trace.
@harawata

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.sql.SQLException: No operations allowed after statement closed.
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT * FROM BLOG
### Cause: java.sql.SQLException: No operations allowed after statement closed.

	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectCursor(DefaultSqlSession.java:127)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectCursor(DefaultSqlSession.java:116)
	at org.apache.ibatis.binding.MapperMethod.executeForCursor(MapperMethod.java:167)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:84)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:57)
	at com.sun.proxy.$Proxy7.cursorAllMysql(Unknown Source)
	at com.youyou.ubatis.datasource.SingleDataSourceTest.testCursorMysql(SingleDataSourceTest.java:216)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
	at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
	at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
	at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Caused by: java.sql.SQLException: No operations allowed after statement closed.
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
	at com.mysql.jdbc.StatementImpl.checkClosed(StatementImpl.java:426)
	at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2174)
	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1216)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
	at com.sun.proxy.$Proxy9.execute(Unknown Source)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.queryCursor(PreparedStatementHandler.java:71)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.queryCursor(RoutingStatementHandler.java:84)
	at org.apache.ibatis.executor.SimpleExecutor.doQueryCursor(SimpleExecutor.java:75)
	at org.apache.ibatis.executor.BaseExecutor.queryCursor(BaseExecutor.java:178)
	at org.apache.ibatis.executor.CachingExecutor.queryCursor(CachingExecutor.java:89)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectCursor(DefaultSqlSession.java:123)
	... 28 more

[YouUWd]

change the code like this works fine, but I'm not sure about any negative effects.

@Override
  protected <E> Cursor<E> doQueryCursor(MappedStatement ms, Object parameter, RowBounds rowBounds, BoundSql boundSql) throws SQLException {
    Configuration configuration = ms.getConfiguration();
    StatementHandler handler = configuration.newStatementHandler(wrapper, ms, parameter, rowBounds, null, boundSql);
    Statement stmt = prepareStatement(handler, ms.getStatementLog());
    Cursor<E> cursor = handler.queryCursor(stmt);
    stmt.closeOnCompletion();
    return cursor;
  }

[harawata]

Please see #1297 .
You need to keep the session open.

[YouUWd]

Please see #1297 .
You need to keep the session open.

how can i keep the session open, it closed itself in SimpleExecutor

@see org.apache.ibatis.binding.BoundBlogMapper#openRangeBlogs

  @Select({
          "SELECT *",
          "FROM blog",
          "ORDER BY id"
  })
  @Options(fetchSize = Integer.MIN_VALUE)
  Cursor<Blog> openRangeBlogs(RowBounds rowBounds);

then the test case failed, and change the datasource from derby to mysql, will get stack trace as you see.

[harawata]

Could you share a complete project like these on GitHub please?

[YouUWd]

Could you share a complete project like these on GitHub please?

see https://github.com/YouUWd/mybatis-issues #1654

[harawata]

Thank you for the repro, @YouUWd !

Using your test, I tracked down the root cause and filed a bug report on Connector/J tracker.
https://bugs.mysql.com/bug.php?id=96786
[EDIT] The MySQL issue was addressed in 9.2.0.

Although it is a driver's bug, I think we can apply your suggested workaround because, as I mentioned in the above ticket, the spec clearly states that closeOnCompletion() can be called after retrieving result set.

So, are you keen to submit a PR?
If yes...

• BatchExecutor#doQueryCursor() needs the same fix.
• You don't have to add tests as it requires MySQL.
• Please understand that your PR might be rejected if there is any problem with the new approach.

If you are busy, it's totally fine. Please just let me know and I'll work on it when I have time.

[harawata]

Hi @YouUWd ,
The PR has been merged.
As there was another change related to Cursor (i.e. #1653 ), you should test the latest 3.5.3-SNAPSHOT with your solution and verify everything works fine.
Thank you for the report and the PR!