check replication privileges of source DB for mariadb 10.5.11

[luyomo]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do? If possible, provide a recipe for reproducing the error. 1.1 Compiled the dm directly because of the ARM version 1.2 Set up the lastest version of mariadb(5.5) 1.3 Could not pass the previlege check

The root cause is that the [REPLICATION CLIENT] has been renamed to [BINLOG MONITOR] .

2. What did you expect to see? Sync the data from mariadb to TIDB properly.

3. What did you see instead? { "result": false, "msg": "[code=26005:class=dm-master:scope=internal:level=medium], Message: fail to check synchronization configuration with type: check was failed, please see detail detail: { "results": [ { "id": 6, "name": "source db replication privilege checker", "desc": "check replication privileges of source DB", "state": "fail", "errors": [ { "severity": "fail", "short_error": "line 1 column 71 near \"MONITOR ON . TO dmuser@% IDENTIFIED BY PASSWORD '8E20346B00C26BF7B9D921E21BF21D05D055D25D'\" " } ], "extra": "address of db instance - 192.168.1.109:3306" }, { "id": 5, "name": "source db dump privilege checker", "desc": "check dump privileges of source DB", "state": "fail", "errors": [ { "severity": "fail", "short_error": "line 1 column 71 near \"MONITOR ON . TO dmuser@% IDENTIFIED BY PASSWORD '8E20346B00C26BF7B9D921E21BF21D05D055D25D'\" " } ], "extra": "address of db instance - 192.168.1.109:3306" } ], "summary": { "passed": false, "total": 8, "successful": 6, "failed": 2, "warning": 0 } }", "sources": [ ] }

