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