Fatal error 1408 when installing mariadb-server / mariadb-upgrade

Question

I recently upgraded my Debian 10 server running MySQL 5.7 to Debian 11 and MariaDB 10.3. That upgrade went smoothly. I then decided to upgrade to Debian 12 and MariaDB 10.11. While performing that upgrade, I encountered an issue with mariadb-server:

Setting up mariadb-server (1:10.11.4-1~deb12u1) ...
dpkg: error processing package mariadb-server (--configure):
 installed mariadb-server package post-installation script subprocess returned error exit status 1
Processing triggers for man-db (2.11.2-2) ...
Processing triggers for libc-bin (2.36-9+deb12u3) ...
Errors were encountered while processing:
 mariadb-server
E: Sub-process /usr/bin/dpkg returned an error code (1)

I re-ran apt install mariadb-server and got the same error. I then ran apt install -f and dpkg --configure -a but they both returned the same error as well.

Next, I ran apt purge mariadb-server and then tried to reinstall with apt install mariadb-server and got the same error as above.

It appears that the installation itself succeedes as I was able to run systemctl start mariadb so that I could then run mariadb-upgrade but that failed part of the way through with:

Phase 4/7: Running 'mysql_fix_privilege_tables'
ERROR 1408 (HY000) at line 429: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
FATAL ERROR: Upgrade failed

Since mariadb_upgrade is run as part of the installation process, I'm assuming this is what is breaking but I don't know that for sure.

Checking /var/log/mysql/error.log, I see the following:

2024-01-15 15:29:32 0 [Note] Starting MariaDB 10.11.4-MariaDB-1~deb12u1 source revision  as process 189707
2024-01-15 15:29:32 0 [Note] InnoDB: Compressed tables use zlib 1.2.13
2024-01-15 15:29:32 0 [Note] InnoDB: Using transactional memory
2024-01-15 15:29:32 0 [Note] InnoDB: Number of transaction pools: 1
2024-01-15 15:29:32 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2024-01-15 15:29:32 0 [Note] InnoDB: Using liburing
2024-01-15 15:29:32 0 [Note] InnoDB: Initializing buffer pool, total size = 256.000GiB, chunk size = 4.000GiB
2024-01-15 15:29:33 0 [Note] InnoDB: Completed initialization of buffer pool
2024-01-15 15:29:33 0 [Note] InnoDB: Buffered log writes (block size=512 bytes)
2024-01-15 15:29:34 0 [Note] InnoDB: 128 rollback segments are active.
2024-01-15 15:29:34 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2024-01-15 15:29:34 0 [ERROR] InnoDB: Error number 2 means 'No such file or directory'
2024-01-15 15:29:34 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2024-01-15 15:29:34 0 [ERROR] InnoDB: Cannot open datafile for read-only: './run_dev/#sql2-7c6-43166.ibd' OS error: 71
2024-01-15 15:29:34 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2024-01-15 15:29:34 0 [ERROR] InnoDB: Error number 2 means 'No such file or directory'
2024-01-15 15:29:34 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2024-01-15 15:29:34 0 [ERROR] InnoDB: Could not find a valid tablespace file for run_dev/#sql2-7c6-43166. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2024-01-15 15:29:34 0 [Warning] InnoDB: Ignoring tablespace for run_dev/#sql2-7c6-43166 because it could not be opened.
2024-01-15 15:29:34 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2024-01-15 15:29:34 0 [ERROR] InnoDB: Error number 2 means 'No such file or directory'
2024-01-15 15:29:34 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2024-01-15 15:29:34 0 [ERROR] InnoDB: Cannot open datafile for read-only: './tmp/#sql2fb_6c47_15.ibd' OS error: 71
2024-01-15 15:29:34 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2024-01-15 15:29:34 0 [ERROR] InnoDB: Error number 2 means 'No such file or directory'
2024-01-15 15:29:34 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2024-01-15 15:29:34 0 [ERROR] InnoDB: Could not find a valid tablespace file for tmp/#sql2fb_6c47_15. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2024-01-15 15:29:34 0 [Warning] InnoDB: Ignoring tablespace for tmp/#sql2fb_6c47_15 because it could not be opened.
2024-01-15 15:29:34 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2024-01-15 15:29:34 0 [ERROR] InnoDB: Error number 2 means 'No such file or directory'
2024-01-15 15:29:34 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2024-01-15 15:29:34 0 [ERROR] InnoDB: Cannot open datafile for read-only: './tmp/#sql70b1_76eb_0.ibd' OS error: 71
2024-01-15 15:29:34 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2024-01-15 15:29:34 0 [ERROR] InnoDB: Error number 2 means 'No such file or directory'
2024-01-15 15:29:34 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2024-01-15 15:29:34 0 [ERROR] InnoDB: Could not find a valid tablespace file for tmp/#sql70b1_76eb_0. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2024-01-15 15:29:34 0 [Warning] InnoDB: Ignoring tablespace for tmp/#sql70b1_76eb_0 because it could not be opened.
2024-01-15 15:29:34 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2024-01-15 15:29:34 0 [ERROR] InnoDB: Error number 2 means 'No such file or directory'
2024-01-15 15:29:34 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2024-01-15 15:29:34 0 [ERROR] InnoDB: Cannot open datafile for read-only: './tmp/#sql70b1_76eb_1.ibd' OS error: 71
2024-01-15 15:29:34 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2024-01-15 15:29:34 0 [ERROR] InnoDB: Error number 2 means 'No such file or directory'
2024-01-15 15:29:34 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2024-01-15 15:29:34 0 [ERROR] InnoDB: Could not find a valid tablespace file for tmp/#sql70b1_76eb_1. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2024-01-15 15:29:34 0 [Warning] InnoDB: Ignoring tablespace for tmp/#sql70b1_76eb_1 because it could not be opened.
2024-01-15 15:29:34 0 [Note] InnoDB: Setting file './ibtmp1' size to 12.000MiB. Physically writing the file full; Please wait ...
2024-01-15 15:29:34 0 [Note] InnoDB: File './ibtmp1' size is now 12.000MiB.
2024-01-15 15:29:34 0 [Note] InnoDB: log sequence number 522161950055957; transaction id 14113259426
2024-01-15 15:29:34 0 [Note] InnoDB: Loading buffer pool(s) from /sas/mysql/ib_buffer_pool
2024-01-15 15:29:34 0 [Warning] 'innodb-file-format' was removed. It does nothing now and exists only for compatibility with old my.cnf files.
2024-01-15 15:29:34 0 [Warning] 'innodb-buffer-pool-instances' was removed. It does nothing now and exists only for compatibility with old my.cnf files.
2024-01-15 15:29:34 0 [Warning] 'innodb-log-files-in-group' was removed. It does nothing now and exists only for compatibility with old my.cnf files.
2024-01-15 15:29:34 0 [Warning] 'innodb-file-format' was removed. It does nothing now and exists only for compatibility with old my.cnf files.
2024-01-15 15:29:34 0 [Warning] 'innodb-buffer-pool-instances' was removed. It does nothing now and exists only for compatibility with old my.cnf files.
2024-01-15 15:29:34 0 [Warning] 'innodb-log-files-in-group' was removed. It does nothing now and exists only for compatibility with old my.cnf files.
2024-01-15 15:29:34 0 [Warning] 'innodb-file-format' was removed. It does nothing now and exists only for compatibility with old my.cnf files.
2024-01-15 15:29:34 0 [Warning] 'innodb-buffer-pool-instances' was removed. It does nothing now and exists only for compatibility with old my.cnf files.
2024-01-15 15:29:34 0 [Warning] 'innodb-log-files-in-group' was removed. It does nothing now and exists only for compatibility with old my.cnf files.
2024-01-15 15:29:34 0 [Note] Server socket created on IP: '0.0.0.0'.
2024-01-15 15:29:34 0 [Note] Server socket created on IP: '::'.
2024-01-15 15:29:34 0 [ERROR] Incorrect definition of table mysql.event: expected the type of column 'time_zone' at position 17 to have character set 'latin1' but found character set 'utf8mb3'.
2024-01-15 15:29:34 0 [ERROR] mariadbd: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
2024-01-15 15:29:34 0 [Note] /usr/sbin/mariadbd: ready for connections.

