Announcement

Collapse
No announcement yet.

Search function problem.

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Search function problem.

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

    Code:
    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?

  • #2
    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)
    );
    Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
    Change CKEditor Colors to Match Style (for 4.1.4 and above)

    Steve Machol Photography


    Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


    Comment


    • #3
      Originally posted by Steve Machol View Post
      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:


      Code:
      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

      Comment


      • #4
        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.
        Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
        Change CKEditor Colors to Match Style (for 4.1.4 and above)

        Steve Machol Photography


        Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


        Comment


        • #5
          Originally posted by Steve Machol View Post
          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.

          Comment


          • #6
            Glad it's working.
            Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
            Change CKEditor Colors to Match Style (for 4.1.4 and above)

            Steve Machol Photography


            Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


            Comment


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

              Code:
              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?

              Comment


              • #8
                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)
                );
                Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
                Change CKEditor Colors to Match Style (for 4.1.4 and above)

                Steve Machol Photography


                Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


                Comment


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

                  Comment


                  • #10
                    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.

                    Comment


                    • #11
                      Thank for very much for letting me know, appreciate it.

                      Comment

                      widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                      Working...
                      X