High Performance Blog

Today one of my scripts prompted the #2013 MySQL error while querying a huge innoDB table (31GB in ~154million rows). Some queries worked, some just failed.

Looking at the logfiles, I saw the following message:

InnoDB: Page checksum 1840120551 (32bit_calc: 1224736073), prior-to-4.0.14-form checksum 1811838366
InnoDB: stored checksum 3031359782, prior-to-4.0.14-form stored checksum 1811838366
InnoDB: Page lsn 47 623631862, low 4 bytes of lsn at page end 623631862
InnoDB: Page number (if stored to page already) 68664,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 22
InnoDB: Page may be an index page where index id is 35
InnoDB: (index “PRIMARY” of table “tracking”.”banner” /* Partition “p4″ */)
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 68664.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.

I tried to check the table -> mysql failed.

I tired to recover the table -> mysql failed.

The filesystem as well as the RAID were in a healthy condition and even rebooting the machine didn’t to the trick (would have been odd if it did). So, I asked my friend Google, and Google pointed me to an interesting post:

I ran into a problem where, when dealing with HUGE tables (location tables for http://Stiggler.com ), there was an innodb page error, and mysql would try over and over to repair it, and would inform me that it could not repair it (and would then try again, etc).

[...]

I put the force_recovery mode to 1, then restarted mysqld, exported the entire database (i expected to get an error when it got to the bad table, but i never had a problem). After dumping the database, i removed the force_recovery option from my.cnf and restarted the service, and after a few moments, it started back up, and the problem was gone.

I can’t remember where exactly I found this quote (sorry!), but what this guy was basically trying to say: Dump your database and wait for the magic to happen!

So I did, tried my query again and it just works! I dunno what magic voodoo effect dumping a database with mysqldump does, but it just works. Really, this seemed like the most stupid approach to fix this issue, but it did the trick!

Next time you hit the “#2013 Lost connection” issue.. try dumping your database!

Today one of my scripts prompted the #2013 MySQL error while querying a huge innoDB table (31GB in ~154million rows). Some queries worked, some just failed. Looking at the logfiles,...