[CONJ-977] java.sql.SQLSyntaxErrorException: (conn=65) invalid fetch size Created: 2022-05-29  Updated: 2022-12-23  Resolved: 2022-12-23

Status: Closed
Project: MariaDB Connector/J
Component/s: MySQL compatibility
Affects Version/s: 3.0.4
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Rahul Assignee: Diego Dupin
Resolution: Not a Bug Votes: 0
Labels: Compatibility


 Description   

Getting Invalid fetch size issue, when trying to setup an java application that uses infinisapn 9.4.4-Final to take care of cache related table with below JDBC URL using mariadb driver 3.0.4

jdbc:mysql://localhost:3307/demo_test_01?permitMysqlScheme

but when i try to install same application with below jdbc URL, it start successfully using mariadb driver 3.0.4

jdbc:mariadb://localhost:3307/demo_test_02?useMysqlMetadata

MariaDB server version: 10.7.3-MariaDB-1:10.7.3+maria~focal
And MariaDB connect/j --> 3.0.4 & 3.0.5

Observation:

  • "jdbc:mysql://localhost:3307/demo_test_01" work fine for same application when using mariadb driver 2.7.3
  • Also unable to fetch tables details when used database tool of IntelliJ IDEA with driver 3.0.4
  • as well as invalid fetch size error in console

Caused by: java.sql.SQLSyntaxErrorException: (conn=65) invalid fetch size
 
	at org.mariadb.jdbc.export.ExceptionFactory.createException(ExceptionFactory.java:280)
 
	at org.mariadb.jdbc.export.ExceptionFactory.create(ExceptionFactory.java:334)
 
	at org.mariadb.jdbc.Statement.setFetchSize(Statement.java:570)
 
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
 
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
 
	at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:118)
 
	at com.sun.proxy.$Proxy47.setFetchSize(Unknown Source)
 
	at com.dummy.app.server.database.wrapping.DummyStatementWrapper.setFetchSize(DummyStatementWrapper.java:403)
 
	at org.infinispan.persistence.jdbc.stringbased.JdbcStringBasedStore.lambda$publish$1(JdbcStringBasedStore.java:533)
 
	at io.reactivex.internal.operators.flowable.FlowableUsing.subscribeActual(FlowableUsing.java:58)
 
	at io.reactivex.Flowable.subscribe(Flowable.java:14409)
 
	at io.reactivex.internal.operators.flowable.FlowableTake.subscribeActual(FlowableTake.java:33)
 
	at io.reactivex.Flowable.subscribe(Flowable.java:14409)
 
	at io.reactivex.internal.operators.flowable.FlowableDoOnEach.subscribeActual(FlowableDoOnEach.java:50)
 
	at io.reactivex.Flowable.subscribe(Flowable.java:14409)
 
	at io.reactivex.internal.operators.flowable.FlowableCountSingle.subscribeActual(FlowableCountSingle.java:34)
 
	at io.reactivex.Single.subscribe(Single.java:3394)
 
	at io.reactivex.Single.blockingGet(Single.java:2655)



 Comments   
Comment by Diego Dupin [ 2022-05-30 ]

This is probably because tools have taken in account a MySQL bug : MySQL connector doesn't follow JDBC spec for fetching size.
JDBC spec https://docs.oracle.com/en/java/javase/17/docs/api/java.sql/java/sql/Statement.html#setFetchSize(int) indicate that setFetchSize must throw an SQLException "if a database access error occurs, this method is called on a closed Statement or the condition rows >= 0 is not satisfied."

MySQL only permit value of Integer.MIN_VALUE that is normally wrong, and when enable, fetch size of 1.

MariaDB 2.7 and 3.0 connectors fetch size correctly (a fetch size of 1000 means that connectors will really fetch 1000 rows of data).
for historical reason, MariaDB 2.7 was supporting Integer.MIN_VALUE. 3.0 that is a complete rewrite follow JDBC spec only.

Some tools might consider "jdbc:mysql" as a mysql connectors having this bug.

About intellij, i've submit an issue : https://youtrack.jetbrains.com/issue/DBE-15654/wrong-use-of-setFetchSize-when-using-mariadb-driver for this problem.

Comment by Diego Dupin [ 2022-06-27 ]

IntelliJ IDEA has correct implementation in recent version

Comment by Diego Dupin [ 2022-12-23 ]

closing since not a connector bug, and tools are correcting behavior

Generated at Thu Feb 08 03:19:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.