error writing to database

[koenr]

We get a dmlwriteexeption with this error message.

Scheduled task failed: Synchroniseer site-groepen met een externe databank (tool_cohortdatabase\task\sync),Fout bij het schrijven van de databank
Debug info:
You can't specify target table 'mdl_cohort' for update in FROM clause
DELETE FROM mdl_cohort
                 WHERE id in (SELECT c.id
                                FROM mdl_cohort c
                           LEFT JOIN mdl_cohort_members cm ON cm.cohortid = c.id
                               WHERE component = 'tool_cohortdatabase' AND cm.id is null
                                     AND c.id NOT IN (select customint1 from mdl_enrol where enrol = 'cohort'))
[array (
)]
Backtrace:
* line 291 of /lib/dml/moodle_read_slave_trait.php: call to moodle_database->query_end()
* line 1167 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->query_end()
* line 615 of /admin/tool/cohortdatabase/classes/sync.php: call to mysqli_native_moodle_database->execute()
* line 386 of /admin/tool/cohortdatabase/classes/sync.php: call to tool_cohortdatabase_sync->cleanup()
* line 52 of /admin/tool/cohortdatabase/classes/task/sync.php: call to tool_cohortdatabase_sync->sync()
* line 253 of /lib/cronlib.php: call to tool_cohortdatabase\task\sync->execute()
* line 167 of /admin/cli/scheduled_task.php: call to cron_run_inner_scheduled_task() 

According to https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause

The query should be something like (look at line 3)

DELETE FROM mdl_cohort
WHERE id in (SELECT c.id
FROM (SELECT * FROM mdl_cohort) AS c
LEFT JOIN mdl_cohort_members cm ON cm.cohortid = c.id
WHERE component = 'tool_cohortdatabase' AND cm.id is null
AND c.id NOT IN (select customint1 from mdl_enrol where enrol = 'cohort'));

which seems to work if executed directly on the DB.

[danmarsden]

thanks @koenr - feel free to submit a PR with a patch - we've obviously only tested that on postgres! - thanks.

[koenr]

Patch only tested on mysql ;-)

[danmarsden]

thanks @koenr looks good to me - merged in, thanks for the PR! :-)