Clean install of version 4.2.0 starting with a blank, new database, attempting to access the Globals menu results in this string of errors:
= = = = = = = = = = = =
ERROR: query failed: SELECT * FROM lang_definitions JOIN lang_constants ON lang_definitions.cons_id = lang_constants.cons_id WHERE lang_id=? AND constant_name = ? LIMIT 1
Error: Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation ‘=’
/var/www/openemr/library/translation.inc.php at 36:sqlStatementNoLog
/var/www/openemr/library/globals.inc.php at 2054:xl(Māori)
/var/www/openemr/interface/super/edit_globals.php at 12:require_once(/var/www/openemr/library/globals.inc.php)
= = = = = = = = = = = =
Seems a little bit unusual to have database errors on a fresh install. It’s simply a matter of fixing the collation settings in the relevant DB tables, but it could really confuse new or novice users.
Now to comb through the database settings for 2 new installs and see what other tables MySQL created with bogus collation options. I’m guessing this is something related to the current version of SQL that’s packaged with Ubuntu 14 (5.5.41 in this case), apparently if it’s not given a specific collation setting then it defaults to latin instead of utf8. I checked with a clean install on MySQL version 5.5.29, and it didn’t exhibit this behavior.
Is there a DB layout document somewhere that describes what all the default table field/value/collation settings should be for a normal install of OpenEMR ?
Just wanted to throw this one out there in case somebody else runs into the same problem.
This shouldn’t happen during a standard install. This usually happens in following scenario on a hosted server:
The user creates a openemr database (which uses the default encoding, which is latin1 in many cases).
Then the user installs OpenEMR (and selects that has already installed the openemr database), which then uses incorrect encoding default.
During a standard install, the OpenEMR database is created during the install and explicitly chooses UTF8 encoding as the default for the database (note you actually select it during the installation in the “UTF-8 Collation” setting and note that if you choose None, it will resort to latin1 and could create your error).
Things to check
Try another install and ensure don’t touch the “UTF-8 Collation” setting.
If still issue then:
Which tables are using latin1?(in phpmyadmin scan through the Collation table on the tabl listing)
In phpmyadmin, when you click on databases, what word do you see to the right of openemr in the only column(entitled collation)?
Thanks for the replies guys. This one was probably my fault for creating the empty DB direct from the command line, and then allowing the OpenEMR install routine to populate the tables. Never really had any problems doing it that way before, but it still could be related to something in the newer version of MySQL that you get with a fully updated load of Ubuntu 14. The collation for the database shows “utf8mb4_general_ci”, which I compared to a running install and that seems to be correct. Had to reset all the collation settings for every table int the database back to utf8 though, apparently the default behavior for MySQL now is to set everything to latin1_swedish_ci.
Something to watch out for anyway, I guess from now on I’ll just play dumb and let the OpenEMR setup routine build the DBs from scratch.