problem with find in set

Question

Hello everyone.

I get the following error:

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

This is the query I was trying to execute:

SELECT ID FROM xs_user_profiles WHERE ID='' AND FIND_IN_SET('1',site_structure);

I looked up the properties of this table and it has charset UTF-8 and Collation UTF-8_general_ci.

This works well with all my sites so I am not sure what's going wrong.

Answer 1

If you are using mysqli, issue this command right after connecting:

$mysqli->set_charset("utf8");

This will set your connection encoding to UTF8 (same as your table uses).

With plain mysql, use this:

mysql_query("SET NAMES utf8", $conn);
mysql_query("SET CHARACTER SET utf8", $conn);

Answer 2

I'm guessing your string literal ('1') is in a different collation due to different connection variables. See here