Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-24557

Logical dump of MySQL users via MariaDB's mariadb-dump generates invalid commands

    XMLWordPrintable

Details

    • Q1/2027 Server Maintenance

    Description

      Steps to reproduce:

      1. Run MySQL Server 8.0.22 (in my case, but probably anything 8.0 that supports roles causes the same problem)
      2. Run the following commands:

      CREATE USER vicentiu@localhost identified with mysql_native_password by 'password';
      GRANT ALL ON *.* TO vicentiu@localhost;
      

      3. With mariadb-dump, attempt a logical dump from MySQL:

      client/mariadb-dump --system=all
      

      The generated output has privileges that are not supported by MariaDB, which makes importing into MariaDB impossible.

      {{code:sql}
      code:sql}
      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 `vicentiu`@`localhost`;
      GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,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 `vicentiu`@`localhost`;

       
      Importing the file with MariaDB (among other errors) one can see:
      

      You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ROLE, DROP ROLE ON . TO `vicentiu`@`localhost`' at line 1
      You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CON...' at line 1

       

      As a quick hack mariadb-dump can comment out unsupported clauses and privileges

      A better 'hack' would be go through all privileges in MySQL 5.7, 8.0, 9.0 and 26.7 and change in mariadb-dump those the we do not support match the closest or next higher privilege version.
      Should be tested with the above MySQL version to add users that each supports one of the MySQL privileges and ensure that we can map them correctly. We should also have one user with all privilege bits set to ensure we can handle this case too.

      Attachments

        Issue Links

          Activity

            People

              alice Alice Sherepa
              cvicentiu Vicențiu Ciorbaru
              Alice Sherepa Alice Sherepa
              Sergei Golubchik Sergei Golubchik
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:

                Time Tracking

                  Estimated:
                  Original Estimate - 0d
                  0d
                  Remaining:
                  Remaining Estimate - 0.5d
                  0.5d
                  Logged:
                  Time Spent - Not Specified
                  Not Specified

                  Git Integration

                    Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.