mysqld constantly shutdown when trying to upgrade from '80033' to '80034'

I left my ghost website running for a week on an ubuntu22.04 droplet on digitalocean and came back seeing mysql.service always in the status of "Server upgrade in progress".

root@ghostonubuntu2204-s-1vcpu-1gb-sfo3-01:~# systemctl status mysql.service
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: activating (start) since Wed 2023-08-23 22:55:08 UTC; 1min 43s ago
    Process: 15825 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
   Main PID: 15833 (mysqld)
     Status: "Server upgrade in progress"
      Tasks: 28 (limit: 1100)
     Memory: 670.6M
        CPU: 1min 431ms
     CGroup: /system.slice/mysql.service
             └─15833 /usr/sbin/mysqld

Aug 23 22:55:08 ghostonubuntu2204-s-1vcpu-1gb-sfo3-01 systemd[1]: Stopped MySQL Community Server.
Aug 23 22:55:08 ghostonubuntu2204-s-1vcpu-1gb-sfo3-01 systemd[1]: mysql.service: Consumed 1min 1.013s CPU tim>
Aug 23 22:55:08 ghostonubuntu2204-s-1vcpu-1gb-sfo3-01 systemd[1]: Starting MySQL Community Server...

I checked the error log and it indicates that mysql server is trying to upgrade from '80033' to '80034' but seems to fail over and over again.

```
root@ghostonubuntu2204-s-1vcpu-1gb-sfo3-01:~# tail -f /var/log/mysql/error.log
2023-08-23T22:53:15.741426Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-08-23T22:53:40.579981Z 4 [System] [MY-013381] [Server] Server upgrade from '80033' to '80034' started.
2023-08-23T22:55:07.392287Z 4 [ERROR] [MY-013178] [Server] Execution of server-side SQL statement 'ALTER TABLE user MODIFY ssl_type enum('','ANY','X509', 'SPECIFIED') NOT NULL; ' failed with error code = 3664, error message = 'Failed to delete SDI 'mysql.user' in tablespace 'mysql'.'.
2023-08-23T22:55:07.400274Z 0 [ERROR] [MY-013380] [Server] Failed to upgrade server.
2023-08-23T22:55:07.401543Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-08-23T22:55:08.356

I had the same issue with my Ghost blog a couple of days ago :(

First of all, I had to enable OS swap because my MySQL daemon was being killed by OOM killer time to time - https://www.digitalocean.com/community/tutorials/how-to-add-swap-space-on-ubuntu-20-04
My current guess is that this issue was triggered by an interrupted update.

Anyway, the next step was to stop mysql service and run upgrade manually (https://dev.mysql.com/doc/refman/8.0/en/upgrading-what-is-upgraded.html):

systemctl stop mysql

mkdir /var/run/mysqld/
chmod -R 777 /var/run/mysqld/

/usr/sbin/mysqld --upgrade=MINIMAL
/usr/sbin/mysqld --upgrade=FORCE

This resolved the 'ALTER TABLE user MODIFY ssl_type enum' issue for me.

Note, I had to open another Digital Ocean console to kill mysqld that I started manually.

ps auxf | grep mysqld
kill -9 XXX

Without that you cannot start systemd's MySQL: Unable to lock ./ibdata1 error: 11

systemctl start mysql
systemctl status mysql

StackExchange Database Administrators Q#330571, answer score: 4