## Authentication

### auth/ldap/auth_ldap_sync_users.php behaves differently in 1.9.17+ than in 1.9.16+

This discussion has been locked because a year has elapsed since the last post. Please start a new discussion topic.
auth/ldap/auth_ldap_sync_users.php behaves differently in 1.9.17+ than in 1.9.16+
I am 1.9 for LDAP authentication with auto creation and auto enrollment (see http://moodle.org/mod/forum/discuss.php?d=200088 ). My version, 1.9.16+ (Build: 20120202), works as expected, for example http://moodle.org/mod/forum/discuss.php?d=200030&parent=872755.

Today I updated the site to 1.9.17+ and observed that auth_ldap_sync_users.php behaves differently. It _updates_ the users who have logged in earlier but does not create new users. I went back to 1.9.16+ since this not the main issue. If the developers need more information, please ask.

The platform is Debian Squeeze, all fresh updates.

Average of ratings: -
Re: auth/ldap/auth_ldap_sync_users.php behaves differently in 1.9.17+ than in 1.9.16+

Hi Visvanath,

this is really strange! According to git, there have been no changes at all in the LDAP auth code between Build 20120202 and current 1.9.17+ (as of today). Git tells me that the only changes have been in:  blog/lib.php calendar/event.php files/index.php lang/en_utf8/error.php lib/editor/htmlarea/coursefiles.php lib/moodlelib.php mod/data/export.php version.php  I've had a quick look at the changes, and nothing strikes me as being able to cause the behaviour you are seeing.

I assume you are running auth_ldap_sync_users.php from the command line and have all the traces (logs) from the runs. I also assume that you don't get any PHP errors in the traces, so you should either see a:  User entries to add: XX  or a:  No users to be added  message somewhere in the traces. If you don't, then there's definitely something wrong somewhere (but don't know where yet). If you do, then Moodle is creating new users (only if needed, of course ).

Saludos. Iñaki.

Average of ratings: -
Re: auth/ldap/auth_ldap_sync_users.php behaves differently in 1.9.17+ than in 1.9.16+
Hi Iñaki

Thanks for checking it. I tried once again, with the same result. This is exactly what I did:
A. In an earlier date:
- Installed a fresh 1.9.16+ (Build: 20120202)
- Set it up for LDAP auto user and course creation with enrollment (nothing more)
- took a complete backup.

B1. Today:
- play back the 1.9.16+ backup (completely removes the old 1.9.16+)
- run auth_ldap_sync_users.php. -> Output
===
Configuring temp table
Creating temp table mdl_extuser
Connecting to ldap...
+ 835 users
Got 835 records from LDAP

User entries to add: 835
Inserted user adminmoodle id 3
Inserted user adminmrbs id 4
Inserted user XXXX.YYYY id 5
...
===
Confirmed through web interface.
(ran even enrol_ldap_sync.php, got all the courses created)

