(Possible Fix) Unable to restore virtualmin backup due to SQL error "Restore failed : ... You are not allowed to create a user with GRANT"

[tom3]

SYSTEM INFORMATION
OS type and version	Ubuntu 20.04
Virtualmin version	Latest
MySQL version	8

Hello,

I’m currently migrating my old virtualmin server (ubuntu 16.04) to a brand new and upgraded server (ubuntu 20.04), but I’m encountering the well known error restore error Restore failed : SQL grant all on database. to ‘user’@’%’ with grant option failed : You are not allowed to create a user with GRANT*

During my process of investigation I’ve found countless posts on this forum with no answers or with a thorough investigation by other users. All seems to conclude to “not fixeable” or “give up”.

I wanted to share my findings, read possible solution that fixed it for me, to increase the hope for other people

When I encountered this error, it looked like it only happened with user accounts that are actually e-mailaccounts.

So when verifying I noticed that this was enabled on those accounts:

When removing the database from the “grant access to”, the restore worked perfectly.

Ok the user cannot access now the database through his/hers e-mailaccount login, but in my case they were not even using it.

Perhaps this might help other people out there?

Other than that, MySQL 8 has removed the option to create users through the “GRANT … TO … IDENTIFIED BY … WITH GRANT OPTION” command. You have to do it seperately, thus old MySQL backups will always fail, since they only contain the single line command.

[Ilia]

This fix, which is going to be included in Virtualmin 7 must handle this.

if you are interested in applying the patch, restarting Webmin and giving it a try, please do.

[tom3]

Hi Ilia, all good and well that this fix will be in virtualmin 7.

But if you make a virtualmin backup from a server that contains a MySQL installation < v8, this still won’t do the trick right?

[Ilia]

I would have to have a look at that kind of cPanel backup first to know for sure. Could you please share privately with me the link for that backup file. I would test and fix a problem in case I could reproduce it.

[Ilia]

… or at least you could share mysql.sql file from cPanel backup file.

[tom3]

Hello Ilia, did you actually read my posts?

I’m talking about restoring virtualmin backups from an older server, to a brand new installation of virtualmin.

There is no CPANEL involved in this story.

In my case it was restoring virtualmin backups from an Ubuntu 16.04 with MySql 5.7.33 to a brand new virtualmin server with Ubuntu 20.04 and MySQL v8.

[tom3]

I’ll take a look if I can replicate this with a smaller database backup, since all of them are around 1-2 GB MySql databases.

[Ilia]

Thanks, that would be great. Although, I think having mysql.sql file from cPanel backup would be a helpful start.

[tom3]

Sorry to say, but you aren’t reading my posts…

I have NO CPANEL…

[Ilia]

Sorry for keep insisting on cPanel backup. My bad. I was really mixing your post up with another post.

Look, if you restore Virtualmin backup, it will contain MySQL dump and there is no reason to worry about it (GRANT statements).