You are viewing a plain text version of this content. The canonical link for it is here.

Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2020/10/26 09:29:52 UTC

[GitHub] [shardingsphere] lwtdev opened a new issue #7918: When the table is not sharding , in 5.0.0, SQL parsing is wrong, but it is correct in 4.1.1

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:
users@infra.apache.org

[GitHub] [shardingsphere] lwtdev commented on issue #7918: When the table is not sharding , in 5.0.0, SQL parsing is wrong, but it is correct in 4.1.1

Posted by GitBox <gi...@apache.org>.

lwtdev commented on issue #7918:
URL: https://github.com/apache/shardingsphere/issues/7918#issuecomment-719393178


   @strongduanmu I have run test case use newest version(2020/10/30 15 master)。 All test cases intended to be supported are tested ok。Test results as fallow:
   
   - **Results Summary**
   
   |SQL Demo Title |SQL Demo |Sharding Column |ShardingProxy(4.1.1)No Sharding |ShardingProxy(5.0.0.RC1)No Sharding |
   |---- |---- |---- |---- |---- |
   |truncate |TRUNCATE customer; |NONE |Y |Y |
   |truncateTable |TRUNCATE TABLE customer; |NONE |Y |Y |
   |createIndex |CREATE UNIQUE INDEX idx_customer_email using btree on customer_email  (email(10)) comment 'index for email'; |NONE |Y |Y |
   |dropIndex |DROP INDEX idx_customer_email on customer_email; |NONE |Y |Y |
   |dropIndexWithAlgorithm |DROP INDEX idx_customer_email on customer_email algorithm = default; |NONE |Y |Y |
   |dropIndexPrimary |DROP INDEX \`PRIMARY\` ON customer_email; |NONE |Y |Y |
   |grant |GRANT SELECT ON db2.customer TO 'jeffrey'@'localhost'; |NONE |Y |Y |
   |setPassword |SET PASSWORD FOR 'bob'@'%.example.org' = 'auth_string'; |NONE |Y |Y |
   |setPasswordWithPasswordFunction |SET PASSWORD FOR 'bob'@'%.example.org' = PASSWORD('auth_string'); |NONE |Y |Y |
   |setCharacterSet |SET Character Set 'utf8'; |NONE |Y |Y |
   |showCreateEvent |SHOW CREATE EVENT event_name; |NONE |Y |N |
   |showCreateUser |SHOW CREATE USER \`user\`; |NONE |Y |N |
   |showCreateView |SHOW CREATE VIEW view_name; |NONE |Y |N |
   |showIndexWithDb |SHOW INDEXES from customer in db1; |NONE |Y |Y |
   |showKeysWithLike |SHOW KEYS in customer in db1 where column_name like '%id%'; |NONE |Y |Y |
   |startSlaveSqlThread |start slave sql_thread until MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = 4; |NONE |Y |Y |
   |startSlaveIoThread |start slave io_thread until RELAY_LOG_FILE  = 'log_name', RELAY_LOG_POS = 4; |NONE |Y |Y |
   |stopSlave |stop slave io_thread for channel 'channel1'; |NONE |Y |Y |
   |stopGroupReplication |STOP GROUP_REPLICATION; |NONE |Y |N |
   
   - **Unsupported SQL Details**
   
   ```sql
   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;
   java.sql.SQLException: 2Unknown exception: [null]
   
   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`;
   java.sql.SQLException: 2Unknown exception: [null]
   
   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;
   java.sql.SQLException: 2Unknown exception: [null]
   
   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;
   java.sql.SQLException: 2Unknown exception: [null]
   ```


----------------------------------------------------------------
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:
users@infra.apache.org

[GitHub] [shardingsphere] strongduanmu commented on issue #7918: When the table is not sharding , in 5.0.0, SQL parsing is wrong, but it is correct in 4.1.1

Posted by GitBox <gi...@apache.org>.

strongduanmu commented on issue #7918:
URL: https://github.com/apache/shardingsphere/issues/7918#issuecomment-719447776


   > @strongduanmu I have run all test case use newest version(2020/10/30 15 master), and found some other problems. you may have a look at this issue:
   > 
   > > When the table is not sharding , in 5.0.0, SQL parsing is wrong, but it is correct before #7978
   
   @lwtdev Thank you very much for your help, I will investigate the exception you listed.


