I connected a remote (Digital Ocean Managed Database) which is MySQL 8 and my Ubuntu virtualmin box has Mariadb and when i am trying to dump I get:
Creating backup for virtual server example.com ..
Dumping MariaDB database example..
.. dump failed!
/usr/bin/mysqldump: unknown variable 'set-gtid-purged=OFF'
Backup failed! See the progress output above for the reason why.
I found this line that may cause the error and already tried to add in mariadb module configuration and in mysqldump command the --set-gtid-purged=AUTO but dont seem to work.
Any idea?
not so sure, but i think mariabd (client) doesn’t support this option set-gtid-purged
if you can’t remove this option, maybe try to install mysql-client ?
not sure if this would also be compatible with local mariadb databases. (if any).
I don’t think that is digital ocean the problem. I think that its just virtualmin doesn’t care if i dumping local databases (which in my setup is MariaDB) and not a remote one which it can be mysql that its just adds the flag…
virtualmin uses whatever db client is installed. your package manager (OS) doesn’t allow both mysql-client and mariadb-client (for good reasons imho), so problem is there. not virtualmin.
anyway, if removing that incompatible line, makes backup work, no need to dig any further.
if it didn’t work that way, i’d probably exclude this db from local virtualmin backup and use some other way (perhaps in DO if there’s such an option…)
As I understand it, this is not an attack against Virtualmin.
It is a bug report, from someone who likes it and uses it for more things than it is usually used.
For sure it not a DO issue. It is irrelevant whether the user or the distro did the decision of the mysql type and version. If Virtualmin is not compatible with mariadb it should be stated in dependencies.
To the bug report now:
Virtualmin for some reason doesn’t understand that the remote database is not compatible with the given setting. This is the issue. It would be nice to git it fixed, so the users don’t have issues in the future.
It does seem to be a bug! Temporarily, you can manually edit the mentioned mysql-lib.pl file and comment out the line $gtidsql = "--set-gtid-purged=OFF";.
So we should already handle this for remote databases by running the SQL show variables like 'gtid_mode' on the remote server to check if that option exists before setting the --set-gtid-purged=OFF flag.
That said, if the mysqldump command set in the module config already has --set-gtid-purged=AUTO we shouldn’t re-set the flag again! I’ll fix that …
Ok I reverted the file back to github one from here and patched successfully after that:
But I again getting the same error. Do I need to --set-gtid-purged to AUTO in mysqldump command?
And I also have to mention that it takes over a minute to list databases in Webmin → Servers → Mariadb Database Server but at least it list them successfully…
@Ilia as I mentioned before, MariaDB is installed in my Ubuntu box, but I don’t use it. I connected to virtualmin a remote Digital Ocean MySQL 8 managed database.
I know you sorted it out, but you should never force a patch to apply. It’s not applying because the file being patched doesn’t match what the patch was derived from, and forcing it would be destructive and do nothing good.
I understand this but I know that my edit which I commented the line before, altered its hash. Anyway after the revert to the original content and patch successfully gave the same error again. I want to thank you all again for your time and the patch but looks like doesn’t help for some reason. I am more than happy to test and share any more info if that helps you