Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
YouUWd opened this issue Sep 5, 2019 · 9 comments
Assignees
Milestone

Comments

@YouUWd
Copy link

@YouUWd YouUWd commented Sep 5, 2019

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

This comment has been minimized.

Copy link
Member

@harawata harawata commented Sep 5, 2019

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

@YouUWd

This comment has been minimized.

Copy link
Author

@YouUWd YouUWd commented Sep 5, 2019

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

This comment has been minimized.

Copy link
Author

@YouUWd YouUWd commented Sep 5, 2019

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

image

@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

This comment has been minimized.

Copy link
Member

@harawata harawata commented Sep 5, 2019

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

@YouUWd

This comment has been minimized.

Copy link
Author

@YouUWd YouUWd commented Sep 6, 2019

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

This comment has been minimized.

Copy link
Member

@harawata harawata commented Sep 6, 2019

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

YouUWd pushed a commit to YouUWd/mybatis-issues that referenced this issue Sep 6, 2019
@YouUWd

This comment has been minimized.

Copy link
Author

@YouUWd YouUWd commented Sep 6, 2019

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

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

@harawata

This comment has been minimized.

Copy link
Member

@harawata harawata commented Sep 9, 2019

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

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 harawata changed the title fetchSize not work in 3.5.x queryCusor() fails in streaming mode of MySQL Connector/J (fetchSize=Integer.MIN_VALUE) Sep 10, 2019
YouUWd pushed a commit to YouUWd/mybatis-3 that referenced this issue Sep 12, 2019
harawata added a commit to YouUWd/mybatis-3 that referenced this issue Sep 15, 2019
mybatisgh-1654 and https://bugs.mysql.com/bug.php?id=96786
MysqlContainer will be removed when/if the driver bug is fixed (and there is no other MySQL specific issue to test).
@harawata harawata self-assigned this Sep 15, 2019
@harawata harawata added this to the 3.5.3 milestone Sep 15, 2019
harawata added a commit that referenced this issue Sep 15, 2019
fix queryCusor() fails in streaming mode of MySQL Connector/J (fetchSize=Integer.MIN_VALUE) #1654

This is a workaround for https://bugs.mysql.com/bug.php?id=96786
@harawata

This comment has been minimized.

Copy link
Member

@harawata harawata commented Sep 15, 2019

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!

@harawata harawata closed this Sep 15, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
2 participants
You can’t perform that action at this time.