Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
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.
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
|
Attachments
Activity
The first step should be to make the full list of MySQL and MariaDB grants. Then we need to decide which ones are redundant and which need to be covered by a more general level privilege. This guarantees we understand the problem completely. Perhaps for some privileges, it makes sense to implement our own version of it. danblack, can you do this?
For example: CREATE ROLE privilege could be replaced by CREATE USER, but is there a use case when one needs to be able to create roles only? MySQL believes so. Should we follow suit?
There is however a problem with the whole logical dump of USERS from MySQL. Whenever MySQL chooses to support an extra privilege, we need to do the same. This will probably become unmaintainable in the long run and we'll probably have to stop doing it at some point. On the other hand, if we want to support migrating from MySQL to MariaDB and make it as painless as possible, we need to have this.
serg If we don't support this properly, I see little value in claiming:
Experimentally this option is designed to be able to dump system information from MySQL-5.7 and 8.0 servers. SQL generated is also
experimentally compatible with MySQL-5.7/8.0. Mappings of implementation specific grants/plugins isn't always one-to-one however.
This just opens up a can of worms on expectations set. On one hand we are designing a feature for MySQL dumps but we don't cover problematic cases. This way we guarantee the feature always remains "experimental". Either we claim it only works for MariaDB, or we adjust the logic (either in mariadb-dump or in the server) to get this in a usable state.
The simplest idea is to force mariadb-dump to "comment out" unknown privileges. That's the easy way out.
On second thought, there isn't a simple way to make this future proof 
. I vote for updating the wording to:
To help in migrating from MySQL to MariaDB, this option is designed to be able to dump system information from MySQL-5.7 and 8.0 servers. SQL generated is also
|
experimentally compatible with MySQL-5.7/8.0. Mappings of implementation specific grants/plugins isn't always one-to-one however between MariaDB and MySQL and will require manual changes.
|
Documenting per last comment in the man as suggested sounds pretty good. A kb page on comparison of grants might also have value if it's not there already and would help evaluating if particular grants are useful.
An alternative to keeping track of MySQL grants added is to whitelist the ones MariaDB supports (and in which version) and version comment the others to the source mysql version. Its still a bit of can of worms but hopefully less so.
I'm less inclined to implement grant classes because its there in MySQL. We could consider them one by one however.
Adding a kb/blog on using the feature as an example is something I could do.
cvicentiu I've committed the wording change on the man page. Do any other described remedies contain value?
cvicentiuserg implementation POC bb-10.2-danielblack-MDEV-24557-mysqldump-understand-all-grants . Doesn't cover CREATE ROLE -> CREATE USER however appropriately uses executable comments on each grant.
This feature definitely wasn't designed to dump MySQL 8.0 tables in a way that can be loaded into MariaDB.
Perhaps it was supposed to do it, but then the design should've better been to read from I_S tables and create GRANT statements in the client. And implement some mapping for privileges that don't exist in MariaDB.
So, my suggestion is either keep the design and clarify the use case. Or keep the use case and redesign. Because currently they don't seem to match.
Thanks for looking/testing.
https://github.com/MariaDB/server/blob/10.3/man/mysqldump.1#L2289 "Mappings of implementation specific grants/plugins isn't always one-to-one however"
I haven't tried to map the grants across as there isn't a specific mapping. Any recommendations?