Description
While testing Moodle 4.1 compatibility, I came across the following issue when running on PostgreSQL 14. I was unable to reproduce it on MySQL. Steps to reproduce:
- Create a new site and install datacleaner
- Visit Site Administration > Development > Make test course and create a test course
- Visit Site Administration > Plugins > Local Plugins > Data cleaner and enable all cleaners. Specifically, I believe you need the course, user and grades cleaners enabled.
- Satisfy the safeguards and run
php admin/local/datacleaner/cli/clean.php --run - When running the grades cleaner, the following error is encountered:
== Running grades cleaner ==
Removing grades 0% (0/2)
!!! Error writing to database !!!
!! ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "mdl_competency_usercomp" references "mdl_grade_grades".
HINT: Truncate table "mdl_competency_usercomp" at the same time, or use TRUNCATE ... CASCADE.
TRUNCATE TABLE mdl_grade_grades
[array (
)]
Error code: dmlwriteexception !!
!! Stack trace: * line 497 of /lib/dml/moodle_database.php: dml_write_exception thrown
* line 293 of /lib/dml/moodle_read_slave_trait.php: call to moodle_database->query_end()
* line 341 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->read_slave_query_end()
* line 846 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end()
* line 2029 of /lib/dml/moodle_database.php: call to pgsql_native_moodle_database->execute()
* line 46 of /local/datacleaner/cleaner/grades/classes/clean.php: call to moodle_database->delete_records()
* line 160 of /local/datacleaner/cli/clean.php: call to cleaner_grades\clean::execute()
!!
Running with --verbose shows that a foreign key constraint is added from mdl_competency_usercomp.grade to mdl_grade_grades.id. This is also applied to some other competency tables. Because all records are being deleted from the grades table, this is performed with a TRUNCATE which doesn't trigger the ON DELETE CASCADE defined in the foreign key constraint.
It would be possible to avoid this by passing an empty array to the $DB->delete_records() call to force it to use DELETE rather than TRUNCATE. However, I did a test when mdl_competency_usercomp was populated with some data and the constraint was not created, because it correctly detected that this isn't actually a foreign key. The grade field stores an actual grade value, not a reference to a mdl_grade_grades record.
This appears to be a flaw in the logic that decides whether a constraint to be added to a field, as it treats no data as matching data. Perhaps to avoid cases like this we could have a defined list of fields in schema_add_cascade_delete that should never have a constraint added?