DM fails when checking privileges due to unsupported syntax on MariaDB

[alastori]

What did you do?

Started a migration task from MariaDB 10.6.13 using TiUP playground and a Dockerized MariaDB source. The DM task failed during pre-checks due to invalid privilege-check logic.

(step-by-step attached) README.md

What did you expect to see?

DM should detect MariaDB and skip incompatible GRANT statements or use MariaDB-compatible syntax.

What did you see instead?

Multiple privilege checks failed due to invalid syntax in MariaDB.

"short_error": "line 1 column 64 near \"MONITOR ON *.* TO `tidb-dm`@`%` IDENTIFIED BY PASSWORD '*F140AD44180E4713D0DDC2FE46ADF5DBBACA16EE'\" "

This failed for:

• source db dump privilege checker
• source db replication privilege checker
• dumper_conn_number_checker

Versions of the cluster

DM version: v8.5.2

Upstream MySQL/MariaDB: 10.6.13-MariaDB-1:10.6.13+maria~ubu2004 mariadb.org binary distribution

Downstream TiDB: v8.5.2

Deployed with TiUP

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

{ "id": 4, "name": "source db dump privilege checker", "desc": "check dump privileges of source DB", "state": "fail", "errors": [ { "severity": "fail", "short_error": "line 1 column 64 near "MONITOR ON . TO tidb-dm@% IDENTIFIED BY PASSWORD '*F140AD44180E4713D0DDC2FE46ADF5DBBACA16EE'" " } ], "extra": "address of db instance - 127.0.0.1:3306" },

[alastori]

cc @OliverS929

[lance6716]

some old discussions https://github.com/pingcap/tiflow/issues/10160 . The known issues are all labeled with "MariaDB"

[alastori]

I did some additional tests and realized that there are two separate problems here:

1. Parser/privilege-check failure on MariaDB for three precheck items:

   • source db dump privilege checker
   • source db replication privilege checker
   • dumper_conn_number_checker The checker/SQL parser chokes on MariaDB’s privilege names (e.g. BINLOG MONITOR) and grammar.

2. Privilege check matrix is outdated. MariaDB ≥10.5 split/renamed MySQL’s REPLICATION CLIENT etc. (added BINLOG MONITOR, REPLICATION MASTER ADMIN, REPLICATION SLAVE ADMIN, …). The checker needs to conditionally verify the correct set for MariaDB ≥10.5.

Workaround

• Ensure the replication user has the right set of privileges (SELECT, PROCESS, RELOAD, BINLOG MONITOR, REPLICATION SLAVE, REPLICATION SLAVE ADMIN, REPLICATION MASTER ADMIN).

• Optionally run dmctl check-task and verify if the errors are limited to the three precheck items above

• Skip the prechecks by adding the config to the task:

  ignore-checking-items: ["all"]
  

Note:

It is possible to skip the first two with ignore-checking-items: ["replication_privilege", "dump_privilege"], but dumper_conn_number_checker isn’t skippable, so we had to fall back to ignore-checking-items: ["all"], which is too coarse.

Suggested fix

1. Detect MariaDB version ≥10.5 and switch to a MariaDB privilege map instead of MySQL’s.

2. Update the checker/parser to understand MariaDB tokens (BINLOG MONITOR, *_ADMIN privs).

3. Consider making dumper_conn_number_checker skippable, the same way the others are.

4. Doc update: publish the minimal MariaDB 10.5+ privilege set, e.g.:

   GRANT SELECT, PROCESS, RELOAD,
         BINLOG MONITOR,
         REPLICATION SLAVE,
         REPLICATION SLAVE ADMIN,
         REPLICATION MASTER ADMIN
   ON *.* TO 'tidb-dm'@'%';
   FLUSH PRIVILEGES;
   

[dveeden]

@alastori thanks this is some very useful info

[dveeden]

/type feature

[dveeden]

/remove-label may-affects-6.5

[dveeden]

/remove-label may-affects-7.1 /remove-label may-affects-7.5 /remove-label may-affects-8.1 /remove-label may-affects-8.5 /remove-label severity/major /remove-label type/bug

[ti-chi-bot[bot]]

@dveeden: The label(s) severity/major, type/bug cannot be applied. These labels are supported: MariaDB, affects-6.5, affects-7.1, affects-7.5, affects-8.1, affects-8.5, affects-9.0, bug-from-internal-test, bug-from-user, duplicate, may-affects-6.5, may-affects-7.1, may-affects-7.5, may-affects-8.1, may-affects-8.5, needs-cherry-pick-release-6.5, needs-cherry-pick-release-7.1, needs-cherry-pick-release-7.5, needs-cherry-pick-release-8.1, needs-cherry-pick-release-8.5, needs-cherry-pick-release-9.0-beta.2, nextgen, question, release-blocker, wontfix.

In response to this:

/remove-label may-affects-7.1 /remove-label may-affects-7.5 /remove-label may-affects-8.1 /remove-label may-affects-8.5 /remove-label severity/major /remove-label type/bug

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the ti-community-infra/tichi repository.

[dveeden]

/remove-severity

[dveeden]

/remove-type bug

[dveeden]

/remove-severity major

[dveeden]

Note that the behavior is documented on https://docs.pingcap.com/tidb/stable/dm-compatibility-catalog/#mariadb-notes

I think support for newer MariaDB versions is a feature, not a bug. This means that it gets added to a new major version and does not get backported unless there are specific reasons for that.