Shardingsphere: In MySQL 8.0.22, Using Mybatis, ShardingProxy select All get ArrayIndexOutOfBoundsException

Created on 23 Mar 2021  ·  5Comments  ·  Source: apache/shardingsphere

Bug Report

Which version of ShardingSphere did you use?

shardingsphere-proxy-5.0.0 alpha
Mysql 8.0.22
SpringBoot 2.4.4
mybatis-spring 2.1.4
JDK11

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

shardingsphere-proxy

Expected behavior

execute code example examples/shardingsphere-proxy-example/shardingsphere-proxy-boot-mybatis-example, when selectAll, occur ArrayIndexOutOfBoundsException, expect return all record. in addition, insert is as expect.

Actual behavior

org.springframework.dao.TransientDataAccessResourceException: 
### Error querying database.  Cause: java.sql.SQLException: java.lang.ArrayIndexOutOfBoundsException: Index 1 out of bounds for length 1
### The error may exist in mybatis/mapper/OrderMapper.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT * FROM t_order;
### Cause: java.sql.SQLException: java.lang.ArrayIndexOutOfBoundsException: Index 1 out of bounds for length 1
; java.lang.ArrayIndexOutOfBoundsException: Index 1 out of bounds for length 1; nested exception is java.sql.SQLException: java.lang.ArrayIndexOutOfBoundsException: Index 1 out of bounds for length 1

    at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:110)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
    at com.sun.proxy.$Proxy52.selectList(Unknown Source)
    at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224)
    at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:147)
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:80)
    at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:152)
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:85)
    at com.sun.proxy.$Proxy57.selectAll(Unknown Source)
    at cn.gl.shardingproxydemo2.mapper.OrderMapperTest.testOrderMapper(OrderMapperTest.java:36)
    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.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:688)
    at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
    at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:149)
    at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:140)
    at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:84)
    at org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115)
    at org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)
    at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:104)
    at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:98)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$6(TestMethodTestDescriptor.java:210)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:206)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:131)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:65)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:129)
    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:127)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:126)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:84)
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1541)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:143)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:129)
    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:127)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:126)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:84)
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1541)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:143)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:129)
    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:127)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:126)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:84)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:32)
    at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
    at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:51)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:108)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:88)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.lambda$execute$0(EngineExecutionOrchestrator.java:54)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.withInterceptedStreams(EngineExecutionOrchestrator.java:67)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:52)
    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:96)
    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:75)
    at com.intellij.junit5.JUnit5IdeaTestRunner.startRunnerWithArgs(JUnit5IdeaTestRunner.java:71)
    at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:33)
    at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:220)
    at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:53)
Caused by: java.sql.SQLException: java.lang.ArrayIndexOutOfBoundsException: Index 1 out of bounds for length 1
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)
    at com.mysql.cj.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:441)
    at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
    at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
    at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:89)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
    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.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427)
    ... 73 more
Caused by: java.lang.ArrayIndexOutOfBoundsException: Index 1 out of bounds for length 1
    at com.mysql.cj.protocol.a.MergingColumnDefinitionFactory.createFromFields(MergingColumnDefinitionFactory.java:61)
    at com.mysql.cj.protocol.a.ColumnDefinitionReader.read(ColumnDefinitionReader.java:80)
    at com.mysql.cj.protocol.a.ColumnDefinitionReader.read(ColumnDefinitionReader.java:40)
    at com.mysql.cj.protocol.a.NativeProtocol.read(NativeProtocol.java:1583)
    at com.mysql.cj.protocol.a.BinaryResultsetReader.read(BinaryResultsetReader.java:70)
    at com.mysql.cj.protocol.a.BinaryResultsetReader.read(BinaryResultsetReader.java:50)
    at com.mysql.cj.protocol.a.NativeProtocol.read(NativeProtocol.java:1596)
    at com.mysql.cj.protocol.a.NativeProtocol.readAllResults(NativeProtocol.java:1650)
    at com.mysql.cj.ServerPreparedQuery.readExecuteResult(ServerPreparedQuery.java:389)
    at com.mysql.cj.ServerPreparedQuery.serverExecute(ServerPreparedQuery.java:208)
    at com.mysql.cj.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:635)
    at com.mysql.cj.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:418)
    ... 90 more


Reason analyze (If you can)

guess sharding proxy return column meta info has problem.

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

sharding rule as examples/shardingsphere-proxy-example/shardingsphere-proxy-boot-mybatis-example/src/main/resources/conf/config-sharding.yaml

