You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "dangmingyang (via GitHub)" <gi...@apache.org> on 2025/12/25 09:33:37 UTC
[I] SQL Statement Parsing Issue with ShardingSphere-JDBC [shardingsphere]
dangmingyang opened a new issue, #37515:
URL: https://github.com/apache/shardingsphere/issues/37515
## Question
**For English only**, other languages will not accept.
Before asking a question, make sure you have:
- Googled your question.
- Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues).
- Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview).
Please pay attention on issues you submitted, because we maybe need more details.
If no response anymore and we cannot reproduce it on current information, we will **close it**.
The proxy-frontend-database-protocol-type specified for my ShardingSphere-Proxy is MySQL. I have also added new storage units and encryption rules to the ZooKeeper governance center via this ShardingSphere-Proxy.
```
REGISTER STORAGE UNIT `test_jdbc` (
URL = 'jdbc:oracle:thin:@//192.168.30.159:1521/ORCL',
USER = 'C##APP_USER',
PASSWORD = '123456',
PROPERTIES('maxPoolSize' = '50')
);
CREATE ENCRYPT RULE `user_name` (COLUMNS (
(NAME = pwd,CIPHER = pwd, ENCRYPT_ALGORITHM ( TYPE ( NAME = 'DBESShardingEncrypt', PROPERTIES ( 'sm3' = 1, 'sm4-key' = '12b366c7c59b4f949bbae8a6f1630611'))) )));
```
Then, when my ShardingSphere-JDBC connects to this Oracle database via this governance center, the SQL statement in my Java program is as follows:
```
@Insert("INSERT INTO \"user_name\" (\"id\", \"username\", \"pwd\") VALUES (#{id} ,#{username}, #{pwd})")
int insert(User user);
```
However, the result translated by ShardingSphere-JDBC and sent to the database for execution is as follows:
```
Logic SQL: INSERT INTO "user_name" ("id", "username", "pwd") VALUES (? ,?, ?)
Actual SQL: test_jdbc ::: INSERT INTO "user_name" ("id", "username", `pwd`) VALUES (?, ?, ?) ::: [6, asssf, #DBESV1#0000000Axxsfsdfsdf3VEi3gA6wEH9lLtr08B4Dba3/yimIkuqv4nobN3REZk=]
### Error updating database. Cause: java.sql.SQLSyntaxErrorException: ORA-00911
```
I noticed that the double quotes (") surrounding the encrypted field pwd have been converted to backticks (`). This does not seem to be a syntax supported by Oracle; instead, it looks like MySQL syntax.
What causes ShardingSphere-JDBC to translate this SQL statement into a MySQL-specific one? Is it because the proxy-frontend-database-protocol-type of the ShardingSphere-Proxy (which executes the DistSQL) is set to MySQL?
If this is the case, how should I configure the Oracle database correctly? I noticed that there is no option for ORACLE in the proxy-frontend-database-protocol-type configuration.
--
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: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] SQL Statement Parsing Issue with ShardingSphere-JDBC [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #37515:
URL: https://github.com/apache/shardingsphere/issues/37515#issuecomment-3691433918
Problem Understanding
I used ShardingSphere-Proxy with the front-end protocol set to MySQL to register Oracle storage units and an encryption rule in the governance center. When ShardingSphere-JDBC connects via that governance center, my logical SQL uses Oracle-style double quotes, but the rewritten SQL turns the encrypted column pwd into backticks, and Oracle returns ORA-00911.
Root Cause
Because the DistSQL was executed on a Proxy whose proxy-frontend-database-protocol-type is MySQL, the governance metadata is persisted with the protocol/database type as MySQL. ShardingSphere-JDBC then loads that governance config and performs parsing/rewriting using the MySQL dialect, so identifiers get backticks instead of Oracle double quotes. Proxy currently supports only MySQL/PostgreSQL/openGauss front-end protocols (official docs: https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-proxy/yaml-config/props/#proxy-frontend-database-protocol-type), so there is no Oracle front-end protocol to drive Oracle-style parsing/rewriting when configuring via Proxy.
Analysis
- In governance mode, the dialect for parsing/rewriting comes from the persisted “protocol type,” not from the datasource URL.
- Executing DistSQL on a MySQL-protocol Proxy fixes that protocol type as MySQL in the registry.
- ShardingSphere-JDBC reads the governance config and selects the MySQL parser/rewriter, which outputs backticks.
- Oracle cannot parse backticks, leading to ORA-00911; the encryption rule itself isn’t at fault.
- This matches the supported front-end protocols: with no Oracle front-end protocol in Proxy, using Proxy to maintain Oracle configs will naturally produce MySQL-style metadata and SQL rewriting.
Conclusion
This is a usage/compatibility issue rather than a product defect. Oracle should be configured so that governance metadata reflects the Oracle dialect and is consumed directly by ShardingSphere-JDBC; using a MySQL-protocol Proxy to create that metadata causes the MySQL dialect to be applied and leads to the observed Oracle syntax error.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] SQL Statement Parsing Issue with ShardingSphere-JDBC [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu closed issue #37515: SQL Statement Parsing Issue with ShardingSphere-JDBC
URL: https://github.com/apache/shardingsphere/issues/37515
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] SQL Statement Parsing Issue with ShardingSphere-JDBC [shardingsphere]
Posted by "dangmingyang (via GitHub)" <gi...@apache.org>.
dangmingyang commented on issue #37515:
URL: https://github.com/apache/shardingsphere/issues/37515#issuecomment-3692025225
> Please consider adopting the correct and standard approach, as any little tricks are unlikely to guarantee effectiveness.
Thank you for your support. However, I have not found any official documentation specifying the standard method to write Oracle-compatible rules to the governance center for this scenario. Could you please provide the specific operation steps or relevant documentation?
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] SQL Statement Parsing Issue with ShardingSphere-JDBC [shardingsphere]
Posted by "dangmingyang (via GitHub)" <gi...@apache.org>.
dangmingyang commented on issue #37515:
URL: https://github.com/apache/shardingsphere/issues/37515#issuecomment-3691877774
> ### Problem Understanding
> I used ShardingSphere-Proxy with the front-end protocol set to MySQL to register Oracle storage units and an encryption rule in the governance center. When ShardingSphere-JDBC connects via that governance center, my logical SQL uses Oracle-style double quotes, but the rewritten SQL turns the encrypted column pwd into backticks, and Oracle returns ORA-00911.
>
> ### Root Cause
> Because the DistSQL was executed on a Proxy whose proxy-frontend-database-protocol-type is MySQL, the governance metadata is persisted with the protocol/database type as MySQL. ShardingSphere-JDBC then loads that governance config and performs parsing/rewriting using the MySQL dialect, so identifiers get backticks instead of Oracle double quotes. Proxy currently supports only MySQL/PostgreSQL/openGauss front-end protocols (official docs: https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-proxy/yaml-config/props/#proxy-frontend-database-protocol-type), so there is no Oracle front-end protocol to drive Oracle-style parsing/rewriting when configuring via Proxy.
>
> ### Analysis
> * In governance mode, the dialect for parsing/rewriting comes from the persisted “protocol type,” not from the datasource URL.
> * Executing DistSQL on a MySQL-protocol Proxy fixes that protocol type as MySQL in the registry.
> * ShardingSphere-JDBC reads the governance config and selects the MySQL parser/rewriter, which outputs backticks.
> * Oracle cannot parse backticks, leading to ORA-00911; the encryption rule itself isn’t at fault.
> * This matches the supported front-end protocols: with no Oracle front-end protocol in Proxy, using Proxy to maintain Oracle configs will naturally produce MySQL-style metadata and SQL rewriting.
>
> ### Conclusion
> This is a usage/compatibility issue rather than a product defect. Oracle should be configured so that governance metadata reflects the Oracle dialect and is consumed directly by ShardingSphere-JDBC; using a MySQL-protocol Proxy to create that metadata causes the MySQL dialect to be applied and leads to the observed Oracle syntax error.
Does that mean we cannot configure Oracle-related rules via DistSQL? Then are we left with no choice but to edit ZooKeeper nodes manually?
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] SQL Statement Parsing Issue with ShardingSphere-JDBC [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #37515:
URL: https://github.com/apache/shardingsphere/issues/37515#issuecomment-3692863709
Proxy currently supports only MySQL/PostgreSQL/openGauss front-end protocols; there is no Oracle front-end protocol, so there is no official guide for configuring Oracle via a MySQL-protocol Proxy.
The request (writing Oracle governance config via a MySQL front-end Proxy) is unsupported, so no official documentation exists.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] SQL Statement Parsing Issue with ShardingSphere-JDBC [shardingsphere]
Posted by "dangmingyang (via GitHub)" <gi...@apache.org>.
dangmingyang commented on issue #37515:
URL: https://github.com/apache/shardingsphere/issues/37515#issuecomment-3695244073
> Proxy currently supports only MySQL/PostgreSQL/openGauss front-end protocols; there is no Oracle front-end protocol, so there is no official guide for configuring Oracle via a MySQL-protocol Proxy.
>
> The request (writing Oracle governance config via a MySQL front-end Proxy) is unsupported, so no official documentation exists.
It is not mandatory to write the Oracle governance configuration through the ShardingSphere-Proxy. The only requirement is to ensure that ShardingSphere-JDBC can normally obtain the governance configuration strategy of Oracle. Is it achieved by integrating the class org.apache.shardingsphere.mode.repository.cluster.zookeeper.ZookeeperRepository for writing operations?
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] SQL Statement Parsing Issue with ShardingSphere-JDBC [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #37515:
URL: https://github.com/apache/shardingsphere/issues/37515#issuecomment-3691988769
Please consider adopting the correct and standard approach, as any little tricks are unlikely to guarantee effectiveness.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] SQL Statement Parsing Issue with ShardingSphere-JDBC [shardingsphere]
Posted by "dangmingyang (via GitHub)" <gi...@apache.org>.
dangmingyang commented on issue #37515:
URL: https://github.com/apache/shardingsphere/issues/37515#issuecomment-3691877066
Does that mean we cannot configure Oracle-related rules via DistSQL? Then are we left with no choice but to edit ZooKeeper nodes manually?
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] SQL Statement Parsing Issue with ShardingSphere-JDBC [shardingsphere]
Posted by "dangmingyang (via GitHub)" <gi...@apache.org>.
dangmingyang commented on issue #37515:
URL: https://github.com/apache/shardingsphere/issues/37515#issuecomment-3691941026
Or should I bypass ShardingSphere-Proxy, integrate a specific SDK of ShardingSphere into my Java project, and use DistSQL configurations to set up rules compatible with Oracle syntax?
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] SQL Statement Parsing Issue with ShardingSphere-JDBC [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #37515:
URL: https://github.com/apache/shardingsphere/issues/37515#issuecomment-3696839418
Please allow me to decline to answer this question and close this discussion.
The reason is that I expect users to follow the standard way of using ShardingSphere, rather than inventing "workarounds" based on immature ideas.
Even if I were to answer "yes," it would be an immature response, as I cannot foresee the unknown potential issues in non-standard scenarios.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org