Backup: MariaDB user restoration issue

Virtualmin
1
SYSTEM INFORMATION
OS type and version RHEL 10.2
Webmin version 2.651
Virtualmin version 8.1.0 GPL
Webserver version REQUIRED
Related packages MariaDB 10.11.15

Hello,

I’m experiencing an issue when restoring a backup with Virtualmin.

When I restore a backup using Backup and Restore => Restore Virtual Servers, the restore process fails when recreating an additional MariaDB user.

Here is the relevant output:

Re-loading MariaDB database mytest_db1 ..

    Creating MariaDB database mytest_db1 ..
    .. done

.. done
Restoring virtual MariaDB users ..
    Creating user1@mytest.com user ..
    .. failed to create user : Failed to add database user : user1@mytest.com : mysql::execute_sql_logged failed : SQL create user 'user1@mytest.com'@'localhost' identified by password failed : You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1

.. done

This user is not the default database user created by Virtualmin. It is an additional database-only user that I created manually using the following command:

virtualmin create-user --domain mytest.com --user user1 --pass 'XXXX' --db-only --mysql mytest_db1

The backup completes successfully, but during the restore, Virtualmin is unable to recreate this database user because the generated SQL appears to be invalid.

Has anyone encountered this issue before, or is this a known bug? Any help would be greatly appreciated.

Thank you!

2

I did some more investigation and found something that might help.

In Webmin → Webmin Actions Log, when I look at the restore logs for my domain, I can see the SQL commands that are executed.

For the additional MariaDB user, the log shows:

create user 'user1@mytest.com'@'localhost' identified by password

As you can see, there is no password (or password hash) after identified by password.

For the primary database user, the log shows:

create user 'mytest'@'localhost' identified by 'MyTestP@ssw0rd!!!!'

So the password is correctly passed for the primary user, but it doesn’t seem to be passed to the CREATE USER command for the secondary MariaDB user.

What’s strange is that the password is present in the backup archive. In the mytest.com_virtualmin_users file I have:

extra=1
user=user1@mytest.com
mysql_pass=XXXXX
db_mysql=mytest_db1

So it looks like the password is successfully saved in the backup, but is somehow not being used during the restore process for additional MariaDB users.

3

I’m just guessing here, could that be a white space?

4

I found the root cause of the issue.

After investigating it, I identified where the restoration logic was going wrong and I’ve submitted a pull request with a proposed fix:

Hopefully this can be reviewed and merged if the approach looks correct.

5

Thanks, I left a comment.