apache / apache/shardingsphere

Table or view does not exist for SINGLE tables after application automatically restarts

Open
#39,084 5 comments 0 reactions 0 assignees View on GitHub
in: JDBC type: bug
Dominant language
Java
Stars
20.8k
Forks
6.9k
Avg merge
11h 38m
Merged PRs (30d)
326

Description

### Which version of ShardingSphere did you use?

ShardingSphere-JDBC 5.5.2

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

ShardingSphere-JDBC

### Expected behavior

Tables configured by the `!SINGLE` rule should always be accessible after the application starts.

SQL executed against these tables should work normally.

### Actual behavior

After one production application instance restarted, SQLs executed against multiple tables configured by the `!SINGLE` rule failed with the following exception:

```text
Caused by: org.apache.shardingsphere.infra.exception.kernel.metadata.TableNotFoundException:
Table or view xxx does not exist.
```

For example:

```text
Caused by: org.apache.shardingsphere.infra.exception.kernel.metadata.TableNotFoundException:
Table or view risk_feed_result_record does not exist.
```

The exception was thrown during SQL binding before the SQL was sent to MySQL.

The stack trace begins with:

```text
org.apache.shardingsphere.infra.exception.kernel.metadata.TableNotFoundException:
Table or view risk_feed_result_record does not exist.

at org.apache.shardingsphere.infra.binder.engine.segment.dml.from.type.SimpleTableSegmentBinder.checkTableExists(SimpleTableSegmentBinder.java:178)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.from.type.SimpleTableSegmentBinder.bind(SimpleTableSegmentBinder.java:90)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.from.TableSegmentBinder.bind(TableSegmentBinder.java:57)
at org.apache.shardingsphere.infra.binder.engine.statement.dml.SelectStatementBinder.bind(SelectStatementBinder.java:64)
at org.apache.shardingsphere.infra.binder.engine.type.DMLStatementBindEngine.bind(DMLStatementEngine.java:61)
at org.apache.shardingsphere.infra.binder.engine.SQLBindEngine.bindSQLStatement(SQLBindEngine.java:68)
```

The final exception reported by MyBatis was:

```text
org.mybatis.spring.MyBatisSystemException

Caused by:

org.apache.ibatis.executor.ExecutorException:
Error preparing statement.

Cause:
org.apache.shardingsphere.infra.exception.kernel.metadata.TableNotFoundException:
Table or view risk_feed_result_record does not exist.
```

All affected tables physically exist in MySQL.

The issue persisted for approximately one hour and did not recover automatically.

Restarting the affected application instance resolved the problem immediately without any code or configuration changes.

### Reason analyze (If you can)

We suspect that the metadata for tables configured by the `!SINGLE` rule was not initialized correctly during application startup.

The issue affected multiple tables configured by the `!SINGLE` rule rather than a single table.

All affected tables physically exist in MySQL.

The exception was thrown from:

```text
SimpleTableSegmentBinder.checkTableExists()
```

which indicates that ShardingSphere considered the tables missing before sending SQL to MySQL.

Only one application instance experienced this issue.

Other application instances with the same configuration did not report this problem.

Restarting the affected application instance immediately resolved the issue.

Therefore, we suspect this may be related to metadata initialization or metadata cache for SINGLE tables during application startup.

### Steps to reproduce the behavior

Unfortunately, we cannot reproduce this issue consistently.

The production environment is:

- ShardingSphere-JDBC 5.5.2
- JDK 21
- Spring Boot
- MyBatis
- MySQL
- Linux
- Standalone mode

The application contains both sharding tables and tables configured by the `!SINGLE` rule.

After one production application instance restarted:

- SQLs executed against multiple tables configured by the `!SINGLE` rule failed with `TableNotFoundException`.
- All affected tables physically exist in MySQL.
- The issue persisted for approximately one hour.
- Restarting the application instance resolved the issue immediately.

Currently we are unable to reproduce the issue in the test environment.

### Simplified configuration

```yaml
mode:
type: Standalone

dataSources:
db0:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://:/
username:
password:

rules:
- !SHARDING

keyGenerators:
snowflake:
type: SNOWFLAKE

shardingAlgorithms:
snowflake-or-time-month:
type: CLASS_BASED

tables:

risk_strategy_record:
actualDataNodes: db0.risk_strategy_record_*
tableStrategy:
complex:
shardingColumns: id,create_time

- !SINGLE
tables:
- "*.*"
defaultDataSource: db0

props:
sql-show: false
sql-comment-parse-enabled: false
check-table-metadata-enabled: false
sql-parser-ignore-missing-table: true
```

### Example SQL

The SQL is a normal query against a table configured by the `!SINGLE` rule.

```sql
SELECT ...
FROM risk_feed_result_record
WHERE ...
```

The same problem also occurred on other tables configured by the `!SINGLE` rule.

### Environment

- ShardingSphere-JDBC: 5.5.2
- JDK: 21
- MySQL
- Spring Boot
- MyBatis
- Linux

### Additional information

- All affected tables physically exist in MySQL.
- The affected tables are configured by the `!SINGLE` rule.
- Only one application instance experienced this issue after restart.
- Other application instances with the same configuration did not report this issue.
- The issue persisted for approximately one hour.
- Restarting the affected application instance immediately resolved the problem.
- We would like to know whether this could be related to metadata initialization for tables managed by the `!SINGLE` rule after application startup.

Contributor guide

Open the contributing guide

Research direction

Start at SimpleTableSegmentBinder.checkTableExists(), then trace how metadata for !SINGLE tables is initialized during ShardingSphere-JDBC startup in Standalone mode. Use the reported 5.5.2, JDK 21, Spring Boot, MyBatis, and MySQL configuration to investigate the restart scenario. Done means affected existing tables remain bindable after application startup without TableNotFoundException.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, mysql, spring-boot
Domain
database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Active
Clarity
Needs clarification
Newbie friendliness
42/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.