Forums | Mahara Community

Support /
Serious corruption in Artefacts Table


Mike Lee's profile picture
Posts: 5

05 July 2018, 11:58 PM

Hi,

 

I'm posting here in the hope that someone can help with an issue that has arisen in our Mahara system.


A few weeks ago, users started reporting that Group pages containing attachments, which have been set up as templates to allow students to copy them were not working correctly. Specifically, when they attempted to download the attached template documents they were getting errors like

 

Not found

The page you are looking for could not be found.

Artefact with id 23596273627 not found
 
We have investigated this fairly extensively and 23596273627 is not a valid Artefact ID and is not present in the mahara,artefact as an ID.
 
However it IS present in (corrupted) entries in the path field e.g. 
 
/228419/228419/23596273627/221551
 
The path field appear to have been updated en masse such that a huge number of records now have 228419 as their highest level parent even though it's just the folder of one particular student. Many path fields do not end in the artefact ID of the record in question (which they should as I understand it) and it appears that as in the case of the example above, one of the delimiting forward slashes has been stripped out of the path field ( because 235962 IS a valid ArtefactID, as is 73627).
 
A separate but potentially related issue is that thousands of Artefact IDs in the artefacts table do not have corresponding files in the filestore (where artefacttype is one of "file", "image", "video", "audio", "archive") but I'm hoping this is just because records are retained in the Artefact table even if the user deletes them from Mahara and hence (presumably) the filestore? That said there aren't enough records with the deleted flag set 1 in artefact_log to account for the the discrepancy.
 
My best guess as to the cause of this is that the Update statement in artefact/lib.php in the function update_hierarchy_path has *somehow*, because of something very specific an individual users has done (e.g. special characters) led to the following code
 
$length = strlen($oldparent->path) + 1;
$params = array($newparent->path, $length, $this->path, db_like_escape("{$this->path}/") . '%');
$sql = "UPDATE {artefact} SET path = ? || SUBSTR(path, ?) WHERE (path = ? OR path LIKE ? )";

execute_sql($sql, $params);

// Make sure that the value of a new path is set.
$this->path = $newparent->path . substr($this->path, $length - 1);
updating and thereby corrupting a large number of records simultaneously. The fact that there's LIKE statement in the WHERE clause is of particular relevance I think.
 
For the record we are on Mahara 17.04.3 and have not customized ANY of core code. We a have a test system which was populated with a historical copy of the production data towards the end of last year. This was upgraded just before our production system was in September last year and the corruption issue is not present in the artefacts table so the problem appears to have been created by subsequent use of the system, rather than by the upgrade process.
 
I have searched the forum for similar issues but the only one I could find that seemed like it might be vaguely related was 8308 (apparently I'm not allowed to post links)

 

The problem we're experiencing seems much more fundamental though.
 
In principle we can develop a SQL script toreconstruct and correct the path field from the parent and id fields by traversing the artefact hierarchy but as some of the files with the corresponding artefact IDs do not appear to be present in the filestore, this won't by itself resolve the problem.
 
Has anyone else had similar issues? Can anyone help please?
 
Regards,
 
Mike
Robert Lyon's profile picture
Posts: 437

09 July 2018, 1:40 PM

Hi Mike,

This is not a problem I've heard of before but I don't think special chars would have caused the issue as the path only involves the integers from the artefact id fields plus the '/' char.

It might have been caused by a blank 'path' field for an artefact.

In htdocs/lib/db/upgrade.php the lines 3300 -> 3368 have the code that made the 'path' data in the first place.

You could try re-running the population of path information by doing the following:

1) Make a copy of the database first to run the test against.

2) Edit the htdocs/local/upgrade.php file and add those lines wrapped in:

    if ($oldversion < 2018070900) {
        require_once(get_config('docroot') . 'artefact/lib.php');

        // Fill the new field with path data.
        ... the lines 3300 -> 3368 from htdocs/lib/db/upgrade.php ...
    }

3) Update htdocs/local/version.php to set the $config->version = 2018070900;
   to match the number in the htdocs/local/upgrade.php

4) You then should be able to run those lines via a link on your site's Admin -> Dashboard page.

If all works fine on database copy then run again against production site.

If it recreates bad paths then could you let me know

Cheers

Robert

Mike Lee's profile picture
Posts: 5

10 July 2018, 1:42 AM

Hi Robert,

Thanks very much for your post. I've been continuing to investigate this myself over the last few days and had also come to the conclusion that the most likely cause was a null path parameter.

