URGENT - 3.1.7.: LDAP-Sync : Error "Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) ....."

URGENT - 3.1.7.: LDAP-Sync : Error "Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) ....."

by Monica Franz -
Number of replies: 4

Hi,

after upgrading Moodle from 3.1.4 to 3.1.7 and using the Scheduled task for LDAP-Snyc, I've recognized that some new users where missing, and started LDAPsysc by CLI.

 I got the following error message:

[AUTH LDAP] The users sync cron has been deprecated. Please use the scheduled task instead.
Default exception handler: Fehler beim Lesen der Datenbank Debug: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='
SELECT u.*
                      FROM mdl_user u
                 LEFT JOIN mdl_tmp_extuser e ON (u.username = e.username AND u.mnethostid = e.mnethostid)
                     WHERE u.auth = ?
                           AND u.deleted = 0
                           AND e.username IS NULL
[array (
  0 => 'ldap',
)]
Error code: dmlreadexception
* line 479 of /lib/dml/moodle_database.php: dml_read_exception thrown
* line 1175 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
* line 765 of /auth/ldap/auth.php: call to mysqli_native_moodle_database->get_records_sql()
* line 70 of /auth/ldap/cli/sync_users.php: call to auth_plugin_ldap->sync_users()
...
After that I checked config.php

$CFG->dboptions = array(
    'dbpersist' => false,       // should persistent database connections be
                                //  used? set to 'false' for the most stable
                                //  setting, 'true' can improve performance
                                //  sometimes
    'dbsocket'  => false,       // should connection via UNIX socket be used?
                                //  if you set it to 'true' or custom path
                                //  here set dbhost to 'localhost',
                                //  (please note mysql is always using socket
                                //  if dbhost is 'localhost' - if you need
                                //  local port connection use '127.0.0.1')
    'dbport'    => '',          // the TCP port number to use when connecting
                                //  to the server. keep empty string for the
                                //  default port
    'dbcollation' => 'utf8_unicode_ci', // MySQL has partial and full UTF-8
                                // support. If you wish to use partial UTF-8
                                // (three bytes) then set this option to
                                // 'utf8_unicode_ci', otherwise this option
                                // can be removed for MySQL (by default it will
                                // use 'utf8mb4_unicode_ci'. This option should
                                // be removed for all other databases.

);


and the db.opt seems to be ok as well:

default-character-set=utf8
default-collation=utf8_unicode_ci

Where does utf8_general_ci come from? How can I correct the error?

We're using MYSQL 5.5.49 and PHP 5.6.20.

Cheers,

Monica


Average of ratings: -
In reply to Monica Franz

Re: URGENT - 3.1.7.: LDAP-Sync : Error "Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) ....."

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Did you copy your database in some way as part of the upgrade?

If you did, you've messed up the collations. Chances are the target database had a different collation default. 

In reply to Howard Miller

Re: URGENT - 3.1.7.: LDAP-Sync : Error "Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) ....."

by Monica Franz -

Hi Howard,

no, the database wasn't changed before upgrading. (just dump making)

moodledata and the database are from the same time.

Cheers,

Monica

In reply to Monica Franz

Re: URGENT - 3.1.7.: LDAP-Sync : Error "Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) ....."

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

And you didn't reload one of those dumps? The database was completely untouched? That would be very strange. Is it possible that your entire database was utf8_general_ci before (this used to be acceptable for Moodle)

Essentially, you need to fix up the collation. 

Big caveat... I have never tried this. I would take a complete database backup and then do the full unicode support conversion. As this should make all your collations consistent anyway...

See MySQL_full_unicode_support

Collations in MySQL are a nightmare. Enough to make you want to use PostgreSQL wink

Average of ratings: Useful (1)
In reply to Howard Miller

Re: URGENT - 3.1.7.: LDAP-Sync : Error "Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) ....."

by Monica Franz -

Hi Howard,

to change the db-system to Postgres is no option for us now. wink

I can remember to have that simular problem in Moodle 1.9. The character set of the temp table had to be utf8 for us but was set to utf8_general_ci. This I could change in the function sync_users, but now the building of the query seems to be in a subfunction somewhere else.

Now I got it working thru deleting the line 'dbcollation' => 'utf8_unicode_ci' in config.php.

Which other effect this has I can't tell. But ldap sync now runs fine.

Cheers,

Monica