A few days ago I was forced to format my PC due to an issue with a Windows update, I managed to make a backup first and now I'm trying to restore the files. I have a mysql instance that I'm having troubles restoring. If i try to run the database I have this error
2022-10-02T20:07:56.238696Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.
2022-10-02T20:07:56.239311Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2022-10-02T20:07:56.239429Z 0 [ERROR] [MY-010119] [Server] Aborting
If i make a new data directory using
--initialize it starts working again (in a clean state of course). Then if I copy all the files except for the mysql directory (when I do that I get the error above) I get a bunch of these errors
2022-10-02T19:59:28.774381Z 1 [ERROR] [MY-011971] [InnoDB] Tablespace 'mysql' Page [page id: space=4294967294, page number=122] log sequence number 2608993377 is in the future! Current system log sequence number 2608601904.
2022-10-02T19:59:28.774773Z 1 [ERROR] [MY-011972] [InnoDB] Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html for information about forcing recovery.
Then errors like these
2022-10-02T19:59:28.775093Z 1 [Warning] [MY-013021] [InnoDB] A transaction id in a record of table `mysql`.`collations` is newer than the system-wide maximum.
And finally the server crashes with this error
2022-10-02T19:59:28.775869Z 1 [ERROR] [MY-013183] [InnoDB] Assertion failure: trx0rec.ic:96:next_undo_offset > undo_offset thread 13140
If I use
innodb_force_recovery = 5 or 6 the server works again but I can't see my old tables.
In the past in situations like these I managed to recover my databases without issue (also looking on the internet this is supposed to be working just fine) but this time it seems something is wrong. My suspects are:
- There's a corrupted file somewhere
- Some permissions got lost in the backup
But i've checked files in the data/mysql folder and they seem fine (they look similar to the ones generated by
--initialize), I also tried to change ownership and permissions on the files but that still didn't help
The version of MySQL I'm using is 22.214.171.124
I had to pause trying to recover this mysql instance for a while but today I tried again and I noticed when I use --initialize it generates a file called mysql.ibd which is missing from my backup, when this file is missing I get the error "Data Dictionary initialization failed.", if I copy my data and add that file the instance works but obviously I won't see my databases
I have already tried this .ibd file is missing and alter table discard tablespace crashing the server
But it didn't work