1. No Profile Picture
    Permanently Banned
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Rep Power

    Illegal mix of collations

    Hi everyone,

    i have php and MySQL 4.1.11 installed on my linux box,i am getting error for mix of collation when i use find_in_set php function also some invalid characters are coming on site from db.

    error is

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

    if any one can help i will be really thankfull.
  2. #2
  3. (retired)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2003
    The Laboratory
    Rep Power
    Yep. MySQL is complaining because it can't work out how to combine utf-8 data and latin-1. What type of data are you storing?

    I think there are two solutions here - the first is to convert all the tables to either latin-1 or utf-8 (I'd go for utf-8, but this will depend on what data you're storing).

    Or, you can probably use mysql keyword CONVERT() in the query. Something like this:

    SELECT CONVERT(fieldname USING utf8) FROM tablename;

    should convert the fieldname into utf8. Not sure how fast this will be, but you're probably going to incur a performance hit.
  4. #3
  5. Plays with fire
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Aug 2003
    Rep Power
    Thank you!

    All I want here is absolutely plain text, but I'm dealing with users pasting Word docs into my forms. I can't stop that so I need to clean it up after.

    I've also tried filtering the data between the form and the database, but have had only limited success there.

    After building dynamic sites for more than 20 years you'd think I could have a decent system by now, but people never stop finding ways to break things.

    Thank you again!
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence

IMN logo majestic logo threadwatch logo seochat tools logo