B2. Today
- play back the 1.9.16+ backup (completely removes the old 1.9.16+)
- overwrite $moodle with 1.9.17+ via CVS - kick the upgrade process. All o.k. - run auth_ldap_sync_users.php. -> Output === Configuring temp table Creating temp table mdl_extuser Connecting to ldap... + 835 users Got 835 records from LDAP No users to be added === Confirmed through the Moodle web interface: No users other than the admin. Am I missing something? Edit: A minor correction: The upgrade complained about unicode. Intefered manually with mysql tool, dropped the database and recreated "CREATE DATABASE dbname default character set utf8 collate utf8_unicode_ci;". Only then played back the database dump. (Haven't touched the privileges though, it must be the old "GRANT ALL PRIVILEGES ON dbname.* TO 'dbuser'@'localhost';".) Average of ratings: - Re: auth/ldap/auth_ldap_sync_users.php behaves differently in 1.9.17+ than in 1.9.16+ Update: I suspect a connection between this problem and the character encoding. Trying to localize it first in the "Backup and Restore" forum: http://moodle.org/mod/forum/discuss.php?d=200610. I'll be glad for any pointers, hints, suggestions, ... Average of ratings: - Re: auth/ldap/auth_ldap_sync_users.php behaves differently in 1.9.17+ than in 1.9.16+ Very likely not a problem with authentication. Instead, dropping and recreating the database breaks unicode in MySQL. Though not 100% clear why it happens. Details here http://moodle.org/mod/forum/discuss.php?d=200610&parent=876533. Average of ratings: - Re: auth/ldap/auth_ldap_sync_users.php behaves differently in 1.9.17+ than in 1.9.16+ The part problem, upgrade breaking unicode is solved not. Details here http://moodle.org/mod/forum/discuss.php?d=200610&parent=876533. The original problem persists. - I start with a perfectly working 1.9.16 test site - Upgrade it to 1.9.17 by overwriting$moodle
- log in as admin to kick the upgrade. No errors.

Also note that Site Administration -> Server ->Environment is all OK before and after upgrade.

Now I execute the sync scripts:
# cd /var/www/moodle_test
# php auth/ldap/auth_ldap_sync_users.php
Configuring temp table
Creating temp table mdl_extuser
Connecting to ldap...
+ 835 users
Got 835 records from LDAP
No users to be added
(Confirmed in the web interface.)

# php enrol/ldap/enrol_ldap_sync.php
== Synching 4002568
Creating Course 4002568...OK!
Empty enrolment for marc.soandso
== Synching 4002572
Creating Course 4002572...OK!
Empty enrolment for hanspeter.soandso
== Synching 4002574
... (hundreds)
== Synching 971
Empty enrolment for 2008c
== Synching 972
Empty enrolment for 2008d
... (hundreds)

The courses are created as confirmed in the web interface. Still no users.

So the original problem still exists: the upgrade broke auth_ldap_sync_users.php, not enrol_ldap_sync.php. Any hints on how to dig into this?

Average of ratings: -
Re: auth/ldap/auth_ldap_sync_users.php behaves differently in 1.9.17+ than in 1.9.16+
Hi Visvanath,

this is a bit strange, but I suspect this might be again related to database character set issues (which could only happen in 1.x).

To test if this is the case, we will need a few modifications to the code, to get some diagnostic information.

Look for the following lines (around line 565)
 $droptablesql[] = 'DROP TEMPORARY TABLE ' .$temptable; $createtemptablesql = 'CREATE TEMPORARY TABLE ' .$temptable . ' (username VARCHAR(64), PRIMARY KEY (username)) ENGINE=MyISAM';
and change them to:
 $droptablesql[] = 'DROP TABLE ' .$temptable; $createtemptablesql = 'CREATE TABLE ' .$temptable . ' (username VARCHAR(64), PRIMARY KEY (username)) ENGINE=MyISAM;
this will make the temporary external users table persistent, so we can have a look at it later.

Then look for the following lines of code (around line 816)
 $sql = "SELECT e.username, e.username FROM$temptable e LEFT JOIN {$CFG->prefix}user u ON e.username = u.username WHERE u.id IS NULL"; and change them to: $sql = "SELECT e.username, e.username FROM $temptable e LEFT JOIN {$CFG->prefix}user u ON e.username = u.username WHERE u.id IS NULL"; echo "Going to execute the following SQL sentence:\n $sql \n"; Then run the auth_ldap_sync_users.php script to populate the external users table. In addition to the usual messages, you'll see a new one with a SQL sentence. Now go to mysql, connect to your moodle database and execute the SQL sentence. Do you get any errors? Do you get the list of new users not yet added to Moodle? If not, does executing a:  SELECT COUNT(*) FROM mdl_extuser; (assuming mdl_ is your Moodle table prefix) return a non-zero number? (it should return the total number of external LDAP users). Saludos. Iñaki. Average of ratings: Useful (1) Re: auth/ldap/auth_ldap_sync_users.php behaves differently in 1.9.17+ than in 1.9.16+ Hi Iñaki It is collation after all: # php auth/ldap/auth_ldap_sync_users.php Configuring temp table Creating temp table mdl_extuser Connecting to ldap... + 835 users Got 835 records from LDAP Going to execute the following SQL sentence: SELECT e.username, e.username FROM mdl_extuser e LEFT JOIN mdl_user u ON e.username = u.username WHERE u.id IS NULL No users to be added mysql> use moodle_test; Database changed mysql> SELECT e.username, e.username FROM mdl_extuser e LEFT JOIN mdl_user u ON e.username = u.username WHERE u.id IS NULL; ERROR 1267 (HY000): Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' mysql> select COUNT(*) FROM mdl_extuser; +----------+ | COUNT(*) | +----------+ | 835 | +----------+ 1 row in set (0.00 sec) My default collate is utf8_unicode_ci. Looks like the newly created mdl_extuser is "utf8_general_ci,IMPLICIT". I went on and ran: mysql> alter table mdl_extuser convert to character set utf8 collate utf8_unicode_ci; mysql> SELECT e.username, e.username FROM mdl_extuser e LEFT JOIN mdl_user u ON e.username = u.username WHERE u.id IS NULL; ERROR 1267 (HY000): Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' mysql> alter table mdl_user convert to character set utf8 collate utf8_unicode_ci; mysql> SELECT e.username, e.username FROM mdl_extuser e LEFT JOIN mdl_user u ON e.username = u.username WHERE u.id IS NULL; +---------------------------+---------------------------+ | username | username | +---------------------------+---------------------------+ | firstname.lastname | firstname.lastname | |... +---------------------------+---------------------------+ 835 rows in set (0.01 sec) P.S. I'll gladly send you shell access and db password, if the process is easier for you that way. Average of ratings: - Re: auth/ldap/auth_ldap_sync_users.php behaves differently in 1.9.17+ than in 1.9.16+ Hi Visvanath, no need to have shell access and the db password It looks like your mdl_user table is using 'utf8_general_ci collation', while the (temporary) external user table is using 'utf8_unicode_ci' (probably from the db defaults). So we just need to make sure the external user table also uses 'utf8_general_ci'. Revert the changes we did to debug the issue, and change the original lines (around line 565) from: $droptablesql[] = 'DROP TEMPORARY TABLE ' . $temptable;$createtemptablesql = 'CREATE TEMPORARY TABLE ' . $temptable . ' (username VARCHAR(64), PRIMARY KEY (username)) ENGINE=MyISAM'; to: $droptablesql[] = 'DROP TEMPORARY TABLE ' . $temptable;$createtemptablesql = 'CREATE TEMPORARY TABLE ' . \$temptable . ' (username VARCHAR(64), PRIMARY KEY (username)) ENGINE=MyISAM character set utf8 collate utf8_general_ci';
This will create the temporary external users table with the same collation as the internal user table, the SQL query will execute without error and you will get your external users added to Moodle

BTW, make sure the internal user table (mdl_user) is reset to the original collation (in fact, I'd drop and re-restore the database from the dump, just to make sure everything works as expected ).

Saludos.
Iñaki.

Average of ratings: Useful (1)
Re: auth/ldap/auth_ldap_sync_users.php behaves differently in 1.9.17+ than in 1.9.16+

Hi,

Just to say that perhaps this bug is related : MDL-13458

Average of ratings: -