OS type and version: CentOS Linux 7.9.2009 Webmin version: 1.981 Virtualmin version: 6.17 Pro
Hi All,
Happy Christmas or whatever you celebrate at this time.
I have been moving sites from an old Cento 5 box to a new Centos 7.
I did the initial install of Virtualmin using the automated script but didn’t add MySQL at install time but allowed Maria DB to be installed at that time.
Now, I have a site which uses MySQL (version 5.0.95) and of course, that won’t run on Maria DB. I am expecting this customer to go away within the next month or two but I really need to support the site meanwhile. Sorting out all the problems converting looks to be a very time consuming issue so I thought I would install MySQL.
So, how can I safely go about installing MySQL at this stage?
Can I just install via YUM in Software Packages or should I go via some other route?
Generally speaking you can transfer most databases from MySQL to MariaDB.
MariaDB and MySQL started out binary compatible as the developer of MariaDB also built the original MySQL.
They also both share most of the same table formats.
Is there a specific problem you are facing?
Oh and if absolutely necessary you can uninstall MariaDB and drop in MySQL.
*** Fact: My is named after first daughter and Maria after second daughter of lead developer. (MySQL was sold to Sun Microsystems, then sold to Oracle) ***
Take care of good backups / dumps of Databases and don’t throw them away , and keep notes about versions / configs / settings used together with those
some times it looked like a migrations / update / downgrade succeeded but in live apllication errors at some point.
While between versions there could be differences so read changelogs , also other default between versions Mariadb for example.
Example:
Experience here ( but not enough knowledge about those!) a ecommerce apllication everything seems ok , but then a payment plugin after a buyer choose that one it went wrong.
So take care of your configs and settings to , and keep track on changelogs!
Downgrades are not only not supported , but a kind of hell of you need them , therefore the backups.
I did a backup on the old server then restored on the new server.
The good news is that I can look into the DB and I can see that the data appears to be in place. But it is not displaying on the web pages so it might be simply not making a connection to the DB or similar.
I didn’t create these pages so it is gonna be a pig to sort out (I’m not too good at that).
I didn’t even think of looking for PHP errors, just jumped at the first thought that came to mind, the change from MySQL to Maria.
And there it is in the logs “stderr: PHP Warning: main(): Couldn’t fetch mysqli in /home/domain/public_html/showlists/display-memberlists.php on line …”
At least I now have a start on tracking the issue down. But enough for today, it has been a very long one.
Just in case anyone else should arrive here with the same issue and as little knowledge as I, here is what resolved my problem.
As above, it had nothing to do with needing to roll back to MySQL.
I eventually found that I was getting an error
“mod_fcgid: stderr: PHP Warning: mysqli::__construct(): (HY000/2000): mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication. Please use an administration tool to reset your password with the command SET PASSWORD = PASSWORD(‘your_existing_password’). This will store a new, and more secure, hash value in mysql.user. If this user is used in other scripts executed by PHP 5.2 or earlier you might need to remove the old-passwords flag from your my.cnf file”.
That was very misleading and took me down a rabbit hole and after trying for a long time to resolve it I discovered that the passwords were already the more secure type.
Finally I found the solution as follows
In my connection script there was
$server = 'localhost';
$user = 'user.name';
$pass = 'Not-a-password';
$db = 'name-of-database';
Note that acording to some sources the ONLY data for “server” is ‘127.0.0.1’
The username had changed from ‘user.name’ to just the last part ie. ‘name’
The pages that use this database were created some 12 or more years ago (on Centos5) but they still functioned perfectly on the new box with Centos 7.
So the solution was, in fact, very simple but my lack of knowledge made it very difficult.
Thanks to those above that pointed me in the right direction.
I’ll also mention that in Virtualmin 7 all distros will use Mariadb, by default, and it will be our recommended database for everyone. That’s the way the Linux community has gone, by and large, and we tend to recommend everyone take the well-traveled path rather that setting out on your own and breaking new trails; the well-traveled path is now Mariadb.
And, for anyone reading this in the future: The solution to whatever problem you’re having is basically never going to be, “Switch back to MySQL.” The two databases are compatible enough that you should not look to that change as a source of problems. In fact, new versions of MySQL are more different and less compatible from old versions of MySQL than Mariadb is from those old versions of MySQL, at this point. You might even find it easier to go to Mariadb than to MySQL 8 (though neither should be very challenging).