4. Versions of the cluster

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

     /opt/dm/bin/dmctl --version 
     Release Version: v2.0.0-beta.2-392-gd103118d-dev
     Git Commit Hash: d103118d5cf074e34d8b7246f49cf3211f263ca1
     Git Branch: master
     UTC Build Time: 2021-07-02 09:30:52
     Go Version: go version go1.16.4 linux/arm64

   • Upstream MySQL/MariaDB server version:

     
     Enter password: 
     Welcome to the MariaDB monitor.  Commands end with ; or \g.
     Your MariaDB connection id is 94
     Server version: 10.5.11-MariaDB-1:10.5.11+maria~focal-log mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

    MySQL [(none)]> select tidb_version();

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tidb_version() | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Release Version: v5.0.2 Edition: Community Git Commit Hash: dc40a093a0058b95fc859f60d4b84337dbdff9f6 Git Branch: heads/refs/tags/v5.0.2 UTC Build Time: 2021-06-09 14:40:45 GoVersion: go1.13 Race Enabled: false TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306 Check Table Before Drop: false | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.008 sec)

    - How did you deploy DM: DM-Ansible or manually?

    manually
    ```

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

    >
    >

5. current status of DM cluster (execute query-status in dmctl)

6. Operation logs

   • Please upload dm-worker.log for every DM-worker instance if possible
   • Please upload dm-master.log if possible
   • Other interesting logs
   • Output of dmctl's commands with problems

7. Configuration of the cluster and the task

8. Screenshot/exported-PDF of Grafana dashboard or metrics' graph in Prometheus for DM if possible

[luyomo]

This is the URL for your reference, can we do the test for the mariad latest version? [REPLICATION CLIENT] has been renamed to [BINLOG MONITOR] https://github.com/saltstack/salt/issues/58297

[lance6716]

I think the mariadb(5.5) is not the latest version, there's a version restriction in doc https://docs.pingcap.com/tidb-data-migration/stable/overview#usage-restrictions

---

oh, you mean 10.5, I'll take a look

[lance6716]

to disable this precheck, you could refer to https://docs.pingcap.com/tidb-data-migration/stable/precheck#disable-checking-items to add

ignore-checking-items: ["replication_privilege"]

in the task configuration file. below is an example in integration tests.

https://github.com/pingcap/dm/blob/6aa96423db1476e8af2cbbb55a28e49bde674f88/tests/downstream_more_column/conf/dm-task-incremental.yaml#L9

[luyomo]

Worked for me. Thanks for your instruction.

[lance6716]

Worked for me. Thanks for your instruction.

Thanks for your confirmation, I'll let this issue open as a TODO.

[lance6716]

there're other limitations of MariaDB as a upstream, like password plugin https://mariadb.com/kb/en/authentication-plugin-ed25519/ so we might add supported version and limitations to official doc of DM.

For this priviledge problem, we acknowledged that parser could treat "BINLOG MONITOR" exactly same as "REPLICATION CLIENT"+"REPLICATION SLAVE".

[lance6716]

rest troublesome privileges are BINLOG REPLAY and CONNECTION ADMIN

[dveeden]

Looks like REPLICATION MASTER ADMIN might also be needed:

[2023/12/01 10:03:46.406 +01:00] [ERROR] [source_config.go:358] ["failed to get server-id, will random choose a server-id for this source"] [error="[code=10001:class=database:scope=upstream:level=high], Message: database driver error, RawCause: Error 1227 (42000): Access denied; you need (at least one of) the REPLICATION MASTER ADMIN privilege(s) for this operation, Workaround: Please check the database connection and the database config in configuration file."] [errorVerbose="[code=10001:class=database:scope=upstream:level=high], Message: database driver error, RawCause: Error 1227 (42000): Access denied; you need (at least one of) the REPLICATION MASTER ADMIN privilege(s) for this operation, Workaround: Please check the database connection and the database config in configuration file.\ngithub.com/pingcap/tiflow/dm/pkg/terror.(*Error).Delegate\n\tgithub.com/pingcap/tiflow/dm/pkg/terror/terror.go:337\ngithub.com/pingcap/tiflow/dm/pkg/terror.DBErrorAdaptArgs\n\tgithub.com/pingcap/tiflow/dm/pkg/terror/adapter.go:39\ngithub.com/pingcap/tiflow/dm/pkg/terror.DBErrorAdapt\n\tgithub.com/pingcap/tiflow/dm/pkg/terror/adapter.go:46\ngithub.com/pingcap/tiflow/dm/pkg/conn.GetSlaveServerID\n\tgithub.com/pingcap/tiflow/dm/pkg/conn/db.go:110\ngithub.com/pingcap/tiflow/dm/pkg/conn.GetAllServerID\n\tgithub.com/pingcap/tiflow/dm/pkg/conn/db.go:68\ngithub.com/pingcap/tiflow/dm/config.(*SourceConfig).AdjustServerID\n\tgithub.com/pingcap/tiflow/dm/config/source_config.go:353\ngithub.com/pingcap/tiflow/dm/config.(*SourceConfig).Adjust\n\tgithub.com/pingcap/tiflow/dm/config/source_config.go:292\ngithub.com/pingcap/tiflow/dm/master.innerCheckAndAdjustSourceConfig\n\tgithub.com/pingcap/tiflow/dm/master/server.go:1371\ngithub.com/pingcap/tiflow/dm/master.checkAndAdjustSourceConfigForDMCtl\n\tgithub.com/pingcap/tiflow/dm/master/server.go:1390\ngithub.com/pingcap/tiflow/dm/master.parseAndAdjustSourceConfig\n\tgithub.com/pingcap/tiflow/dm/master/server.go:1352\ngithub.com/pingcap/tiflow/dm/master.(*Server).OperateSource\n\tgithub.com/pingcap/tiflow/dm/master/server.go:1481\ngithub.com/pingcap/tiflow/dm/pb._Master_OperateSource_Handler.func1\n\tgithub.com/pingcap/tiflow/dm/pb/dmmaster.pb.go:4493\ngithub.com/grpc-ecosystem/go-grpc-prometheus.init.(*ServerMetrics).UnaryServerInterceptor.func3\n\tgithub.com/grpc-ecosystem/go-grpc-prometheus@v1.2.0/server_metrics.go:107\ngo.etcd.io/etcd/server/v3/etcdserver/api/v3rpc.Server.ChainUnaryServer.func7.1.1\n\tgithub.com/grpc-ecosystem/go-grpc-middleware@v1.3.0/chain.go:25\ngo.etcd.io/etcd/server/v3/etcdserver/api/v3rpc.Server.newUnaryInterceptor.func4\n\tgo.etcd.io/etcd/server/v3@v3.5.2/etcdserver/api/v3rpc/interceptor.go:71\ngo.etcd.io/etcd/server/v3/etcdserver/api/v3rpc.Server.ChainUnaryServer.func7.1.1\n\tgithub.com/grpc-ecosystem/go-grpc-middleware@v1.3.0/chain.go:25\ngo.etcd.io/etcd/server/v3/etcdserver/api/v3rpc.Server.newLogUnaryInterceptor.func3\n\tgo.etcd.io/etcd/server/v3@v3.5.2/etcdserver/api/v3rpc/interceptor.go:78\ngo.etcd.io/etcd/server/v3/etcdserver/api/v3rpc.Server.ChainUnaryServer.func7.1.1\n\tgithub.com/grpc-ecosystem/go-grpc-middleware@v1.3.0/chain.go:25\ngo.etcd.io/etcd/server/v3/etcdserver/api/v3rpc.Server.ChainUnaryServer.func7\n\tgithub.com/grpc-ecosystem/go-grpc-middleware@v1.3.0/chain.go:34\ngithub.com/pingcap/tiflow/dm/pb._Master_OperateSource_Handler\n\tgithub.com/pingcap/tiflow/dm/pb/dmmaster.pb.go:4495\ngoogle.golang.org/grpc.(*Server).processUnaryRPC\n\tgoogle.golang.org/grpc@v1.54.0/server.go:1345\ngoogle.golang.org/grpc.(*Server).handleStream\n\tgoogle.golang.org/grpc@v1.54.0/server.go:1722\ngoogle.golang.org/grpc.(*Server).serveStreams.func1.2\n\tgoogle.golang.org/grpc@v1.54.0/server.go:966\nruntime.goexit\n\truntime/asm_amd64.s:1650"]

Looks like it otherwise can't check the server_id variable and run SHOW SLAVE HOSTS. Maybe only the latter, in case the error message is a bit misleading.