Virtualmin restore fails with mysql error

Virtualmin
1

Operating system: ubuntu
OS version: 20.04

issue mentioned here, but not resolved: Restore backup mysql user permissions error

when restoring a backup on ubuntu 20.04 with

virtualmin restore-domain --source /path/to/backup.tar.gz --all-features --all-domains

you get

Restore failed : SQL grant all on username.* to ‘ftp@domain’@‘localhost’ with grant option failed : You are not allowed to create a user with GRANT

solution mentioned doesn’t solve the problem.
this also breaks “transfer server” function from the virtualmin UI.

2

I’ve seen this before a while back. The guy wound up having to update his MySQL and then grant all permissions to root before he did the restore. It had something to do with the idea that the script can’t grant permissions to a user that doesn’t exist or something like that.

I’ll look around for it. It was a couple years ago I think I remembered seeing it.

3

I am quite sure it has something to do with the increased security model of mysql 8.x/mariadb as restore is working in ubuntu 18.04 without problems. @Ilia ?

1 Like
4

I will have to give it another try. I have saved this issue and I will test and fix it before new Virtualmin release (now is time for working on the theme).

when restoring a backup on ubuntu 20.04 with

If you do the restore from UI do you get the same error or it only happens when restoring from the CLI?

virtualmin restore-domain --source /path/to/backup.tar.gz --all-features --all-domains

That backup.tar.gz file where was it generated - which OS, which MySQL/MariaDB version and which version of Webmin/Virtualmin (just curious).

Moreover, if you run systemctl force-reload mysql and try again does it happen to work?

5

I only tried with the transfer server option (not backup and restore) which doesn’t give any errors.
backup was created on ubuntu 18.04, mysql 5.7.33-0ubuntu0.18.04.1-log , virtualmin 6.14
force restarting mysql didn’t change anything…

6

I’m also experiencing this same issue on Ubuntu 20.04 for some restores.

7

I having a similar issue migrating sites from a legacy server.

Source server:
MariaDB version: 5.5.68 / CentOS 7.9

Destination server:
MySQL version: 8.0.23 / Ubuntu 20.04.2 LTS

It doesn’t work in the UI nor does it work using the command line backup tool.

The error is:

Restore failed : SQL grant all on massage.* to 'massage'@'localhost' with grant option failed : You are not allowed to create a user with GRANT

I tried:

systemctl force-reload mysql but that didn’t work.

One has to deep dive if root has grant privileges. On another system root@‘localhost’ didn’t have the GRANT permission, but root@‘127.0.0.1’ did !

But this time I can’t figure it out.

8

Made a bit of progress on this issue. Steps to reproduce:

  1. Backup a domain
  2. Restore to MySQL 8 server

Error reported:

Restore failed : SQL grant all on hepple\_db.* to ‘hepple_user’@‘localhost’ with grant option failed : You are not allowed to create a user with GRANT

  1. Log into MySQL as root AND localhost (it should default to that)

  2. Do

SHOW GRANTS FOR ‘root’@‘localhost’;

The resultant permissions that are spit out are huge but the bottom line is they don’t contain the GRANT ALL permission.

In other words, if SHOW GRANTS has to be specific about which permissions exist, instead of this line:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION

It means root doesn’t actually have full permissions.

On one system, I was able to get it working by using -h127.0.0.1 and that root user had full permission. On the current system I’m working on I don’t have the same luck so can’t get past this problem.

Here are some guys on Stack battling with something similar and where I learnt about the GRANTs available Their remedy of upgrade server won’t work for me.

9

The only new update I have here is:

  1. When I just backup and restore the database, the restore works. When I backup the entire site, the restore fails.

  2. Apparently with MySQL 8 you can’t Grant like before. The functionality was removed.

10

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.