LDAP Sync Task Error - Existing temptables found when disposing database

LDAP Sync Task Error - Existing temptables found when disposing database

by Marcus Wynwood -
Number of replies: 23

Hi everyone,

I have Moodle setup to use my organisations Active Directory for authentication, and it works great.

Users can login with their credentials, Moodle creates an account for them, and away they go.


The trouble is, users can't be added to their classes until their accounts have been created - after they login.

The answer seems to be the LDAP Sync Task - this should slurp every account that exists on AD and put them on Moodle.


When I run the command:

php moodle/admin/tool/task/cli/schedule_task.php --execute\\auth_ldap\\task\\sync_task

I get a screen full of dots as it's working away, and then I get this error:

...... used 199013 dbqueries ... used 223.62434101105 seconds
Shecduled task failed: LDAP user sync job (auth_ldap\task\sync_task), Error writing to database
Potential coding error - existing temptables found when disposing database. Must be dropped!


The database user has full permissions granted. 

The server is:

  • Moodle 3.0.2
  • PHP 5.4.16
  • MariaDB 5.5.44
  • CentOS 7

Any clues appreciated.

Thanks!

Average of ratings: -
In reply to Marcus Wynwood

Re: LDAP Sync Task Error - Existing temptables found when disposing database

by Ken Task -
Picture of Particularly helpful Moodlers

If you have a large user base, might want to increase some php settings ... memory used, time to run if those are currently the defaults and have never been tweaked upwards.   Reason, depending upon how ldap auth is configured, you could be pulling across a lot of data.

There's another script located in moodlecode/auth/ldap/cli/ called sync_users.php

Run as root from moodlecode directory (but could be anywhere if you put in the full path):

php auth/ldap/cli/sync_users.php

Takes no parameters and does read the settings in the configuration of LDAP in the Moodle UI.   Have found the more 'accurate' the settings are for LDAP the less junk one gets into mdl_user table.  By that I mean, the OU's that gets students and staff is best rather than using  the top level OU + DC's for the domain.  If using the latter, one could get machines, etc. ... ie, a bunch of junk as far as users go.

'spirit of sharing', Ken

Average of ratings:Useful (1)
In reply to Marcus Wynwood

Re: LDAP Sync Task Error - Existing temptables found when disposing database

by Sam Stevens -

On your LDAP settings have you by any chance left "User attribute" blank or set it to cn?

Average of ratings: -
In reply to Marcus Wynwood

Re: LDAP Sync Task Error - Existing temptables found when disposing database

by Ken Task -
Picture of Particularly helpful Moodlers

One other thing about the error reported ... check the apache error logs.   If you see 'Server has gone away ....' , you need to increase max_packet_allowed in MySQL config. If you don't have that setting in the config, add a line and set to 64M.   That value is a guess.

'spirit of sharing', Ken

Average of ratings: -
In reply to Ken Task

Re: LDAP Sync Task Error - Existing temptables found when disposing database

by Marcus Wynwood -

Thanks so much for your help Ken - it's working!

I increased the 'memory used' and 'time to run' values in php.ini, and filtered my LDAP results with the Object Class setting in Moodle - and it worked perfectly.

This gave the process more memory, more time, and less data to deal with.

It created about 28,500+ accounts in about 40 minutes, using this command:

php moodle/admin/tool/task/cli/schedule_task.php --execute\\auth_ldap\\task\\sync_task
Cheers!
Average of ratings: -
In reply to Marcus Wynwood

Re: LDAP Sync Task Error - Existing temptables found when disposing database

by Ken Task -
Picture of Particularly helpful Moodlers

Welcome.   Feel free to rate the posting that helped useful. ;)

Am curious though ... why the choice to run the schedule_task.php script vs the sync script in auth/ldap/cli/ ?

'spirit of sharing', Ken

Average of ratings: -
In reply to Ken Task

Re: LDAP Sync Task Error - Existing temptables found when disposing database

by Marcus Wynwood -

G'day Ken,

I'll be sure to rate your post - thanks for pointing me in the right direction.


