Mysql 8 won't start after update

SYSTEM INFORMATION
OS type and version Ubuntu Linux 20.04.5
Webmin version 2.013
Virtualmin version 7.5
Related packages mysql-client-8.0 8.0.32-0buntu0.20.04.1
mysql-client-core-8.0 8.0.32-0buntu0.20.04.1
mysql-common 5.8+1.0.5ubuntu2
mysql-server-core-8.0 8.0.32-0buntu0.20.04.1
php-mysql 7.4+75
php7.4-mysql 7.4.3-4ubuntu2.17

Hello all
I have a VPS which had a clean ubuntu 20 + virtualmin install last year and has been running OK, doing the usual updates that show up in webmin by apt. The server hosts a dozen customer accounts (websites + email). The VPS is beefy (xeon octacore, 32G RAM, SSD), so no lack of resources.

Today there were a bunch of updates and suddenly I get an error, saying Mysql is not configured and wont start anymore.
Looking into the errors, I see that mysql has been updated to version 8 and I guess the previous version it had was 5.8, looking into what’s installed under the mysql name (pls see “related packages”). I am aware that ubuntu 20 supposedly ships with mysql 8, probably the image the hosting company has for vps had version 5.8

/var/log/mysql says

[Server] /usr/sbin/mysqld (mysqld 8.0.32-0buntu0.20.04.1) starting as process 16667
[ERROR] [MY-011011] [Server] Failed to find valid data directory.
[ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
[ERROR] [MY-010119] [Server] Aborting

systemctl status mysql.service says:

● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: failed (Result: exit-code) since Wed 2023-01-25 10:27:26 WET; 16min ago
Process: 4357 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
Process: 4366 ExecStart=/usr/sbin/mysqld (code=exited, status=1/FAILURE)
Main PID: 4366 (code=exited, status=1/FAILURE)
Status: “Server shutdown complete”

systemd[1]: mysql.service: Scheduled restart job, restart counter is at 5.
systemd[1]: Stopped MySQL Community Server.
systemd[1]: mysql.service: Start request repeated too quickly.
systemd[1]: mysql.service: Failed with result ‘exit-code’.
systemd[1]: Failed to start MySQL Community Server.

and /etc/mysql/my.cnf is empty (i.e: it’s a shortcut to mysql.cnf, which is all greyed out)

mysql databases are located in /var/lib/mysql/ (each under it’s on subdirectory)

Trying to install mysql via package update gives the following error:

Setting up mysql-server-8.0 (8.0.32-0buntu0.20.04.1) …
Renaming removed key_buffer and myisam-recover options (if present)
Specified filename /var/lib/mysql/ibdata1 does not exist.
mysqld will log errors to /var/log/mysql/error.log
2023-01-25T11:02:51.456936Z 0 [ERROR] [MY-010946] [Server] Failed to start mysqld daemon. Check mysqld error log.
Warning: Unable to start the server.
Job for mysql.service failed because the control process exited with error code.
See “systemctl status mysql.service” and “journalctl -xe” for details.
invoke-rc.d: initscript mysql, action “start” failed.

  • mysql.service - MySQL Community Server
    Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
    Active: activating (auto-restart) (Result: exit-code) since Wed 2023-01-25 11:02:53 WET; 11ms ago
    Process: 22988 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
    Process: 23011 ExecStart=/usr/sbin/mysqld (code=exited, status=1/FAILURE)
    Main PID: 23011 (code=exited, status=1/FAILURE)
    Status: “Server shutdown complete”
    dpkg: error processing package mysql-server-8.0 (–configure):
    installed mysql-server-8.0 package post-installation script subprocess returned error exit status 1
    Processing triggers for man-db (2.9.1-1) …
    Processing triggers for libc-bin (2.31-0ubuntu9.9) …
    Errors were encountered while processing:
    mysql-server-8.0
    E: Sub-process /usr/bin/dpkg returned an error code (1)

Tried some solutions found online, including here in the forums, like uninstalling and reinstalling mysql, trying to run virtualmin wizard (that halts on mysql), nothing worked

Maybe it’s just a question of editing some configuration files, but I don’t know which and what to put there. Also, I need some help to recreate the ibdata1 file that is missing.

Right now system is down and customers complaining. Any help is much appreciated.

Thanks in advance and best regards
Alex

Update: after trying everything I found, saved the latest email directoried from users in local unit, formatted the server, reinstalled fresh ubuntu and virtualmin, restored from 2 day old s3 backups and uploaded the emails to get everything in sync.

To VM admins/devs (Joe): not sure if it’s a script bug in vm7, but to give you some insight, I’ve had this VPS for years, first in ububtu 16 and, last year, upgraded it to ubuntu 20 with virtualmin 6, using a format, fresh install and restore from S3.
Back then, Ubuntu 20 came with php7.4 and Mysql 5.8 and found a number of issues with VM6 in using greylists, because IPv6 was not properly supported. I have sowftware updates enabled, so daily it performs some updates. VM automatically upgraded to 7 and yesterday Mysql upgraded to 8, which led to all this situation, not being able to start.
Today’s ubuntu 20 shipped with php-fpm 7.4, mariadb 10 and VM is 7.5. As soon as I installed memcached I lost the php-fpm server status in the dashboard, but all backups have restored ok-ish.
Maybe it’s just my bad doing, but found that VM has a number of issues with ubuntu 20 that didn’t have with ubuntu 16, namedly mysql updates, greylisting with ipv6, dnssec. Also, it has errors when restoring, not creating the right usergroups in php socs, leading to php-fpm server halting and having to manually edit the php-fpm site files. Just my five cents…

We did not cause your MySQL upgrade. We do not provide MySQL, and never have (not in VM6, not in pre-VM6 and not in VM7). MySQL has never come from us. And, MariaDB, our new default DB on all supported distros, also does not come from us and never will. You chose an OS (and maybe some other third party repositories), and we respect that choice.

Virtualmin offers to update from whatever repositories you have configured. If you’ve configured a repo that will change major versions of the database, that’s out of our control (and wildly dangerous…no Linux distro does that, as far as I know, so I’m not sure how you would have gotten there without knowing you were going there). Only a major OS upgrade should change the database version, and you should prepare for that major change, following the docs provided by your OS vendor and the database developers.

I also want to mention that Virtualmin virtual-server module reached version 7.x last year…if you’re just now updating, you were months out of date. That’s a bad idea. But Virtualmin does not automatically update! We provide a list of available updates and button you can click to install them (after reviewing them, of course). There are packages that provide automatic updating on Ubuntu, but we do not install them as part of the Virtualmin install.

I’m sorry you have had an issue with your database, and we’ll try to help when we can (even with stuff unrelated to our software), but you’re dumping a lot of stuff on us that we simply don’t do, and that’s really frustrating. It’s difficult to help when the problem is being presented as “here’s all the ways you broke my server”, when literally none of it was us.

If there are other unrelated outstanding issues you need help with (or want to report), we’re happy to talk about it in a new topic. I have to insist on the “one problem per topic” rule. Forum Guidelines: Please read before posting!

does this mean you have both maria & mysql installed ?

No, just Maria

Thank you for the reply Joe.

Sorry if my previous post came out the wrong way. Just to be clear: I’m not throwing VM staff under the bus, just reporting what happened. I know that VM uses whatever repos are available, the whole reason of my text is to give some insight on things that did not work perfectly, maybe will help in future VM updates or in VM documentation.

The host is Contabo, like any other host allows you to choose an OS for the VPS. The OS comes “bare” with their repo and the official OS repos (ubuntu focal), and I did not change or add any repo before or after the install. Virtualmin does all the “AMP” install from the repos and I just keep the software update option enabled in nightly “only updates”, not “distro upgrades”.
Last year install was with VM6, which later updated to VM7 when it became available. Like all other updates, it worked smoothly.

VM6 with Ubuntu 20 (contabo version) had some issues regarding milter greylist, which would not work properly out-of-the-box with IP6, as stated in several topics here in the forums, which I used for finding a solution - did not open a topic for that. In my new VM7/U20 install I just didn’t enable IP6, so not to have the same issue.

Last week nightly update, already in VM7, changed Mysql to 8. I am aware that it has to do with contabo updating their repos and making a major mess, not blaming VM, just reporting.

This new install, like the previous, is just the plain vanilla LAMP, the only things I add are memcached and mod_pagespeed. Upon adding memcached, the PHP-FPM in the “Server Status” disappeared, and reappeared after a few hours, maybe a “Authentic theme” cache issue.

The only issues importing backups from S3 were some virtual servers FPM socks files, that pointed to non-existing usergroups, leading to FPM not starting. That happened because of the order I reimported the servers, and was solved by editing the affected sock files and changing to correct usergroup. My fault totally, but anyway maybe there is room for improving the import scripts or alerts.
Mysql databases imported OK into MariaDB, there were some alerts regarding collation (utf8mb) but all are working fine.

DNSSEC is an issue when using Cloudflare (already was an issue before this mysql mess), leading to email delivery problems with Microsoft 365, but I’ll just leave that for a future topic.

I am very happy with VM, been using it for over a decade now and it’s my panel of choice and has been solidly improving, one sees that community requests and sugestions are included in the several updates (letsencrypt, wilcard certificates, base virtual server, …), something that does not happen in other panels. Once again, thank you guys for that.

On a side note, don’t know if already exists, but a snapshot/rollback feature for the webmin/virtualmin itself (not the servers, just the config and LAMP files) could come in handy for when the host does a mess, like in this case. Maybe be a Pro feature.

Best regards
Alex