terrymanu commented on issue #38709:
URL:
https://github.com/apache/shardingsphere/issues/38709#issuecomment-4556039415
Hi @dangmingyang, thanks for reporting this.
I agree this is a bug. Even if ShardingSphere-Proxy does not currently
support MySQL temporary table DDL, it should fail with a clear
unsupported-operation error before executing the SQL, instead of sending
`CREATE TEMPORARY TABLE` to the backend and then failing during metadata
refresh with a `NullPointerException`.
The root cause is that MySQL temporary tables are session-scoped and are not
visible through the normal `information_schema` metadata loading path used by
ShardingSphere's push-down metadata refresh. After the backend successfully
creates the temporary table, ShardingSphere tries to refresh table metadata as
if it were a normal table, receives no table metadata, and later dereferences a
null table object. This also explains the follow-up behavior: MySQL reports
that the temporary table already exists in the session, while ShardingSphere
metadata still does not contain it, so later DML is rejected as `Table or view
'test_temp' does not exist`.
A reasonable fix for now is to explicitly reject MySQL `CREATE TEMPORARY
TABLE` and `DROP TEMPORARY TABLE` before backend execution, for example by
throwing `UnsupportedSQLOperationException`, so users receive a deterministic
unsupported-SQL error and ShardingSphere does not enter metadata refresh for
temporary tables.
A PR from the community is welcome. The suggested scope is:
- Preserve or expose the `TEMPORARY` semantic in the parsed MySQL DDL
statement.
- Add a pre-execution validation path for temporary table DDL in
Proxy/runtime.
- Return a clear unsupported-operation exception instead of executing the
SQL.
- Add regression tests covering `CREATE TEMPORARY TABLE` / `DROP TEMPORARY
TABLE`, ensuring no NPE and no metadata refresh attempt for temporary tables.
Suggested labels: `type: bug`, `in: Proxy`, `in: Kernel`, `in: SQL parse`,
`db: MySQL`.
The reply above is based on the analysis below; the detailed reasoning is
kept here for reference and follow-up contributors.
## Reference Analysis
### Issue Type
Bug.
The reported behavior is not an unsupported-feature message. It is an
internal `NullPointerException` after the backend has already executed the
temporary-table DDL.
### Confidence
High.
The issue report is internally consistent, and the code path matches the
reported symptoms.
### Evidence
- MySQL grammar accepts temporary table syntax:
-
`parser/sql/engine/dialect/mysql/src/main/antlr4/imports/mysql/DDLStatement.g4`
- `CREATE TEMPORARY? TABLE ...`
- `DROP TEMPORARY? ...`
- Parser test data already contains temporary-table DDL cases:
- `test/it/parser/src/main/resources/sql/supported/ddl/create-table.xml`
- `test/it/parser/src/main/resources/sql/supported/ddl/drop-table.xml`
- The common `CreateTableStatement` model does not currently expose a
`temporary` flag:
-
`parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/statement/type/ddl/table/CreateTableStatement.java`
- Push-down metadata refresh treats `CreateTableStatement` as refreshable:
-
`mode/core/src/main/java/org/apache/shardingsphere/mode/metadata/refresher/pushdown/PushDownMetaDataRefreshEngine.java`
- The create-table refresher loads table metadata and then persists it:
-
`mode/core/src/main/java/org/apache/shardingsphere/mode/metadata/refresher/pushdown/type/table/CreateTablePushDownMetaDataRefresher.java`
- MySQL metadata loading relies on `information_schema`, where session
temporary tables are not visible to the normal table metadata query:
-
`database/connector/dialect/mysql/src/main/java/org/apache/shardingsphere/database/connector/mysql/metadata/data/loader/MySQLMetaDataLoader.java`
- The metadata persistence path dereferences table metadata and can hit the
reported null table object:
-
`mode/core/src/main/java/org/apache/shardingsphere/mode/metadata/persist/metadata/service/TableMetaDataPersistService.java`
- A suitable exception type already exists:
-
`infra/common/src/main/java/org/apache/shardingsphere/infra/exception/generic/UnsupportedSQLOperationException.java`
### Root Cause
ShardingSphere accepts MySQL temporary-table DDL at parse time and allows it
to reach backend execution.
After execution, the push-down metadata refresh path assumes the created
object is a normal persistent table. Because MySQL temporary tables are
session-scoped and are not returned by the normal `information_schema` metadata
loader, ShardingSphere cannot load table metadata, resulting in a null table
metadata object and then an internal NPE.
### Expected Behavior
Before full temporary-table support exists, ShardingSphere-Proxy should
reject MySQL temporary-table DDL clearly and deterministically.
For example:
```text
Unsupported SQL operation: CREATE TEMPORARY TABLE.
or an equivalent ShardingSphere unsupported-operation error.
It should not execute the SQL first, should not attempt normal metadata
refresh for temporary tables, and should not expose a NullPointerException.
Recommended Fix
Implement the minimal unsupported-feature guard:
Carry the TEMPORARY attribute from the MySQL parser into the DDL statement
model, or otherwise make it detectable before execution.
Detect MySQL CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE before backend
execution.
Throw UnsupportedSQLOperationException or the project-standard unsupported
SQL exception.
Ensure temporary-table DDL does not enter push-down metadata refresh.
Add regression tests for both create and drop temporary table DDL.
Duplicate Check
No exact duplicate or existing merged fix was found from the current issue
text, the reported NPE message, or the temporary-table scenario. Related older
issues appear to cover different metadata-loading cases and do not resolve this
specific Proxy temporary-table DDL path.
Next Action
Keep the issue open as a bug and invite a community PR implementing the
explicit unsupported-operation path for MySQL temporary-table DDL.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
