#1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICI
Moderator: General Moderators
#1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICI
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
#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
-
ianlandsman
- Forum Newbie
- Posts: 24
- Joined: Thu Dec 30, 2004 9:50 pm
- Location: New York
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.ianlandsman wrote:You just need to be careful to use UTF8 or Latin when you build databases.
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
thx in advance for your time,
Burr
-
ianlandsman
- Forum Newbie
- Posts: 24
- Joined: Thu Dec 30, 2004 9:50 pm
- Location: New York
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.
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.
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
thx
Burr
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
thx
Burr
-
ianlandsman
- Forum Newbie
- Posts: 24
- Joined: Thu Dec 30, 2004 9:50 pm
- Location: New York
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.
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.