freddie's Repair & Optimize Database (vB integration) [Archive]

TECK

05-01-2002, 10:00 PM

As we all know, there is a great tool included in the /extras folder, called repair.php.
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 (https://vborg.vbsupport.ru/showthread.php?s=&threadid=37193)

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

UPDATE
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

05-02-2002, 05:12 AM

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

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

TECK

05-02-2002, 05:12 AM

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

Kaelon

05-02-2002, 05:27 AM

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

05-02-2002, 05:29 AM

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/index.php?action=head

Any thoughts?

TECK

05-02-2002, 05:30 AM

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

05-02-2002, 05:31 AM

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

05-02-2002, 05:37 AM

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

05-02-2002, 05:49 AM

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

TECK

05-02-2002, 05:52 AM

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

scsa20

05-02-2002, 05:55 AM

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

scsa20

05-02-2002, 06:02 AM

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

snyx

05-02-2002, 06:03 AM

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.

Logician

05-02-2002, 06:03 AM

@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

scsa20

05-02-2002, 06:17 AM

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

TECK

05-02-2002, 06:20 AM

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

scsa20

05-02-2002, 06:25 AM

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

Floris

05-02-2002, 06:35 AM

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

scsa20

05-02-2002, 06:38 AM

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)

TECK

05-02-2002, 06:40 AM

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.

Zzed

05-02-2002, 07:05 AM

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/showthread.php?s=&threadid=45063&highlight=1016

John

05-02-2002, 07:17 AM

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

DWZ

05-02-2002, 07:28 AM

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

05-02-2002, 07:46 AM

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

Logician

Nemesis77

05-02-2002, 08:54 AM

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

05-02-2002, 09:01 AM

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

05-02-2002, 09:02 AM

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

05-02-2002, 09:07 AM

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

05-02-2002, 09:11 AM

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

05-02-2002, 09:11 AM

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

Meirion

05-02-2002, 09:12 AM

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

05-02-2002, 09:30 AM

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

05-02-2002, 01:00 PM

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

05-02-2002, 01:25 PM

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

05-02-2002, 01:33 PM

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

05-02-2002, 01:40 PM

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

05-02-2002, 01:51 PM

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

05-02-2002, 01:52 PM

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

05-02-2002, 01:55 PM

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

05-02-2002, 01:59 PM

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

My 0.2 cents..

Regards,
Logician

TECK

05-02-2002, 02:04 PM

Originally posted by FireFly
Yes nakkid, even your script - can fail. Don't be so brutal and yell at people for doing nothing other than clicking a button.firefly, is not my script. i only did as usual a little mod to it, you know i'm not the best at this. but when i know for sure one thing, i put my foot on the floor. is obvious that a script can fail, nothing is perfect, but 80%??? kmon... you can admit firefly that there is no diff between phpmyadmin and a query ran in VB. i have the tendency to believe that 225 is alot more stable in this area.

and i dont yell. i simply state that people have a tendency to blamne someone(something) else for their mistakes. so what if corrupted? if is no good, why would it be in the VB package??? i doubth Jelsoft wants to promote a crappy code image.

i know freddie from this board and he pulls amasing stuff. period. there is no but, maybe or how. and people had problems with the script because maybe it wasnt well documented? look at this situaton with Kaelon. he ran the script with the board ON. now we all know what to do. constructive, right?

@Slynderdale: dont panic, that's me.. when you will know me better, you will see that this is my wierd way to talk sometimes. dont take it bad because that's how i am, i say what i think... sometimes to fast.

regulars start laughing at me while i pull one of my panic attacs... and i join them also shortly after...

Slynderdale

05-02-2002, 02:05 PM

that was taken around 1 am in the morning, that was my stats

Admin

05-02-2002, 02:15 PM