I took the update query UPDATE {artefact} SETpath=?||SUBSTR(path,?) WHERE (path=? OR path LIKE ?) from the code and ran similar select statements with a null value for path and as anticipated, they return all the records in the table except for two records in the table that actually have NULL values for path (not sure how or why this happened).

In addition, I've noticed that ALL  the records that were created AFTER the 24th of May do seem to have valid paths etc, and all the records before this have an invalid path, all pointing to the same parent. This certainly lends weight to the theory that a bug in Mahara Core somehow led to every record on the system being updated by the UPDATE statement when a particular user did *something* unusual, probably on or around May 24th.

Thanks for your suggested fix. I was planning to write a bespoke SQL script but will have a look at doing what you've suggested instead as potentially it will save a lot of time.

I'll let you know how we get on.

Regards,

Mike

 

Robert Lyon's profile picture
Posts: 437

10 July 2018, 9:03 AM

Hi Mike

It might be worthwhile to upgrade or apply this patch

https://reviews.mahara.org/#/c/8492/3

which restricts the updating of path

Cheers

Robert

Mike Lee's profile picture
Posts: 5

10 July 2018, 9:34 PM

Hello again Robert,

Thanks for pointing me in the direction of the patch. I went through the (rather involved) process of creating an OpenID account and a public key etc but even having done that I get the mesage

Code Review - Error

The page you requested was not found, or you do not have permission to view this page

If click on Go Anonymous instead, I just get a blank page with no code on it.

Could you perhaps please point to the the Bug number so I can hopefully find it in Source Tree?

Anyway, the immediate task is to proceed with the Artefacts Path fix as discussed but I did want to just check whether or not we should be concerned that thousands of Artefact IDs in the artefacts table do not appear to have corresponding files in the filestore (where artefacttype is one of "file", "image", "video", "audio", "archive")?

I ask because I want to avoid the risk that we do all the work to fix the Artefacts table path but then find that our Mahara system is still broken because of the discrepancies between the IDs in the table and the files in the filestore.

Regards,

Mike

 

 

 

 

 

 

Mike Lee's profile picture
Posts: 5

10 July 2018, 11:12 PM

I've looked through the commits. Looks like Bug 1724603 - Adding 'owner' column to update for update_hierarchy_path() as per

https://bugs.launchpad.net/mahara/+bug/1724603

Is that correct?

Thanks,

Mike

 

 

Robert Lyon's profile picture
Posts: 437

11 July 2018, 7:37 AM

Hi Mike,

Ah sorry about that - just after I replied our gerrit server was to be upgraded and so is temporarily not working

but yes that is the correct bug report

the patch change can also be seen merged into the mahara repo at

https://git.mahara.org/mahara/mahara/commit/4b1f57b90374b3cc772b1560e1d3d05184716128

Cheers

Robert

Mike Lee's profile picture
Posts: 5

11 July 2018, 10:06 PM

Thanks again for getting back to me. I can see the fix on the link you sent. Looks like it was mainly a performance improvement enhancement but it should certainly reduce the risk of the problem reoccurring once we've fixed the data.

Do you have any comment whether or not we should be concerned that thousands of Artefact IDs in the artefacts table do not appear to have corresponding files in the filestore (where artefacttype is one of "file", "image", "video", "audio", "archive")?

I'm just concerned about the risk that we fix the data issues in the artefacts table but still find that our users are having similar problems due to the discrepancies with the filestore highlighted above.

 

 

Robert Lyon's profile picture
Posts: 437

12 July 2018, 10:37 AM

Hi Mike

The files saved into the dataroot (datastore) are done by the fileid value and not the artefact id value - though in most cases they are the same  value.

To double check if files are missing from dataroot you can get a list of the file name with path relative to dataroot path by doing the following query:

SELECT a.id AS artefact_id, aff.fileid AS file_id,
  CASE WHEN a.artefacttype = 'profileicon'
    THEN CONCAT('artefact/file/profileicons/originals/', (aff.fileid % 256), '/', aff.fileid)
    ELSE CONCAT('artefact/file/originals/', (aff.fileid % 256), '/', aff.fileid)
  END AS dataroot_path
FROM artefact a
JOIN artefact_file_files aff ON aff.artefact = a.id
ORDER BY a.id ASC;

If the files are indeed missing - this could have been due to:

1) a server admin deleting files from dataroot directly (not likely)

2) on deleting a file in mahara, via artefact/file.lib.php delete() function the unlinking of file from dataroot worked but the subsequent delete records from database failed, possibly due to bad path info (more likely)

Cheers

Robert

 

9 results