4

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.

2
  • Do you see anything in the /var/log/mysql/error.log file that might point to an issue? There are instances where an upgrade from 5.x to 8.0 can result in MySQL hanging at this stage, particularly if there is any hint of corruption on one of the mysql system tables ...
    – matigo
    Apr 27, 2021 at 0:28
  • For me it's clearly a serious MySQL bug. Yes, there was a problem in my stored procedures that turned out incompatible with 8.0. But the whole server module become critically broken using over 30% of CPU without any way to stop it. Maybe I post my solution as answer.
    – Harry
    Apr 27, 2021 at 11:23

3 Answers 3

2

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 ;)

2
  • this repo no longer offers 5.7
    – deltree
    Sep 23, 2021 at 0:20
  • But...this isn't in anyway helpful to the answer. You just reverted back to 5.7
    – Chris S
    Jul 19, 2022 at 14:31
1

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.