----------------------------------------------------------------
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:
users@infra.apache.org

[GitHub] [shardingsphere] strongduanmu commented on issue #7918: When the table is not sharding , in 5.0.0, SQL parsing is wrong, but it is correct in 4.1.1

Posted by GitBox <gi...@apache.org>.

strongduanmu commented on issue #7918:
URL: https://github.com/apache/shardingsphere/issues/7918#issuecomment-718808156

## DCL Statements

@lwtdev I have checked the MySQL documentation again and found that the definition of the `SET PASSWORD` statement has changed in version 8.0, and version 5.7 supports PASSWORD systax.

```sql
SET PASSWORD [FOR user] = password_option

password_option: {
'auth_string'
| PASSWORD('auth_string')
}
```
I think we should support these two different versions of the syntax at the same time.

## DAL Satements

> Maybe we can support show create ? when we support create ? . I found that there are already some issues related to this.

Thank you very much for your suggestions. We will check similar syntax combinations later and support these SQLs as soon as possible.

> ShardingProxy(5.0.0.RC1)No Sharding
showKeysWithLike[hasShardingKey:none]; Support:false; SQL: SHOW KEYS in customer in db1 where column_name like '%id%';
java.sql.SQLException: 2Unknown exception: [Conversion = 'i']

This is caused by a sql parse error and will be fixed in this PR.

## RL Statements

> ShardingProxy(5.0.0.RC1)No Sharding
startSlaveIoThread[hasShardingKey:none]; Support:false; SQL: start slave io_thread until RELAY_LOG_FILE = 'log_name', RELAY_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', RELAY_LOG_POS = 4`'

This SQL will be supported after PR is merged.

Thank you very much for the various test cases you have been providing. This is very important for the improvement of the SS 5.X version.
In order to facilitate the quick communication and resolution of follow-up questions, would you mind sending your WeChat account to my e-mail——strongduanmu@gmail.com?

----------------------------------------------------------------
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:
users@infra.apache.org

[GitHub] [shardingsphere] lwtdev edited a comment on issue #7918: When the table is not sharding , in 5.0.0, SQL parsing is wrong, but it is correct in 4.1.1

Posted by GitBox <gi...@apache.org>.

