shardingsphere icon indicating copy to clipboard operation
shardingsphere copied to clipboard
verified publisher icon apache

sharding proxy information_schema not work, it is bug?

Open hhniao opened this issue 2 years ago • 5 comments

Bug Report

For English only, other languages will not accept.

first question. select * from tables; return empty set

shardingsphere-proxy | [ERROR] 2023-03-01 03:37:34.590 [Connection-3-ThreadExecutor] o.a.s.p.f.c.CommandExecutorTask - Exception occur: shardingsphere-proxy | java.lang.NullPointerException: null shardingsphere-proxy | at java.base/java.util.Objects.requireNonNull(Objects.java:208) shardingsphere-proxy | at java.base/java.util.Optional.of(Optional.java:113) shardingsphere-proxy | at org.apache.shardingsphere.sqlfederation.advanced.resultset.SQLFederationResultSetMetaData.findTableName(SQLFederationResultSetMetaData.java:180) shardingsphere-proxy | at org.apache.shardingsphere.sqlfederation.advanced.resultset.SQLFederationResultSetMetaData.getTableName(SQLFederationResultSetMetaData.java:137) shardingsphere-proxy | at org.apache.shardingsphere.infra.executor.sql.execute.result.query.impl.driver.jdbc.metadata.JDBCQueryResultMetaData.getTableName(JDBCQueryResultMetaData.java:42) shardingsphere-proxy | at org.apache.shardingsphere.proxy.backend.response.header.query.impl.MySQLQueryHeaderBuilder.build(MySQLQueryHeaderBuilder.java:40) shardingsphere-proxy | at org.apache.shardingsphere.proxy.backend.response.header.query.QueryHeaderBuilderEngine.build(QueryHeaderBuilderEngine.java:55) shardingsphere-proxy | at org.apache.shardingsphere.proxy.backend.communication.DatabaseCommunicationEngine.processExecuteFederation(DatabaseCommunicationEngine.java:226) shardingsphere-proxy | at org.apache.shardingsphere.proxy.backend.communication.DatabaseCommunicationEngine.execute(DatabaseCommunicationEngine.java:168) shardingsphere-proxy | at org.apache.shardingsphere.proxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.execute(MySQLComQueryPacketExecutor.java:92) shardingsphere-proxy | at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:110) shardingsphere-proxy | at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:77) shardingsphere-proxy | at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) shardingsphere-proxy | at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) shardingsphere-proxy | at java.base/java.lang.Thread.run(Thread.java:833)

second question. select * from columns; 30000 - Unknown exception: Index 20 out of bounds for length 20

shardingsphere-proxy | [ERROR] 2023-03-01 03:40:27.603 [Connection-4-ThreadExecutor] o.a.s.p.f.c.CommandExecutorTask - Exception occur: shardingsphere-proxy | java.lang.IndexOutOfBoundsException: Index 20 out of bounds for length 20 shardingsphere-proxy | at java.base/jdk.internal.util.Preconditions.outOfBounds(Preconditions.java:64) shardingsphere-proxy | at java.base/jdk.internal.util.Preconditions.outOfBoundsCheckIndex(Preconditions.java:70) shardingsphere-proxy | at java.base/jdk.internal.util.Preconditions.checkIndex(Preconditions.java:266) shardingsphere-proxy | at java.base/java.util.Objects.checkIndex(Objects.java:359) shardingsphere-proxy | at java.base/java.util.ArrayList.get(ArrayList.java:427) shardingsphere-proxy | at org.apache.shardingsphere.sqlfederation.advanced.resultset.SQLFederationResultSetMetaData.getColumnName(SQLFederationResultSetMetaData.java:111) shardingsphere-proxy | at org.apache.shardingsphere.infra.executor.sql.execute.result.query.impl.driver.jdbc.metadata.JDBCQueryResultMetaData.getColumnName(JDBCQueryResultMetaData.java:47) shardingsphere-proxy | at org.apache.shardingsphere.proxy.backend.response.header.query.QueryHeaderBuilderEngine.build(QueryHeaderBuilderEngine.java:51) shardingsphere-proxy | at org.apache.shardingsphere.proxy.backend.communication.DatabaseCommunicationEngine.processExecuteFederation(DatabaseCommunicationEngine.java:226) shardingsphere-proxy | at org.apache.shardingsphere.proxy.backend.communication.DatabaseCommunicationEngine.execute(DatabaseCommunicationEngine.java:168) shardingsphere-proxy | at org.apache.shardingsphere.proxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.execute(MySQLComQueryPacketExecutor.java:92) shardingsphere-proxy | at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:110) shardingsphere-proxy | at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:77) shardingsphere-proxy | at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) shardingsphere-proxy | at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) shardingsphere-proxy | at java.base/java.lang.Thread.run(Thread.java:833)

Which version of ShardingSphere did you use?

maybe 5.3.1? i dont know. im use this docker image apache/shardingsphere-proxy:latest docker compose file from here examples/docker/shardingsphere-proxy/sharding

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

ShardingSphere-Proxy

SQL command

mysql> show databases; +--------------------+ | schema_name | +--------------------+ | information_schema | | mysql | | performance_schema | | sharding_db | | shardingsphere | | sql_hint | | sys | +--------------------+ 7 rows in set (0.01 sec)

mysql> use information_schema; Database changed mysql> show tables; +------------------------------+------------+ | Tables_in_information_schema | Table_type | +------------------------------+------------+ | TABLES | BASE TABLE | | SCHEMATA | BASE TABLE | | COLUMNS | BASE TABLE | | ENGINES | BASE TABLE | | ROUTINES | BASE TABLE | | PARAMETERS | BASE TABLE | | VIEWS | BASE TABLE | +------------------------------+------------+ 7 rows in set (0.02 sec)

