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.

[Zzed]

Originally posted by 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:

Error 28 means that MySql ran out of disk space. Check your disk space.

You can also check out this thread for more info: http://www.vbulletin.com/forum/showt...highlight=1016

[John]

:s - if I run this, what are the chances that it's going to eat my MySQL db?

[DWZ]

Just installed it, worked fine

Not much change in database size though, from 1.8MB to 1.74MB

oh well.

I don't know if its possible, but how about a feature where it will automatically close your forum with the message "Forum temporally closed for Repair & Optimizing Database". It will then send you a backup of the database (just in-case), and after you get it, it will start to optimize. After its all done, it will open the forum again automatically.

On a side note, I thought it was OK to distribute original vBulletin code, if you get permission from the people who made it. Just like in that updated vB code JS thingy.

[Logician]

Originally posted by scsa20
ok, I have multi-admins on my board and they like to snop around some, so how can I protect this from being open up and only allow's only userid 1 (me) to access it (kind of how the admin log works that you set in the config file)...any ideas how to do that??

Simple: Rename the directory where your vbulletin files reside! If you dont use any other script (such as news files) this will prevent ALL users accessing your vbulletin&database.

While they receive the lovely 404 Not found message, you can work with your database, then rename your dir back to whatever it is in the first place..

Or change your admins' usergroup to "member" for a while.. Let them taste how does it feel to be an ordinary user

Logician

[Nemesis77]

Awesome Hacks. Works Perfect.

Just have one question since I've never used the repair.php script until Now. For a couple tables I got this Message

"40 clients is using or hasn't closed the table properly"

What does this mean?

And is it better to close the board when running the repair script or run it when no one is on the board?

Thanks again for a great hack.

[Floris]

Originally posted by nakkid
remember the note: not supported by Jelsoft.
this is a hack made by freddie. honestly, that's why i post it.
if i'm wrong, firefly will correct this in no time... i'm sure about that.

I am sure he will if it is the case, but since repair.php is in the extras/ folder which is distributed with vbulletin.zip and therefor a part of the files where the license is applying too .. I consider it otherwise But that is just me.

[Kaelon]

Welp, I had a back-up, thankfully. I ran it on the backup, and it didn't cause me any problems. Even though I had suspended the forums while I was running it, I am guessing that someone had just made a post or fiddled with the posts table while it was being optimized to cause the error. Just my thoughts.

My optimized results - went from 64 megs to 62 megs. It's something. ;-)

Kaelon

[TECK]

Originally posted by xiphoid
since repair.php is in the extras/ folder which is distributed with vbulletin.zip and therefor a part of the files where the license is applying too .. I consider it otherwise But that is just me.

not really. mailtest.php have no note into it, as VB file and also the repair.php. those files are considered extras. still your way of thinking makes alot of sense XI.

Originally posted by Kaelon
Welp, I had a back-up, thankfully. I ran it on the backup, and it didn't cause me any problems. Even though I had suspended the forums while I was running it, I am guessing that someone had just made a post or fiddled with the posts table while it was being optimized to cause the error. Just my thoughts.

i'm glad you are back in business...
i changed the file, added some clear guidelines on how to operate with the script. i dont know if you downloaded the script again...

[Sparkz]

Well, if it becomes a problem, just change the instructions to 'copy extras/repair.php to you admin folder' and do all the filemods from there

[Floris]

side note
I do not mean to ditch you here, I am as much new to the forum as many, and Just want to be sure on things. I like it that you integrated it. Looks good and would be a nice addition. There are more files I thought of integrating, and so I did a month ago. Just no use publishing them and/or sharing them because they actually are original vb source code