Coming here from StackOverflow because I saw a similar question being closed as off-topic on there.
I have corrupted my local MySQL instance in my development environment. I was running a set of migration scripts to add emoji support to our MySQL databases.
The team involved had no problems running these, and all the production migrations were fine. On my local machine, I had some large tables and my computer stopped responding half way through (was on a video call), and I landed up restarting my laptop.
On restart, it turned out that MySQL (
mysqld) wouldn't start up.
Here is an extract of the message:
InnoDB: End of page dump 2018-12-12 14:22:14 7fff99966380 InnoDB: uncompressed page, stored checksum in field1 2460282070, calculated checksums for field1: crc32 1414672877, innodb 1242738654, none 3735928559, stored checksum in field2 2655453953, calculated checksums for field2: crc32 1414672877, innodb 2655453953, none 3735928559, page LSN 54 2863355779, low 4 bytes of LSN at page end 2863355779, page number (if stored to page already) 132898, space id (if created with >= MySQL-4.1.1 and stored already) 279183 InnoDB: Page may be an index page where index id is 32782 InnoDB: Also the page in the doublewrite buffer is corrupt. InnoDB: Cannot continue operation. InnoDB: You can try to recover the database with the my.cnf InnoDB: option: InnoDB: innodb_force_recovery=6 2018-12-12 14:22:14 7fff99966380 InnoDB: Assertion failure in thread 140735770157952 in file buf0dblwr.cc line 559 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.6/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 12:22:14 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. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388608 read_buffer_size=131072 max_used_connections=0 max_threads=151 thread_count=0 connection_count=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 68238 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong...
I tried to restart
mysqld with a number of different options like `innfodb_force_recovery=6```. but I had no luck in any of these cases.
Here is an example of a command that was running in a migration script when the failure occurred:
-- execute("ALTER TABLE `survey_responses` ROW_FORMAT=DYNAMIC CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
my.cnf looks like this:
[mysqld] sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES innodb_log_file_size = 500331648 max_allowed_packet = 128M innodb_file_format = Barracuda innodb_large_prefix = 1 innodb_file_per_table = 1 innodb_strict_mode = 1 init_connect='SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci' character_set_server = 'utf8mb4' collation_server = 'utf8mb4_unicode_ci'
No matter what I have tried, I can't seem to get back up and running. Before going full nuclear and setting up 7 apps and DB's from scratch, I'd like to learn more about possible recovery.
I didn't do any dumps before running the migrations (bad I know- but non production data, migration was smooth for a lot of others etc.)
/usr/local/var/mysql I have all the files:
ibdata1 ib_logfile_0 ib_logfile_1
Then a directory for each database containing the
ibd files (for each table), and then a
I backed up the whole directory, and then deleted the ibdata1 + the log files. These files rebuilt, but after that point mysql shows that the tables don't exist (find it interesting how there is a single file for all DB's in the instance - a bit scary - because migrating 1 DB can affect all)
Is there a way I can recover from these files? Especially the DB's that were not involved in this migration?
I've lost a bit of faith in InnoDB around this, because if something like this happened in production, it would have been a nightmare.
Any help would be appreciated.
I have looked at these with no resolve: MySQL data recovery with INNODB https://serverfault.com/questions/449201/recover-mysql-database-mysql-mysqldump-gives-table-database-tablename-doe?newreg=ee4023883b43454bb672ee010f7f1962#449238