Backup of mysql failed : mysql::execute_sql failed : SQL select distinct host from user where user = ? failed

[vander.host]

SYSTEM INFORMATION
OS type and version	Ubuntu 24.04.01
Webmin version	2.202
Virtualmin version	7.20.2
Webserver version	Stock Apache
Related packages	MariaDB

Hi there,

On a new installation MySQL backups are failing. In fact, the server at defaults and Ubuntu 24-04.01 uses MariaDB.

The full error message is this:

Copying Logrotate configuration ..
.. done
.. backup of mysql failed : mysql::execute_sql failed : SQL select distinct host from user where user = ? 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  
Backing up Webmin ACL files ..
.. done

This error is hard to troubleshoot because it appears very Virtualmin specific. Why would the distinct host be looked for per user?

All I could find is this post:

Changing Db to mariadb

Guys, I changed DB to Mariadb and uploaded backed up data bases, and now I’m trying to update users and I get this error: SQL select * from user where user = '' failed : View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Does anyone know how to fix this issue, please?. Thanks |—|—| ||| |OS type and version:|UBUNTU 20.04| |Webmin version:|REQUIRED| |Virtualmin version:|REQUIRED| |Related products version:|RECOMMEND…

That post might be irrelevant because the user complains about all kinds of MariaDB things and only later encounters the exact error. Their remedy was to redo all site restores which I am unable to do.

Any troubleshooting steps would be appreciated!

Maybe does Virtualmin query the local database to find out which host is hosting what?

UPDATE: Daily backups have the actual Perl library where this is failing, namely mysql-lib.pl and web-lib-funcs.pl:

that corresponds to your MariaDB server version for the right syntax to use near ''' at line 1 mysql/mysql-lib.pl (line 359)
 ../web-lib-funcs.pl (line 4264)

[vander.host]

Still no luck.

But I can simulate the problem in another way.

I click “manage” on the database, and I get this:

Fatal Error!
SQL select table_schema,table_name from information_schema.views where table_schema = ? 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

Help!

[vander.host]

Webmin also failing:

SQL select table_schema,table_name from information_schema.views where table_schema = ? 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

This is above my paygrade.

[vander.host]

I’ve resorted to cross posting to the Webmin forum, maybe someone can help me there:

Redetect MySQL module linking because of broken MariaDB linking

SYSTEM INFORMATION OS type and version Ubuntu 24.04 Webmin version 2.202 MariaDB version 10.11.8 I know this is the Webmin forum, but I think my problem is related to the interaction of Webmin and Virtualmin. When I did a Virtualmin installation on a stock Ubuntu 24.04 server, the MySQL broke. Actually MariaDB broke because as you know a stock installation of Ubunutu 24.04 installs MariaDB. In Webmin the issue is evident in two places: When I looked at the Webmin screen, bef…

[Ilia]

What about the mysql -u root -p command, does it work or fails as well?

Webserver version Stock Apache

Anamnesis: Is this a completely clean install of Virtualmin on Ubuntu 24.04, or was there a database already installed before Virtualmin? Was the database perhaps converted from MySQL to MariaDB?

Did this error start appearing at a specific point in time, or did it occur right after the clean Virtualmin installation?

At last, what is your VPS provider?

[vander.host]

@Ilia

mysql -u root -p

Yes this works.

@Ilia

Is this a completely clean install of Virtualmin on Ubuntu 24.04

Yes completely clean install.

@Ilia

or was there a database already installed before Virtualmin?

No, there was no previous database installed.

@Ilia

Was the database perhaps converted from MySQL to MariaDB?

No.

@Ilia

Did this error start appearing at a specific point in time, or did it occur right after the clean Virtualmin installation?

This is a good question. I don’t believe we would have picked it up straight after installation. Straight after installation we restored databases (but not users) using the command line. The databases all worked.

Then we renamed main domains, and added aliases to the main domains that previously existed.

Only a few days later when we first attempted Virtualmin backups we noticed that the MySQL/MariaDB linking is broken.

@Ilia

At last, what is your VPS provider?

We’re our own host. We run Proxmox hypervisors and deploy our own VPSses.

My gut feeling is something went from with the initial Virtualmin install.

[vander.host]

Update: We rebooted and now it’s working.