when i execute select statement, it cause an error: This version of ShardingSphere-Proxy doesn't yet support this SQL. 'You have an error in your SQL syntax'

[15806659098]

Question

I have a problem when i execute select statement. I use ShardingSphere-Proxy, version is 5.1.2 and Mysql JDBC Driver is 8.0.28. The error is as follows. It similar to another problem #17888 . I think it may be caused by PrepareStatement. Error querying database. Cause: java.sql.SQLSyntaxErrorException: This version of ShardingSphere-Proxy doesn't yet support this SQL. 'You have an error in your SQL syntax' The error may exist in file [D:\apache-tomcat-9.0.58\webapps\service_war\WEB-INF\classes\mapper\AcademicMapper.xml] The error may involve com.dao.AcademicDao.queryAcademicByMingcheng-Inline The error occurred while setting parameters SQL: SELECT knowledgename,knowledgecontent FROM Academic WHERE knowledgename like "%"?"%" Cause: java.sql.SQLSyntaxErrorException: This version of ShardingSphere-Proxy doesn't yet support this SQL. 'You have an error in your SQL syntax'; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: This version of ShardingSphere-Proxy doesn't yet support this SQL. 'You have an error in your SQL syntax' The select statement in my AcademicMapper.xml show in next picture. It has no problem when i use MySql connected directly. Thanks.

17888

[strongduanmu]

@15806659098 Thank you for your feedback. What is your sharding configuration?

[15806659098]

Only read-write separation is configured. The server.xml as follows:

rules:
- !AUTHORITY
users:
- root@%:987534
provider:
type: ALL_PRIVILEGES_PERMITTED
props:
sql-show: true
proxy-mysql-default-version: 5.7.38
proxy-default-port: 3310

The config-readwrite-splitting.yaml as follows.

databaseName: test
dataSources:
master:
url: jdbc:mysql://10.30.24.174:3306/tcm?useUnicode=true&useJDBCCompliantTimezoneShift=true&serverTimezone=UTC&characterEncoding=utf8&useSSL=false
username: root
password: 98753
connectionTimeoutMilliseconds: 3000
idleTimeoutMilliseconds: 5000
maxLifetimeMilliseconds: 9000
maxPoolSize: 50
minPoolSize: 1
slave:
url: jdbc:mysql://10.30.24.176:3306/tcm?useUnicode=true&useJDBCCompliantTimezoneShift=true&serverTimezone=UTC&characterEncoding=utf8&useSSL=false
username: root
password: 98753
connectionTimeoutMilliseconds: 3000
idleTimeoutMilliseconds: 5000
maxLifetimeMilliseconds: 9000
maxPoolSize: 50
minPoolSize: 1
rules:
- !READWRITE_SPLITTING
dataSources:
readwrite_ds:
type: Static
props:
write-data-source-name: master
read-data-source-names: master,slave
loadBalancerName: round
loadBalancers:
round:
type: ROUND_ROBIN`

[15806659098]

@strongduanmu Thank you. This is My configuration. Only read-write separation is configured. server.xml

 rules:
 - !AUTHORITY
   users:
     - root@%:987534
   provider:
     type: ALL_PRIVILEGES_PERMITTED
props:
   sql-show: true
   proxy-mysql-default-version: 5.7.38
   proxy-default-port: 3310

config-readwrite-splitting.yaml

databaseName: test
dataSources:
 master:
   url: jdbc:mysql://10.30.24.174:3306/tcm?useUnicode=true&useJDBCCompliantTimezoneShift=true&serverTimezone=UTC&characterEncoding=utf8&useSSL=false
   username: root
   password: 98753
   connectionTimeoutMilliseconds: 3000
   idleTimeoutMilliseconds: 5000
   maxLifetimeMilliseconds: 9000
   maxPoolSize: 50
   minPoolSize: 1
 slave:
   url: jdbc:mysql://10.30.24.176:3306/tcm?useUnicode=true&useJDBCCompliantTimezoneShift=true&serverTimezone=UTC&characterEncoding=utf8&useSSL=false
   username: root
   password: 98753
   connectionTimeoutMilliseconds: 3000
   idleTimeoutMilliseconds: 5000
   maxLifetimeMilliseconds: 9000
   maxPoolSize: 50
   minPoolSize: 1
rules:
- !READWRITE_SPLITTING
 dataSources:
   readwrite_ds:
     type: Static
     props:
       write-data-source-name: master
       read-data-source-names: master,slave
     loadBalancerName: round
 loadBalancers:
   round:
     type: ROUND_ROBIN

[github-actions[bot]]

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

[strongduanmu]

This issue has not been finished, so I will reopen it.