lwtdev opened a new issue #7918:
URL: https://github.com/apache/shardingsphere/issues/7918
## Bug Report
I have tested some SQL between 4.1.1 and 5.0.0-RC1, and found some SQL
parsing errors in 5.0.0, but it works well in 4.1.1.
### Which version of ShardingSphere did you use?
5.0.0-RC1 ( 2020-10-26T16 master)
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
### Expected behavior
parse corrent
### Actual behavior
parse error
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule
configuration, when exception occur etc.
- **Results in 4.1.1 and 5.0.0-RC1**
```
ShardingProxy(4.1.1)No Sharding
truncate[hasShardingKey:none]; Support:true; SQL: TRUNCATE customer;
ds_0|TRUNCATE customer|
ShardingProxy(5.0.0.RC1)No Sharding
truncate[hasShardingKey:none]; Support:false; SQL: TRUNCATE customer;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of
ShardingProxy doesn't yet support this SQL. 'Cannot find table rule with logic
table: 'customer''
ShardingProxy(4.1.1)No Sharding
truncateTable[hasShardingKey:none]; Support:true; SQL: TRUNCATE TABLE
customer;
ds_0|TRUNCATE TABLE customer|
ShardingProxy(5.0.0.RC1)No Sharding
truncateTable[hasShardingKey:none]; Support:false; SQL: TRUNCATE TABLE
customer;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of
ShardingProxy doesn't yet support this SQL. 'Cannot find table rule with logic
table: 'customer''
ShardingProxy(4.1.1)No Sharding
createIndex[hasShardingKey:none]; Support:true; SQL: CREATE UNIQUE
INDEX idx_customer_email using btree on customer_email (email(10)) comment
'index for email';
ds_0|CREATE UNIQUE INDEX idx_customer_email using btree on customer_email
(email(10)) comment 'index for email'|
ShardingProxy(5.0.0.RC1)No Sharding
createIndex[hasShardingKey:none]; Support:false; SQL: CREATE UNIQUE
INDEX idx_customer_email using btree on customer_email (email(10)) comment
'index for email';
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of
ShardingProxy doesn't yet support this SQL. 'Cannot find table rule with logic
table: 'customer_email''
ShardingProxy(4.1.1)No Sharding
dropIndex[hasShardingKey:none]; Support:true; SQL: DROP INDEX
idx_customer_email on customer_email;
ds_0|DROP INDEX idx_customer_email on customer_email|
ShardingProxy(5.0.0.RC1)No Sharding
dropIndex[hasShardingKey:none]; Support:false; SQL: DROP INDEX
idx_customer_email on customer_email;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of
ShardingProxy doesn't yet support this SQL. 'Cannot find table rule with logic
table: 'customer_email''
ShardingProxy(4.1.1)No Sharding
dropIndexWithAlgorithm[hasShardingKey:none]; Support:true; SQL: DROP INDEX
idx_customer_email on customer_email algorithm = default;
ds_0|DROP INDEX idx_customer_email on customer_email algorithm = default|
ShardingProxy(5.0.0.RC1)No Sharding
dropIndexWithAlgorithm[hasShardingKey:none]; Support:false; SQL: DROP INDEX
idx_customer_email on customer_email algorithm = default;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of
ShardingProxy doesn't yet support this SQL. 'Cannot find table rule with logic
table: 'customer_email''
ShardingProxy(4.1.1)No Sharding
dropIndexPrimary[hasShardingKey:none]; Support:true; SQL: DROP INDEX
`PRIMARY` ON customer_email;
ds_0|DROP INDEX `PRIMARY` ON customer_email|
ShardingProxy(5.0.0.RC1)No Sharding
dropIndexPrimary[hasShardingKey:none]; Support:false; SQL: DROP INDEX
`PRIMARY` ON customer_email;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of
ShardingProxy doesn't yet support this SQL. 'Cannot find table rule with logic
table: 'customer_email''
ShardingProxy(4.1.1)No Sharding
grant[hasShardingKey:none]; Support:true; SQL: GRANT SELECT ON
db2.invoice TO 'jeffrey'@'localhost';
ds_0|GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost'|
ShardingProxy(5.0.0.RC1)No Sharding
grant[hasShardingKey:none]; Support:false; SQL: GRANT SELECT ON
db2.invoice TO 'jeffrey'@'localhost';
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of
ShardingProxy doesn't yet support this SQL. 'Cannot find table rule with logic
table: 'invoice''
ShardingProxy(4.1.1)No Sharding
setPassword[hasShardingKey:none]; Support:true; SQL: SET PASSWORD FOR
'bob'@'%.example.org' = PASSWORD('auth_string');
ds_0|SET PASSWORD FOR 'bob'@'%.example.org' = PASSWORD('auth_string')|
ShardingProxy(5.0.0.RC1)No Sharding
setPassword[hasShardingKey:none]; Support:false; SQL: SET PASSWORD FOR
'bob'@'%.example.org' = PASSWORD('auth_string');
java.sql.SQLException: 2Unknown exception: [Conversion = '.']
ShardingProxy(4.1.1)No Sharding
setCharacterSet[hasShardingKey:none]; Support:true; SQL: SET
Character Set 'utf8';
ds_0|SET Character Set 'utf8'|
ShardingProxy(5.0.0.RC1)No Sharding
setCharacterSet[hasShardingKey:none]; Support:false; SQL: SET
Character Set 'utf8';
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of
ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SET Character
Set 'utf8'`'
ShardingProxy(4.1.1)No Sharding
showCreateEvent[hasShardingKey:none]; Support:true; SQL: SHOW
CREATE EVENT event_name;
ds_0|SHOW CREATE EVENT event_name|
ShardingProxy(5.0.0.RC1)No Sharding
showCreateEvent[hasShardingKey:none]; Support:false; SQL: SHOW
CREATE EVENT event_name;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of
ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SHOW CREATE
EVENT event_name`'
ShardingProxy(4.1.1)No Sharding
showCreateUser[hasShardingKey:none]; Support:true; SQL: SHOW CREATE USER
`user`;
ds_0|SHOW CREATE USER `user`|
ShardingProxy(5.0.0.RC1)No Sharding
showCreateUser[hasShardingKey:none]; Support:false; SQL: SHOW CREATE USER
`user`;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of
ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SHOW CREATE
USER `user``'
ShardingProxy(4.1.1)No Sharding
showCreateView[hasShardingKey:none]; Support:true; SQL: SHOW CREATE VIEW
view_name;
ds_0|SHOW CREATE VIEW view_name|
ShardingProxy(5.0.0.RC1)No Sharding
showCreateView[hasShardingKey:none]; Support:false; SQL: SHOW CREATE VIEW
view_name;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of
ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SHOW CREATE
VIEW view_name`'
ShardingProxy(4.1.1)No Sharding
showIndexes[hasShardingKey:none]; Support:true; SQL: SHOW INDEXES from
ai in customer;
ds_0|SHOW INDEXES from ai in customer|
ShardingProxy(5.0.0.RC1)No Sharding
showIndexes[hasShardingKey:none]; Support:false; SQL: SHOW INDEXES from
ai in customer;
java.sql.SQLException: 2Unknown exception: [Can not route tables for `[ai]`,
please make sure the tables are in same schema.]
ShardingProxy(4.1.1)No Sharding
showKeys[hasShardingKey:none]; Support:true; SQL: SHOW KEYS from
customer;
ds_0|SHOW KEYS from customer|
ShardingProxy(5.0.0.RC1)No Sharding
showKeys[hasShardingKey:none]; Support:true; SQL: SHOW KEYS from
customer;
ds_00|SHOW KEYS from customer|
ShardingProxy(4.1.1)No Sharding
showKeys[hasShardingKey:none]; Support:true; SQL: SHOW KEYS in ai in
customer where column_name like '%id%';
ds_0|SHOW KEYS in ai in customer where column_name like '%id%'|
ShardingProxy(5.0.0.RC1)No Sharding
showKeys[hasShardingKey:none]; Support:false; SQL: SHOW KEYS in ai in
customer where column_name like '%id%';
java.sql.SQLException: 2Unknown exception: [Conversion = 'i']
ShardingProxy(4.1.1)No Sharding
startSlaveSqlThread[hasShardingKey:none]; Support:true; SQL: start
slave sql_thread until MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = 4;
ds_0|start slave sql_thread until MASTER_LOG_FILE = 'log_name',
MASTER_LOG_POS = 4|
ShardingProxy(5.0.0.RC1)No Sharding
startSlaveSqlThread[hasShardingKey:none]; Support:false; SQL: start
slave sql_thread until MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = 4;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of
ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `start slave
sql_thread until MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = 4`'
ShardingProxy(4.1.1)No Sharding
startSlaveIoThread[hasShardingKey:none]; Support:true; SQL: start
slave io_thread until RELAY_LOG_FILE = 'log_name', MASTER_LOG_POS = 4;
ds_0|start slave io_thread until RELAY_LOG_FILE = 'log_name',
MASTER_LOG_POS = 4|
ShardingProxy(5.0.0.RC1)No Sharding
startSlaveIoThread[hasShardingKey:none]; Support:false; SQL: start
slave io_thread until RELAY_LOG_FILE = 'log_name', MASTER_LOG_POS = 4;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of
ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `start slave
io_thread until RELAY_LOG_FILE = 'log_name', MASTER_LOG_POS = 4`'
ShardingProxy(4.1.1)No Sharding
stopSlave[hasShardingKey:none]; Support:true; SQL: stop slave
io_thread for channel 'channel1';
ds_0|stop slave io_thread for channel 'channel1'|
ShardingProxy(5.0.0.RC1)No Sharding
stopSlave[hasShardingKey:none]; Support:false; SQL: stop slave
io_thread for channel 'channel1';
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of
ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `stop slave
io_thread for channel 'channel1'`'
ShardingProxy(4.1.1)No Sharding
stopGroupReplication[hasShardingKey:none]; Support:true; SQL: STOP
GROUP_REPLICATION;
ds_0|STOP GROUP_REPLICATION|
ShardingProxy(5.0.0.RC1)No Sharding
stopGroupReplication[hasShardingKey:none]; Support:false; SQL: STOP
GROUP_REPLICATION;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of
ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `STOP
GROUP_REPLICATION`'
```
- **4.1.1 config**
```yaml
schemaName: spsqltest_nosharding
#
dataSources:
ds_0:
url:
jdbc:mysql://127.0.0.1:3306/spsqltest_nosharding?serverTimezone=UTC&useSSL=false
username: root
password: root135
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
shardingRule:
defaultDataSourceName: ds_0
defaultDatabaseStrategy:
none:
defaultTableStrategy:
none:
```
- **5.0.0-RC1 config**
```yaml
schemaName: spsqltest_nosharding
#
dataSourceCommon:
username: root
password: root135
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
maintenanceIntervalMilliseconds: 30000
#
dataSources:
ds_00:
url:
jdbc:mysql://127.0.0.1:3306/spsqltest_nosharding?serverTimezone=UTC&useSSL=false
#
rules:
- !SHARDING
defaultDatabaseStrategy:
none:
defaultTableStrategy:
none:
```
- **SQL for creating tables**
create before shardingsphere start
```sql
-- @title:createTableCustomer
CREATE TABLE `customer` (
`id` bigint(20) NOT NULL COMMENT 'primary key',
`party_id` bigint(20) NOT NULL COMMENT 'user id',
`status` smallint(6) DEFAULT NULL COMMENT 'status 1 enable 0 disable',
PRIMARY KEY (`id`),
KEY `party_id_index` (`party_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='customer';
-- @title:createTableCustomerEmail
CREATE TABLE customer_email (
`id` bigint(20) NOT NULL COMMENT 'primary key',
`party_id` bigint(20) NOT NULL COMMENT 'user id',
`status` smallint(6) DEFAULT NULL COMMENT 'status 1 enable 0 disable',
PRIMARY KEY (`id`),
KEY `party_id_index` (`party_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='customer email';
```
- **SQL for testing**
```sql
-- @title:truncate,hasShardingKey:none
TRUNCATE customer;
-- @title:truncateTable,hasShardingKey:none
TRUNCATE TABLE customer;
-- @title:createIndex,hasShardingKey:none
CREATE UNIQUE INDEX idx_customer_email using btree on customer_email
(email(10)) comment 'index for email';
-- @title:dropIndex,hasShardingKey:none
DROP INDEX idx_customer_email on customer_email;
-- @title:dropIndexWithAlgorithm,hasShardingKey:none
DROP INDEX idx_customer_email on customer_email algorithm = default;
-- @title:dropIndexPrimary,hasShardingKey:none
DROP INDEX `PRIMARY` ON customer_email;
-- @title:grant,hasShardingKey:none
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
-- @title:setPassword,hasShardingKey:none
SET PASSWORD FOR 'bob'@'%.example.org' = PASSWORD('auth_string');
-- @title:setCharacterSet,hasShardingKey:none
SET Character Set 'utf8';
-- @title:showCreateEvent,hasShardingKey:none
SHOW CREATE EVENT event_name;
-- @title:showCreateUser,hasShardingKey:none
SHOW CREATE USER `user`;
-- @title:showCreateView,hasShardingKey:none
SHOW CREATE VIEW view_name;
-- @title:showIndexes,hasShardingKey:none
SHOW INDEXES from ai in customer;
-- @title:showKeys,hasShardingKey:none
SHOW KEYS from customer;
-- @title:showKeys,hasShardingKey:none
SHOW KEYS in ai in customer where column_name like '%id%';
-- @title:startSlaveSqlThread,hasShardingKey:none
start slave sql_thread until MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS =
4;
-- @title:startSlaveIoThread,hasShardingKey:none
start slave io_thread until RELAY_LOG_FILE = 'log_name', MASTER_LOG_POS = 4;
-- @title:stopSlave,hasShardingKey:none
stop slave io_thread for channel 'channel1';
-- @title:stopGroupReplication,hasShardingKey:none
STOP GROUP_REPLICATION;
```
### Example codes for reproduce this issue (such as a github link).
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
[email protected]
