It’s just one of those days! I began receiving emails like this:
Database error in vBulletin myversion:
Invalid SQL:
INSERT INTO searchcore_text (searchcoreid, keywordtext)
VALUES ( 1584876, ‘ SOME TEXT WAS HERE.’ )
ON DUPLICATE KEY UPDATE searchcoreid = VALUES(searchcoreid), keywordtext = VALUES(keywordtext);MySQL Error : Table ‘./mydatabase/searchcore_text’ is marked as crashed and last (automatic?) repair failed
Error Number : 144
Request Date : Wednesday, January 29th 2014 @ 07:19:29 AM
Error Date : Wednesday, January 29th 2014 @ 07:19:29 AM
IP Address : xxxxx
Username : someuser
Classname : vB_Database
MySQL Version :
As you can see, no fun indeed. My vBulletin searchcore_text table corrupted. Because our forum is large with around 1000 people on at any given time, hundreds of these emails were coming in each day. I should have repaired this sooner but needed questions answered, and I was in the middle of a systemic server crash. But anyway.
The accepted advice from vBulletin is to run a repair on the table. I could not turn off MySQL because the forum needed to stay up, this ruled out using myisamchk as you are supposed to shut down mysqld for that tool. This means I had to use REPAIR TABLE from inside mysql itself. It is thought that this is slower, but oh well. I have a lot of data, but not THAT much (about 1.2 million rows in this table). So be it.
The first step in a MYISAM repair is make a backup. If your repair fails, it can cause worse damage, so back it up, and if the first repair doesn’t work, you can try another type. To get my backup, I couldn’t run mysqlhotcopy because it simply says the table is corrupt and quits. So I was left with the only option and that is to manually copy the 3 data files to a backup location. Any MYISAM table is made up of 3 files with extensions “frm”, “MYD”, and “MYI”.
To back them up, I first made a directory to store the backup, I put it in the home directory of the account with the corrupt table:
[bash light=”true”]cp searchcore_text.* /home/myaccount/safetybackup[/bash]
My files were about 1.5GB so it took a few seconds.
Next, I wanted to run the actual repair command from its own window session using the screen command. Since I am on Windows and use Putty to connect to my server, if my session is lost and Putty closes, I don’t want to lose the process or to be able to monitor it. So type this to create a screen for running the repair:
[bash light=”true”]screen –S repair[/bash]
Note that is a capital “S” and the word “repair” is just a name I’m choosing for the screen, it can be whatever name you want. Now, even if Putty crashes or times out, if I log back in to the server, this screen will still be there with any commands I’ve run still going.
With this screen attached (verify with ‘screen –ls’, also try ‘echo $STY’), I run the repair:
mysql
use your_forum_database;
repair table searchcore_text;After that command I get a blank cursor just sitting there, the repair is now doing its thing.
I won’t go in to all the options of using screen, but at this point I opened a new Putty terminal and logged in to the server. You can also switch screens in the same terminal if you like.
In the second terminal window I have a fresh command line, and my mysql repair is happily going along in the other screen. You can see with “screen –ls” that your repair screen is attached.
Now I type this to see the process:
mysql
SHOW FULL PROCESSLIST;This gives me a small table where I see the “State” of the repair. It should say “Repair by sorting” for example. If it says anything else like using “keycache”, you may need to adjust your MySQL settings and try again. Keycache method could take forever, there are horror stories.
In my case, I ran in to an issue where the /tmp folder filled up to capacity causing MySQL to just stop repairing. No errors, no messages, it just stops, you have to monitor your temp folder!
To fix that, I created a folder in the mysql data directory to use as a temp folder, set my.cnf variable, set “mysql” as the owner of the folder, and restarted MySQL. After that, I ran the repair again. Note that my /tmp folder was only 500MB, the repair easily needed over 1.2GB to run. With the new temp folder set, the repair operation took all of 5 minutes.
I verified the repaired table was good, then deleted my earlier backup. I also had to reset the mysql temp folder and restart the service again. I do that with this command:
/etc/init.d/mysqld restartIn a few seconds, it is restarted and rocking away! With the table repaired, there was a couple days of activity on the forum not indexed, so I did have to tell vBulletin to rebuild the search index. That itself is no small operation.
References:
http://dev.mysql.com/doc/refman/5.5/en/repair-table.html
http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_tmpdir
http://www.linux.com/learn/tutorials/285795-taking-command-of-the-terminal-with-gnu-screen-