local_o365\task\usersync delta-sync only recieves around 20 User and full-sync fails

[codeislandboy]

Moodle-Version: 4.1.2 (Build: 20230313) Plugin-Version: 2022112805

I have an Azure-AD with 1500 users which should be synchronized via local_o365\task\usersync.

When running a full sync, the task always runs into an error: "Scheduled task failed: sync users with Azure AD. (local_o365\task\usersync),Error reading database. !!! Error reading database !!!"

If delta sync is used instead, users will be imported and created correctly. However, only about 20 users are received each day: Execute scheduled task: synchronize users with Azure AD. (local_o365\task\usersync] ... started 07:50:07. Current memory use 59.4 MB. ...... Starting sync ...... No skiptoken (delta) stored. ...... Using deltatoken. ...... Using delta sync. ...... Contacting Azure AD... ...... Got response from Azure AD ...... Storing deltatoken ...... Clearing skiptoken (delta) (none received) ...... 18 users received. Syncing... ...... 18 users in chunk. Syncing...

I have already tried to run the task more often - without success.

[weilai-irl]

Hi @codeislandboy

Please turn on full debugging, i.e. set "Debug messages" to "DEVELOPER: extra Moodle debug messages for developers", and run full sync again. It should show additional details of the DB reading error, e.g. what query it's trying to run when the error happens, in which line of while file etc.

Delta sync only receives users whose data in Azure AD have changed since the last delta sync, so all delta sync runs, apart from the initial one, will typically receive less users. This is expected.

Regards, Lai

[codeislandboy]

Hi @weilai-irl Thanks for the clarification regarding delta-sync. I have changed the debug mode to DEVELOPER and started a new full-sync. The following error is displayed:

Scheduled task failed: Synchronisieren Sie Benutzer mit Azure AD. (local_o365\task\usersync),Fehler beim Lesen der Datenbank Debug info: MySQL server has gone away SELECT * FROM wnvj_auth_oidc_token WHERE userid = ? AND oidcusername = ? [array ( 0 => '5', 1 => '[USERNAME]', )] Backtrace:

• line 293 of /lib/dml/moodle_read_slave_trait.php: call to moodle_database->query_end()
• line 1278 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->query_end()
• line 1508 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->get_records_sql()
• line 1459 of /lib/dml/moodle_database.php: call to moodle_database->get_records_select()
• line 1327 of /local/o365/classes/feature/usersync/main.php: call to moodle_database->get_records()
• line 1162 of /local/o365/classes/feature/usersync/main.php: call to local_o365\feature\usersync\main->sync_existing_user()
• line 292 of /local/o365/classes/task/usersync.php: call to local_o365\feature\usersync\main->sync_users()
• line 196 of /local/o365/classes/task/usersync.php: call to local_o365\task\usersync->sync_users()
• line 259 of /lib/cronlib.php: call to local_o365\task\usersync->execute()
• line 120 of /lib/cronlib.php: call to cron_run_inner_scheduled_task()
• line 73 of /lib/cronlib.php: call to cron_run_scheduled_tasks()
• line 81 of /admin/cron.php: call to cron_run()

!!! Fehler beim Lesen der Datenbank !!!

Debug info: MySQL server has gone away SELECT RELEASE_LOCK(?) AS unlocked [array ( 0 => '[11111111111111111111111]', )] Error code: dmlreadexception Stack trace: * line 494 of /lib/dml/moodle_database.php: dml_read_exception thrown

• line 293 of /lib/dml/moodle_read_slave_trait.php: call to moodle_database->query_end()
• line 1278 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->query_end()
• line 1679 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->get_records_sql()
• line 161 of /lib/classes/lock/mysql_lock_factory.php: call to moodle_database->get_record_sql()
• line 120 of /lib/classes/lock/lock.php: call to core\lock\mysql_lock_factory->release_lock()
• line 130 of /lib/cronlib.php: call to core\lock\lock->release()
• line 73 of /lib/cronlib.php: call to cron_run_scheduled_tasks()
• line 81 of /admin/cron.php: call to cron_run()

[weilai-irl]

Hi @codeislandboy

Please refer to MySQL documentation at https://dev.mysql.com/doc/refman/8.0/en/gone-away.html for this error. The most probable cause of this error is MySQL server timeout. Please review the timeout setting.

If you have a lot of users in your Azure AD, running full sync can take long time to finish. The biggest tenant that we (my employer, Enovation Solution, which is a Moodle partner and Microsoft Education Specialist partner) actively manage and support have close to 100K users, and I have seen github community users with over 250K users. The exact time it takes to run the user sync task depend on a few factors:

• Number of users,
• Field mapping settings,
• Web server performance,
• DB server performance,
• Connection status between web and DB server.

Since the error is not introduced by the plugin code itself, you will need to investigate into this I'm afraid.

Regards, Lai

[jwcatau]

Hi @codeislandboy and @weilai-irl,

This might not be the same root cause, but we've discovered an issue with full user sync as well, in Issue #2323 I just logged. It was PostgreSQL not MySQL, but worth looking at, as you might be getting a database timeout at the same point?

I've also created a draft pull request for it, but if the change works for you as well, maybe it resolves two issues and tests it's compatibility with MySQL as well as PostgreSQL.

Good luck!

[jwcatau]

Looking a bit further, I also saw Issue #2273 which mentions Issue #2274 as a fix; almost identical to my Issue #2323.

[RayCoSector]

Hi all, to fix this issue do what is mentioned here on my blog. It's happened to a few of our larger customers when they run a full sync or when it hits a Error in delta usersync: Error in API call. (I'm suspecting caused by a duplicate email) then the sync does not use the deltatoken.

I imagine the value of your max_allowed_packet will be set to the default value of 16mb, set this to 256mb and the sync will complete without the database going away.

Regards, Ray.