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: [email protected]