lwtdev edited a comment on issue #7918:
URL: https://github.com/apache/shardingsphere/issues/7918#issuecomment-718344884


   @strongduanmu Glad to see your detailed reply.
   
   ## DCL Statements
   >I refer to the official MySQL documentation, the SET PASSWORD statement should not support = PASSWORD('auth_string').
   
   I try `password('auth_string')` in MySQL, it works:
   ```sql
   mysql> show variables like '%innodb_version%';
   +----------------+--------+
   | Variable_name  | Value  |
   +----------------+--------+
   | innodb_version | 5.7.23 |
   +----------------+--------+
   1 row in set, 1 warning (0.00 sec)
   
   mysql> select user,host from mysql.user where user = 'test1';
   +-------+------+
   | user  | host |
   +-------+------+
   | test1 | %    |
   +-------+------+
   1 row in set (0.00 sec)
   
   mysql> SET PASSWORD FOR 'test1'@'%' = PASSWORD('auth_string');
   Query OK, 0 rows affected, 1 warning (0.00 sec)
   ```
   
   But this usage is a bit strange， so  I adjusted this SQL, remove `password` function, and works well in 5.0.0:
   
   ```sql
   ShardingProxy(4.1.1)No Sharding
   setPassword[hasShardingKey:none];	 Support:true;	SQL: SET PASSWORD FOR 'bob'@'%.example.org' = 'auth_string';
   ds_0|SET PASSWORD FOR 'bob'@'%.example.org' = 'auth_string'|
   
   
   ShardingProxy(5.0.0.RC1)No Sharding
   setPassword[hasShardingKey:none];	 Support:true;	SQL: SET PASSWORD FOR 'bob'@'%.example.org' = 'auth_string';
   ds_00|SET PASSWORD FOR 'bob'@'%.example.org' = 'auth_string'|
   ```
   
   ## DAL Satements
   
   >The SHOW CREATE EVENT, SHOW CREATE USER and SHOW CREATE VIEW statements are not supported in 4.X and 5.X versions.
   
   I have test `create view` and `show create view` statements in 4.1.1. `create view` is not supported but 'show create view` is works well.
   ```sql
   mysql> show create view v2;
   +------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
   | View | Create View                                                                                                                            | character_set_client | collation_connection |
   +------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
   | v2   | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `customer`.`id` AS `id` from `customer` | utf8                 | utf8_general_ci      |
   +------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
   1 row in set (0.00 sec)
   ```
   Maybe we can support `show create ?`  when we support `create ?` . I found that there are already some issues related to this.
   
   
   
   >The syntax of the SHOW INDEXES and SHOW KEYS statements are as follows, the first parameter of the "from in" clause is "tbl_name", which is the ai table in the case.
   
   I adjusted this SQL, and the test results are as follows:
   ```sql
   ShardingProxy(4.1.1)No Sharding
   showKeysWithLike[hasShardingKey:none];	 Support:true;	SQL: SHOW KEYS in customer in db1 where column_name like '%id%';
   ds_0|SHOW KEYS in customer in db1 where column_name like '%id%'|
   
   
   ShardingProxy(5.0.0.RC1)No Sharding
   showKeysWithLike[hasShardingKey:none];	 Support:false;	SQL: SHOW KEYS in customer in db1 where column_name like '%id%';
   java.sql.SQLException: 2Unknown exception: [Conversion = 'i']
   ```
   ## RL Statements
   
   >According to the definition of MySQL documents, the start slave io_thread until RELAY_LOG_FILE = 'log_name', MASTER_LOG_POS = 4; statement seems to be illegal.
   
   Yes, this SQL is illegal, the grammer check is ok, but parameter for `io_thread` is illegal.I adjusted this SQL, and the test results are as follows:
   
   ```sql
   ShardingProxy(4.1.1)No Sharding
   startSlaveIoThread[hasShardingKey:none];	 Support:true;	SQL: start slave io_thread until RELAY_LOG_FILE  = 'log_name', RELAY_LOG_POS = 4;
   ds_0|start slave io_thread until RELAY_LOG_FILE  = 'log_name', RELAY_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', RELAY_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', RELAY_LOG_POS = 4`'
   ```


----------------------------------------------------------------
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:
users@infra.apache.org

[GitHub] [shardingsphere] strongduanmu edited a comment on issue #7918: When the table is not sharding , in 5.0.0, SQL parsing is wrong, but it is correct in 4.1.1

Posted by GitBox <gi...@apache.org>.

strongduanmu edited a comment on issue #7918:
URL: https://github.com/apache/shardingsphere/issues/7918#issuecomment-718808156

## DCL Statements

@lwtdev I have checked the MySQL documentation again and found that the definition of the `SET PASSWORD` statement has changed in version 8.0, and version 5.7 supports PASSWORD systax.

```sql
SET PASSWORD [FOR user] = password_option

