Bug Report: Issue with "referencefileid" Column During Tiles Format Course Restoration with Tilesphoto

[Ben Wu]

Description:

While restoring a Moodle Tiles Format course that has tile background photos, we observed an anomaly in the database. Specifically, a "0" is appended to the "referencefileid" column within the Files database table, which serves as a foreign key to the Files_Reference table. This has led to data integrity issues in Moodle instances.

Moodle: 4.0.9 and 4.1.4. Tiles plugin: 4.1.0.2 (2023030500). Database: both MySQL and PostgreSQL.

Steps to Replicate:

1. Create a new course with Tiles Format.
2. Upload a few (4) background photos to tiles.
3. Query the photos on the Files database table, their referencefileid are Null.
4. Perform a backup of the course.
5. Restore the course backup into a new or existing course.
6. Query the photos of the newly restored course on the Files database table. Note that the "referencefileid" column for the restored Tilesphoto record has a "0" appended.

Expected Behavior:

The "referencefileid" column of the restored course’s tilesphoto should correctly showing Null as the original course.

Actual Behavior:

A "0" is appended to the "referencefileid," leading to a database inconsistency issue.

We appreciate your attention to this issue. Thank you.

[Ben Wu]

We discovered this problem during a probe into a significant issue within our setup. This problem has converged with another bug associated with Moodle's remote repository, leading to extensive database table anomalies that have impacted multiple courses.

[Ben Wu]

I think the issue comes from the update_file_record function of the restore_format_tiles_plugin.class.php. It seems the fs->create_file_from_storedfile function is setting referencefileid to 0 instead of Null when creating the new file, despite it’s actually a ‘Null' in the oldfile. I don’t know whether it’s a good practice but can we explicitly update referencefileid to null if the old file had it as null, before line 372:

if ($oldfile->get_referencefileid() === null) { $DB->set_field('files', 'referencefileid', null, ['id' => $newfile->get_id()]); }

‌

[Ben Wu]

I think the problem probably lies in the Moodle’s create_file_from_storedfile function in the lib/filestorage/file_storage.php here:

if ($key == 'referencefileid' or $key == 'referencelastsync') { $value = clean_param($value, PARAM_INT);}

Before this code block, the $newrecordobject’s referencefileid is empty. But after this block, the $newrecordobject’s referencefileid is 0 now.

What is disorienting is that at the end of the function, when return $this->get_file_instance($newrecord), the returned object’s referencefileid is empty. However, if we print $newrecord or $DB->get_record_sql("SELECT * FROM {files} WHERE pathnamehash = ?", array($newrecord->pathnamehash)), we will see the referencefileid is 0, which means, regardless what was returned by this function, the 0 value has been added to the database.

‌

[David Watson]

Thanks for this detail Ben.

I had a look at it and I think you are right so I opened an issue in the core tracker to fix the issue at source: https://tracker.moodle.org/browse/MDL-80938

In the meantime I will add something into the 4.3 beta to handle it along the lines of the workaround you proposed. The 4.3 beta code is planned to be rolled out to 4.1 and 4.2 in the next few weeks

[David Watson]

Fixes issue #165 referencefileid

→ <<cset 40f1a05ee2d6>>

[Ben Wu]

Hi David,

Thank you very much for investigating and taking action to solve the issue.

Ben