11-27-2022 MySQL Issue ibdata1 corruption

Tags
mysqlerrors
Status
Done

Issue

2022-11-27T04:02:11.075594Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.30-22) starting as process 21377
2022-11-27T04:02:11.215516Z 0 [Warning] [MY-013907] [InnoDB] Deprecated configuration parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=209715200. Please use innodb_redo_log_capacity instead.
2022-11-27T04:02:11.237445Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-11-27T04:02:11.237538Z 1 [ERROR] [MY-012271] [InnoDB] The innodb_system data file 'ibdata1' must be writable
2022-11-27T04:02:11.237561Z 1 [ERROR] [MY-012278] [InnoDB] The innodb_system data file 'ibdata1' must be writable
2022-11-27T04:02:11.237590Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2022-11-27T04:02:11.237734Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2022-11-27T04:02:11.237769Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-11-27T04:02:11.238274Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.30-22)  Percona Server (GPL), Release '22', Revision '7e301439b65'.
2022-11-27T04:02:45.304979Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2022-11-27T04:02:45.307097Z 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-log-path: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2022-11-27T04:02:45.307177Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
2022-11-27T04:02:45.307199Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.30-22) starting as process 22000
2022-11-27T04:02:45.358655Z 0 [Warning] [MY-013907] [InnoDB] Deprecated configuration parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=209715200. Please use innodb_redo_log_capacity instead.
2022-11-27T04:02:45.361800Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-11-27T04:02:46.073779Z 1 [ERROR] [MY-012263] [InnoDB] The Auto-extending innodb_system data file './ibdata1' is of a different size 0 pages (rounded down to MB) than specified in the .cnf file: initial 768 pages, max 0 (relevant if non-zero) pages!
2022-11-27T04:02:46.073829Z 1 [ERROR] [MY-012960] [InnoDB] Cannot create redo log files because data files are corrupt or the database was not shut down cleanly after creating the data files.
2022-11-27T04:02:47.073718Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2022-11-27T04:02:47.074071Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2022-11-27T04:02:47.074115Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-11-27T04:02:47.074925Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.30-22)  Percona Server (GPL), Release '22', Revision '7e301439b65'.

  • Disabled systemctl disable apparmor, didn’t fix the issue.
  • Disabled monit, didn’t fix the issue.
  • Someone deleted ib_logfile0 and ib_logfile1, restored them didn’t fix the issue.
  • Downgraded to 8.0.19 due to a failed/half upgrade to 8.0.29
    • 2022-11-08 04:28:52 upgrade percona-server-server:amd64 8.0.19-10-1.bionic 8.0.29-21-1.bionic
    • Didn’t fix the issue.
  • Upgraded back to 8.0.29, didn’t fix the issue.
    • 2022-11-28T12:12:13.724969Z 1 [ERROR] [MY-012526] [InnoDB] Upgrade is not supported after a crash or shutdown with innodb_fast_shutdown = 2. This redo log was created with MySQL 8.0.29-21, and it appears logically non empty. Please follow the instructions at http://dev.mysql.com/doc/refman/8.0/en/upgrading.html
  • Nuked mysql configuraiton and added
  • innodb_force_recovery = 3
    innodb-stats-persistent             = 0
    innodb-stats-transient-sample-pages = 20
    innodb-stats-auto-recalc            = 0
  • MySQL started and databases could be dumped successfully.

Issue Cause

Ultimately the issue was caused by the innodb_fast_shutdown = 2 configuration option during a improper shutdown of the mysql server.