Recent Topics
  1. b2evolution CMS Support Forums
  2. b2evolution Support
  3. General Support
  4. MySQL Error: Illegal mix of collations [Maybe solved]

MySQL Error: Illegal mix of collations [Maybe solved]

1 Sep 12, 2007 18:44    

b2evo version: 1.10.2

This weird message appeared several times today in logs. A user tried to post a comment he copied and pasted from MS Word (and so I assume it had funky propietary MS stuff). He got the following:

MySQL error!

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

Your query: Check URL against antispam blacklist

.

The full error message in my log is:

[error] b2evolution error: MySQL error!Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'like'(Errno=1267)Your query: Check URL against antispam blacklistSELECT aspm_string\r / FROM evo_antispam\r / WHERE ', qu\xe9 haria Bolivar de cara a la globalizacion? Qu\xe9 haria Bolivar en el conflicto Israelo-Palestino? Conduciria Bol\xedvar ).' LIKE CONCAT('%',aspm_string,'%')\r / LIMIT 0, 1 in /home/libenie2/public_html/blog/inc/_misc/_db.class.php at line 522 / REQUEST_URI: /blog/anarco/comment_post.php / HTTP_REFERER: http://liberal-venezolano.net/blog/index/2006/03/13/la_nueva_bandera

I went to phpmyadmin and checked the settings there. MySQL connection collation was set to "utf8_general_ci", so I changed it to "latin1_spanish_ci" and after that tried to post a comment similar to the original with success.

It is the first time I notice this. The locale of the blog is set to es-VE with charset iso-8859-1.

I don't know if my action was permanent though. I guess when the MySQL server restarts, this collation stuff will be reset and the problem could reappear. Any suggestion?

Back to top

2 Sep 13, 2007 03:53

Im not really sure but the "text" content your friend posted from Word to your blog was under a different charset than the one your blog's mySQL DB is set to, hence the conflict.

If I'm not mistaken, the latest phpMyAdmin have an option "Multi-Collation" or "Mix Collation" which will make your DB open for any charset.

Personally though, it is better to use Unicode (utf-8 most common), problem is, MS Office's default charset is automatically set to the OS' default charset. You can edit it in MS Office (example Word)'s settings to use unicode/utf-8, that's what I always do when I sit to a new computer I haven't setup.

Unicode housed almost all "commonly used" characters in today's existing languages, so you shouldn't have problems.

Back to top


Form is loading...