freddie's Repair & Optimize Database (vB integration)

As we all know, there is a great tool included in the /extras folder, called [high]repair.php[/high].
I think not alot of people use it, because probably they don't know the gold mine hidden on it, related to tables optimisation.

freddie's script will repair your database, but ALSO will optimize the tables. So what is that means? It will remove all the empty spaces that clutter the database. The result? A smaller database size.

I ran the script on my localhost. At the beggining my database was 1.63MB. After I completed the repair&optimize process, my database was shrinked to 0.82MB!! That's almost half of the original size!!

So I decided to integrate this tool directly into my admin CP.
NOTE: The script is made by freddie, I only adapt it to the VB security and made it look like part of your CP. Also, my goal was to be able to optimize easy my database (with one click), not to repair it.

You can use this script in combination with another hack, also made freddie:
Admin Quick Stats

[high]INSTALL PROCEDURE[/high]
In /admin/index.php, find:

makenavoption("vBulletin Options","options.php?t=0","<br>");

BELOW THIS, add:

makenavoption("Repair & Optimize Data","repair.php?action=start","<br>");

Now, upload repair.php into your /admin folder. You are done.

[high]UPDATE[/high]
Quoted from freddie's post:

Originally posted by freddie
I wrote repair.php as something that I could run nightly on my forum as it was getting periodic corruption. It has run nightly for probably a year now and I have not had a corrupt table since.

I thought that perhaps it might help out others and be an easier than telling everyone to go run myisamchk on their databases to fix problems. That being said, I only tested it on my forum so that is why it says "Not Supported". It won't work on non MyISAM tables and it won't work if the user table is corrupted.

[TECK]

"getting ready to run the script" screenshot...

UPDATE: refresh to see the new text warning...

[TECK]

"Repair & Optimize (in action)" screenshot...

[Kaelon]

This is a great script! I never knew it existed.

I ran it a couple of times, and here's a problem that I ran into:

Checking table post
TABLE OPERATION TYPE TEXT
post check warning Table is marked as crashed and last repair failed
post check warning Size of indexfile is: 1652736 Should be: 1024
post check error Can't read indexpage from filepos: -1
forums.post check error Corrupt
Repairing table post
post repair error 28 when writing to datafile

Any ideas how to fix this?

Thanks!

Kaelon

[Kaelon]

Oh, boy - not good. After the above error, I'm unable to access my forums.

I get the following error:

Database error in vBulletin 2.2.5:

Invalid SQL: SELECT COUNT(*) AS posts FROM post
mysql error:

mysql error number: 0

Date: Wednesday 01st of May 2002 11:24:24 PM
Script: http://www.alleria.com/forums/forums/index.php?s=
Referer: http://www.alleria.com/forums/admin/...hp?action=head

Any thoughts?

[TECK]

i quote from freddie:

This script will attempt to repair corrupted databases. While it can repair most common errors, it can not repair everything.

he probably can give us more information related to this issue.

[Kaelon]

Yes, however, this problem didn't exist until I ran the repair.php script. It seems to have generated this problem. Do we know anything about the post Table that I could do to fix it by running several queries on it?

Here it is again:

Checking table post
TABLE OPERATION TYPE TEXT
post check warning Table is marked as crashed and last repair failed
post check warning Size of indexfile is: 1652736 Should be: 1298432
post check error Found 31835 keys of 31834
forums.post check error Corrupt
Repairing table post
post repair error 28 when writing to datafile

[Kaelon]

That's really unfortunate. The forums were working as intended and it seemed to be running just fine prior to my running this script. It appeared that however it attempted to correct one of the tables ended up creating a problem sufficient enough to cause the forums to stop working.

I recommend that users not use this script until we know a bit more about it.

Kaelon

[scsa20]

or at least make a backup of the database before running the script

[TECK]

good point.. i will add this in the info.. in the repair.php file.
i only put the text that freddie had in there...