Scheduled task failed: Sync users with Azure AD. (local_o365\task\usersync)

[maisnada]

Hi,

I have the following error when running sync. Could you help? Tks

Moodle 4.0.3

Oracle Database 11g Release 11.2.0.4.0 - 64bit

Execute scheduled task: Baixar os tipos de conteúdo H5P disponíveis em h5p.org (core\task\h5p_get_content_types_task)
... started 10:15:03. Current memory use 3.1 Mb.
Não foi possível obter informações sobre os tipos de conteúdo H5P disponíveis. Falha na conexão com o repositório H5P
... used 1 dbqueries
... used 1.2583639621735 seconds
Scheduled task complete: Baixar os tipos de conteúdo H5P disponíveis em h5p.org (core\task\h5p_get_content_types_task)
Execute scheduled task: Sincronizar usuários com o AD do Azure (local_o365\task\usersync)
... started 10:15:04. Current memory use 5.9 Mb.
...... Starting sync
...... No skiptoken (full) stored.
...... Forcing full sync.
...... Contacting Azure AD...
...... Got response from Azure AD
...... Clearing skiptoken (full) (none received)
...... 62236 users received. Syncing...
...... 10000 users in chunk. Syncing...
... used 4 dbqueries
... used 315.6729991436 seconds
Scheduled task failed: Sincronizar usuários com o AD do Azure (local_o365\task\usersync),Erro ao ler a base de dados (ORA-00905: missing keyword
SELECT COUNT(a.id)
FROM m_user a
JOIN m_user b
WHERE a.email LIKE b.email
AND a.id < b.id
AND a.deleted = 0
AND b.deleted = 0
AND a.suspended = 0
AND b.suspended = 0
[array (
)])
Debug info:
ORA-00905: missing keyword
SELECT COUNT(a.id)
FROM m_user a
JOIN m_user b
WHERE a.email LIKE b.email
AND a.id < b.id
AND a.deleted = 0
AND b.deleted = 0
AND a.suspended = 0
AND b.suspended = 0
[array (
)]
Backtrace:

• line 277 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->query_end()
• line 1171 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()
• line 1671 of /lib/dml/moodle_database.php: call to oci_native_moodle_database->get_records_sql()
• line 1101 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->get_record_sql()
• line 1744 of /lib/dml/moodle_database.php: call to oci_native_moodle_database->get_record_sql()
• line 1954 of /lib/dml/moodle_database.php: call to moodle_database->get_field_sql()
• line 465 of /local/o365/lib.php: call to moodle_database->count_records_sql()
• line 982 of /local/o365/classes/feature/usersync/main.php: call to local_o365_users_with_same_email_exist()
• 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 253 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()

Ran 0 adhoc tasks found at Fri, 02 Sep 2022 10:15:02 -0300
Cron script completed correctly
Cron completed at 10:20:20. Memory used 6.2 Mb.
Execution took 317.323086 seconds

[weilai-irl]

Hi @maisnada,

The error message suggests Oracle database complains from the query. The same query runs fine in MySQL (our primary development DB) and doesn't complain in PostgreSQL, so this is a case where different DBMS handles the query differently.

I know this query in particular will be updated in the next release as part of the fix to another issue, so it should not complain again. You can upgrade your plugins after the next release, or if you are eager, feel free to apply the PR that I'm going to link to this issue and see if it works.

Regards,
Lai

[maisnada]

Lai, thanks very much for the help ;D