Error after updateing

aperezcrespo wrote on Thursday, February 26, 2009:

Hi

I just updated an old installation and now I get the following creating or listing encounters.

ERROR: query failed: select formdir, user, form_name, form_id, deleted from forms where encounter = ‘6’ and pid=‘1’ ORDER BY FIND_IN_SET(formdir,‘vitals’) DESC, date DESC

Error: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation ‘find_in_set’

Thanks

cfapress wrote on Friday, February 27, 2009:

That error is coming from MySQL, through OpenEMR. Unfortunately I don’t know how to fix it off the top of my head. I can tell you that you need to correct the ‘collations’ in MySQL.

OpenEMR uses the utf8_unicode_ci collation. Perhaps your database is set to latin1_swedish_ci as a default?

Jason

aperezcrespo wrote on Friday, February 27, 2009:

I have no idea or understanding as to how to fix it either… and yes latin1_swedish_c1 is the default.

Thanks

cfapress wrote on Monday, March 02, 2009:

Well… Here’s some documentation from MySQL about collations:
http://dev.mysql.com/doc/refman/5.1/en/charset.html

And more to the point:
http://dev.mysql.com/doc/refman/5.1/en/charset-repertoire.html

The error definitely has something to do with the default character set of your database and the character set of the rows being used in the MySQL query. It seems that perhaps the string comparison done by this command:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

… could be returning something in an invalid character set. I get the feeling that the Swedish character set can be a little fussy about strings.

Would it be possible for you to change the default character set for your database server? Here are some instructions about it:
http://dev.mysql.com/doc/refman/5.1/en/charset-connection.html

Jason

ideaman911 wrote on Wednesday, March 04, 2009:

Guys;

I found and noted the fix for this back in Nov.  I hope it is being fixed in the CVS, which I as a Windows guy know NOTHING about.  The fix has nothing to do with the language - it is one of those software error  mis-directs because computers are ignorant.  The fix was (and still is)

Found cause of issue from 11/9/08; in openemr\library\forms.inc near line 43 needs // ahead of $sql .="ORDER BY FIND_IN_SET… " there are too many arguments for the next statement otherwise.  This might want to be revised in the future if the listed order is not workable, but for now leave as adjusted to simply comment.

Good Luck.

Joe Holzer

aperezcrespo wrote on Wednesday, March 04, 2009:

Hi

  Thanks for that it saved me from a full upgrade.  As it turns out it is a mysql 4.x with php4x.  That compiled  version of Mysql did not support utf8_unicode_ci (or so it complained when I tried to activate it).  But this seems to be the fix.  I have other installs with MySQL5x and PHP5x but this error doesnt show up.  I wonder if anyone has the why to that.

Anyway
Thanks a mill

ideaman911 wrote on Wednesday, March 04, 2009:

Guys;

I still cannot fathom how that works on a non-windows system?!  Too many arguments is exactly that.  That it ONLY seems to show up on the Windows install is suspicious at the least.  But since I made that fix we have had ZERO similar occurrences.

Jason;  Any chance of your guys who DO know this stuff can look at that specific case and address that?  Thanks.

Joe Holzer

aperezcrespo wrote on Thursday, March 05, 2009:

This isnt a windows system, its OpenSuse 10.0.  Thats what got me.

Thanks

cfapress wrote on Friday, March 06, 2009:

I believe that ajperezcrespo hit the nail on the head a few posts back. He was getting the error with MySQL v4 without support for the utf8_unicode_ci collation.

His MySQL v5 installations were working just fine. This might be because the newer versions of MySQL support unicode by default.

Jason