LDAP - Error writing to database

LDAP - Error writing to database

by David Blanchard -
Number of replies: 6

Hi all,

First time trying LDAP authentication.

Win Server 2008 & XAMPP 1.74.

I've entered all the LDAP details i believe are nessecary and get the following error.

C:\>c:\xampp\php\php.exe -f c:\xampp\htdocs\training\auth\ldap\cli\sync_users.ph
p
Connecting to LDAP server...
Creating temporary table tmp_extuser
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
...................Default exception handler: Error writing to database Debug: D
uplicate entry '1-user name' for key 'mdl_tmpextu_mneuse_uix'
INSERT INTO mdl_tmp_extuser (username,mnethostid) VALUES(?,?)
[array (
  0 => 'user name',
  1 => '1',
)]
* line 397 of \lib\dml\moodle_database.php: dml_write_exception thrown
* line 878 of \lib\dml\mysqli_native_moodle_database.php: call to moodle_databas
e->query_end()
* line 938 of \auth\ldap\auth.php: call to mysqli_native_moodle_database->insert
_record_raw()
* line 670 of \auth\ldap\auth.php: call to auth_plugin_ldap->ldap_bulk_insert()
* line 65 of \auth\ldap\cli\sync_users.php: call to auth_plugin_ldap->sync_users
()

!!! Error writing to database !!!
Potential coding error - existing temptables found when disposing database. Must
 be dropped!

Can someone please help or offer any advice?

If i change subcontext to no then i get:

C:\>c:\xampp\php\php.exe -f c:\xampp\htdocs\training\auth\ldap\cli\sync_users.ph
p
Connecting to LDAP server...
Creating temporary table tmp_extuser
Did not get any users from LDAP -- error? -- exiting
Potential coding error - existing temptables found when disposing database. Must
 be dropped!

Average of ratings: -
In reply to David Blanchard

Re: LDAP - Error writing to database

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
What are your LDAP settings for:

* User Attribute:
* Contexts:

Also, I assume you are using Moodle 2.0.x or 2.1.x, as you don't specify it anywhere smile

Saludos.
Iñaki.
In reply to Iñaki Arenaza

Re: LDAP - Error writing to database

by David Blanchard -

Hi,

I am using Moodle 2.1, fresh download the day of the error message!

User Attriubute: *blank*

By contexts i guess you mean search subcontexts, this is set to Yes. If i select No then i get the error

C:\>c:\xampp\php\php.exe -f c:\xampp\htdocs\training\auth\ldap\cli\sync_users.ph
p
Connecting to LDAP server...
Creating temporary table tmp_extuser
Did not get any users from LDAP -- error? -- exiting
Potential coding error - existing temptables found when disposing database. Must
 be dropped!

Any help will be greatly appreciated.

Thanks.

In reply to David Blanchard

Re: LDAP - Error writing to database

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
By contexts I meant the actual contexts you had configured in your settings smile. But now that you've told me what your user attribute setting is, I think the problem is with it, not with the contexts setting smile

If you leave the user attribute blank, it uses the default setting for that kind of LDAP directory. In your case (MS Active Directory) this means we are using the 'cn' (common name) attribute. This is the name you see in the user listings in 'Active Directory Users and Computers', under the 'Name' column.

The problem with this attribute in Active Directory is that it's not globally unique, it's only unique inside a given "container" (organizational unit or folder, like 'Users'). This means you can actually have two (or more) users with the same 'cn' as long as they are in different OUs.

But Moodle needs each user to have a globally unique username, so if you have two (or more) users with the same 'cn', either you change the 'cn' (which I assume is not acceptable) or use a LDAP attribute that is guaranteed to be unique among all users.

The good news is that there's such an attribute: sAMAccountName (the username your users need to type to logon in Windows). The bad news is that any existing Moodle users that have been created so far using the LDAP auth plugin have the wrong username (unless their 'cn' and their 'sAMAccountName' attribute have the same value). And Moodle doesn't know how that the old username and the new username (once you change the user attribute setting) refer to the same user.

So you'll need to manually edit all those existing users to change their usernames to the right value *before* you change the user attribute setting (and I'd do it in maintenance mode, to make sure they don't log in while you are doing the changes).

After you do all the username changes, you can change the user attribut setting and run the auth/ldap/cli/sync.php script. It should run without any errors about duplicate usernames.

Saludos.
Iñaki.
Average of ratings: Useful (1)
In reply to Iñaki Arenaza

Re: LDAP - Error writing to database

by David Blanchard -

That worked a treat - thank you very much for the help!
Now to see if i can get NTLM working for single sign-on! smile


David.

In reply to David Blanchard

Re: LDAP - Error writing to database

by David McGeown -

Hi, I'm using the attribute sAMAccountName, but still having this issue.

 

It is only affecting about 10 out of 1000 users.....

 

In reply to Iñaki Arenaza

Re: LDAP - Error writing to database

by Susan Mangan -

This should be added to the documentation.   Finding this post seriously helped me after pulling my hair out!

 

"If you leave the user attribute blank, it uses the default setting for that kind of LDAP directory. In your case (MS Active Directory) this means we are using the 'cn' (common name) attribute. This is the name you see in the user listings in 'Active Directory Users and Computers', under the 'Name' column.

The problem with this attribute in Active Directory is that it's not globally unique, it's only unique inside a given "container" (organizational unit or folder, like 'Users'). This means you can actually have two (or more) users with the same 'cn' as long as they are in different OUs.

But Moodle needs each user to have a globally unique username, so if you have two (or more) users with the same 'cn', either you change the 'cn' (which I assume is not acceptable) or use a LDAP attribute that is guaranteed to be unique among all users.

The good news is that there's such an attribute: sAMAccountName (the username your users need to type to logon in Windows). The bad news is that any existing Moodle users that have been created so far using the LDAP auth plugin have the wrong username (unless their 'cn' and their 'sAMAccountName' attribute have the same value). And Moodle doesn't know how that the old username and the new username (once you change the user attribute setting) refer to the same user."