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.

[Meirion]

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.

[TECK]

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.

[Slynderdale]

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

[Slynderdale]

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.

[TECK]

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.

[Velocd]

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..^_^'

[Admin]

Originally posted by nakkid
i just cant accept the idea it will fail.

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.

[Slynderdale]

Originally posted by nakkid
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.

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

[Velocd]

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.

[Logician]

Originally posted by nakkid
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?

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!

My 0.2 cents..

Regards,
Logician