Mysql Collation issue

  • Resolved anil1220

    (@anil1220)


    18 years, 7 months ago

    Hi Everybody,

    I am newbe for this forum, Here is my issue:

    Caused by: java.sql.SQLException: Illegal mix of collations (latin1_swedish_ci,I

    I am getting above error. I have checked collation in my.cnf file and got to know that it is utf8.

    When I checked table collation by using show table status like ‘ xxx’; command got to know that it is having latin1_swedish_ci, and checked other tables are also have same collation.

    Where as when I checked different database tables those are having utf8 collation.

    By observing all results I understood that the database’s (I am having issue) collation has changed. I am thinking to change the collation by using alter command.

    I am I right here? This is production database so I need to be 200 % sure before going to deploy. Please any one suggest any thing over looked here or any thing needs to checked.

    Any help would be greatly Appreciated !!! Thanks in advance.

    Regards,
    Anil Kumar

Viewing 3 replies - 1 through 3 (of 3 total)

  • I’m not exactly sure why (could just be because the SQL used to create the WP tables etc. doesn’t spell out the charset/collation for each table, and somewhere burried deep within the bowels of MySQL lies a ‘fallback’ default of latin1_swedish_ci. I’ve run across the same issue (not only with WP) generally I do the following:

    ALTER DATABASE     db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

    that said, I’ve never run across an error like you’re talking about, everything seems to work OK without doing that but if folks dig in they’d likely find the same situation. my “my.cnf” also sets utf8 as default, so again ,me thinks this is a MySQL issue…

    ps. you may also need to alter each table that exists already, since they won’t be changed with the alter db, also, you could re-compile mySQL using
    ./configure --with-charset=utf8 --with-collation=utf8_general_ci if it’s your own box. by default it’s compiled with latin1_swedish_ci unless you tell it something else (many hosting providers don’t specify this, so that’s where you end up with the swedish issue.

    Possible fix:

    https://www.vbulletin.com/forum/showthread.php?t=121120

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Mysql Collation issue’ is closed to new replies.

Tags