The reason I used schedule_task.php is because it seems to be the new way to operate in v3.

The scheduled tasks can be accessed and edited right from within Moodle, which is awesome.

I was just using the command line for testing.


I didn't use the sync_users.php script, because it contains this comment:

 @deprecated since Moodle 3.0 MDL-51824 - please do not use this CLI script any more, use scheduled task instead.
 @todo MDL-50264 This will be deleted in Moodle 3.2.


Thanks again for your help!

Average of ratings:Useful (1)
In reply to Marcus Wynwood

Re: LDAP Sync Task Error - Existing temptables found when disposing database

by Ken Task -
Picture of Particularly helpful Moodlers

Thanks for response. 

<begin red in face> I've gotta start reading the 'fine print', don't I?  Duh! :\ </end red in face>

Good thing only one 'client' has opted to go to 3.latest ... 'client' doesn't use LDAP so I haven't had to deal with that.   Do have 'clients' that host internally and use LDAP ... will have to adjust the automated setups on those instances when/if they get to 3.0.highest or 3.2.x

So we'll exchange useful ratings! ;)

'spirit of sharing, Ken


Average of ratings: -
In reply to Marcus Wynwood

Re: LDAP Sync Task Error - Existing temptables found when disposing database

by Samir Patel -

I am having the same error as indicated in your original post -- Could you please provide the specific php.ini or mysql variables changed to get this to work for you?

I have increased the mysql max packet size to 64M, and increased the php memory allocation max to 2048M and max execution time to 300s. The problem still persists:


Scheduled task failed: LDAP users sync job (auth_ldap\task\sync_task),Error writing to database

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

Average of ratings: -
In reply to Samir Patel

Re: LDAP Sync Task Error - Existing temptables found when disposing database

by Emma Richardson -
Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Plugin developers

Turn debugging on on the site and then run it again.  You should get a more detailed error message.

I had a plugin recently that for some reason caused an error with the sync process.

Average of ratings: -
In reply to Samir Patel

Re: LDAP Sync Task Error - Existing temptables found when disposing database

by Marcus Wynwood -

G'day Samri,

The two php.ini variables I changed were:

max_execution_time = 0
memory_limit = 8G

These settings may be a bit extreme - scripts get unlimited time to run, and 8Gb of memory - but it worked.

I hope that helps smile

Average of ratings: -
In reply to Samir Patel

Re: LDAP Sync Task Error - Existing temptables found when disposing database

by Samir Patel -

Thank you for the responses. It turns out the php or mysql settings for memory / timeout limits etc. were never the problem.

After trying different organizational units to sync (and finding that most of them in fact did work), I discovered that in the one which was not working, for one user, the samaccountname field in active directory had a trailing space after the name. Apparently this does not sync well with moodle ldap!

Average of ratings: -
In reply to Samir Patel

Re: LDAP Sync Task Error - Existing temptables found when disposing database

by jais taneja -

Hi all,

i am facing the same error except one line as:

... used 1694 dbqueries

... used 5.8489360809326 seconds

Scheduled task failed: LDAP users sync job (auth_ldap\task\sync_task),The given

username contains invalid characters

Potential coding error - existing temptables found when disposing database. Must

 be dropped!

i already add the two line in php.ini and also max packed allow in mysql but still got the same error so i am thinking that it is because moodle doesn't support special characters in username and in my AD there are special character in username.


can anyone please help me out with this issue and guide me correctly if i am doing something wrong.

Thanks in advance


Average of ratings: -
In reply to jais taneja

Re: LDAP Sync Task Error - Existing temptables found when disposing database

by Ken Task -
Picture of Particularly helpful Moodlers

Don't know that ldap sync uses site policies or even checks against site policies, but it's the only thing related that might be what you are seeking.

http://site/admin/settings.php?section=sitepolicies

Allow extended characters in usernames

which has the following as a comment:

Enable this setting to allow students to use any characters in their usernames (note this does not affect their actual names). The default is "false" which restricts usernames to be alphanumeric lowercase characters, underscore (_), hyphen (-), period (.) or at symbol (@)

