When i create fields in a table i see collation field and it is set to latin1_swedish_ci

I never had this collation things.. in oldl database..

Does anyone know how can i disable this collation.

I also had problem in sql query in php myadmin

//query
 SELECT *
FROM `hb_customer_type`
WHERE type_name = 'Composit'
LIMIT 0 , 30 

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

    i recently upgraded from 3.23 to 4.x and started getting this too. Only answer I've found is to change the collation field by field to utf8_general_ci.

    Why the hell is the default latin1_swedish_ci ???? :glare:

    I too would love to know if there's an easier way to deal with this.

      a year later

      I attended the 2006 MySQL users conference and learned the answer as to why the default is latin1_swedish_ci. It's because the authors of MySQL are Sweedish. It would be so nice to be able to change the default to latin1_bin or what ever you use.

        Well, you can always recompile MySQL and change that:

        ./configure --with-charset=utf8 --with-collation=utf8_general_ci

        or alter the way the mysqld service starts:

        mysqld --character-set-server=utf8 --collation-server=utf8_general_ci

        or just convert your databases:

        ALTER DATABASE `myDB` CHRACTER SET utf8 COLLATE utf8_general_ci;
// run the following query on each table:
ALTER TABLE `tableName` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
          amchargue wrote:

          i recently upgraded from 3.23 to 4.x and started getting this too. Only answer I've found is to change the collation field by field to utf8_general_ci.

          Collations and encodings are connected. You can only use one of the utf8 collations if you're using utf8 encoding.

          You can only use one of the latin1 collations if you're using latin1 encoding.

          You MUST be fully aware of what client encoding you're using in the database. It's a good idea to set the database encoding to the same as the client.

          Then set the collation as appropriate.

          The error message you're getting is from having a mixture of tables / columns in different encodings. This is legal but extremely awkward; don't do it if you can avoid it.

          Why the hell is the default latin1_swedish_ci ???? :glare:

          The original developers of MySQL are Swedish. Moreover, this collation happens to work pretty well for many other European languages as well (Certainly English, possibly French and German).

          Having Swedish collation means that strings containing Swedish characters will be sorted in the same order as they appear in Swedish dictionaries. As it happens, this is the same as it would be in an English dictionary (except that we don't have those letters).

          The _ci suffix means it's case insensitive.

          Mark

            I ran into a similar collation problem yesterday, but my problem was with sqlYOG. I had been using the latest verysion for about a month with no problem, but yesterday, my problems started.

            On my development server, I have been echoing my sql statements to the browser so I can check the values to make sure what I'm asking for and what I want are the same thing. Yesterday, when I was doing this, after about 2 hours of work, I started getting a collation error in sqlYOG, but not in the brower, which seemed to executing the query. Every time I copied the sql and pasted it in sqlYOG, same error. I was getting very frustrated. Finally, I decided to revert back to an older version of sqlYOG and the problem stopped. I will check it again on Monday to see if the error still occurs, but I think I need to put in a bug report with sqlYOG about this.

            I much prefer phpMyAdmin, but alas, the dbadmin thinks it's too insecure and won't load it. Since it runs from the browser, she's probably right. It's just soooo much easier than sqlYOG.

              I will just repeat my advice:

              • Ensure that all tables and columns in your DB are in the same encoding and collation (You can see this easily enough using mysqldump to dump the structure out)
              • Make sure that's the same as the encoding that you're using in PHP and HTML.
              • Ensure that the database connection encoding is the same again

              and you should have no problems.

              Mark

                I checked all of this at least 3 times. Maybe more. When I got to work this morning. I talked to my boss about it and he told me to send him the sql statement I was having trouble with and he would look at it. He was somewhat familiar with the problem. I could not duplicate the problem, so I still think it was something with sqlYOG. Like I said, everything was working in the browser, I just couldn't double check in sqlYOG to make sure I was returning the right data. Some of my sql statements in this project are fairly complicated and I have a very demanding customer, so I always want to triple check everything so he continues to think I'm indespensable. 🙂

                  Write a Reply...