Strange SQL Error

[Paul M]

We got a couple of these today - a strange error I have not seen before.

Database error in vBulletin 3.0.6:

Invalid SQL:
SELECT faqname, faqparent, phrase.text AS title
FROM faq AS faq
INNER JOIN phrase AS phrase ON(phrase.phrasetypeid = 7000 AND phrase.varname = faq.faqname)
WHERE phrase.languageid IN(-1, 0, 1)
AND (
faqparent IN('vb_use_cookies')
OR
faqname IN('vb_use_cookies')
)

mysql error: Illegal mix of collations (latin1_bin,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='

mysql error number: 1267

Date: Monday 21st of February 2005 05:47:22 PM
Script: http://www.xxxxxx.co.uk/board/faq.ph...l&titlesonly=0
Referer: http://www.xxxxxx.co.uk/board/faq.ph...l&titlesonly=0
Username: Unregistered
IP Address: x.x.x.x

Anyone seen this before or have any suggestions as to the cause ?

[Marco van Herwaarden]

The default language for your database is not set good, and you probably added a table or column using a different character set (default) the the others.

HAve been discussed before, the reason lay in some changes in MySQL.

(Not sure where this was discussed, here or on vbcom, search character set i think.

[Paul M]

We have not added anything to the faq or phrase tables, but I'll try a search as you suggest.

[Zachery]

Originally Posted by Paul M

We have not added anything to the faq or phrase tables, but I'll try a search as you suggest.

Its because you are running MySQL 4.1.X

I think http://www.vbulletin.com/forum/showt...ighlight=mysql has some info that might help.

[Paul M]

Yeah, I ended up doing a search on the error number and came across that very thread.

The advice in it is a bit confusing though. It talks about adding an entry to "my.ini" - which is something I can't find (sounds very much like a windows file ?). It then degenerates into arguments which doesn't help.

I'm guessing from looking at it that I need to run ;

[sql]CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci[/sql]
against the FAQ table, but it's not terribly clear .....

I don't quite follow the bit about passwords yet either - our forum is not having any trouble connecting to the database.

[Marco van Herwaarden]

If you have access to phpmyadmin, i think it is showing the character set.

[theinz]

Guys, I found a solution to this problem. I had this on my site as well. If you have phpymyAdmin with your host provider do the following:

1. Open vb_phrase in your VB database
2. Click on structure
3. Select the change icon next to "varname"
4. Change the COLLATION to latin1_swedish_ci

This will fix the problem.

tony

[Zachery]

Originally Posted by theinz

Guys, I found a solution to this problem. I had this on my site as well. If you have phpymyAdmin with your host provider do the following:

1. Open vb_phrase in your VB database
2. Click on structure
3. Select the change icon next to "varname"
4. Change the COLLATION to latin1_swedish_ci

This will fix the problem.

tony

Or upgrade to mysql 4.1.12

[RichieBoy67]

I just ran into the same issue downgrading from a newer version to an older version... One way to get past that was to delete that line at every occurence.... I also realized that when you dump you can set the dump so that it is compatable with older versions... This did the trick but the database was then updated to a newer version anyways...

Did you move databases recently??? I still don't understand how this would just start happening unless you downgraded...

[Zachery]

That error exsists in MySQL 4.1.X before 4.1.12 and not in 4.0.X

They would have upgraded from 3 or 4.0.x to 4.1.x and started getting the problem.