Check the box and save.   Fingers crossed.  Then TIA (try it again!)

'spirit of sharing', Ken

Average of ratings:Useful (2)
In reply to Ken Task

Re: LDAP Sync Task Error - Existing temptables found when disposing database

by jais taneja -
Thank you ken for replying i'll check this out and let you know if it works.
Average of ratings: -
In reply to jais taneja

Re: LDAP Sync Task Error - Existing temptables found when disposing database

by Mustafa A. -

Hi Jais,


I had the same problem today. It turned out that one of the Active Directory users had a white space in her name.

I added the second line into the sync_users() function in auth/ldap/auth.php:

$user->username = trim(core_text::strtolower($user->username));
$user->username = str_replace(' ', '', $user->username);


Average of ratings: -
In reply to Mustafa A.

Re: LDAP Sync Task Error - Existing temptables found when disposing database

by jais taneja -

hey, 

i think so ken's ideas work in site admin i allowed username to use special characterand now my error has changed from above line to this one:


... used 6726 dbqueries

... used 17.934537172318 seconds

Scheduled task failed: LDAP users sync job (auth_ldap\task\sync_task),Error writ

ing to database

Potential coding error - existing temptables found when disposing database. Must

 be dropped!

and numbers of user added also increased as earlier i have used 1694 dbqueries and know its increases to 6726 .

But now i have other issue does anyone know what its is?


thanks ken & Mustafa for sharing smile

Average of ratings: -
In reply to jais taneja

Re: LDAP Sync Task Error - Existing temptables found when disposing database

by Ken Task -
Picture of Particularly helpful Moodlers

So turn on debugging and let's see what we get.

Got command line access?   One can run the task for syncing LDAP from command line.

Now before you try again, think I'd get a dump of current Moodle users into a spreadsheet of some sort so I might be able to compare what's in Moodle now with a view of LDAP search to compare.

The one thing that even the command line scripts don't tell you is the account that failed.  And, if I re-call, the importing is not alpha ... so one has a hard time of telling what account failed.

CLI script:

mysql -u [user] -p[password] -e "use [nameofmoodledb];select id,auth,username,password,firstname,lastname,email from mdl_user where auth='ldap'"> ldapaccounts.txt

Then cat ldapaccounts.txt to view.

'spirit of sharing', Ken


Average of ratings:Useful (2)
In reply to jais taneja

Re: LDAP Sync Task Error - Existing temptables found when disposing database

by Emma Richardson -
Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Plugin developers

Is there a reference to a user just before or after the error - normally that will indicate which user has an issue with their account.

Average of ratings:Useful (1)
In reply to Emma Richardson

Re: LDAP Sync Task Error - Existing temptables found when disposing database

by jais taneja -

hey,


yes ken i am running my script on command line and i am out of town for few days please be in touch i'll get back to you once i am back.


Thanks for helping me smile

Average of ratings: -
In reply to Emma Richardson

Re: LDAP Sync Task Error - Existing temptables found when disposing database

by Ken Task -
Picture of Particularly helpful Moodlers

Would be nice if it worked like that, but don't think it does cause that's where it errors ... won't accept data therefore/also  won't display whatever. :|

'spirit of sharing', Ken

Average of ratings:Useful (1)
In reply to Ken Task

Re: LDAP Sync Task Error - Existing temptables found when disposing database

by jais taneja -

hey ken,

its done the script is running successfully thanks a lot for help.

smile

Average of ratings: -
In reply to jais taneja

Re: LDAP Sync Task Error - Existing temptables found when disposing database

by Satish Patel -

Dear Ken,


I get "Error: Internal script failure" while I tried to log in with LDAP credential. Please help.

Average of ratings: -
In reply to Satish Patel

Re: LDAP Sync Task Error - Existing temptables found when disposing database

by Ken Task -
Picture of Particularly helpful Moodlers

@Satish ...

while this thread is about LDAP, it's specifically about LDAP sync which is not the same thing you are reporting.   Suggest posting your own question in a new thread of this forum first.

'spirit of sharing', Ken

Average of ratings: -