MySQL Forums
Forum List  »  InnoDB

*FIXED* - Re: Database keeps crashing - Assertion Failure?
Posted by: Chris DiGanci
Date: January 26, 2014 10:37AM

So after battling this for a while, I think I finally have it fixed, and would like to share the solution.

I did break mysql from cpanel to see if that would fix it. (did a dump and a copy of the /var/lib/mysql folder first)

http://blog.cpanel.net/replacing-mysql-with-percona-in-11-36/

It made it 2 glorious minutes before crashing. No long think it's a mysql problem directly.

Tried dumping each database one by one, dropping, deleting the directory, create, import - same problem.

At this point I've almost ruled out hardware just based on the fact it's the same error over and over (the SMART alerts I get are for age but nothing bad, also did the best I could testing the memory without rebooting the box).

This is what seemed to have done the trick.
added to my.cnf

innodb_force_recovery= 4

restarted mysql in read-only, then dumped the database

mysqldump --all-databases --add-drop-table > dumpfile.sql

(I also made another copy of /var/lib/mysql just in case)

got a list of all of the databases into a file

mysql -e "show databases" > /tmp/dbnames

Edited out information_schema, performance_schema and mysql from /tmp/dbnames

dumped just the mysql table (for safety concerns)

mysqldump mysql > mysql.sql

Now, if I was going to do this over again (and I hope I don't have to), I would have excluded the mysql table from the -all-db dump (ran into innodb_table_status tablespace exists, but didn't exist, but wouldn't let me create because it exists, but wouldn't let me alter it because it didn't loop).

Quick loop through the list and dropped the databases (in Bash):

for F in `cat /tmp/dbnames`; do echo Dropping $F; mysql -e "drop database $F"; echo $F Dropped; done

Found a bunch of databases that had errant table names, errors like these:

ERROR 1051 (42S02) at line 1: Unknown table

So after it got done I saw that there were still more databases to be had, I removed the directories for the orphaned tables. I stopped mysql, moved the ib* files to a different directory and restarted mysql. Came up nice and clean, recreated the files by itself, imported the dump file and it got stuck on the mysql tables tablespace. Deleted all of the lines for that table from my dumpfile and was able to get everything back in.

Kept having issues with the mysql.innodb* tables, so I was able to delete the ibd files (I couldn't alter, discard, import, delete, create, show create or anything with them) and recreate them using these queries I found:

CREATE TABLE `innodb_index_stats` (
`database_name` varchar(64) COLLATE utf8_bin NOT NULL,
`table_name` varchar(64) COLLATE utf8_bin NOT NULL,
`index_name` varchar(64) COLLATE utf8_bin NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
`stat_value` bigint(20) unsigned NOT NULL,
`sample_size` bigint(20) unsigned DEFAULT NULL,
`stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

CREATE TABLE `innodb_table_stats` (
`database_name` varchar(64) COLLATE utf8_bin NOT NULL,
`table_name` varchar(64) COLLATE utf8_bin NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`n_rows` bigint(20) unsigned NOT NULL,
`clustered_index_size` bigint(20) unsigned NOT NULL,
`sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

Has been running without issue for 9+ hours now, which is the longest it's gone without failure in a few weeks.

Hope this helps someone.

Chris

Options: ReplyQuote


Subject
Views
Written By
Posted
*FIXED* - Re: Database keeps crashing - Assertion Failure?
21422
January 26, 2014 10:37AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.