Mysql/MariaDB Upgrade from Debian 9 to 10

I read the release notes for Debian:

https://www.debian.org/releases/stable/amd64/release-notes/

Did the standard upgrade from Debian 9(stretch) to Debian 10 (buster)

In this blog we will cover the proper way to upgrade mysql as to not to get this problem. While I’m not saying this covers all steps mysql/mariadb upgrade, its a good lesson on what to expect if upgrade is done incorrectly.

apt update
apt-get upgrade
sudo mysqldump --all-databases -u root -p > debian_9to10_20191202_mysqlbackup.sql
###Conclusion of this blog post:You need to restart before the upgrade. ###
###The key is to release any locks on a database from a apache2 or other ###
###services that have active connections. ###
###So Reboot at this step (reboot here too) ###
change the /etc/apt/source.list to new debian buster stable.
apt update
apt-get upgrade
#went through standard review of install package maintainer stuff or show diff.
apt full-upgrade
#went through standard review of install package maintainer stuff or show diff.
#reboot

This what happened. Watch the failure, and concluding next steps.
Unfortunately mariaDB did not start, and started to get error messages.

[ERROR] InnoDB: Failed to find tablespace for table lm.recall_db in the cache. Attempting to load the tablespace with space id 103
2019-12-08 22:24:36 171 [ERROR] InnoDB: Failed to find tablespace for table mysql.innodb_index_stats in the cache. Attempting to load the tablespace with space id 2
2019-12-08 22:24:36 171 [ERROR] InnoDB: Refusing to load ‘./mysql/innodb_index_stats.ibd’ (id=105, flags=0x21); dictionary contains id=2, flags=0

…..

[ERROR] InnoDB: In pages [page id: space=0, page number=11412] and [page id: space=0, page number=11399] of index url of table wordpress.wp_redirection_404

tail -f /var/log/mysql/error.log

Because I use InnoDB there is no recovery/repair options because it not suppose to crash? The logs talk about

mysql [ERROR] InnoDB: Corruption of an index tree: table dump + drop + reimport

BEFORE YOU PROCEED WITH READING PLEASE USE MYSQLDUMP AND MAKE A COPY OF YOUR DATA, OR MAKE A COPY OF THE WHOLE MYSQL FOLDER. GOOGLE IT.

NEXT STEPS IN THIS BLOG CONTAINS Some material may be CATASTROPHIC for BUSINESS UNDER THESE CONDITIONS.

In order to do anything I had to get mysql started. We need to start the recovery mode. Read online but we are starting from 1, then take one step at a time.
vi /etc/mysql/mariadb.conf.d/50-server.cnf

and add

innodb_force_recovery =1

You continue

