mysql 8.0 incompatibility - show grants using

[luyomo]

What did you do?

Sync data from Aurora 8.0.23 to TiDB Cloud using DM. And failed to create the task.

What did you expect to see?

Succeed to sync data from Aurora to TiDB Cloud.

What did you see instead?

detail: {                                                                                                                                                           [23/4699]
            "results": [                                                                                                                                                         
                    {                                                                                                                                                            
                            "id": 0,                                                                                                                                             
                            "name": "mysql_version",                                                                                                                             
                            "desc": "check whether mysql version is satisfied",                                                                                                  
                            "state": "warn",                                                                                                                                     
                            "errors": [                                                                                                                                          
                                    {                                                                                                                                            
                                            "severity": "warn",                                                                                                                  
                                            "short_error": "version suggested less than 8.0.0 but got 8.0.23"                                                                    
                                    }                                                                                                                                            
                            ],                                                                                                                                                   
                            "extra": "address of db instance - ar3vyl3zlmk15x.cxmxisy1o2a2.us-east-1.rds.amazonaws.com:3306"                                                     
                    },                                                                                                                                                           
                    {                                                                                                                                                            
                            "id": 6,                                                                                                                                             
                            "name": "source db replication privilege checker",                                                                                                   
                            "desc": "check replication privileges of source DB",                                                                                                 
                            "state": "fail",                                                                                                                                     
                            "errors": [                                                                                                                                          
                                    {                                                                                                                                            
                                            "severity": "fail",                                                                                                                  
                                            "short_error": "Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for 

the right syntax to use near 'USING rds_superuser_role@%' at line 1"
}
],
"extra": "address of db instance - ar3vyl3zlmk15x.cxmxisy1o2a2.us-east-1.rds.amazonaws.com:3306"
},
{
"id": 1,
"name": "source db dump privilege checker",
"desc": "check dump privileges of source DB",
"state": "fail",
"errors": [
{
"severity": "fail",
"short_error": "Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for
the right syntax to use near 'USING rds_superuser_role@%' at line 1"
}
],
"extra": "address of db instance - ar3vyl3zlmk15x.cxmxisy1o2a2.us-east-1.rds.amazonaws.com:3306"
}
],
"summary": {
"passed": false,
"total": 10,
"successful": 7,
"failed": 2,
"warning": 1
}
}"

Versions of the cluster

DM version (run dmctl -V or dm-worker -V or dm-master -V):

tiup dmctl -V
tiup is checking updates for component dmctl ...
Starting component `dmctl`: /home/admin/.tiup/components/dmctl/v6.1.0/dmctl/dmctl -V
Release Version: v6.1.0
Git Commit Hash: 9f5e3cea2fae0a3dce6cc5ca79e3bcd654b8aa99
Git Branch: heads/refs/tags/v6.1.0
UTC Build Time: 2022-06-07 11:55:24
Go Version: go version go1.18.2 linux/amd64

Upstream MySQL/MariaDB server version:

8.0.23

Downstream TiDB cluster version (execute SELECT tidb_version(); in a MySQL client):

MySQL [test]> SELECT tidb_version();`
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                                          |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v5.4.0-20220421
Edition: Enterprise
Git Commit Hash: 5c597dc4f7017eab7935705a347c96c097711ce6
Git Branch: heads/refs/tags/v5.4.0-20220421
UTC Build Time: 2022-04-21 10:42:03
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

How did you deploy DM: tiup or manually?

tiup dm deploy datasync v6.1.0 /opt/tidb/dm-cluster.yml

Other interesting information (system version, hardware config, etc):

>
>

current status of DM cluster (execute query-status <task-name> in dmctl)

(paste current status of DM cluster here)

[luyomo]

MySQL [(none)]> SHOW GRANTS
-> ;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for admin@% |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE ROLE, DROP ROLE ON . TO admin@% WITH GRANT OPTION |
| GRANT APPLICATION_PASSWORD_ADMIN,CONNECTION_ADMIN,REPLICATION_APPLIER,ROLE_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,XA_RECOVER_ADMIN ON . TO admin@% WITH GRANT OPTION |
| GRANT rds_superuser_role@% TO admin@% |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.001 sec)

MySQL [(none)]> SHOW GRANT FOR root@% USING rds_superuser_role@%;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GRANT FOR root@% USING rds_superuser_role@%' at line 1

[luyomo]

MySQL [(none)]> SHOW GRANTS FOR admin@% USING rds_superuser_role@%;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for admin@% |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE ROLE, DROP ROLE ON . TO admin@% WITH GRANT OPTION |
| GRANT APPLICATION_PASSWORD_ADMIN,CONNECTION_ADMIN,REPLICATION_APPLIER,ROLE_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,XA_RECOVER_ADMIN ON . TO admin@% WITH GRANT OPTION |
| GRANT rds_superuser_role@% TO admin@% |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.001 sec)

[dveeden]

This may be related to #6381 and pingcap/tidb#7968

The new syntax is listed on https://dev.mysql.com/doc/refman/8.0/en/show-grants.html

[dveeden]

Looks like the syntax is already supported in TiDB:

sql> SHOW GRANTS FOR 'root'@'%';
+-------------------------------------------------------------+
| Grants for root@%                                           |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
| GRANT 'myrole'@'%' TO 'root'@'%'                            |
+-------------------------------------------------------------+
2 rows in set (0.0008 sec)

sql> SHOW GRANTS FOR 'root'@'%' USING 'myrole';
+-------------------------------------------------------------+
| Grants for root@%                                           |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
| GRANT 'myrole'@'%' TO 'root'@'%'                            |
+-------------------------------------------------------------+
2 rows in set (0.0008 sec)

sql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v6.2.0-alpha-489-gb90dee785
Edition: Community
Git Commit Hash: b90dee785d44db8795143d7fea5eb7dc1207b31a
Git Branch: master
UTC Build Time: 2022-07-25 06:26:24
GoVersion: go1.18
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: unistore
1 row in set (0.0009 sec)

See also: https://docs.pingcap.com/tidb/stable/sql-statement-show-grants

[lance6716]

when we found there's a role in SHOW GRANTS, we will use append USING role [, role ...] to query the grants again, so we now can get the full grants.

https://github.com/pingcap/tidb/blob/c76888143cd16287282e52f1cac555d66b565d5b/util/dbutil/variable.go#L134

But I didn't notice that there must be FOR user_or_role before USING role [, role ...], so we will construct a query with wrong syntax to upstream 😂