password_option: {
'auth_string'
| PASSWORD('auth_string')
}
```
I think we should support these two different versions of the syntax at the same time.

## DAL Satements

> Maybe we can support show create ? when we support create ? . I found that there are already some issues related to this.

Thank you very much for your suggestions. We will check similar syntax combinations later and support these SQLs as soon as possible.

This is caused by a sql parse error and will be fixed in this PR.

## RL Statements

This SQL will be supported after PR is merged.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] lwtdev commented on issue #7918: When the table is not sharding , in 5.0.0, SQL parsing is wrong, but it is correct in 4.1.1

Posted by GitBox <gi...@apache.org>.

lwtdev commented on issue #7918:
URL: https://github.com/apache/shardingsphere/issues/7918#issuecomment-719145871


   @strongduanmu 
   > I think we should support these two different versions of the syntax at the same time.
   
   I will keep `setPassword` and `setPasswordWithPasswordFunction` test case the same time.


----------------------------------------------------------------
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:
users@infra.apache.org

[GitHub] [shardingsphere] lwtdev commented on issue #7918: When the table is not sharding , in 5.0.0, SQL parsing is wrong, but it is correct in 4.1.1

Posted by GitBox <gi...@apache.org>.

lwtdev commented on issue #7918:
URL: https://github.com/apache/shardingsphere/issues/7918#issuecomment-718344884


   @strongduanmu Glad to see your detailed reply.
   
   ## DCL Statements
   >I refer to the official MySQL documentation, the SET PASSWORD statement should not support = PASSWORD('auth_string').
   
   I found a demo in [MySQL documentation](https://dev.mysql.com/doc/refman/8.0/en/show-index.html)
   
   ```sql
   SET PASSWORD FOR 'jeffrey'@'localhost' = 'auth_string';
   ```
   And I try `password('auth_string')` in MySQL, it works well:
   ```sql
   mysql> show variables like '%innodb_version%';
   +----------------+--------+
   | Variable_name  | Value  |
   +----------------+--------+
   | innodb_version | 5.7.23 |
   +----------------+--------+
   1 row in set, 1 warning (0.00 sec)
   
   mysql> select user,host from mysql.user where user = 'test1';
   +-------+------+
   | user  | host |
   +-------+------+
   | test1 | %    |
   +-------+------+
   1 row in set (0.00 sec)
   
   mysql> SET PASSWORD FOR 'test1'@'%' = PASSWORD('auth_string');
   Query OK, 0 rows affected, 1 warning (0.00 sec)
   ```
   ## DAL Satements
   
   >The SHOW CREATE EVENT, SHOW CREATE USER and SHOW CREATE VIEW statements are not supported in 4.X and 5.X versions.
   
   I have test `create view` and `show create view` statements in 4.1.1. `create view` is not supported but 'show create view` is works well.
   ```sql
   mysql> show create view v2;
   +------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
   | View | Create View                                                                                                                            | character_set_client | collation_connection |
   +------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
   | v2   | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `customer`.`id` AS `id` from `customer` | utf8                 | utf8_general_ci      |
   +------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
   1 row in set (0.00 sec)
   ```
   Maybe we can support `show create ?`  when we support `create ?` . I found that there are already some problems related to this.
   
   
   
   >The syntax of the SHOW INDEXES and SHOW KEYS statements are as follows, the first parameter of the "from in" clause is "tbl_name", which is the ai table in the case.
   
   I adjusted this SQL, and the test results are as follows:
   ```sql
   ShardingProxy(4.1.1)No Sharding
   showKeysWithLike[hasShardingKey:none];	 Support:true;	SQL: SHOW KEYS in customer in db1 where column_name like '%id%';
   ds_0|SHOW KEYS in customer in db1 where column_name like '%id%'|
   
   
   ShardingProxy(5.0.0.RC1)No Sharding
   showKeysWithLike[hasShardingKey:none];	 Support:false;	SQL: SHOW KEYS in customer in db1 where column_name like '%id%';
   java.sql.SQLException: 2Unknown exception: [Conversion = 'i']
   ```
   ## RL Statements
   
   >According to the definition of MySQL documents, the start slave io_thread until RELAY_LOG_FILE = 'log_name', MASTER_LOG_POS = 4; statement seems to be illegal.
   
   Yes, this SQL is illegal, the grammer check is ok, but parameter for `io_thread` is illegal.I adjusted this SQL, and the test results are as follows:
   
   ```sql
   ShardingProxy(4.1.1)No Sharding
   startSlaveIoThread[hasShardingKey:none];	 Support:true;	SQL: start slave io_thread until RELAY_LOG_FILE  = 'log_name', RELAY_LOG_POS = 4;
   ds_0|start slave io_thread until RELAY_LOG_FILE  = 'log_name', RELAY_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', RELAY_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', RELAY_LOG_POS = 4`'
   ```


----------------------------------------------------------------
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:
users@infra.apache.org

[GitHub] [shardingsphere] strongduanmu commented on issue #7918: When the table is not sharding , in 5.0.0, SQL parsing is wrong, but it is correct in 4.1.1

Posted by GitBox <gi...@apache.org>.

strongduanmu commented on issue #7918:
URL: https://github.com/apache/shardingsphere/issues/7918#issuecomment-716470754


   @lwtdev Thank you very much for your feedback on this issue. 👍 I will investigate the reason of this bug.


----------------------------------------------------------------
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:
users@infra.apache.org

[GitHub] [shardingsphere] lwtdev edited a comment on issue #7918: When the table is not sharding , in 5.0.0, SQL parsing is wrong, but it is correct in 4.1.1

Posted by GitBox <gi...@apache.org>.

lwtdev edited a comment on issue #7918:
URL: https://github.com/apache/shardingsphere/issues/7918#issuecomment-718344884


   @strongduanmu Glad to see your detailed reply.
   
   ## DCL Statements
   >I refer to the official MySQL documentation, the SET PASSWORD statement should not support = PASSWORD('auth_string').
   
   I try `password('auth_string')` in MySQL, it works:
   ```sql
   mysql> show variables like '%innodb_version%';
   +----------------+--------+
   | Variable_name  | Value  |
   +----------------+--------+
   | innodb_version | 5.7.23 |
   +----------------+--------+
   1 row in set, 1 warning (0.00 sec)
   
   mysql> select user,host from mysql.user where user = 'test1';
   +-------+------+
   | user  | host |
   +-------+------+
   | test1 | %    |
   +-------+------+
   1 row in set (0.00 sec)
   
   mysql> SET PASSWORD FOR 'test1'@'%' = PASSWORD('auth_string');
   Query OK, 0 rows affected, 1 warning (0.00 sec)
   ```
   
   But this usage is a bit strange， so  I adjusted this SQL, remove `password` function, and works well in 5.0.0:
   
   ```sql
   ShardingProxy(4.1.1)No Sharding
   setPassword[hasShardingKey:none];	 Support:true;	SQL: SET PASSWORD FOR 'bob'@'%.example.org' = 'auth_string';
   ds_0|SET PASSWORD FOR 'bob'@'%.example.org' = 'auth_string'|
   
   
   ShardingProxy(5.0.0.RC1)No Sharding
   setPassword[hasShardingKey:none];	 Support:true;	SQL: SET PASSWORD FOR 'bob'@'%.example.org' = 'auth_string';
   ds_00|SET PASSWORD FOR 'bob'@'%.example.org' = 'auth_string'|
   ```
   
   ## DAL Satements
   
   >The SHOW CREATE EVENT, SHOW CREATE USER and SHOW CREATE VIEW statements are not supported in 4.X and 5.X versions.
   
   I have test `create view` and `show create view` statements in 4.1.1. `create view` is not supported but 'show create view` is works well.
   ```sql
   mysql> show create view v2;
   +------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
   | View | Create View                                                                                                                            | character_set_client | collation_connection |
   +------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
   | v2   | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `customer`.`id` AS `id` from `customer` | utf8                 | utf8_general_ci      |
   +------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
   1 row in set (0.00 sec)
   ```
   Maybe we can support `show create ?`  when we support `create ?` . I found that there are already some problems related to this.
   
   
   
   >The syntax of the SHOW INDEXES and SHOW KEYS statements are as follows, the first parameter of the "from in" clause is "tbl_name", which is the ai table in the case.
   
   I adjusted this SQL, and the test results are as follows:
   ```sql
   ShardingProxy(4.1.1)No Sharding
   showKeysWithLike[hasShardingKey:none];	 Support:true;	SQL: SHOW KEYS in customer in db1 where column_name like '%id%';
   ds_0|SHOW KEYS in customer in db1 where column_name like '%id%'|
   
   
   ShardingProxy(5.0.0.RC1)No Sharding
   showKeysWithLike[hasShardingKey:none];	 Support:false;	SQL: SHOW KEYS in customer in db1 where column_name like '%id%';
   java.sql.SQLException: 2Unknown exception: [Conversion = 'i']
   ```
   ## RL Statements
   
   >According to the definition of MySQL documents, the start slave io_thread until RELAY_LOG_FILE = 'log_name', MASTER_LOG_POS = 4; statement seems to be illegal.
   
   Yes, this SQL is illegal, the grammer check is ok, but parameter for `io_thread` is illegal.I adjusted this SQL, and the test results are as follows:
   
   ```sql
   ShardingProxy(4.1.1)No Sharding
   startSlaveIoThread[hasShardingKey:none];	 Support:true;	SQL: start slave io_thread until RELAY_LOG_FILE  = 'log_name', RELAY_LOG_POS = 4;
   ds_0|start slave io_thread until RELAY_LOG_FILE  = 'log_name', RELAY_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', RELAY_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', RELAY_LOG_POS = 4`'
   ```


----------------------------------------------------------------
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:
users@infra.apache.org

[GitHub] [shardingsphere] tristaZero closed issue #7918: When the table is not sharding , in 5.0.0, SQL parsing is wrong, but it is correct in 4.1.1

Posted by GitBox <gi...@apache.org>.

tristaZero closed issue #7918:
URL: https://github.com/apache/shardingsphere/issues/7918


   


----------------------------------------------------------------
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:
users@infra.apache.org

[GitHub] [shardingsphere] lwtdev edited a comment on issue #7918: When the table is not sharding , in 5.0.0, SQL parsing is wrong, but it is correct in 4.1.1

Posted by GitBox <gi...@apache.org>.

lwtdev edited a comment on issue #7918:
URL: https://github.com/apache/shardingsphere/issues/7918#issuecomment-719145871


   @strongduanmu 
   > I think we should support these two different versions of the syntax at the same time.
   
   I will keep `setPassword` and `setPasswordWithPasswordFunction` test case the same time.
   
   >In order to facilitate the quick communication and resolution of follow-up questions, would you mind sending your WeChat account to my e-mail——strongduanmu@gmail.com? 😀
   
   Account has been sent, please check~
   


----------------------------------------------------------------
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:
users@infra.apache.org

[GitHub] [shardingsphere] tristaZero commented on issue #7918: When the table is not sharding , in 5.0.0, SQL parsing is wrong, but it is correct in 4.1.1

Posted by GitBox <gi...@apache.org>.

tristaZero commented on issue #7918:
URL: https://github.com/apache/shardingsphere/issues/7918#issuecomment-716472227


   @lwtdev @strongduanmu Aha, nice to meet both of you once again. 😃 


----------------------------------------------------------------
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:
users@infra.apache.org

[GitHub] [shardingsphere] strongduanmu commented on issue #7918: When the table is not sharding , in 5.0.0, SQL parsing is wrong, but it is correct in 4.1.1

Posted by GitBox <gi...@apache.org>.

strongduanmu commented on issue #7918:
URL: https://github.com/apache/shardingsphere/issues/7918#issuecomment-718052604


   @lwtdev Thank you very much for the detailed information, and according to the test case you provided, I investigated the cause of the error, and the results are as follows:
   
   ## DDL Statements
   
   The exception in the execution of the DDL statement is a bug in the 5.X version. This [PR](https://github.com/apache/shardingsphere/pull/7949) will fix this problem.
   
   ```sql
   -- @title:truncate,hasShardingKey:none
   -- DDL
   TRUNCATE customer;
   
   -- @title:truncateTable,hasShardingKey:none
   -- DDL
   TRUNCATE TABLE customer;
   
   -- @title:createIndex,hasShardingKey:none
   -- DDL
   CREATE UNIQUE INDEX idx_customer_email using btree on customer_email  (email(10)) comment 'index for email';
   
   -- @title:dropIndex,hasShardingKey:none
   -- DDL
   -- DROP INDEX idx_customer_email;
   DROP INDEX idx_customer_email on customer_email;
   
   -- @title:dropIndexWithAlgorithm,hasShardingKey:none
   -- DDL
   DROP INDEX idx_customer_email on customer_email algorithm = default;
   
   -- @title:dropIndexPrimary,hasShardingKey:none
   -- DDL
   DROP INDEX `PRIMARY` ON customer_email;
   ```
   
   ## DCL Statements
   
   The `GRANT Statement` is executed abnormally, which is also caused by a BUG, and this [PR](https://github.com/apache/shardingsphere/pull/7949) will fix it.
   I refer to the official [MySQL documentation](https://dev.mysql.com/doc/refman/8.0/en/set-password.html), the `SET PASSWORD` statement should not support `= PASSWORD('auth_string')`.
   
   
   ```sql
   -- @title:grant,hasShardingKey:none
   -- DCL
   GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
   
   -- @title:setPassword,hasShardingKey:none
   -- DCL
   SET PASSWORD FOR 'bob'@'%.example.org' = PASSWORD('auth_string');
   ```
   
   ## DAL Statements
   
   The `SET Character Statement` occur exception is caused by SQL parse error and it will be fixed together.
   The `SHOW CREATE EVENT`, `SHOW CREATE USER` and `SHOW CREATE VIEW` statements are not supported in 4.X and 5.X versions.
   
   ```sql
   -- @title:setCharacterSet,hasShardingKey:none
   -- DAL
   SET Character Set 'utf8';
   
   -- @title:showCreateEvent,hasShardingKey:none
   -- DAL
   SHOW CREATE EVENT event_name;
   
   -- @title:showCreateUser,hasShardingKey:none
   -- DAL
   SHOW CREATE USER `user`;
   
   -- @title:showCreateView,hasShardingKey:none
   -- DAL
   SHOW CREATE VIEW view_name;
   
   -- @title:showIndexes,hasShardingKey:none
   -- DAL
   SHOW INDEXES from ai in customer;
   
   -- @title:showKeys,hasShardingKey:none
   -- DAL
   SHOW KEYS from customer;
   
   -- @title:showKeys,hasShardingKey:none
   -- DAL
   SHOW KEYS in ai in customer where column_name like '%id%';
   ```
   
   The syntax of the `SHOW INDEXES` and `SHOW KEYS` statements are as follows, the first parameter of the "from in" clause is "tbl_name", which is the `ai` table in the case. If this table does not exist, a similar exception will occur——`[Can not route tables for `[ai]`, please make sure the tables are in same schema.]`:
   
   ```sql
   SHOW [EXTENDED] {INDEX | INDEXES | KEYS}
       {FROM | IN} tbl_name
       [{FROM | IN} db_name]
       [WHERE expr]
   ```
   
   ## RL Statements
   
   The exception of `start slave sql_thread until MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = 4;` and `stop slave io_thread for channel 'channel1';` statements are caused by sql parse error, and it will be fixed soon.
   
   ```sql
   -- @title:startSlaveSqlThread,hasShardingKey:none
   -- RL
   start slave sql_thread until MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = 4;
   
   -- @title:startSlaveIoThread,hasShardingKey:none
   -- RL
   start slave io_thread until RELAY_LOG_FILE  = 'log_name', MASTER_LOG_POS = 4;
   
   -- @title:stopSlave,hasShardingKey:none
   -- RL
   stop slave io_thread for channel 'channel1';
   
   -- @title:stopGroupReplication,hasShardingKey:none
   -- RL
   STOP GROUP_REPLICATION;
   ```
   
   According to the definition of MySQL documents, the `start slave io_thread until RELAY_LOG_FILE  = 'log_name', MASTER_LOG_POS = 4;` statement seems to be illegal.
   
   ```sql
   START {SLAVE | REPLICA} [thread_types] [until_option] [connection_options] [channel_option]
   
   thread_types:
       [thread_type [, thread_type] ... ]
   
   thread_type:
       IO_THREAD | SQL_THREAD
   
   until_option:
       UNTIL {   {SQL_BEFORE_GTIDS | SQL_AFTER_GTIDS} = gtid_set
             |   MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos
             |   RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos
             |   SQL_AFTER_MTS_GAPS  }
   
   connection_options:
       [USER='user_name'] [PASSWORD='user_pass'] [DEFAULT_AUTH='plugin_name'] [PLUGIN_DIR='plugin_dir']
   ```
   
   The `STOP GROUP_REPLICATION` statement is not currently supported.
   
   ---------------
   Thank you again for the detailed information, and welcome you to test again after the [PR](https://github.com/apache/shardingsphere/pull/7949) is merged. 😉
   
   


----------------------------------------------------------------
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:
users@infra.apache.org

[GitHub] [shardingsphere] lwtdev commented on issue #7918: When the table is not sharding , in 5.0.0, SQL parsing is wrong, but it is correct in 4.1.1

Posted by GitBox <gi...@apache.org>.

lwtdev commented on issue #7918:
URL: https://github.com/apache/shardingsphere/issues/7918#issuecomment-719423752


   @strongduanmu I have run all test case use newest version(2020/10/30 15 master), and found some other problems. you may have a look at this issue:
   
   > When the table is not sharding , in 5.0.0, SQL parsing is wrong, but it is correct before #7978


----------------------------------------------------------------
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:
users@infra.apache.org