[SOP Series 32] Troubleshooting DM Setup for MySQL 8.0 Synchronization Link Errors

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 【SOP 系列 32】DM 搭建 MySQL 8.0 同步链路报错解决思路

| username: Billmay表妹

Background

DM v2.0 introduced new features and experimental support for MySQL 8.0. However, due to some strong demands, there is a need to try DM 1.0 to support MySQL 8.0. The versions used are as follows:

Item Version
MySQL mysql-community-server-8.0.25-1.el7.x86_64
DM v1.0.0-alpha
TiDB v5.4.2

Problem Introduction

After starting the task using DM’s start-task, the program throws an error. Use query-status to view the error details:

      {
        "id": 4,
        "name": "source db dump privilege checker",
        "desc": "check dump privileges of source DB",
        "state": "fail",
        "errorMsg": "line 1 column 83 near \"FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%`\" \
github.com/pingcap/errors.AddStack\
\t/go/pkg/mod/github.com/pingcap/errors@v0.11.4/errors.go:174\
github.com/pingcap/errors.Trace\
\t/go/pkg/mod/github.com/pingcap/errors@v0.11.4/juju_adaptor.go:15\
github.com/pingcap/parser.(*Parser).Parse\
\t/go/pkg/mod/github.com/pingcap/parser@v0.0.0-20191112053614-3b43b46331d5/yy_parser.go:137\
github.com/pingcap/parser.(*Parser).ParseOneStmt\
\t/go/pkg/mod/github.com/pingcap/parser@v0.0.0-20191112053614-3b43b46331d5/yy_parser.go:156\
github.com/pingcap/tidb-tools/pkg/check.verifyPrivileges\
\t/go/pkg/mod/github.com/pingcap/tidb-tools@v3.0.7-0.20191202034632-451c58d281c7+incompatible/pkg/check/privilege.go:125\
github.com/pingcap/tidb-tools/pkg/check.(*SourceDumpPrivilegeChecker).Check\
\t/go/pkg/mod/github.com/pingcap/tidb-tools@v3.0.7-0.20191202034632-451c58d281c7+incompatible/pkg/check/privilege.go:58\
github.com/pingcap/tidb-tools/pkg/check.Do.func2\
\t/go/pkg/mod/github.com/pingcap/tidb-tools@v3.0.7-0.20191202034632-451c58d281c7+incompatible/pkg/check/check.go:118\
runtime.goexit\
\t/usr/local/go/src/runtime/asm_amd64.s:1357\
grants[0] GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%`",
        "instruction": "",
        "extra": "address of db instance - 172.16.114.221:3306"
      },
      {
        "id": 5,
        "name": "source db replication privilege checker",
        "desc": "check replication privileges of source DB",
        "state": "fail",
        "errorMsg": "line 1 column 83 near \"FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%`\" \
github.com/pingcap/errors.AddStack\
\t/go/pkg/mod/github.com/pingcap/errors@v0.11.4/errors.go:174\
github.com/pingcap/errors.Trace\
\t/go/pkg/mod/github.com/pingcap/errors@v0.11.4/juju_adaptor.go:15\
github.com/pingcap/parser.(*Parser).Parse\
\t/go/pkg/mod/github.com/pingcap/parser@v0.0.0-20191112053614-3b43b46331d5/yy_parser.go:137\
github.com/pingcap/parser.(*Parser).ParseOneStmt\
\t/go/pkg/mod/github.com/pingcap/parser@v0.0.0-20191112053614-3b43b46331d5/yy_parser.go:156\
github.com/pingcap/tidb-tools/pkg/check.verifyPrivileges\
\t/go/pkg/mod/github.com/pingcap/tidb-tools@v3.0.7-0.20191202034632-451c58d281c7+incompatible/pkg/check/privilege.go:125\
github.com/pingcap/tidb-tools/pkg/check.(*SourceReplicatePrivilegeChecker).Check\
\t/go/pkg/mod/github.com/pingcap/tidb-tools@v3.0.7-0.20191202034632-451c58d281c7+incompatible/pkg/check/privilege.go:96\
github.com/pingcap/tidb-tools/pkg/check.Do.func2\
\t/go/pkg/mod/github.com/pingcap/tidb-tools@v3.0.7-0.20191202034632-451c58d281c7+incompatible/pkg/check/check.go:118\
runtime.goexit\
\t/usr/local/go/src/runtime/asm_amd64.s:1357\
grants[0] GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%`",
        "instruction": "",
        "extra": "address of db instance - 172.16.114.221:3306"
      },

Direct Cause

