ANSWER #1
This feels clumsy doing this in Windows but here it goes. If you are able to login to mysql, then run this script
set MYSQL_USER=root
set MYSQL_PASS=rootpassword
set SQLSTMT=SELECT CONCAT('REPAIR TABLE ',table_schema,'.',table_name,';')
set SQLSTMT=%SQLSTMT% FROM information_schema.tables WHERE engine='InnoDB'
set MYSQL_CONN=-u%MYSQL_USER% -p%MYSQL_PASS%
echo %SQLSTMT%
mysql %MYSQL_CONN% -ANe"%SQLSTMT%" > C:\windows\InnoDBCheck.sql
After you run this script, add this to my.ini
[mysqld]
init-file=C:\windows\InnoDBCheck.sql
Then, restart mysql
net stop mysql
met start mysql
during the startup, just after crash recovery, the
Forgive me for such a crude solution since I have very little dealing with MySQL for Windows (that's not sarcasm, I am serious).
ANSWER #2
Here is something more serious
I just checked my MySQL 5.6.10 no-install ZIP file. It has innochecksum.
C:\>dir \MySQL_5.6.10\bin\i*
Volume in drive C has no label.
Volume Serial Number is 2C92-485B
Directory of C:\MySQL_5.6.10\bin
01/22/2013 07:05 PM 4,065,792 innochecksum.exe
01/22/2013 07:05 PM 3,706,880 innochecksum.pdb
2 File(s) 7,772,672 bytes
0 Dir(s) 160,596,770,816 bytes free
C:\>
Download that ZIP and try running that .
You could then collect all the .ibd
files with something like this:
cd "C:\Program Files\MySQL\MySQL 5.5\data"
dir *.ibd /s/b > C:\ibdfiles.bat
You can then edit C:\ibdfiles.bat
and prepend innochecksum against every filename.
Sorry this is not a full answer, but at least you can a hold of innochecksum.exe
I've been having the exact same issues on my OpenVZ VPS's recently. Both running Debian 7 (official OpenVZ template), both with MySQL 5.6.23 from the Dotdeb repository. Random InnoDB corrupted tables, with the dump in the error log showing strange data that is definitely not part of any of my own databases, nor any files stored in my container.
Both started only recently (within the past month or so), one server was running fine for over a year prior, the other was a brand new VPS with fresh install of Debian.
My KVM VPS's running the exact same configurations have been fine thus far.
I'm sorry I can't offer a solution, but now I at least know that this is most likely not something that I caused. It has been extremely frustrating trying to figure out what is happening.
I'll let my host know that you have been having the exact same problems. Perhaps with this info they can figure out what the heck is going on. If they and I can get to the bottom of this I'll definitely let you know.
Best Answer
In general case once InnoDB tablespace is corrupt you need to re-create tablespaces from scratch. This is the safest way especially if you're not familiar with InnoDB files/format.
In some cases InnoDB writes to the error log file information about corrupted page number and index it belongs to. For example,
InnoDB crashed at that page but it doesn't mean other pages are OK. To estimate how badly a tablespace is corrupt
innochecksum
might help. Note however it's often misleading.innochecksum
checks individual pages, but not the tablespace structure. For example, all-zero page is valid ininnochecksum
's opinion while the page should have contained some data.To check that all tablespaces are good you can read data from all indexes (PRIMARY and all secondary) of all tables. For example, to check
sakila
.actor
you need two selects:If you suspect that only a secondary index is corrupt you may drop and re-create it. I describe this case in https://twindb.com/repair-corrupted-innodb-table-with-corruption-in-secondary-index/ .
If PRIMARY index is corrupt you may want to dump the table drop it and reload again. I.e. you don't reload whole database, just one table.