fbpx

Whilst restoring a MariaDB Backup from one Virtualmin server to another, MySQL create and grant errors

Background

On a legacy server with an older version of MariaDB, restoring to a newer Virtualmin server that run MySQL 8 might be problematic. The errors manifest themselves like so:

Restore failed : SQL grant all on `6009\_rpa\_data`.* to '6009'@'localhost' with grant option failed : You are not allowed to create a user with GRANT

You might also encounter this error at other places in the system:

The server requested authentication method unknown to the client [caching_sha2_password]

There is not a magic bullet for solving this, and the reason is that permissions on MySQL 8 is more strict so you probably have to fiddle.

Solutions

Here are solutions that might work:

Do not backup database with complete backup

Sometimes the solution is to not backup the database and do it separately.

Update 15 August 2021

As the this Stack article, MySQL has issues with grant and the sequence is different.

They summarize it as so in that article:

Starting with MySQL 8 you no longer can (implicitly) create a user using the GRANT command. Use CREATE USER instead, followed by the GRANT statement:

Notice the multi step process. Please note, this article pertains to root. If you’re working on a shared server, root will not be applicable.

mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'PASSWORD';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

One way to troubleshooted is by opening two SSH windows:

SSH Window #1:

mysql -uroot
Test grant statement.
It fails

mysql> grant all privileges on database.* to [email protected] with grant option;
ERROR 1410 (42000): You are not allowed to create a user with GRANT

SSH Window #2:

mysql -uroot
Create user, which also fails!

mysql> create user [email protected] identified by 'secret';
ERROR 1396 (HY000): Operation CREATE USER failed for 'broken-user'@'localhost'
mysql> drop user [email protected];
Query OK, 0 rows affected (0.00 sec)

However, after dropping the user, and then doing grant again, it works!

mysql> create user [email protected] identified by 'secret';
Query OK, 0 rows affected (0.00 sec)

Now grant all is working!

mysql> grant all privileges on broken-user.* to [email protected] with grant option;
Query OK, 0 rows affected (0.01 sec)

Here are the original solutions:

Grant all privileges

First login as root to MySQL. Then:

grant all privileges on database.* to [email protected]'%' with grant option;

Be extremely careful of typos, as the resultant error completely throws one off.

Fiddle with Database Import

You might also want to try Edit Database / Import Database. It seems when backups are restored and MySQL Grant issues occur, these databases become orphaned and one way to re-establish the site link is to re-import them to the correct site.

Create User Again and Grant Again

In MySQL 8, user creation and grant was split. So what you could try is to create the user again, without a grant statement, and then try adding grant permissions.

References

Share this article

Share on facebook
Share on twitter
Share on linkedin

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to Top