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

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: 8892890a14f47ecff1a066942e451b2a6156007f org.apache.ibatis.executor.SimpleExecutor#doQueryCursor line 74: stmt.closeOnCompletion();

Comment From: harawata

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

Comment From: 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

Comment From: YouUWd

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

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

@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;
  }

Comment From: harawata

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

Comment From: 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.

Comment From: harawata

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

Comment From: YouUWd

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

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

Comment From: 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.

Comment From: 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!