Can't start MariaDB after replacing 5.6 with 10.4

Question

I have an old VM I use for dev testing - it runs CentOS 7.9 with nginx, PHP, and MariaDB. The MariaDB version was really old (5.6), but it was working well enough for me until today when I wanted to use a function it doesn't support. So I decided to try to upgrade it to a version more similar to the production server (which runs 10.2 - I chose the 10.4 repo). I'm a programmer, not a server guru, so I looked for guidance on the web. I followed a modified combination of https://www.liquidweb.com/kb/centos-7-upgrade-mariadb/ and https://www.ezeelogin.com/kb/article/how-to-upgrade-mariadb-5-5-to-10-x-in-centos-7-486.html. Neither tutorial matched my situation exactly - for systemctl and yum remove commands they called the old service mariadb, whereas in my case it was called mysql, and after yum remove mysql, I had to also do yum remove mysql-community-common before the install would complete without file conflicts. At that point 10.4 seemed to install okay. But it won't start, complaining thus:

Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.

systemctl status mariadb.service said this:

● mariadb.service - MariaDB 10.4.34 database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
  Drop-In: /etc/systemd/system/mariadb.service.d
           └─migrated-from-my.cnf-settings.conf
   Active: failed (Result: exit-code) since Sun 2024-05-26 06:02:39 JST; 38s ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 21916 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE)
  Process: 21836 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
  Process: 21834 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
 Main PID: 21916 (code=exited, status=1/FAILURE)
   Status: "MariaDB server is down"

That seems rather uninformative, at least to my untrained eyes - what error code? what exit code? It seems like there should be some code numbers or something. I also tried journalctl -xe, but that made even less sense to me, not saying anything at all about errors or even database services. I searched the web again for help but didn't find anything applicable, so I'm here. Can you guys help?

UPDATE: I discovered that /var/log/mysqld (which the error message didn't suggest looking in, and I'm not that server-smart) had a clue - here are the last several lines:

2024-05-26  6:08:04 0 [Note] InnoDB: 10.4.34 started; log sequence number 604951747; transaction id 1878277
2024-05-26  6:08:04 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2024-05-26  6:08:04 0 [Note] InnoDB: Buffer pool(s) load completed at 240526  6:08:04
2024-05-26  6:08:04 0 [Note] Plugin 'FEEDBACK' is disabled.
2024-05-26  6:08:04 0 [ERROR] Failed to setup SSL
2024-05-26  6:08:04 0 [ERROR] SSL error: SSL_CTX_set_default_verify_paths failed
2024-05-26  6:08:04 0 [ERROR] Aborting

So it is complaining of a problem with SSL, but I have no SSL on this server, as it is simply a VM on my Windows PC to use for testing PHP code I'm developing. And /etc/my.cnf.d/server.cnf (the only config file with any substance) has ssl = false, so why is it trying to do something with SSL?

Update #2: I found a bug report about MariaDB 10.4 with the error I was getting (which resolved when the person downgraded to 10.3). One piece of advice was to comment out the other SSL lines in the config file. So I did that, and the SSL complaint went away. But it still failed - this time the complaint was:

2024-05-27  7:17:08 0 [ERROR] mysqld: Can't create/write to file '/var/run/mysqld/mysqld.pid' (Errcode: 2 "No such file or directory")
2024-05-27  7:17:08 0 [ERROR] Can't start server: can't create PID file: No such file or directory

/var/run is a symbolic link to /run, and there was no /run/mysqld directory at all. This seems far more low-level than I should have to mess with, but I created a /run/mysqld directory with 777 permissions (that's more open permissions than anything else in /run, so it feels like a weird thing to have to do), and the service finally started. But...

Now when I try to use my application, it acts as if my PHP session isn't sticking - I have to keep logging in. I didn't touch anything in PHP or my application code, so it apparently has something to do with the MariaDB installation, even though I can't fathom why. The same code works fine on my production server running MariaDB 10.2. I guess I'm making progress, but this is all very weird.

I dug into this new issue more, and it continues to be mysterious. My production server and VM are intentionally very similar. The location where session data is stored is the same (var/lib/php/session), 770 with group "apache" (who knows why, since I'm running nginx, but whatever). There are recent session files in that directory on both servers, so it's not a new location. The web server's user on both is www-user. But on only my production server was that user in the apache group - I remedied that on my VM, but it still can't write to that directory, and I don't know why (nor how upgrading MariaDB could have anything to do with it). I did run yum update during the process, but that shouldn't be changing configurations of stuff, and the PHP and nginx versions are unchanged.

Update #3: Since I couldn't figure out why var/lib/php/session wouldn't work, I changed session.save_path to a location I know works (it is used for other temp files my code produces). So I guess, never mind. If this was a production server I would be more concerned about these oddities, but for now on this VM, I don't care as long as it works. I plan on building a new Debian server for both VM and production when I get time to figure out how to set up everything I need (I'm leaving CentOS because I don't want to be on the bleeding edge).

Update #4: Apparently my attempt to change the path was not successful, but reloading php-fpm was needed for the group change to take effect, so it works with var/lib/php/session now anyway, so it's all sorted. Thanks for your attempts to help.

Answer 1

I would have done it differently, because it is a long stretch between versions of MariaDB. I recently had this issue with MariaDB.

1- create a snapshot of the VM.

And then, as root...

I would undo the upgrade, delete MariaDB, then upgrade to the new MariaDB with the explicit version and import the old data. Disable SSL if it messes up the upgrade, however, even on a dev machine, it should be enabled to make things easier later on:

Find the exact point when the upgrade was done.

yum history

Once you have identified the transaction ID, you can use it to roll back the operation

yum history rollback <transaction_id>

After rolling back to MariaDB 5.6, you need to export your database data. You can do this using mysqldump.

mysqldump -u root -p --all-databases > all_databases.sql

remove the old MariaDB installation.

yum remove mariadb-server mariadb

Install the new version of MariaDB (10.4 in this case).

yum install mariadb-server-10.4 mariadb-10.4

If SSL was not used in the old database and you need to disable it in the new setup, ensure the following lines are in your MariaDB configuration file (/etc/my.cnf or /etc/my.cnf.d/server.cnf)

[mysqld]
ssl = 0

Restart the db:

systemctl restart mariadb

Restore the data from the dump file:

mysql -u root -p < all_databases.sql

Upgrades can be (are) tricky when too long has gone between updates. There are some columns that have changed types over time and stuff like that, in MariaDB, which can make and upgrade tedious.

Good luck!