problem with attachments table

[BenH]

got up in the morning and found 300 emails with the above error

Database error in vBulletin 2.2.5:

Invalid SQL: INSERT INTO attachment (attachmentid,userid,dateline,filename,visible,hash) VALUES (NULL,1227,1028034598,'ìëìåê&ñðèé 1.jpg','1','92923f823f8953ab962c79a626fd87d4')
mysql error: Duplicate entry '65535' for key 1

mysql error number: 1062

Date: Tuesday 30th of July 2002 09:09:58 AM
Script: http://forums.chiquititas.co.il/newreply.php
Referer: http://forums.chiquititas.co.il/newr...threadid=64448

im using a hack in this table , but this is being used for more then a month without any problem , so please help me found the prob .

thanks
Ben

[Freddie Bingham]

You have more than 65000 attachments?

You need to run these queries in phpMyAdmin:

ALTER TABLE attachment CHANGE attachmentid attachmentid INT UNSIGNED NOT NULL AUTO_INCREMENT

ALTER TABLE post CHANGE attachmentid attachmentid INT UNSIGNED DEFAULT '0' NOT NULL

[BenH]

ok , the probelm fixed ;->
thanks alot

and yes , i have over 65,000 attachments , over 3gb of files ..
i suggest in the next version there will be something that will automaticly run these commends or something ..

[Freddie Bingham]

Yes they should have never been set to smallint's. That will be fixed in 3.0

[Essam]

i have the same proplem,, my attachment table is 3GB too

Database montada_forum running on localhost 
Error

SQL-query :  

ALTER TABLE attachment CHANGE attachmentid attachmentid INT UNSIGNED NOT NULL AUTO_INCREMENT 

MySQL said: 


Duplicate entry '60823' for key 1

by the way did :
ALTER TABLE post CHANGE attachmentid attachmentid INT UNSIGNED DEFAULT '0' NOT NULL

and had no errors

[Harlen]

Bump

[Steve Machol]

maoz440, please post the exact and complete error messgae. However AFAIK 3 GBs is just too large for a single MySQL db table. You may need to start purning attachments.

[Steve Machol]

That error should have been fixed by running Freddie's queries posted above.

[Steve Machol]

Yes, IMO the size of the table may in fact be causing this problem.

If you empty this table I am not sure which other steps would be necessary to eliminate any potential errors. I imagine at the very least you should run repair.php on the database and perhaps update all the counters as well.