From the stack information, we can see that the error is thrown by SourceDumpPrivilegeChecker and SourceReplicatePrivilegeChecker, both ultimately failing at check.verifyPrivileges. Both SourceDumpPrivilegeChecker and SourceReplicatePrivilegeChecker are located in the github.com/pingcap/tidb-tools/pkg/check package, with the code as follows:

// SourceDumpPrivilegeChecker source code
func (pc *SourceDumpPrivilegeChecker) Check(ctx context.Context) *Result {
  result := &Result{
    Name:  pc.Name(),
    Desc:  "check dump privileges of source DB",
    State: StateFailure,
    Extra: fmt.Sprintf("address of db instance - %s:%d", pc.dbinfo.Host, pc.dbinfo.Port),
  }

  grants, err := dbutil.ShowGrants(ctx, pc.db, "", "")
  if (err != nil) {
    markCheckError(result, err)
    return result
  }

  verifyPrivileges(result, grants, dumpPrivileges)
  return result
}

// SourceDumpPrivilegeChecker source code
func (pc *SourceDumpPrivilegeChecker) Name() string {
  return "source db dump privilege checker"
}

//
func (pc *SourceReplicatePrivilegeChecker) Check(ctx context.Context) *Result {
  result := &Result{
    Name:  pc.Name(),
    Desc:  "check replication privileges of source DB",
    State: StateFailure,
    Extra: fmt.Sprintf("address of db instance - %s:%d", pc.dbinfo.Host, pc.dbinfo.Port),
  }

  grants, err := dbutil.ShowGrants(ctx, pc.db, "", "")
  if (err != nil) {
    markCheckError(result, err)
    return result
  }

  verifyPrivileges(result, grants, replicationPrivileges)
  return result
}

Both methods first call dbutil.ShowGrants and then call verifyPrivileges.

// dbutil.ShowGrants partial code
func ShowGrants(ctx context.Context, db QueryExecutor, user, host string) ([]string, error) {
  if host == "" {
    host = "%"
  }

  var query string
  if user == "" {
    // for current user.
    query = "SHOW GRANTS"
  } else {
    query = fmt.Sprintf("SHOW GRANTS FOR '%s'@'%s'", user, host)
  }

  readGrantsFunc := func() ([]string, error) {
    rows, err := db.QueryContext(ctx, query)
    if err != nil) {
      return nil, errors.Trace(err)
    }
    defer rows.Close()
....

We can see that it essentially executes the show grants for statement. We can manually execute this statement or the grant statement:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%`;

GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`%`;

Executing this statement on TiDB will result in an error.

Root Cause

The structure of the MySQL 8.0 privilege table is different from previous versions. Taking mysql.user as an example, the differences are as follows:

image.png
image.png1380×224 43.6 KB

TiDB’s privilege mechanism is compatible with MySQL 5.7, which causes the SQL statement to fail.

Workaround

Upgrade DM Version

Undoubtedly, this is always the correct approach. In the spirit of exploration, we will try other possible solutions here.

TiDB Ignores Authorization Statement Execution Errors

You can refer to PR: https://github.com/pingcap/parser/pull/1319

This has not been attempted here.

Modify DM Source Code

Idea: The error is thrown during the check phase, so we can consider commenting out the relevant check items.

In DM v1.0.0-alpha, the definition of check items is placed in the dm/checker/checker.go file, with the source code as follows:

c.checkList = append(c.checkList, check.NewMySQLBinlogEnableChecker(instance.sourceDB, instance.sourceDBinfo))
c.checkList = append(c.checkList, check.NewMySQLBinlogFormatChecker(instance.sourceDB, instance.sourceDBinfo))
c.checkList = append(c.checkList, check.NewMySQLBinlogRowImageChecker(instance.sourceDB, instance.sourceDBinfo))
//c.checkList = append(c.checkList, check.NewSourcePrivilegeChecker(instance.sourceDB, instance.sourceDBinfo))
c.checkList = append(c.checkList, check.NewTablesChecker(instance.sourceDB, instance.sourceDBinfo, checkTables))

Clearly, NewSourcePrivilegeChecker is the check item that synchronizes the error. We comment out this line of code, recompile dm-worker, dm-master, and dmctl, and replace the corresponding binaries in the test environment. Restart the synchronization task. The program runs smoothly.

Special Note:

  • Here, only the minimum permissions are given to the synchronization user: SELECT, RELOAD, REPLICATION SLAVE, REPLICATION CLIENT, SHOW VIEW.
  • This method has not been fully verified, so do not use it in a production environment.