mysql> select * from tables; Empty set

mysql> select * from columns; 30000 - Unknown exception: Index 20 out of bounds for length 20 mysql> select * from columns; 30000 - Unknown exception: Index 20 out of bounds for length 20 mysql>

error logs

shardingsphere-proxy | [ERROR] 2023-03-01 03:27:18.777 [Connection-1-ThreadExecutor] o.a.s.p.f.c.CommandExecutorTask - Exception occur: shardingsphere-proxy | java.lang.IndexOutOfBoundsException: Index 20 out of bounds for length 20 shardingsphere-proxy | at java.base/jdk.internal.util.Preconditions.outOfBounds(Preconditions.java:64) shardingsphere-proxy | at java.base/jdk.internal.util.Preconditions.outOfBoundsCheckIndex(Preconditions.java:70) shardingsphere-proxy | at java.base/jdk.internal.util.Preconditions.checkIndex(Preconditions.java:266) shardingsphere-proxy | at java.base/java.util.Objects.checkIndex(Objects.java:359) shardingsphere-proxy | at java.base/java.util.ArrayList.get(ArrayList.java:427) shardingsphere-proxy | at org.apache.shardingsphere.sqlfederation.advanced.resultset.SQLFederationResultSetMetaData.getColumnName(SQLFederationResultSetMetaData.java:111) shardingsphere-proxy | at org.apache.shardingsphere.infra.executor.sql.execute.result.query.impl.driver.jdbc.metadata.JDBCQueryResultMetaData.getColumnName(JDBCQueryResultMetaData.java:47) shardingsphere-proxy | at org.apache.shardingsphere.proxy.backend.response.header.query.QueryHeaderBuilderEngine.build(QueryHeaderBuilderEngine.java:51) shardingsphere-proxy | at org.apache.shardingsphere.proxy.backend.communication.DatabaseCommunicationEngine.processExecuteFederation(DatabaseCommunicationEngine.java:226) shardingsphere-proxy | at org.apache.shardingsphere.proxy.backend.communication.DatabaseCommunicationEngine.execute(DatabaseCommunicationEngine.java:168) shardingsphere-proxy | at org.apache.shardingsphere.proxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.execute(MySQLComQueryPacketExecutor.java:92) shardingsphere-proxy | at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:110) shardingsphere-proxy | at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:77) shardingsphere-proxy | at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) shardingsphere-proxy | at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) shardingsphere-proxy | at java.base/java.lang.Thread.run(Thread.java:833)

Reason analyze (If you can)

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

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

hhniao avatar Mar 01 '23 03:03 hhniao

The information schema is still in the experimental stage. @tuichenchuxin hi, please have a lool.

FlyingZC avatar Mar 01 '23 04:03 FlyingZC

Thanks @FlyingZC . information schema is still in the experimental stage. @hhniao you may get more details by this https://github.com/apache/shardingsphere/issues/24378 Empty results are acceptable, but exceptions should not be raised. Do you have any interest to fix it?

tuichenchuxin avatar Mar 01 '23 05:03 tuichenchuxin

@tuichenchuxin sorry, i dont know java. When I saw this #11994 I thought it had been solved

hhniao avatar Mar 01 '23 06:03 hhniao

im use it for laravel. it is not work, when i execute command php artisan migrate this sql will be execute. select * from information_schema.tables where table_schema = 'sharding_db' and table_name = 'migrations' and table_type = 'BASE TABLE' create table migrations, when last sql empty result.

hhniao avatar Mar 01 '23 06:03 hhniao

Problem Understanding

You're experiencing issues with ShardingSphere-Proxy when executing these queries:

  1. SELECT * FROM information_schema.tables; - Returns empty results + NullPointerException
  2. SELECT * FROM information_schema.columns; - IndexOutOfBoundsException: "Index 20 out of bounds for length 20"

Root Cause Analysis

After code analysis, the core reasons for this issue are:

🔍 Current Implementation Status

  • ✅ information_schema.SCHEMATA table is fully implemented (related bugs fixed in August 2024)
  • ❌ TABLES and COLUMNS tables lack dedicated implementation, relying on generic DatabaseMetaDataExecutor

🎯 Root Causes

  1. Experimental Feature Limitation: information_schema in ShardingSphere is still marked as experimental
  2. Incomplete Implementation: Only SCHEMATA has dedicated handlers, other tables use generic processing logic
  3. Insufficient Edge Case Handling: DatabaseMetaDataExecutor doesn't handle all edge cases properly in certain metadata scenarios

📊 Current Status

  • Issue remains in "status: volunteer wanted" state
  • Master branch doesn't yet include complete fixes for TABLES/COLUMNS
  • Community contribution needed for full implementation

Solutions

🚀 Workaround Solutions (Recommended)

  1. Bypass ShardingSphere: Use direct database connection for Laravel migrate operations
  2. Version Upgrade: Use latest ShardingSphere version (though issues may persist)
  3. Configuration Adjustment: Configure Laravel to skip information_schema related queries

🔧 Long-term Solution

Requires community contribution to implement:

  • Dedicated SelectInformationTablesExecutor and SelectInformationColumnsExecutor
  • Improved exception handling and boundary checks
  • Complete unit test coverage

Recommendation

Given this is an experimental feature, it's advisable to avoid information_schema related queries in production environments or connect directly to the native database for schema-related operations.

terrymanu avatar Nov 17 '25 04:11 terrymanu