tiflow
tiflow copied to clipboard
pingcap
Why does DM synchronize when processing the dll statement , need the super permission of the database
What did you do?
"subTaskStatus": [
{
"name": "tt001",
"stage": "Paused",
"unit": "Sync",
"result": {
"isCanceled": false,
"errors": [
{
"ErrCode": 10006,
"ErrClass": "database",
"ErrScope": "downstream",
"ErrLevel": "high",
"Message": "startLocation: [position: (mysql-bin.254102, 3287139), gtid-set: 2d4a7176-a010-11ed-b3e3-fa163e61dfdf:1-204603540,8697b88c-3151-11ec-9386-fa163e9e65e0:1-106366178], endLocation: [position: (mysql-bin.254102, 3287374), gtid-set: 2d4a7176-a010-11ed-b3e3-fa163e61dfdf:1-204603541,8697b88c-3151-11ec-9386-fa163e9e65e0:1-106366178], origin SQL: [Alter table `tmp_data`.`tmp_tb` \r\n\tchange `modifier` `modifier` varchar(10) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '修改人22']: execute statement failed: SET TIMESTAMP = 1713514079",
"RawCause": "Error 1227 (42000): Access denied; you need (at least one of) the SUPER/Admin privilege(s) for this operation",
"Workaround": ""
}
],
"detail": null
},
What did you expect to see?
No response
What did you see instead?
No response
Versions of the cluster
Starting component dm: /root/.tiup/components/dm/v1.15.1/tiup-dm display testdm Cluster type: dm Cluster name: testdm Cluster version: v8.0.0 Deploy user: tidb SSH type: builtin
current status of DM cluster (execute query-status <task-name> in dmctl)
No response
@Fyzo What privilege do you configure for DM? This is the privilege dm requires (https://docs.pingcap.com/tidb/stable/dm-worker-intro#downstream-database-user-privileges), could you please compare with your configuration, update if needed and check whether it works? thanks.
source db: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, ALTER, REPLICATION SLAVE, REPLICATION CLIENT ON *.*
target db: 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 *.*
why ddl need SUPER/Admin privilege ?
@Fyzo What privilege do you configure for DM? This is the privilege dm requires (https://docs.pingcap.com/tidb/stable/dm-worker-intro#downstream-database-user-privileges), could you please compare with your configuration, update if needed and check whether it works? thanks.
@Fyzo Could you please provide more detailed information so that we can further investigate? 1, source type and version 2, target tidb version 3, sql executed which could trigger this issue 4, dm and TiDB logs
@Fyzo Could you please provide more detailed information so that we can further investigate? 1, source type and version 2, target tidb version 3, sql executed which could trigger this issue 4, dm and TiDB logs
1, source type and version ： MySQL8.0 2, target tidb version: MySQL8.0 (when tidb , dm is ok) 3, sql executed which could trigger this issue： alter table add column 4, dm and TiDB logs： see attach file tt.log
@Fyzo DM is designed to migrate MySQL-compatible databases (such as MySQL, MariaDB, and Aurora MySQL) into TiDB, DM target must be TiDB.
@Fyzo DM is designed to migrate MySQL-compatible databases (such as MySQL, MariaDB, and Aurora MySQL) into TiDB, DM target must be TiDB.
i see, but can you tell why need super privileges,(where is the code line ) ,thanks
@Fyzo DM is designed to migrate MySQL-compatible databases (such as MySQL, MariaDB, and Aurora MySQL) into TiDB, DM target must be TiDB.
i see, but can you tell why need super privileges,(where is the code line ) ,thanks
The error happens when DM executes SET TIMESTAMP = 1713514079 to target database, you can check why your target database require such privilege.
