Search function problem.

[MattH]

I currently get this when trying to use the search function on my site:

Database error in vBulletin 3.8.4:

Invalid SQL:

                    SELECT * FROM search AS search
                    WHERE searchhash = '54ed3de99668b4a2d9b59f3a4b90bbd7'
                        AND userid = 1
                        AND completed = 1;

MySQL Error   : Table 'npulsere_vb383.search' doesn't exist
Error Number  : 1146
Request Date  : Sunday, September 20th 2009 @ 06:22:15 PM
Error Date    : Sunday, September 20th 2009 @ 06:22:16 PM
Script        : http://www.npulse-rebirth.com/search.php?do=process
Referrer      : http://www.npulse-rebirth.com/forum.php
IP Address    : 69.123.93.121
Username      : Matt
Classname     : vB_Database
MySQL Version : 4.1.22-standard

I have no clue what caused the problem but i went to the installation setup and deleted the search table but i so stupidly didn't make a backup of it. How would i insert a fresh search table to my phpMyAdmin?

[Steve Machol]

For some reason you are missing the search table. You can run this query to recreate it:

CREATE TABLE search (
searchid INT UNSIGNED NOT NULL AUTO_INCREMENT,
userid INT UNSIGNED NOT NULL DEFAULT '0',
ipaddress CHAR(15) NOT NULL DEFAULT '',
personal SMALLINT UNSIGNED NOT NULL DEFAULT '0',
query VARCHAR(200) NOT NULL DEFAULT '',
searchuser VARCHAR(200) NOT NULL DEFAULT '',
forumchoice MEDIUMTEXT,
prefixchoice MEDIUMTEXT,
sortby VARCHAR(200) NOT NULL DEFAULT '',
sortorder VARCHAR(4) NOT NULL DEFAULT '',
searchtime float NOT NULL DEFAULT '0',
showposts SMALLINT UNSIGNED NOT NULL DEFAULT '0',
orderedids MEDIUMTEXT,
announceids MEDIUMTEXT,
dateline INT UNSIGNED NOT NULL DEFAULT '0',
searchterms MEDIUMTEXT,
displayterms MEDIUMTEXT,
searchhash VARCHAR(32) NOT NULL DEFAULT '',
titleonly SMALLINT UNSIGNED NOT NULL DEFAULT '0',
completed SMALLINT UNSIGNED NOT NULL DEFAULT '1',
PRIMARY KEY (searchid),
UNIQUE KEY searchunique (searchhash, sortby, sortorder)
);

[MattH]

For some reason you are missing the search table. You can run this query to recreate it:

CREATE TABLE search (
searchid INT UNSIGNED NOT NULL AUTO_INCREMENT,
userid INT UNSIGNED NOT NULL DEFAULT '0',
ipaddress CHAR(15) NOT NULL DEFAULT '',
personal SMALLINT UNSIGNED NOT NULL DEFAULT '0',
query VARCHAR(200) NOT NULL DEFAULT '',
searchuser VARCHAR(200) NOT NULL DEFAULT '',
forumchoice MEDIUMTEXT,
prefixchoice MEDIUMTEXT,
sortby VARCHAR(200) NOT NULL DEFAULT '',
sortorder VARCHAR(4) NOT NULL DEFAULT '',
searchtime float NOT NULL DEFAULT '0',
showposts SMALLINT UNSIGNED NOT NULL DEFAULT '0',
orderedids MEDIUMTEXT,
announceids MEDIUMTEXT,
dateline INT UNSIGNED NOT NULL DEFAULT '0',
searchterms MEDIUMTEXT,
displayterms MEDIUMTEXT,
searchhash VARCHAR(32) NOT NULL DEFAULT '',
titleonly SMALLINT UNSIGNED NOT NULL DEFAULT '0',
completed SMALLINT UNSIGNED NOT NULL DEFAULT '1',
PRIMARY KEY (searchid),
UNIQUE KEY searchunique (searchhash, sortby, sortorder)
);

Ok, i created the table, and Repaired/Optimized the tables, but no i receive this error:

Database error in vBulletin 3.8.4:

