deleting attachments by date

[rick]

Originally posted by Steve Machol
Yes, that row would be a specific attachment. You could try accessing this table with phpMyAdmin and deleting that row. It may cause other problems with the thread though but if the problem is here then it might be worth it.

Thanks for the response Steve - Im sorry, my board has been down for 3 days now and it is frustrating me - Im losing my community and I know it is not your fault, im sorry for offending.

I have deleted the offending record as you suggested. It took a very long time (several minutes) for the delete to complete. Once I removed the offending row, and two rows on either side of it I went and rand myismchk once again;

Checking MyISAM file: attachment.MYI
Data records: 27126 Deleted blocks: 3
- check file-size
- check key delete-chain
- check record delete-chain
- check index reference
- check data record references index: 1
- check record links
MyISAM-table 'attachment.MYI' is usable but should be fixed

I am now going to run myismchk -r and then repair table attachment; from the mySQL client. Once this has completed I will try to post an attachment to the board once again and report back.

Thank you for your assistance. I do appreciate it.

[rick]

I've just finished up with running myismchk -r and repair on the attachment table. I logged into the forum and tryed to make a test post containing a small image attachment, however, it did not work. The image took nearly 4 minutes to complete posting. Once the post did complete, the image pulled up quickly from the database - the problem only occurs when posting an attachment or deleting one.

I am now trying to make a new sql dump file of the attachment table. If the dump is successful I am going to drop the attachment table and then attempt to recreate the table from the sql dump file.

Any other suggestions?

[rick]

I was able to successfully dump the attachment table to a text file with no errors. I then dropped the attachment table and proceeded to read in the sql dump file. I was able to recreate the attachment table without any errors. I then went back to the forum and tried to submit a post containing a small attachment -- 4 minutes later the post finally went through.

After all of this, the problem remains.

Also - Can anyone please help with the following error???
forum# myisamchk -R -v -v attachment.MYI
The value of the sort key is bigger than max key: 32. <--- What does this mean!!??

The only thing I can think to try at this point is to completely drop the attachment table once again, however, this time only recreate the db schema - leave the table completely empty. Once I have the empty attachment table back in place I'll try posting an attachment and see if the problem continues.

Any suggestions? Comments? All help would be most appreciated.

thanks

[Steve Machol]

Originally posted by rick

The value of the sort key is bigger than max key: 32. <--- What does this mean!!??

Sorry, I wish I knew. A search of the MySQL home page didn't bring up anything.

I suspect this have something to do the the attachmentid column using smallint(5) but I'm not sure. Perhaps the total number of attachments exceeds the allowable limit for this field.

[rick]

Originally posted by Steve Machol
I suspect this have something to do the the attachmentid column using smallint(5) but I'm not sure. Perhaps the total number of attachments exceeds the allowable limit for this field.

hrm, you might be onto something here...Is there any others that may know a bit more about the SQL side of things and what this error might mean?

I have just finished up recreating an empty attachment table and then went back to my forum and tried to post an attachment and it worked without issue. There were no delays in posting or deleting the image.

I am led to believe there is something either wrong with the attachment id field as you suggested or perhaps the data itself is somehow interfering with db updates, as that is the only time the problem presents itself.

Thanks Steve, im gonna start looking in the direction you suggested...

Any other comments suggestions based on the latest findings?

Thanks again.

[rick]

Well, just for kicks I started to delete from my attachment table by date starting with newest attachments first. I removed the last 32 days of attachments and did not have any improvement in attachment posting performace.

I am going to continue to delete from the attachment table until it either works or there are no records left in the attachment table. I know from my above experimentation that a new, empty attachment table works fine - so in theory, if this current table is eventually emptied, it to should work fine. I am hopeing that in the process of deleting the attachment table records I will remove a bad data block and the problem will dissappear.

After each delete I am performing a `repair table attachment;` command and then trying to post an image to the board.

Once the repair finishes up I keep trying the following command, but I continue to get the same unknown error;

forum# myisamchk -R -v -v -v attachment.MYI
The value of the sort key is bigger than max key: 32.

I think im chasing my tail now.

[rick]

This problem has been fully resolved and you probably wouldnt believe what it was...

read all about it here -- http://www.vbulletin.com/forum/showt...threadid=62628

Hats off to the vB Team for helping save our community!

This should probably be closed -