Announcement Announcement Module
Collapse
No announcement yet.
SQL Error Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL Error

    #1
    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 17th Nov '04, 3:39pm.
    Tags: None
  • #2
    bumping up

    having the same problem, is their a possible solution?

    Currently using php4.3.9 and mysql 4.1.7

    Comment

    • #3
      At this time we do not recomend running vBulletin with mysql 4.1.7
      Zachery Woods
      vBulletin & Forum Runner Support

      Comment

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

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

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

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

              Comment

              • #8
                thanks tamarian. worked for me

                Comment

                Working...
                X