server config: examples/shardingsphere-proxy-example/shardingsphere-proxy-boot-mybatis-example/src/main/resources/conf/server.yaml

    <select id="selectAll" resultMap="baseResultMap">
        SELECT * FROM t_order;
    </select>

Example codes for reproduce this issue (such as a github link).

https://github.com/GLBB/sharding-proxy-demo

I wanner analyse this problem, but encounter difficulties, I don't known how to in idea debug proxy code, anyone can help me?
when run org.apache.shardingsphere.proxy.Bootstrap, told me config error.

Exception in thread "main" Cannot create property=authentication for JavaBean=org.apache.shardingsphere.proxy.config.yaml.YamlProxyServerConfiguration@16f7c8c1
 in 'reader', line 65, column 1:
    authentication:
    ^
Cannot create property=users for JavaBean=org.apache.shardingsphere.infra.metadata.auth.builtin.yaml.config.YamlUserRuleConfiguration@24a35978
 in 'reader', line 66, column 3:
      users:
      ^
Cannot create property=authorizedSchemas for JavaBean=org.apache.shardingsphere.infra.metadata.auth.builtin.yaml.config.YamlUserConfiguration@df27fae
 in 'reader', line 70, column 7:
          password: sharding
          ^
Unable to find property 'authorizedSchemas' on class: org.apache.shardingsphere.infra.metadata.auth.builtin.yaml.config.YamlUserConfiguration
 in 'reader', line 71, column 26:
          authorizedSchemas: sharding_db
                             ^

 in 'reader', line 67, column 5:
        root:
        ^

 in 'reader', line 66, column 3:
      users:
      ^

    at org.yaml.snakeyaml.constructor.Constructor$ConstructMapping.constructJavaBean2ndStep(Constructor.java:312)
    at org.yaml.snakeyaml.constructor.Constructor$ConstructMapping.construct(Constructor.java:189)
    at org.yaml.snakeyaml.constructor.Constructor$ConstructYamlObject.construct(Constructor.java:345)
    at org.yaml.snakeyaml.constructor.BaseConstructor.constructObject(BaseConstructor.java:182)
    at org.yaml.snakeyaml.constructor.BaseConstructor.constructDocument(BaseConstructor.java:141)
    at org.yaml.snakeyaml.constructor.BaseConstructor.getSingleData(BaseConstructor.java:127)
    at org.yaml.snakeyaml.Yaml.loadFromReader(Yaml.java:450)
    at org.yaml.snakeyaml.Yaml.loadAs(Yaml.java:410)
    at org.apache.shardingsphere.infra.yaml.engine.YamlEngine.unmarshal(YamlEngine.java:54)
    at org.apache.shardingsphere.proxy.config.ProxyConfigurationLoader.loadServerConfiguration(ProxyConfigurationLoader.java:73)
    at org.apache.shardingsphere.proxy.config.ProxyConfigurationLoader.load(ProxyConfigurationLoader.java:59)
    at org.apache.shardingsphere.proxy.Bootstrap.main(Bootstrap.java:47)
Caused by: Cannot create property=users for JavaBean=org.apache.shardingsphere.infra.metadata.auth.builtin.yaml.config.YamlUserRuleConfiguration@24a35978
 in 'reader', line 66, column 3:
      users:
      ^
Cannot create property=authorizedSchemas for JavaBean=org.apache.shardingsphere.infra.metadata.auth.builtin.yaml.config.YamlUserConfiguration@df27fae
 in 'reader', line 70, column 7:
          password: sharding
          ^
Unable to find property 'authorizedSchemas' on class: org.apache.shardingsphere.infra.metadata.auth.builtin.yaml.config.YamlUserConfiguration
 in 'reader', line 71, column 26:
          authorizedSchemas: sharding_db
                             ^

 in 'reader', line 67, column 5:
        root:
        ^

    at org.yaml.snakeyaml.constructor.Constructor$ConstructMapping.constructJavaBean2ndStep(Constructor.java:312)
    at org.yaml.snakeyaml.constructor.Constructor$ConstructMapping.construct(Constructor.java:189)
    at org.yaml.snakeyaml.constructor.BaseConstructor.constructObject(BaseConstructor.java:182)
    at org.yaml.snakeyaml.constructor.Constructor$ConstructMapping.constructJavaBean2ndStep(Constructor.java:297)
    ... 11 more
Caused by: Cannot create property=authorizedSchemas for JavaBean=org.apache.shardingsphere.infra.metadata.auth.builtin.yaml.config.YamlUserConfiguration@df27fae
 in 'reader', line 70, column 7:
          password: sharding
          ^
