ldap_sync_users problem

ldap_sync_users problem

by Mark Andrews -
Number of replies: 5

Hi - I have LDAP working on out server to authenticate users, but am trying to move to the next stage of using auth_ldap_sync_users.php to Sync moodle records to our AD.

However when I run auth_ldap_sync_users.php I get the following SQL message(s):

Configuring temp table connecting to ldap + 1000 users

Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

SELECT u.id, u.username FROM mdl16_user u LEFT JOIN mdl16_extuser e ON u.idnumber = e.idnumber WHERE u.auth='ldap' AND u.deleted='0' AND e.idnumber IS NULL

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

SELECT e.idnumber,1 FROM mdl16_extuser e LEFT JOIN mdl16_user u ON e.idnumber = u.idnumber WHERE u.id IS NULL OR (u.id IS NOT NULL AND u.deleted=1)

I've had a look at the Database and cannot find the table mdl16_extuser, could this be the problem? Ive checked all my tables and they are all utf8_unicode.

If I run the page again with debugging on I get: Parse error: parse error, unexpected T_STRING in C:\VLE\auth\ldap\auth_ldap_sync_users.php on line 22

I am running moodle 1.6.3+, on server 2003 & IIS

My tech guys seem to be of little help and it would ease admin if i could get this working - Thanks in advance.

Mark

Average of ratings: -
In reply to Mark Andrews

Re: ldap_sync_users problem

by Iñaki Arenaza -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

The mdl16_extuser is a temporary table that's created on the fly by auth_ldap_sync_users.php and removed as soon as the script finishes. So it's not surprising you can't find it smile

It seems MySQL is creating the temporary table with a different UTF-8 enconding (utf8_general_ci) than your normal tables encoding (utf8_unicode_ci). I just don't know why, but you can try forcing the encoding in the code.

Edit C:\VLE\auth\ldap\lib.php and around line 1513 (line number for 1.6.4+) you'll see code that reads:

execute_sql('CREATE TEMPORARY TABLE ' . $CFG->prefix .'extuser (idnumber VARCHAR(64), PRIMARY KEY (idnumber)) TYPE=MyISAM',false);

and change it to read:

execute_sql('CREATE TEMPORARY TABLE ' . $CFG->prefix .'extuser (idnumber VARCHAR(64), PRIMARY KEY (idnumber)) TYPE=MyISAM CHARACTER SET utf8 COLLATE utf8_unicode_ci',false);

and see if it helps.

Saludos. Iñaki.

Average of ratings: -
In reply to Iñaki Arenaza

Re: ldap_sync_users problem

by Mark Andrews -

Hi Iñaki, Thanks for you help.

Changing the code above has fixed the script so that it runs! - I had a feeling I had to make a change in the SQL but was not confident in what or where to put it.

Thanks again

Mark

Average of ratings: -
In reply to Mark Andrews

Re: ldap_sync_users problem

by Rahim Virani -
We had the same problem in Moodle 1.8.3 I changed line 360 to reflect the above in auth/ldap/auth.php and it fixed the problem.

Inaki, should I put a bug report in? Please advise? I have also heard that this might be a mysql bug (http://bugs.mysql.com/bug.php?id=27534)

Interested to know your thoughts.
Average of ratings: -
In reply to Rahim Virani

Re: ldap_sync_users problem

by Iñaki Arenaza -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

I'd say this is more of a MySQL configuration issue than a Moodle bug (and I'm not sure this is the same problem than the MySQL bug you reference to).

You need to make sure you configure your MySQL client settings and your MySQL server settings to use the same collation (in addition to use the UTF-8 character set).

Moodle can't know what collation type you specified for your tables/colums, so it just specifies the UTF-8 character which in turn makes MySQL to use the default collation (utf8_general_ci). If your tables/colums are using an incompatible collation, then you need to either patch Moodle to specify your particular collation (like I suggested above) or convert your tables/colums to the default collation (this can be easily be done with phpmyadmin, for example).

Anyway is up to you to open a bug if you feel this should be addressed in a different way smile

Saludos. Iñaki.

Average of ratings: -
In reply to Iñaki Arenaza

Re: ldap_sync_users problem

by Séverin Terrier -
Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Testers Picture of Translators
Hello,

i had the same problem with the latest Moodle 1.9.5+ from yesterday, and CAS authentication (suppose it would still be the same problem with LDAP) sad

For me, just adding "CHARACTER SET utf8" fixed the problem smile
I suppose that the collation type is issued from the encoding...

It would be good to have it included in future releases wink
Séverin
Average of ratings: -