Page 1 of 1

#1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICI

Posted: Tue Jan 11, 2005 4:34 pm
by Burrito
whenever I'm using phpmyadmin and I run a query using something like "select * from my_table where my_field like '%bob%'" I get an error message like the following:

#1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'like'

I get the same thing if I use the "=" operator too.

I did a google on this and found out that it is a problem with the version of phpmyadmin I'm using so I upgraded to the newest RC but I still have the problem. I'm assuming it is because all of the tables were created using the old version?

in any event, is there a script or something that I can use to update all of my tables so that I can run queries from phpmyadmin to grab info?

I know this question might be better suited in a different forum, but since you guys are always so helpful, and perhaps some of you have run across this, thought I'd run it by here first.

thx,

Burrito

Posted: Tue Jan 11, 2005 4:38 pm
by feyd
:arrow: Jam(moderator) may have run into this issue before.. You may want to PM or email (if possible) him.

Posted: Wed Jan 12, 2005 9:41 am
by ianlandsman
I ran into this as well. Using phpmyadmin I switched my character type to utf8_general_ci and that fixed it. It's kind of a pain that MySQL assumes the swedish character type by default on the new versions. You just need to be careful to use UTF8 or Latin when you build databases.

Posted: Wed Jan 12, 2005 9:55 am
by Burrito
ianlandsman wrote:You just need to be careful to use UTF8 or Latin when you build databases.
It's not the building of the new ones that I'm concerned with. I need to somehow convert all of my old tables that are using the wrong char set to the correct char set so that I can run queries against them.

there is a plethora of information about this on the web...but no one seems to have a good answer for a way to fix it.

Assuming I could go through and change all of the fields (individually) on each table (using phpmyadmin) to the correct set, that would take 4 years and 62 days to do it on ALL of my tables. There certainly has to be a "fix" script or something out there.... no?

I already owe feyd a pie, so I'll buy whoever can figure this one out 2 chocolate doughnuts :P.

thx in advance for your time,

Burr

Posted: Wed Jan 12, 2005 10:10 am
by ianlandsman
I believe you can use ALTER TABLE, did you see this:

http://dev.mysql.com/doc/mysql/en/Charset-table.html

You can also alter the collation on a per query basis:
http://dev.mysql.com/doc/mysql/en/Charset-collate.html

Here is how you set collation on a column level when creating a column
http://dev.mysql.com/doc/mysql/en/Charset-column.html
Similar syntax may work when altering but I can't find an example. Let us know if it works :-)

Remember collation and character set are different. Collation is how a character set is ordered so U before Z that type of thing.

Posted: Wed Jan 12, 2005 10:11 am
by Burrito
nm, I guess I could just write one myself.

ianlandsman, your suggestion worked when I changed it. I will just write something that will recurse all of the tables and fields on my db that are varchars.

now the question becomes, to whom do I need to give the doughnuts?

ianlandsman, I'll give you one, and just eat the other :P

thx

Burr

Posted: Wed Jan 12, 2005 10:13 am
by Burrito
doh! hate when I'm posting at the same time as someone else...I'll look at those and see what I can come up with.

thx again.

Burr

Posted: Wed Jan 12, 2005 10:18 am
by ianlandsman
Sweeeet I like doughnuts. Actually this reminds me of a time I was in Hawaii and I had the freshest krispy creme ever. It literally melted in my mouth. mmmmmmm

Posted: Wed Jan 12, 2005 10:22 am
by JAM
As ianlandsman mention, it will most likely work after changing the default language selection on the front page.

I'm swede, but my work is based solely on english language both at work aswell as home, so I haven't personally ran into this issue (as I use English ISO-Latin). On the other hand, I havn't yet needed the use of extended characters so I've allways stuck to 'the Latin way of typing'. ;)

I'm also using either the zipped version or cvs-version of the database. If you do not, it might be that the installer is 'smart' and changes the language while installing the database? I've always used the zipped or cvs-versions of it myself. Might be something to think about.

Good luck, and return if it doesn't help.