Unable to find property 'authorizedSchemas' on class: org.apache.shardingsphere.infra.metadata.auth.builtin.yaml.config.YamlUserConfiguration
 in 'reader', line 71, column 26:
          authorizedSchemas: sharding_db
                             ^

    at org.yaml.snakeyaml.constructor.Constructor$ConstructMapping.constructJavaBean2ndStep(Constructor.java:312)
    at org.yaml.snakeyaml.constructor.Constructor$ConstructMapping.construct(Constructor.java:189)
    at org.yaml.snakeyaml.constructor.BaseConstructor.constructObject(BaseConstructor.java:182)
    at org.yaml.snakeyaml.constructor.BaseConstructor.constructMapping2ndStep(BaseConstructor.java:373)
    at org.yaml.snakeyaml.constructor.SafeConstructor.constructMapping2ndStep(SafeConstructor.java:147)
    at org.yaml.snakeyaml.constructor.BaseConstructor.constructMapping(BaseConstructor.java:354)
    at org.yaml.snakeyaml.constructor.Constructor$ConstructMapping.construct(Constructor.java:170)
    at org.yaml.snakeyaml.constructor.BaseConstructor.constructObject(BaseConstructor.java:182)
    at org.yaml.snakeyaml.constructor.Constructor$ConstructMapping.constructJavaBean2ndStep(Constructor.java:297)
    ... 14 more
Caused by: org.yaml.snakeyaml.error.YAMLException: Unable to find property 'authorizedSchemas' on class: org.apache.shardingsphere.infra.metadata.auth.builtin.yaml.config.YamlUserConfiguration
    at org.yaml.snakeyaml.introspector.PropertyUtils.getProperty(PropertyUtils.java:132)
    at org.yaml.snakeyaml.introspector.PropertyUtils.getProperty(PropertyUtils.java:121)
    at org.yaml.snakeyaml.constructor.Constructor$ConstructMapping.getProperty(Constructor.java:322)
    at org.yaml.snakeyaml.constructor.Constructor$ConstructMapping.constructJavaBean2ndStep(Constructor.java:240)
    ... 22 more

All 5 comments

Let me make a check for this report.

Hi, @GLBB
I have reproduced the Exception that you mentioned(ArrayIndexOutOfBoundsException).
And I found the reason was in the connection url.

I prepared a sharding env for testing:

  • ds_0.t_order
  • ds_1.t_order

Create DLL is:

CREATE TABLEt_order( order_idint NOT NULL AUTO_INCREMENT, user_idint NOT NULL, statusvarchar(45) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (order_id) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

All the records are

  • 1,1,'ok'
  • 2,2,'ok'
  • 3,3,'ok'

case 1: set 'useServerPrepStmts' to false (default is false)

Logic SQL: SELECT * FROM t_order; Actual SQL: ds_0 ::: SELECT * FROM t_order ORDER BY order_id ASC ; Actual SQL: ds_1 ::: SELECT * FROM t_order ORDER BY order_id ASC ;

the database packet codec(from PacketCodec.class) was like:

  • |.....1.1.ok |
  • |.....2.2.ok |
  • |.....3.3.ok |
  • and the test result was as expected.

case 2: set 'useServerPrepStmts' to true

Logic SQL: SELECT * FROM t_order; Actual SQL: ds_0 ::: SELECT * FROM t_order ORDER BY order_id ASC ; Actual SQL: ds_1 ::: SELECT * FROM t_order ORDER BY order_id ASC ;

the database packet codec(from PacketCodec) was like:

  • |...............o|
    |k |
  • |...............o|
    |k |
  • |...............o|
    |k |
  • so we got the Exception.

Now, you may think about whether the parameter 'useServerPrepStmts' is useful to your project.

For more details, we need to be studied.

Reference to this blog:
https://shardingsphere.apache.org/blog/cn/material/proxy/

We do not need to set the 'useServerPrepStmts' param in proxy clients.
Proxy already has this parameter when it connects to the database.

@RaigorJiang
image
In sharding proxy example, config useServerPrepStmts = true, may need to modify this example configuration.
in addition, I want to consult you about how to start up sharding proxy in IDEA for convenient learn shardingProxy, can give me any article, it realy help newer to learn proxy.
best regards!

@GLBB
The reason your proxy started failed was the param 'authorizedSchemas' has been removed.
See https://github.com/apache/shardingsphere/pull/9300

Now you can refer this PR, modify 'server.yaml'
https://github.com/apache/shardingsphere/pull/9888/files

Was this page helpful?
0 / 5 - 0 ratings