Invalid SQL:

                        SELECT
                        DISTINCT thread.threadid
                        FROM thread AS thread 
                        
                        INNER JOIN post AS post ON(thread.threadid = post.threadid )
                        WHERE MATCH(post.title, post.pagetext) AGAINST ('test')
                        LIMIT 500;

MySQL Error   : Can't find FULLTEXT index matching the column list
Error Number  : 1191
Request Date  : Sunday, September 20th 2009 @ 06:46:09 PM
Error Date    : Sunday, September 20th 2009 @ 06:46:09 PM
Script        : http://www.npulse-rebirth.com/search.php?do=process
Referrer      : http://www.npulse-rebirth.com/
IP Address    : 69.123.93.121
Username      : Matt
Classname     : vB_Database
MySQL Version : 4.1.22-standard

[Steve Machol]

Then you will need to remove the Fulltext Indices here:

Admin CP -> vBulletin Options -> Search Type -> Remove Fulltext Indices -> Yes -> Go

After that go back and rechoose the fulltext option so the indexes are rebuilt.

Personally I would be more worried about why the search table was deleted.

[MattH]

Then you will need to remove the Fulltext Indices here:

Admin CP -> vBulletin Options -> Search Type -> Remove Fulltext Indices -> Yes -> Go

After that go back and rechoose the fulltext option so the indexes are rebuilt.

Personally I would be more worried about why the search table was deleted.

That worked, thanks alot. Well previously, i had to change hosts, upload a backup, and upgrade from 3.8.3 > 3.8.4. Restoring my backup files was most likely the cause of the problem.

Thanks again, Steve.

[Steve Machol]

Glad it's working.

[MattH]

Hrm, i now get this when trying to do to my Inbox.

Database error in vBulletin 3.8.4:

Invalid SQL:

        SELECT
            SUM(IF(readtime <> 0, 1, 0)) AS confirmed,
            SUM(IF(readtime = 0, 1, 0)) AS unconfirmed
        FROM pmreceipt
        WHERE userid = 1;

MySQL Error   : Table 'npulsere_vb383.pmreceipt' doesn't exist
Error Number  : 1146
Request Date  : Tuesday, September 22nd 2009 @ 04:58:29 AM
Error Date    : Tuesday, September 22nd 2009 @ 04:58:29 AM
Script        : http://www.npulse-rebirth.com/private.php
Referrer      : http://www.npulse-rebirth.com/forum.php
IP Address    : 69.123.93.121
Username      : Matt
Classname     : vB_Database
MySQL Version : 4.1.22-standard

Apprently pm's haven't been working for a few weeks now, basically when i ran a restore. Is there Query to restore the table for this one?

[Steve Machol]

Hmmm...another missing table. Something happened to this database.

Here is the query to create that table:

CREATE TABLE pmreceipt (
pmid INT UNSIGNED NOT NULL DEFAULT '0',
userid INT UNSIGNED NOT NULL DEFAULT '0',
touserid INT UNSIGNED NOT NULL DEFAULT '0',
tousername VARCHAR(100) NOT NULL DEFAULT '',
title VARCHAR(250) NOT NULL DEFAULT '',
sendtime INT UNSIGNED NOT NULL DEFAULT '0',
readtime INT UNSIGNED NOT NULL DEFAULT '0',
denied SMALLINT UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (pmid),
KEY userid (userid, readtime),
KEY touserid (touserid)
);

[MattH]

Sorry to be a pest, but would you mind telling me how to add the reputation table back?

[Mitch100degrees]

CREATE TABLE reputation (
reputationid INT UNSIGNED NOT NULL AUTO_INCREMENT,
postid INT UNSIGNED NOT NULL DEFAULT '1',
userid INT UNSIGNED NOT NULL DEFAULT '1',
reputation INT NOT NULL DEFAULT '0',
whoadded INT UNSIGNED NOT NULL DEFAULT '0',
reason VARCHAR(250) DEFAULT NULL DEFAULT '',
dateline INT UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (reputationid),
KEY userid (userid),
KEY whoadded_postid (whoadded, postid),
KEY multi (postid, userid),
KEY dateline (dateline)
);

They are all in install/mysql-schema.php if you need anymore.

[MattH]

Thank for very much for letting me know, appreciate it.