Very large database table mdl_block_xp_filters

[elz-hwr]

Hey there,

we are using the block_xp Plugin for Moodle 4.1.9 in version 3.15.2 and MariaDB 10.11.6.0.12.1.

Our "mdl_block_xp_filters" table has become very large. It now comprises around 12 GB and 44 million entries. On closer inspection, we have seen that there are also entries for courses in which the xp block is not even activated. The entries vary between approx. 5 and > 40 per course. It seems that the table is not being cleaned up at all. What can we do to clean up the data? Is there a regular job for this?

Regards,
Doreen (Berlin School of Economics and Law)

[FMCorz]

Hi @elz-hwr,

Thank you for your report. There used to be an issue #154 where the filters were duplicated when a course was restored as merge. We have addressed it in the past, but you may still find a lot of data for those.

The filters are not logs, they are rules determining how points are collected by users as they perform actions. As such, there aren't any task to remove them as they are not meant to be deleted.

You may find it useful to identify where XP is used in courses:

SELECT courseid FROM block_xp_config WHERE enabled = 1;
SELECT DISTINCT courseid from block_xp WHERE xp > 0;
SELECT DISTINCT courseid from block_xp_log WHERE time > 1704067200;
-- Where 1704067200 is Jan 1 2024, and returns courses with activity since then

Based on the above, you could blindly delete the filters directly from the database for the course IDs that aren't found in the above. After you remove the entries, you would have to purge block_xp's cache filters, or your site's cache.

Let me know how you go, happy to help. Meanwhile, can you confirm whether you are/were using backup restores as merge?

[FMCorz]

On another note, if you are using XP+ (local_xp) the query on block_xp_log should be adapted to local_xp_log.

[elz-hwr]

Thank you very much for your help. We have cleaned up the table with your query and then removed duplicates (we had up to 14 million entries for one course).

We often work with rate imports/restore, which is practically our daily business. This has probably multiplied exponentially over the years (we don't have xp+).

Here is a summary of our findings:
-if the levelup block is created again in a course where the entries in mdl_block_xp_filter have already been deleted, the standard rules are NOT pulled again and recorded as entries in the table; the rules then remain empty and must be recorded manually for the course (no rules = no points are recorded = plugin records no progress)
-when duplicating a course with existing rules, only these rules are created for the new course, but not the standard rules as well
-if course content is imported into a new course with an activated Level Up block, the standard rules (= entries in the table) are supplemented by all rules of the source course (even if they are identical)
-if course content is imported into a new course without an activated Level Up block (default setting), only the rules of the source course are adopted; this corresponds to course duplication.

[FMCorz]

Brilliant, thank you so much for all the detailed test results!