[Forum] Cant create new threads

[Micaiah]

Recently i had some trouble involving white pages showing up instead of my forums i contacted the support staff and i managed to get them back up after a database problem in my attachment table, unfortunatly now i realied no one can post a thread and get this error

Database error in vBulletin 4.0.2:
Invalid SQL:
SELECT
fd.thumbnail_dateline, fd.filesize, IF(fd.thumbnail_filesize > 0, 1, 0) AS hasthumbnail, fd.thumbnail_filesize,
a.dateline, a.state, a.attachmentid, a.counter, a.contentid, a.filename, a.userid, a.settings,
at.contenttypes
FROM attachment AS a
INNER JOIN filedata AS fd ON (fd.filedataid = a.filedataid)
LEFT JOIN attachmenttype AS at ON (at.extension = fd.extension)
WHERE
a.posthash = '0446545a14f2ca0e0f38b5046cf43e24'
AND
a.userid = 1
AND
a.contenttypeid = 1

ORDER BY a.contentid, a.attachmentid;
MySQL Error : Unknown column 'a.contentid' in 'field list'
Error Number : 1054
Request Date : Tuesday, April 6th 2010 @ 03:25:38 PM
Error Date : Tuesday, April 6th 2010 @ 03:25:38 PM

I was told i was missing a field in my attachment table and have re-added it the following way as told

Field: state
Type: Enum
Length/Values: 'visible','moderation'
(with those single quotes around them)
Null: not null
Default: visible

This bought my forums back although not fully for some reason
Any help much loved

[Lynne]

Micaiah, it looks like you are now missing another field from your attachment table called "contentid". From mysql-schema.php:
contentid INT UNSIGNED NOT NULL DEFAULT '0'

Field: contentid
Type: INT
Attributes: Unsigned
Null: not null
Default: 0

[Micaiah]

hmm i added it but it hasnt changed :S this is a list of fields in my attachment table atm

attachmentid
userid
dateline
thumbnail_dateline
filename
filedata
visible
counter
filesize
postid
filehash
posthash
thumbnail
thumbnail_filesize
extension
state
contentid

just in case i might be missing some others, ive no idea how that happened Hmm i also backed up the databases when upgrading to 4.0 and that was only last week, would it be an idea to over write the databases to see if it helps? my 4.0 was working fine until installing a mod then it went pop lol

[Lynne]

You have a 3.8 attachment table. Did the upgrade script run OK for you?

[Micaiah]

the update went fine i didnt get any errors or anything

[Lynne]

Can you upload attachments on your site? Do you have a table called "filedata" in your database?

[Micaiah]

there is a pile called filedata on the database but when replying to a post i got this error

SELECT
fd.thumbnail_dateline, fd.filesize, IF(fd.thumbnail_filesize > 0, 1, 0) AS hasthumbnail, fd.thumbnail_filesize,
a.dateline, a.state, a.attachmentid, a.counter, a.contentid, a.filename, a.userid, a.settings,
at.contenttypes
FROM attachment AS a
INNER JOIN filedata AS fd ON (fd.filedataid = a.filedataid)
LEFT JOIN attachmenttype AS at ON (at.extension = fd.extension)
WHERE
a.posthash = '7c011a4b4866e407857b8c3048502b5b'
AND
a.userid = 1
AND
a.contenttypeid = 1

ORDER BY a.contentid, a.attachmentid;
MySQL Error : Unknown column 'a.settings' in 'field list'
Error Number : 1054
Request Date : Wednesday, April 7th 2010 @ 09:39:17 PM

[Micaiah]

Cant log into my admincp now after looking through the manuel to find a solution i tried running tools.php but it didnt help now when i log into the admin cp i get the log in sucessful screen but then i get taken back to entering my username and password, help much loved xP

[Lynne]

It doesn't look like your upgrade actually completed successfully. The attachments/assets area of your site seems to be pretty messed up. I'm not sure how that happened. We could get all the necessary fields you need from the install/mysql-schema.php file and add them, but I wonder if that would do the trick. Here is a list of the fields that are supposed to be in the attachment table along with the keys:

    attachmentid INT UNSIGNED NOT NULL AUTO_INCREMENT,
    contenttypeid INT UNSIGNED NOT NULL DEFAULT '0',
    contentid INT UNSIGNED NOT NULL DEFAULT '0',
    userid INT UNSIGNED NOT NULL DEFAULT '0',
    dateline INT UNSIGNED NOT NULL DEFAULT '0',
    filedataid INT UNSIGNED NOT NULL DEFAULT '0',
    state ENUM('visible', 'moderation') NOT NULL DEFAULT 'visible',
    counter INT UNSIGNED NOT NULL DEFAULT '0',
    posthash VARCHAR(32) NOT NULL DEFAULT '',
    filename VARCHAR(100) NOT NULL DEFAULT '',
    caption TEXT,
    reportthreadid INT UNSIGNED NOT NULL DEFAULT '0',
    settings MEDIUMTEXT,
    PRIMARY KEY (attachmentid),
    KEY contenttypeid (contenttypeid, contentid, attachmentid),
    KEY contentid (contentid),
    KEY userid (userid, contenttypeid),
    KEY posthash (posthash, userid),
    KEY filedataid (filedataid, userid)

You can try to add the fields to your attachment table and see if it then runs OK.

Is this a live board or a test site? Any chance you can start over from a database backup? (I'm just worried about what else may be wrong in your database if your attachment table is so messed up.)

[Micaiah]

i added those and also tried the 4.0.3 update, the update got rid if the white pages error i got again and actually i usually use google chrome and a few images like the forum old/new images were missing but i switched to IE and they were apearing perfectly also on chrome i couldnt log into the admincp but i can easily on IE so maybe that might be a cookie issue or something? also i couldnt post new threads before or view some areas but seems i can do that now on both chrome and IE, maybe running the 4.0.3 update fixed the databases? or can that happen

ohh its not a test site its a live one, but luckily only a small one lol

[Micaiah]

being a few days and the site seems to be going fine now only database error i have is when people go to view profiles


SELECT album.*,
a.attachmentid, album.moderation,
fd.thumbnail_dateline, fd.thumbnail_width, fd.thumbnail_height, IF(fd.thumbnail_filesize > 0, 1, 0) AS hasthumbnail

FROM album AS album
LEFT JOIN attachment AS a ON (album.coverattachmentid = a.attachmentid)
LEFT JOIN filedata AS fd ON (fd.filedataid = a.filedataid)

WHERE
album.userid = 1
AND
album.state IN ('public', 'private', 'profile')


ORDER BY album.lastpicturedate DESC
LIMIT 2;

MySQL Error : Unknown column 'album.coverattachmentid' in 'on clause'
Error Number : 1054
Request Date : Tuesday, April 13th 2010 @ 03:39:19 PM
Error Date : Tuesday, April 13th 2010 @ 03:39:19 PM
Script : http://www.eternalbonds.com/forum/member.php?1-Fenex
Referrer : http://www.eternalbonds.com/forum/index.php

[Micaiah]

would like a lil light shed on this dont mean to be rude about it lol

[Lynne]

You seem to be missing another field. This one is in your album table.

coverattachmentid INT UNSIGNED NOT NULL DEFAULT '0'

There is a modification over on vbulletin.org by Calorie that will take a look at your database and compare it to a totally default database for the verion of vbulletin your are running. You may want to run that modification and see if it can find all the fields/tables you are missing so you can add them in.