mysqldump –all-databases –add-drop-database –add-drop-table > dump_after_crash_20191208.sql -u root -p

  • Notes:
  • https://jira.mariadb.org/browse/MDEV-15234 [Deals with TABLESPACE and possible recovery?]
  • https://blog.ghost3k.net/articles/mysql/169/fixing-mysql-innodb-index-corruption [Deals with possibly using optimize command to fix the table, in my case as soon as I did anything to a broken table mariadb/mysql server crashed and need to be restarted. This Article was helpful to learn the thought process]
  • https://www.percona.com/blog/2008/07/04/recovering-innodb-table-corruption/ [similar to above trying to use optimize to fix table]
  • https://forums.cpanel.net/resources/innodb-corruption-repair-guide.395/ [helpful in the trying to find the issue, after that it gets into drop, create insert data which I didn’t want to do]
  • https://mariadb.com/kb/en/library/innodb-recovery-under-maria/+comments/354 [Comments on recovery in InnoDB and how it not suppose to crash, I’m running on bare hardware so not virtualized ]
  • https://serversforhackers.com/c/mysqldump-with-modern-mysql [Trying to mysqldump after upgrade and crash to see if I can export data before I’m forced to completely corrupt the data, first mention the PV program]
  • https://mariadb.com/kb/en/library/innodb-data-dictionary-troubleshooting/ [This now gets toward the end aka playing with .ibd and .frm files which are files that mysql uses to store data. I was not able to use this info other to learn the process of possible recovery]
  • https://bugs.mysql.com/bug.php?id=67179 [Similar issue as mine, Tablespace is missing for table ‘mysql/innodb_index_stats’ , this one is for windows so not related but good to see how they troubleshoot]
  • googling: InnoDB: You should dump + drop + reimport the table to fix the corruption.
  • https://dba.stackexchange.com/questions/116730/corrupted-innodb-table-mysqlcheck-and-mysqldump-crash-server [related to my problem, using command mysqlcheck --all-databases -u root -p when it gets to a table that is corrupt it crashes the whole mysql server. Its hard to fix it if DB is not ON.
  • [https://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html] Lets read up on recovery options for innodb.
  • As I proceed from 1 to 2 to 3, googling this exact search yelded few links “innodb_force_recovery = 4”
  • [https://www.gcsdstaff.org/roodhouse/?p=3070] Quick intro on repairing corrupt innodb.
  • [https://bugs.mysql.com/bug.php?id=64517] bug that seems to related but its old.
  • [https://dba.stackexchange.com/questions/65728/forcing-innodb-recovery-of-a-corrupted-database] Getting dangerous. At this point the hopes of recovery are low, and knowing we have a full dump prior to upgrade we are forcing innodb recovery of corrupter database. Please note that if this is bad. copy mysql folders to make a copy of db files if need be.
  • [http://download.nust.na/pub6/mysql/doc/refman/5.1/en/forcing-recovery.html] More technical force recovery to learn more on what it does.
  • [https://bugs.mysql.com/bug.php?id=67595] Mysqlbug that has similar errors as mine, except it was running Windows Enterprise 64bit
  • [https://bugs.launchpad.net/percona-xtrabackup/+bug/1541763] Another ticket. Somewhat similar but not Really. Do read do.
  • [https://www.sqlservercentral.com/forums/topic/corrupted-innodb-table-crashing-mysql-instance-how-to-recover-table] ok read on what people are saying.
  • [google search “innodb_index_stats Warning : Tablespace is missing for table “] We are recovering individual tables to see if its just one table preventing recovery of others.
  • [https://forums.percona.com/discussion/49806] Related to innodb_index_stats innodb_table_stats slave_master_info slave_relay_log_info slave_worker_info
  • [https://dba.stackexchange.com/questions/54608/innodb-error-table-mysql-innodb-table-stats-not-found-after-upgrade-to-mys/54624#54624] yet more on innodb_table_stats
  • [https://bobcares.com/blog/mysql-tablespace-is-missing-for-table/] This now gets very deep. We are playing with idb and frm files… be aware what this means. No going back at this point.
  • [https://dba.stackexchange.com/questions/48166/cannot-open-table-mysql-innodb-index-stats] More playing with tables, and restoring and overwriting and changing structures… this is bad.
  • [https://jira.mariadb.org/projects/MDEV/issues/MDEV-21244?filter=allopenissues] Good read on a bug, upgrade causes crash.
  • [https://wordpress.stackexchange.com/questions/298734/wp-redirection-404-table-has-grown-to-7gb] What is wp-redirection-404 table?
  • [google “mysql dump restore table all databases how to resture only one”] Trying to restore failed tables from backup. It looks like you can’t restore one table if you backuped all tables without extra scripts. [https://linuxize.com/post/how-to-back-up-and-restore-mysql-databases-with-mysqldump/]

[https://www.cyberciti.biz/faq/linux-completely-reinstall-mysql-server/] Backup and restore. Learning how to restore to understand what can go wrong.

As you imagine my story didn’t end well. After 2 days we were left with no choice but to restore from a mysqldump we did prior to upgrade. I hope this allows you to understand what could go wrong and make appropriate changes to your upgrade process to make sure you are covered.

Have fun!

Lucas