1. How do I get mariadb_upgrade / the maraidb-server installation working?
2. What do I do about the ignored tablespaces mentioned in the error log?

Thanks!

Answer 1

Ended up submitting a bug report and got help from a MariaDB developer on how to fix this: https://jira.mariadb.org/browse/MDEV-33262.

In case that link breaks, here was his summary of the problem:

What I think is happening here:

• you've inherited mysql.event table from MySQL, the column time_zone is utf8 there.
• in MariaDB, this column is and always was using latin1.
• mariadb-upgrade handles upgrades between different MariaDB versions, it has various commands to upgrade system tables to the latest version. For example, it adds the column time_zone to the event table, if it doesn't exist. But because time_zone column never ever was utf8, it doesn't try to change an existing column to latin1.

We'd better fix it to simplify migration from 5.7.

What you can do now — if your mysql.event table is empty, you can simply drop it. mariadb-upgrade will create it again using the correct definition. Or you can use ALTER TABLE and fix the character set of this one column.

I ran the following commands to check the mysql.events table and determined that the table was empty:

~# mysql
# MariaDB [(none)]> use mysql;
# MariaDB [mysql]> select count(*) from event;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.000 sec)
 
# MariaDB [mysql]> select * from event;
Empty set (0.000 sec)

I then dropped the mysql.event table and confirmed that it had been deleted:

# MariaDB [mysql]> drop table event;
Query OK, 0 rows affected (0.584 sec)
 
# MariaDB [mysql]> select * from event;
ERROR 1146 (42S02): Table 'mysql.event' doesn't exist

I then exited the mysql console and ran mariadb-upgrade and it said the database had already been upgraded:

~# mariadb-upgrade 
This installation of MariaDB is already upgraded to 10.11.6-MariaDB.
There is no need to run mysql_upgrade again for 10.11.6-MariaDB.
You can use --force if you still want to run mysql_upgrade

I then ran mariadb-upgrade --force and it ran successfully and installing apt packages no longer kills mysql.