SQL Error

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Fataqui
    Member
    • Jun 2004
    • 57

    SQL Error

    Hi

    When doing a search 'FAQ', if the option 'Search In' is set to 'Search Titles & Text' I get this SQL error, this happens on any Windows server I try running VB under (IIS, Sambar, Apache), on my Unix server this does not happen! I tried different versions of PHP including 4.2.1 up to 4.3.9 and different versions of MySQL Server.


    The VB error ouput! (it's always the same thing!)

    Code:
    Database error in vBulletin 3.0.3:
    Invalid SQL: 
    SELECT faqname, faqparent, phrase.text AS title
    FROM bbs_faq AS faq
    INNER JOIN bbs_phrase AS phrase ON(phrase.phrasetypeid = 7000 AND phrase.varname = faq.faqname)
    WHERE phrase.languageid IN(-1, 0, 1)
    AND (
    faqparent IN('vb_avatar_how', 'vb_calendar_how', 'vb_clear_cookies', 'vb_email_member', 'vb_email_notification', 'vb_memberlist_how', 'vb_pm_explain', 'vb_referrals_explain', 'vb_rss_syndication', 'vb_smilies_explain', 'vb_special_codes', 'vb_update_profile')
    OR
    faqname IN('vb_avatar_how', 'vb_calendar_how', 'vb_clear_cookies', 'vb_email_member', 'vb_email_notification', 'vb_memberlist_how', 'vb_pm_explain', 'vb_referrals_explain', 'vb_rss_syndication', 'vb_smilies_explain', 'vb_special_codes', 'vb_update_profile')
    )
     
    mysql error: Illegal mix of collations (latin1_bin,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='
    mysql error number: 1267
     
    Date: Wednesday 17th of November 2004 07:24:57 PM
    Script: http://......?s=&do=search&q=php&match=all&titlesonly=0
    Referer: http://....../faq.php?
    Username: .....
    IP Address: .....
    Is there something I can do to fix this!

    TIA

    F!
    Last edited by Fataqui; Wed 17 Nov '04, 3:39pm.
  • Axel
    Member
    • May 2002
    • 54

    #2
    bumping up

    having the same problem, is their a possible solution?

    Currently using php4.3.9 and mysql 4.1.7

    Comment

    • Zachery
      Former vBulletin Support
      • Jul 2002
      • 59097

      #3
      At this time we do not recomend running vBulletin with mysql 4.1.7

      Comment

      • Axel
        Member
        • May 2002
        • 54

        #4
        But I can't change the SQL-Version. It's installed by the hoster and I don't have any influence.

        Is there any workarround? Or is there currently a list where known problems with mysql 4.1.7 are listed?

        Comment

        • Mind
          New Member
          • Dec 2001
          • 10
          • 3.0.1

          #5
          I had the same issue when upgrading to MySQL 4.1.8.

          For some reasons (I'm not yet familiar with the collation feature), MySQL has set the 'varname' column of the 'phrase' table to latin1_bin whereas all the other tables and columns of vB are latin_swedish_ci.

          I just changed 'varname' to latin1_swedish_ci and the problem was gone.

          Here the MySQL query to do so:
          Code:
          ALTER TABLE `phrase` CHANGE `varname` `varname` VARCHAR( 250 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL

          Comment

          • poi@nwdnb
            New Member
            • Sep 2002
            • 12

            #6
            I also have this problem. I'm using Mysql 4.1.7. Also Apache/1.3.33 (Unix) mod_perl/1.29 PHP/4.3.10 mod_macro/1.1.2 mod_ssl/2.8.22 OpenSSL/0.9.7e but i reckon it's just Mysql thats relevant.

            I don't understand all this collation stuff but i have had problems when comapring text fields (any collated ones) as i think it treats SQL text input at UTF8 but mysql compiles with default collation as latin1_swedish_ci and so sets all tables to this. What little reading i've done suggests a compile from source code is necessary to change this default, which isn't brilliant considering the difficulty/impossibility for some people and the myriad of scripts out there that don't specify a collation.

            I'm going to try Mind's suggestion now it seems a reasonable course of action. Does anyone know more about this issue?

            Comment

            • tamarian
              Senior Member
              • Oct 2000
              • 784
              • 1.1.x

              #7
              I found a slightly better fix:

              Code:
              ALTER  TABLE  `phrase`  CHANGE  `varname`  `varname` VARCHAR( 250  )  NOT  NULL
              It doesn't set any charset, so it defaults to your mysql config.
              vB Drupal Community Plumbing | vB Survey | vBusy | vB Spell | vBouncer

              Comment

              • Wynex
                Member
                • Mar 2006
                • 46

                #8
                thanks tamarian. worked for me

                Comment

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