Description:
I started to use the util.dumpxxx() and dumpload() features of MySQLShell but when I tried to restore the dump, I got an error that prevented my users being restored.
I believe this is due to incorrect handling of roles in the @.users.sql file.
Looking in here I see all the roles are created using "CREATE USER .." statements and listed in alphabetical order alongside the actual users.
My first user is "andyb@'1.2.3.4' with a default role of "role_dev".
As "a" comes before "r" it fails to create the user as the role doesn't yet exist.
As this is the first user, no users are created.
How to repeat:
Create a test database containing users and roles, ensuring the roles have names starting (alphabetically) after the users.
e.g.
create role 'role_dev';
grant select on *.* to 'role_dev';
create user 'alpha'@'%' identified by '123ABC#' DEFAULT ROLE 'role_dev';
Take a dump of the database using
util.dumpInstance("/dbbackups/, {users: true, consistent:true})
on a clean database restore the dump using:
util.loadDump("c:\\dbbackups",{loadUsers: true})
At the end of the restore process you should get the error:
ERROR: While executing user accounts SQL: MySQL Error 3162 (HY000): Authorization ID `role_beta`@`%` does not exist.: CREATE USER IF NOT EXISTS 'alpha'@'1.2.3.4' IDENTIFIED WITH 'mysql_native_password' AS '*******************************' DEFAULT ROLE `role_name`@`%` . . . . .
Util.loadDump: Authorization ID `role_dev`@`%` does not exist. (MYSQLSH 3162)
Suggested fix:
Roles need to be Identified and created before any users are created
Description: I started to use the util.dumpxxx() and dumpload() features of MySQLShell but when I tried to restore the dump, I got an error that prevented my users being restored. I believe this is due to incorrect handling of roles in the @.users.sql file. Looking in here I see all the roles are created using "CREATE USER .." statements and listed in alphabetical order alongside the actual users. My first user is "andyb@'1.2.3.4' with a default role of "role_dev". As "a" comes before "r" it fails to create the user as the role doesn't yet exist. As this is the first user, no users are created. How to repeat: Create a test database containing users and roles, ensuring the roles have names starting (alphabetically) after the users. e.g. create role 'role_dev'; grant select on *.* to 'role_dev'; create user 'alpha'@'%' identified by '123ABC#' DEFAULT ROLE 'role_dev'; Take a dump of the database using util.dumpInstance("/dbbackups/, {users: true, consistent:true}) on a clean database restore the dump using: util.loadDump("c:\\dbbackups",{loadUsers: true}) At the end of the restore process you should get the error: ERROR: While executing user accounts SQL: MySQL Error 3162 (HY000): Authorization ID `role_beta`@`%` does not exist.: CREATE USER IF NOT EXISTS 'alpha'@'1.2.3.4' IDENTIFIED WITH 'mysql_native_password' AS '*******************************' DEFAULT ROLE `role_name`@`%` . . . . . Util.loadDump: Authorization ID `role_dev`@`%` does not exist. (MYSQLSH 3162) Suggested fix: Roles need to be Identified and created before any users are created