MySQL fails to start after a failed upgrade and bosh recreate

Products

• VMware Tanzu Application Service for VMs

Issue/Introduction

You are running a TAS and it fails on pre-start on MySQL vm. For this example TAS is upgraded from 2.11.27 to TAS version 4.0.8.

To get more details:
 

bosh -d <cf-deployment> ssh <failing-mysql-vm>

Looking into /var/vcap/sys/log/pxc-mysql/mysql.err.log  throws:
 

2023-10-23T05:14:23.638968Z 0 [ERROR] [FATAL] InnoDB: Table flags are 0 in the data dictionary but the flags in file ./ibdata1 are 0x4000! 2023-10-23 05:14:23 0x7efd001aa740 InnoDB: Assertion failure in thread 139625093572416 in file ut0ut.cc line 924 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 05:14:23 UTC - mysqld got signal 6 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. Attempting to collect some information that could help diagnose the problem. As this is a crash and something is definitely wrong, the information collection process might fail. Please help us make Percona XtraDB Cluster better by reporting any bugs at https://jira.percona.com/projects/PXC/issues

Environment

Product Version: 4.0

Resolution

The error message

 (InnoDB: Table flags are 0 in the data dictionary but the flags in file ./ibdata1 are 0x4000!`) 

is a common sign of downgrading from MySQL v8.0 to v5.7. MySQL v5.7 cannot interpret the data files and panics and throws this error message. Downgrade might have happened if you have an upgrade that failed and bosh recreate was executed on the failing MySQL vm.

To resolve issue:

1. bosh -d <cf-deployment> ssh <failing-mysql-vm> 

2. To verify if MySQL has been upgraded or not, list files in /var/vcap/store/pxc-mysql. If there is a file named mysql.ibd file OR a directory named '#innodb_redo' , the instance has been previously upgraded.

3. Make a backup of the database data folder so you have the option to revert to database original state if needed
cp -r /var/vcap/store/pxc-mysql /var/vcap/store/pxc-mysql-backup

4. If MySQL had been previously upgraded , deleting the two files written by MySQL 5.7 , namely removing the files: 
/var/vcap/store/pxc-mysql/ib_logfile0
/var/vcap/store/pxc-mysql/ib_logfile1

5. Apply Change on OpsManager UI to continue with the upgrade