Importing mysql5.8 dump into mariadb 10.5 does not let me use already created users

Question

I've a very fresh installation of mariadb-server-10.5 (1:10.5.15-0+deb11u1) on a freshly installed debian 11.1 .

On the old machine with mysql-server (5.5.9999+default) and debian 9.6 I created a dump like this:

mysqldump -u root -pSOMEPW --all-databases > all_databases.dump

and I loaded this dump on the new server:

source /path/to/all_databases.dump

. The source took a while, did not result any error, however it beeped once at the end (no visible error or warning message).

Checking the mysql.user table it has only 3 entries for root, mysql and mariadb.sys , so I tried to create users (which were existing and used on the old machine) with this command:

create user 'testuser'@'localhost' identified by 'pw';

but it result this error:

ERROR 1396 (HY000): Operation CREATE USER failed for 'testuser'@'localhost'

.

With a short script checking all the tables of the mysql db the 'testuser' appears in 3 different tables, but as a User only in the db table twice like this:

| Host      | Db            | User          | Select_priv
| localhost | somedb        | testuser      | Y
| localhost | somedbp2      | testuser      | Y

.

I think that might cause create user to fail.

How could I fix this issue without losing the information in the db table?

Thanks.

The error is shown because the user already exists? You can check that using: `select user,host from mysql.user;` — Luuk, Jul 09 '22 at 09:31
Check if the rights assigned to the user are OK, using `SHOW GRANTS FOR 'testuser'@'localhost'` (and compare that with your old server. — Luuk, Jul 09 '22 at 09:35
mysql.user is a view, not a table. Did you run `mysql_upgrade --force` after import? — Georg Richter, Jul 10 '22 at 03:29

score 0 · Answer 1 · answered Jul 16 '22 at 09:17

In general you need to run mysql_upgrade whenever you switch to a more recent MySQL or MariaDB release, or after importing a backup taken from an older major version.

This is especially true for MariaDB 10.4 and later when importing from MySQL or from MariaDB 10.3 or earlier, as the internal privilege tables changed substantially with 10.4.

mysql.user table was replaced by mysql.global_priv in 10.4, allowing for more fine grained authentication control, e.g. supporting multiple authentication plugins for a single user.

So now mysql.user is just a VIEW presenting information from mysql.global_priv in a backwards compatible way. Simple information like user and host name can still be modified via that view directly as it is an updateable view, but this does not work for the more complex columns.

And commands like CREATE USER now directly operate on the mysql.global_priv table anyway, the errors you are getting are due to that table not being present in your imported dump.

The good news is: mysql_upgrade will take care of the necessary conversion, and after that CREATE USER should work again.

See also: https://mariadb.com/kb/en/mysql_upgrade/

See also: https://mariadb.com/kb/en/mysqlglobal_priv-table/

Importing mysql5.8 dump into mariadb 10.5 does not let me use already created users

1 Answers1