0

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 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.)

In /usr/local/var/mysql I have all the files:

ibdata1
ib_logfile_0
ib_logfile_1

Then a directory for each database containing the frm and ibd files (for each table), and then a db.opt.

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.

Edit:

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

migrated from stackoverflow.com Dec 14 '18 at 18:45

This question came from our site for professional and enthusiast programmers.

  • 1
    "Coming here from StackOverflow" Did you mean to post on Database Administrators? That's where this would be on-topic. – Filburt Dec 14 '18 at 10:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.