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:BELOW THIS, add:Now, upload repair.php into your /admin folder. You are done. :)

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

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

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

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

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:

Any ideas how to fix this?

Thanks!

Kaelon

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

I get the following error:

Any thoughts?

i quote from freddie:he probably can give us more information related to this issue.

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:

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

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

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

hey, I might be stupid (cuz somethings I run stuff without backing up files) but when it something that "so'pose to fix" something, I aways make a back up ;)

hey, it works great, no problems found :D ;)

im sorry, but I ran this script when I had an error given to me from phpmyadmin calling my post table corrupt. after running this script it fixed my post table, but left 8 other tables corrupt. I had to then manually fix them. my experience.

@Kaelon: Try this:
Login PhpMyAdmin and use this command:
REPAIR TABLE post;

Before using it, login Admin CP (if you still can) and disable your board.

You can also try to run repair.php again, but this time after you disabled your board.

Hope it fixes your problem. And if does BACKUP immediately..

@Nakkid: It's not a good idea to repair your database, when someone has access to it. It can cause corruptions. I suggest turning the board off from AdminCP, BEFORE applying any repair (or any direct action) process. :)

@All: Your database is everything for your board and probably for your site. Dont ever think about to touch it, BEFORE backing it up! ;)

Regards,
Logician

well...there was no one on my board at the time besides me, so I just ran it without closeing my board :D ;)

it's all added into the start info.. thanks logician. look at the first screenshot to see the difference (refresh).

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??

I am not sure you are allowed to distribute original vBulletin code?

well...not like that from the config file, just a little something that checks to see if it's usernumber 1 and if it is to conturn on like nothing but if it's a different user the to show them a message saying they don't have access to it (something to put into that repair.php file that nakkid posted)

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.

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

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

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.

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 :p

Logician

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.

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.

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

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. :)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...

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 :)

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 :)

I think on my board that hunting for similar sounding names would not prove
fruitful at all.
I suppose it all greatly depends on how many users you have as to the
intensity of an opperation like this. My php & SQL knowledge ends at this
level!

I would much rather concentrate on a script that would use the IPs in the
posts table and look for users using the same IP within a certain time
frame, eg 15 mins. This should catch people out when they change login from
their real name to the false name in order to flame someone.
Obviously doing a search like that on the post table would be a
server-destroying style query, so my plan would be to feed it a user name
that was suspicious. It would then crank back through the last 20 posts of
that user, grab the IPs, then see if anyone had used that IP within 15 mins
of that post time.

Does that make sense? Do you think it would work?

----edit----
fyi, this post was a result of VB's email reply feature going a bit mad. I replied to another topic by email and my reply ended up in two other threads, and not the thread I actually replied too.

i think i know now why the database size was a huge difference for me... i played earlier with the built in VB style installer and overwriten the vbulletin.style. that added extra data to the tables. the script cleaned everything... not sure 100%, but it did made it alot smaller.

the same thing happened to me when i ran it, it corrupted 80% of my database, i had to use phpmyadmin and manual reapair them, just a warning to some people, running freedy's repair scripted could corrupted the database like it did with mine, it doesnt happen to many, but only to a small few, so just keep that in mind, to fix it, get php my admin or some thing that lets you run queries and use:

REPAIR TABLE `table`

for example:

REPAIR TABLE `useractivation`

it worked for me

im not saying the scripted is no good, its a good scripted, also i did shut down my board and it corrupted the database, also when i had my forums shut down for a couple days and used phpmyadmin to optomize some tables it corrupted them.

i'm sorry, but i cannot accept this. if you had the board OFF it will not corrupt it. i did this test: i logged in with only one user to the board and while browsing the threads i ran the script. it corrupted only the threads table. it cannot corrupt several tables. is IMPOSSIBLE. there is nothing to say more. how it can corrupt several tables if it acceses each one by one?

maybe, if you have an old version of PHP and MySQL. i know people here they run VB on versions that is not even suppose to work well.

please don't try to convince me that it doesnt work because it does. Slynderdale, i guarantee you that you did something at that time, when the tables got corrupted. what do you mean is good coded?? is the same freaky code used. they are both web applications. in other words you try to tell me that phpmyadmin is alot better then VB? i never used phpmyadmin, because i consider it a piece of junk. you want to use something decent, use mysqlmanager.

anyway, the way is made my mod, it uses all the new coding built in VB. i just cant accept the idea it will fail. unless someone plays with your board in the same time.

Everything went perfectly for me, although I didn't notice any changes afterwords :( Would it work better if I used the "advanced" way of running it instead (through URL)?

How can I figure out my database size anyway, I've never checked..^_^'

Wakey wakey, scripts can fail. Yes nakkid, even your script - can fail. Don't be so brutal and yell at people for doing nothing other than clicking a button. If it happens to so many people, something must be wrong, even if it worked for you. Everything is possible, don't void possibilities just because you don't think it can happen.

My two cents.

i didnt say phpmyadmin is better and i wasnt trying to convince you it doesnt work, i just told you what happen to me, i think it was database not the scripted, no one was on the bored and i went to optomized and it corrupted them, im sorry if you misunderstood me, ikm not trying to make enimes or any one mad, i was just stating what happned to me, i personaly like your hacks that you make and i wanna say thank you for all that you do

I didn't experience any problems at all, so it just must differ from board to board depending on versions of Mysql, version of Vbulletin, etc.

You may be missing something:

If you turn your board off, it's very likely that you'll not get corrupted tables, but this has no guarantee either. Because even if you disable your board, users logging in your board can access some tables. Although board does not show them itself anymore, as far as I know, it continues to work in the background (global.php) access some tables such as sessions, access etc. How would your board know you are an admin and show you the board even if it's closed, if it werent keep working? It just keeps on working and access tables, but simply does not show the members the results.

However the access to the db reduces significiantly when you disable the board, so it's very unlikely that you come across a corruption unless you have a very high traffic in your board or you're unlucky. Slynderdale may be one of them.

IMHO: I wouldnt apply any repair action to mysql db, if there is no problems with it. As we all know it's a general rule: Dont touch your computer, if it's working! :p

My 0.2 cents..

Regards,
Logician