Originally posted by nakkid
and i dont yell. i simply state that people have a tendency to blamne someone(something) else for their mistakes. so what if corrupted? if is no good, why would it be in the VB package??? i doubth Jelsoft wants to promote a crappy code image.
Oh you do yell, maybe you can't see it but you are. The script in vBulletin's zip file is not like your file. It's different, you altered it and added stuff - no matter what you did, you changed it.
And I don't care if the PHP authors wrote the script themselves, the fact is, many people can't get it to work correctly. So unless everyone here except you are complete idiots, something must be wrong, right?

Originally posted by nakkid
@Slynderdale: dont panic, that's me.. when you will know me better, you will see that this is my wierd way to talk sometimes. dont take it bad because that's how i am, i say what i think... sometimes to fast.

regulars start laughing at me while i pull one of my panic attacs... and i join them also shortly after...
Maybe you should start thinking before you post? Think over what you're going to say, and choose your words carefully. I don't know about others, but I certainly don't laught at your panic attacks, they actually annoy me quite a bit. If you feel angry don't take it out on the forum users, get a cigarette. People don't deserve your yells.

See, I'm not yelling.

TECK

05-02-2002, 02:17 PM

true. i apologise about the tension i created like an idiot. i mean it.

now, about the script. if i understand correctly, people had problems with my mod? not with freddie's file?

Sparkz

05-02-2002, 02:32 PM

Originally posted by nakkid
true. i apologise about the tension i created like an idiot. i mean it.

No offence, but it seems to me that this is something you do alot.

Maybe you should consider grabbing a glass of water, or maybe even counting to 100 before posting when you see something that makes you go off like this?

Freddie Bingham

05-02-2002, 03:02 PM

Hmm lots of talk about me.

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.

Kaelon - your post table was corrupted before you opened repair.php. You can have corruption without it being to the point of causing visible problems. The error 28 that the script generated shows that your database ran out of space while trying to repair the post table. There was nothing inherent to the script that caused your problem. Your post table is very small so for this to have occurred would have to be a fault of your hosting company in terms of managing disk space. In fact, the corrupted index that brought up the repair procedure to begin with was probably caused by the lack of disk space.

Ren

05-02-2002, 03:09 PM

Very nice , I installed this and backed up my forums and it worked great .. thanks :)

NTLDR

05-02-2002, 08:06 PM

Very nice addition to the Admin CP, thank you nakkid and freddie :D

@Slynderdale would you mind sharing how you got the custom avatar and attachment count in the stats?

Floris

05-03-2002, 11:22 AM

NTLDR: hack by freddie (https://vborg.vbsupport.ru/showthread.php?s=&threadid=37193)

NTLDR

05-03-2002, 12:11 PM

Originally posted by xiphoid
NTLDR: hack by freddie (https://vborg.vbsupport.ru/showthread.php?s=&threadid=37193)

I have freddies hack installed, but I wanted to know how the avatar and attachment count was added as that isn't part of the freddies hack (or at least not the version I installed).

I'll take another look.

Floris

05-03-2002, 12:22 PM

it is in his attached screenshot

Stretchr

05-27-2002, 01:25 AM

The script seems to be working for me but I'm getting this error:

Warning: Supplied argument is not a valid MySQL result resource in /home/wdj/www/forums/admin/db_mysql.php on line 154

FYI, here's the block which includes line 154 in my db_mysql.php

function fetch_array($query_id=-1,$query_string="") {
// retrieve row
if ($query_id!=-1) {
$this->query_id=$query_id;
}
if ( isset($this->query_id) ) {
$this->record = mysql_fetch_array($this->query_id);
} else {
if ( !empty($query_string) ) {
$this->halt("Invalid query id (".$this->query_id.") on this query: $query_string");
} else {
$this->halt("Invalid query id ".$this->query_id." specified");
}
}

Any help would be appreciated.

Also: I didn't realize until I ran it the first time you modified repair.php. Might make a note of that, (unless you already did and I missed it somewhere) or, better yet, rename repair.php to something else and change it in the hack.

Anyway, this is really handy to have available from the Admin console. Thank you!