Add Support MariaDB 10 For Performance & Compatibilty

The reason I ask for support to MariaDB 10 in addition to performance 40% better also about the future compatibility. MariaDB 5.5 is too old and very old 2012. Many changes were done mariadb10 and have to readjust from version 5.5.

If we do not start from now to use the latest software, it will be a pain in the future. As it is now, MariaDB 5.5 can not immediately to version 10.1.x need to MariaDB version 10 in advance. Now mariadb10.2.3 beta is out, it will be a lot of steps to upgrade. version v5.5 and then v5.5.50 - v10 - v10.1 - v10.2 - v10.2.3. I've tried a fresh install and upgrade but webmin can not read MariaDB-10.

Hopefully virtualmin could follow, because some of the control panel is already using the latest version of MariaDB-10.1.30.

Best Regard.

Status: 
Closed (works as designed)

Comments

wpfqouta's picture
Submitted by wpfqouta on Sun, 02/05/2017 - 08:23

Assigned: » Unassigned

Howdy -- Virtualmin uses the MySQL or MariaDB database that's included in your Linux distribution.

While we do recommend using that version for stability and security reasons, Virtualmin does typically support other database versions.

And a number of folks do perform such upgrades, though we don't provide official packages or steps to go about that.

Are you seeing a particular problem with MariaDB 10 support though? If you're experiencing an error when MariaDB 10 support, what error or problem are you experiencing?

We've been running MariaDB 10 successfully on several of our database servers with Webmin/Virtualmin. It was more or less a drop-in replacement. We're using 10 primarily to have multi-master databases under Galera. Has been working fine for months. Any of these sorts of replacements are really not for the faint of heart, but it's straightforward enough.

wpfqouta's picture
Submitted by wpfqouta on Mon, 02/06/2017 - 05:51

Hi Sir Andrey

Many of the problems after the upgrade, webmin / virtualmin can not read the location and structure of MariaDB-10.x, database creation can not.

Webmin can be read only version, restart, stop and start.

I tried a fresh install by add the repository of the MariaDB, and MariaDB-10.x versions installed, but also not able to read configuration.

wpfqouta's picture
Submitted by wpfqouta on Mon, 02/06/2017 - 05:54

Sir **cruiskeen **

Upgrade succeeded only to a basic database structure, just like a little wordpress and plugin. But for me to experience many complex database tables are corrupt & webmin/virtmin can not read mariadb-10 configuration.

Do you have time to share a tutorial how to upgrade to version MariaDB-10.1.30? and how webmin/virtualmin can read configuration mariadb-10.x? I really appreciate.

It should be able to work, though you may need to go into the MySQL/MariaDB settings screen and Webmin and update some of the options there. Some of the paths may be different.

If you're receiving an error, let us know what the error is.

While we don't provide any official support for upgrading away from packages included with your distribution, MariaDB 10 should indeed work -- but if you see any errors that are related to Webmin's support for MariaDB, just let us know the exact error you're seeing.

wpfqouta's picture
Submitted by wpfqouta on Mon, 02/06/2017 - 11:32

Many of the problems I encountered, such as can not create user on virtual-server-creation, change password, priviligas, user permissions problem and others.

MariaDB-10 issue has been discussed from 4 years ago since mid-2013, but there has been no response from webmin/virtualm up to 4 years. What no plans to use the new software version?

I'm still survive with virtualmin to some of my websites, because it is very light.

MariaDB 10 can be run with create a database & user manual, but I am not convinced by the performance.

In our case we just built a new server image that included MariaDB 10 instead of 5. That all was really quite smooth as I remember it. I think perhaps your issue here is that you did an UPGRADE from 5 to 10 on a server with an existing database???? Did you run the mysql_upgrade script after you put in the new database? It's really hard to know since there's not a lot of concrete information here, but my guess is that this is more a problem of a bad database upgrade than a Virtualmin problem. See https://mariadb.com/kb/en/mariadb/upgrading-from-mariadb-55-to-mariadb-100/ -- If you do an upgrade on CentOS it does not run the mysql upgrade automatically so you need to do that or you'll have a lot of weird database problems.

I'm also interested of the support of mariadb in virtualmin.

Actualy the offcial debian package stick to mysql 5.5, which is very old, and mysql does not provide their own debian repo. Mariadb provide it well, and replace the mysql commands.

Mariadb has also better back-compatibility for SQL commands (you have to disable the Strict SQL Mode in mysql 5.7) , as performances enhancements. Mysql will also remove the query cache feature in their next major release, where mariadb will not.

I actualy use mariadb with docker by copying the database and the permissions from mysql, once they are created by virtualmin, because I did not find an other good way.

Hi, I have now one problem with webmin and even virtualmin. I am not able to access anything that is associated with mysql. In webmin it show nothing. In Virtualmin it stops on reading database settings. If I tried it in CLI and with strace, it shows SIGSEGV error and this is the tail of it:

open("/etc/mysql/my.cnf", O_RDONLY)     = 3
fstat(3, {st_mode=S_IFREG|0644, st_size=1673, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f59bc3ba000
read(3, "\n\n\n[client]\nport\t\t= 3306\nsocket\t"..., 4096) = 1673
--- SIGSEGV {si_signo=SIGSEGV, si_code=SEGV_MAPERR, si_addr=0} ---
+++ killed by SIGSEGV +++

I am not familiar with strace but maybe it helps you

Wow, it looks like the perl MySQL client code is crashing.

As a work-around, try editing /etc/webmin/mysql/config and change the nodbi= line to nodbi=1

now it show in webmin -> mysql server The MySQL client program /usr/bin/mysql does not accept passwords passed using the MYSQL_PWD environment variable. To ensure that Webmin is able to fully communicate with MySQL, this option should be turned off on the module configuration page. Virtualmin is fully working again

You should also set nopwd=1 in that same file.

We are using successfully Mariadb as of 5.5 till 10.2 but we have issue with virtualmin changing user password. virtualmin/webmin use:

MariaDB [(none)]> update user set authentication_string = password('Pa$$word') where user = 'username' and host = 'localhost';
ERROR 1046 (3D000): No database selected

but this syntax is wrong. As you see executed in mysql result in ERROR. the right syntax is:

MariaDB [(none)]> set password for 'username'@'localhost' = password('Pa$$word');
Query OK, 0 rows affected (0.00 sec)

Could be that your MySQL version is detected incorrectly. If you go to Webmin -> Servers -> MySQL Database, what version is shown at the top of the page?

So for classic MySQL 5.7.6 and above, Virtualmin uses "update user". The "set password" command is only for versions before 5.7.6.

Does MariaDB use different version numbers than MySQL?

probably they can use different versions but should be compatible. I checked that the last mysql version is 8.X so probably they use different numbers. But how we can set webmin/vuirtualmin to use this "old" syntax so the change password function to work properly?

is there a how to for upgrading mysql to maria db ? If not on a clean centos 7 what would be the right steps so that in the virtualmin installer it sees mariadb ?