Serious MYSQL Problem
 

After optimizing the tables in PHPMYADMIN, i have got a serious error in my 'attachment' table. I tried "Repair table Attachment" with phpmydmin and i also tried to repair it with the suplied repair.php file in the extras folder, but no luck sofar.

Is there still a way to repair it, or to recreate it?


Here's the error that Vbulletin gave me:

---------
Database error in vBulletin 2.2.7:

Invalid SQL:
SELECT
post.*,post.username AS postusername,post.ipaddress AS ip,user.*,userfield.*,icon.title as icontitle,icon.iconpath,
attachment.attachmentid,attachment.filename,attach ment.visible AS attachmentvisible,attachment.counter
,avatar.avatarpath,NOT ISNULL(customavatar.avatardata) AS hascustomavatar,customavatar.dateline AS avatardateline
FROM post
LEFT JOIN icon ON icon.iconid=post.iconid
LEFT JOIN user ON user.userid=post.userid
LEFT JOIN userfield ON userfield.userid=user.userid
LEFT JOIN avatar ON avatar.avatarid=user.avatarid
LEFT JOIN customavatar ON customavatar.userid=user.userid
LEFT JOIN attachment ON attachment.attachmentid=post.attachmentid
WHERE post.postid IN (0,13598,13606,13610,13616,13695,13697,13871)
ORDER BY dateline

mysql error: Can't open file: 'attachment.MYD'. (errno: 144)

mysql error number: 1016

Date: Thursday 30th of January 2003 07:38:26 AM
Script: http://www.housequake.com/forum/foru...&threadid=2153
Referer: http://www.housequake.com/forum/foru...?s=&forumid=12
-----------

And here's the result using the repair.php:

-----------
housequake.attachment check warning Table is marked as crashed and last repair failed
housequake.attachment check warning 1 clients is using or hasn't closed the table properly
housequake.attachment check error Found 73 keys of 72
housequake.attachment check error Corrupt
Repairing table attachment
housequake.attachment repair error 122 when writing to datafile

mysql error: Can't open file: 'attachment.MYD'. (errno: 144)
mysql error number: 1016
-----------


I very much hope somebody can help me out...

Run repair.php file, included in the vBulletin .zip package.
Make sure you close the board before you do this and also that you have enough free space onto your server.
If your database is 150mb, you should have an extra 160mb of free space.

If it won't work, restore the database using the previous backup file you have.

Thanks, but i already tried using the Repair.php, as i stated in my post already. But that didn't work out. The only table thats corrupt is the "attachment" table, but should it be possible to recreate that table again after i deleted it? If so, what querie should i use, to have the same attachment structure as before?
Is that possible?

Thanks for helping me out a bit!

run repair.php more than once, sometimes it helps...

if it really doesn't work, delete the table and recreate it:
[sql]CREATE TABLE attachment (
attachmentid smallint(5) unsigned DEFAULT '0' NOT NULL auto_increment,
userid int(10) unsigned DEFAULT '0' NOT NULL,
dateline int(10) unsigned DEFAULT '0' NOT NULL,
filename varchar(100) NOT NULL,
filedata mediumtext NOT NULL,
visible smallint(5) unsigned DEFAULT '0' NOT NULL,
counter smallint(5) unsigned DEFAULT '0' NOT NULL,
PRIMARY KEY (attachmentid)
)[/sql]

Thanks!

I don't know which solution worked, because i first delete the searchindex, which seems to occupie alot of MB.
After that i tried the repair.php a couple of times, and then i used the repair table attachment in PHPmyadmin. And all of that worked! So now i am a happy man again.

:)
good to hear :)