"Data Dictionary upgrade from MySQL 5.7 in progress" takes forever for some small databases

Question

I made release update recently from 18.04 to 20.04. After update SQL doesn't work. After checking status with systemctl status mysql.service I get this:

● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: activating (start) since Mon 2021-04-26 00:09:58 CEST; 3s ago
    Process: 115994 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
   Main PID: 116002 (mysqld)
     Status: "Data Dictionary upgrade from MySQL 5.7 in progress"
      Tasks: 29 (limit: 9450)
     Memory: 382.6M
     CGroup: /system.slice/mysql.service
             └─116002 /usr/sbin/mysqld

Will it get up eventually? I see mysql processes use up a lot of CPU (like 30%), but 4 hours have passed and it's like nothing happens. Is it broken?

BTW, I have important data in the DB. The only backup I have is just copied /var/lib/mysql directory. But I guess it would not be easy to recover?

The databases I have on that server are really small, so I'm so surprised it takes so long.

Answer 1

i solved it by removing mysql

$ apt remove mysql-*

and then installing back mysql 5.7 folowing this guide

https://www.claudiokuenzler.com/blog/991/install-mysql-5.7-on-ubuntu-20.04-focal-avoid-8.0-packages

happily i found back my data without having to restore a backup

I really think automatic upgrade with a broken compatibility version is a bug ;)

Answer 2

It turned out that was indeed an error in one of my databases, incompatibility in stored procedures. I figured exact error by looking at /var/log/mysql/error.log file as suggested in a comment above.

However - MySQL server became totally unresponsive, taking roughly 30% of server CPU all the time. I tried to kill the process, it was restarted immediately.

Even sudo apt remove mysql-server mysql-server-8.0 didn't help.

I tried everything to just start the server normally without converting or importing data, but nothing worked.

So here's what I did to recover my data and start over:

I created a new virtual machine, installed Ubuntu Server 18.04 LTS on it, installed MySQL, copied all my backed up MySQL data files onto it, changed owner to mysql user, started the server and then I was able to use mysqldump to export all of my databases to "proper" sql files.

Then I purged all MySQL server packages with sudo apt purge mysql-* and then reinstalled fresh MySQL server. Then imported my data from sql files. Of course, as I could expect - one of the databases couldn't be imported because of the incompatibility mentioned above, but I fixed the offending code and imported remaining data.

Everything works. BTW, I learn the hard way to use proper quoting of field and table names, as some common words could become keywords in future versions of MySQL. So ALWAYS quote the names in backticks, even if it seems unnecessary.

Answer 3

Simplest solution to get your server running smoothly again without losing data:

sudo apt purge mysql-* (but don't delete the mysql data folder)

and then install mariadb sudo apt install mariadb-server

I did that and everything runs smoothly again using the mysql-5.7 data folder.