search

apache / shardingsphere

Distributed SQL transaction & query engine for data sharding, scaling, encryption, and more - on any database.
Apache License 2.0
19.76k stars 6.69k forks source link

Table does not exist exception occur when connecting to Proxy with an uppercase table #11868

Open strongduanmu opened 3 years ago

strongduanmu commented 3 years ago

Bug Report

Which version of ShardingSphere did you use?

master branch

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

ShardingSphere-Proxy

Expected behavior

No exception occur when connecting to Proxy

Actual behavior

[ERROR] 2021-08-18 09:25:42.467 [ShardingSphere-Command-0] o.a.s.p.f.c.CommandExecutorTask - Exception occur: 
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'demo_ds_0.t_order_federate' doesn't exist
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2491)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2449)
    at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:939)
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:103)
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
    at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.impl.ProxyStatementExecutorCallback.execute(ProxyStatementExecutorCallback.java:40)
    at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:73)
    at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:66)
    at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:44)
    at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:85)
    at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:64)
    at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:101)
    at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.parallelExecute(ExecutorEngine.java:97)
    at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:82)
    at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:65)
    at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.ProxyJDBCExecutor.execute(ProxyJDBCExecutor.java:71)
    at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.useDriverToExecute(ProxySQLExecutor.java:184)
    at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.execute(ProxySQLExecutor.java:137)
    at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.execute(ProxySQLExecutor.java:126)
    at org.apache.shardingsphere.proxy.backend.communication.ProxyLockEngine.doExecute(ProxyLockEngine.java:103)
    at org.apache.shardingsphere.proxy.backend.communication.ProxyLockEngine.execute(ProxyLockEngine.java:81)
    at org.apache.shardingsphere.proxy.backend.communication.DatabaseCommunicationEngine.execute(DatabaseCommunicationEngine.java:126)
    at org.apache.shardingsphere.proxy.backend.text.data.impl.UnicastDatabaseBackendHandler.execute(UnicastDatabaseBackendHandler.java:57)
    at org.apache.shardingsphere.proxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.execute(MySQLComQueryPacketExecutor.java:57)
    at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:98)
    at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:72)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:748)
[INFO ] 2021-08-18 09:25:42.469 [ShardingSphere-Command-1] ShardingSphere-SQL - Logic SQL: SET NAMES utf8mb4
[INFO ] 2021-08-18 09:25:42.469 [ShardingSphere-Command-1] ShardingSphere-SQL - SQLStatement: MySQLSetStatement()
[INFO ] 2021-08-18 09:25:42.469 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: SET NAMES utf8mb4
[INFO ] 2021-08-18 09:25:42.469 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_1 ::: SET NAMES utf8mb4
[INFO ] 2021-08-18 09:25:42.477 [ShardingSphere-Command-1] ShardingSphere-SQL - Logic SQL: SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
[INFO ] 2021-08-18 09:25:42.477 [ShardingSphere-Command-1] ShardingSphere-SQL - SQLStatement: MySQLSetTransactionStatement(isolationLevel=null, scope=null, accessMode=null)
[INFO ] 2021-08-18 09:25:42.477 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
[INFO ] 2021-08-18 09:25:42.477 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_1 ::: SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
[INFO ] 2021-08-18 09:25:42.484 [ShardingSphere-Command-1] ShardingSphere-SQL - Logic SQL: SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))
[INFO ] 2021-08-18 09:25:42.484 [ShardingSphere-Command-1] ShardingSphere-SQL - SQLStatement: MySQLSetStatement()
[INFO ] 2021-08-18 09:25:42.484 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))
[INFO ] 2021-08-18 09:25:42.484 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_1 ::: SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))

Reason analyze (If you can)

The mysql protocol converts t_order_FEDERATE to t_order_federate, causing an exception to be thrown during execution.

image

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

Create a table with an uppercase table name in the database, and then write a program to connect to Proxy.

The program can refer to issues #11757.

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

TeslaCN commented 3 years ago

I found something about MySQL case sensitivity. https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html

github-actions[bot] commented 1 year ago

Hello , this issue has not received a reply for several days. This issue is supposed to be closed.

github-actions[bot] commented 5 months ago

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.