Moodle

Also affects Moodle 2.0.3. Table seems to grow about 5Mb a day after each run of automated backups.

Thanks for reporting this.

If you find out anything more, please add information here.

I have just duplicated the backup_controllers table (just in case) and truncated the original. Obviously this won't save space but I'll see what happens after the next set of backups, which I have moved forward to 9pm (BST) this evening.

I'm not sure what other info or details I can provide, but if you suggest them, I will provide them.

Closer inspection shows that that the base64-encoded serialised array held in backup_controllers.controller is the main culprit, easily reaching 1Mb of text in places. I'm not sure if that's relevant or not though. Can supply one if requested (maybe privately, not sure if there's sensitive data in there or not).

Edited to add: the worst offender is 3.1Mb of data in the controllers field, and we have lots over 2Mb too.

I can't actually run the backups at the moment due to MDL-28180. As soon as I have either succeeded or given up on trying to fix the data, I'll let you know.

Since truncating the backup_controllers table last week, we now have 271 entries in it, mostly with the operation set to 'backup', and the table itself has already reached 90Mb. In one row, the data in the controller column was just 169Kb, much lower then the previously noted 2-3Mb, but I am still having trouble base64-decoding and unserialising it, just to see what it is.

What I can see is a lot of "RECURSION" (without the quotes, but wish asterisks) in the resultant unserialised array. I'm not sure if this is a fault with how I'm working with it, or if this is what the data contains, but it seems a bit odd.

Our backups are now working again. The backup_controllers table now has 662 entries in it and is now 224, making up around one third of our whole database's storage requirement.

I don't think I can add any more detail to this report unless there is something specific you want me to locate and report on.

+1ed, this is seems to be very odd behaviour. Truncating the table seems to have no adverse effects.

Today's stats: 1,241 rows, 420Mb in size which is roughly half of the Moodle database.

Running Moodle 2.1.1+ (Build: 20110916). backup_controller table is 273 MB out of 350 MB total (78%). Run backups x3 per week and only retain most recent version.

This needs to be resolved (more than a minor issue).

Also, could someone please confirm that truncating the BACKUP_CONTROLLER table will not cause any adverse effects. I see that a couple of people have done this but would like someone to officially confirm that this workaround is OK before I do it.

Hi Robert.

I have truncated the backup_controllers table once, and backups have continued after without any apparent problems. If you are concerned that this might cause problems somehow or you just want to play it safe, use a program like PHPMyAdmin to copy the table (to, for example, backup_controllers_backup) and then truncate the original. This way you can restore it pretty quick if you get problems (I haven't).

I hope this is official enough.

Edited to add: todays stats are 2,260 rows, 800Mb, or around two thirds of the whole database.

Performed truncate of backup_controller table. All appears to be working with daily backups after two days...so I would say that workaround is successful...but wish it was not necessary.

Our backup_controllers table just topped 1Gb so have done yet another truncate. If removing the data from this table is causing no problems, why are we keeping it in the first place? Can't the automatic course backups perform a truncate on the successful completion of the backups?

Hi,

Since my backup_controller table also uses half of the space used for the whole db, I had a look at the table data. As far as I could see there are some interesting points:
Most of the entities are automated course backups with the status 700 (STATUS_AWAITING), execution time 0 (immediately), and the purpose 50 (MODE_AUTOMATED). If one orders the table entries by itemid and id, you will notice due to creation time and modification time of entities with the same itemid (which is the course id), that every week two entries are generated with almost one day difference between the first and the second entry.
As the backups could be found in the backup folder, I assume that the controllers are saved in the DB and won't be deleted after they are finished. Compare backup_cron_helper.class.php: function launch_automated_backup. The status is set to STATUS_AWAITING which results in saving the controller to DB (/backup/controller/backup_controller.class.php: function set_status). To my understanding, the problem is that a controller is saved when setting the status to ‘awaiting’, but it won’t be deleted when changing the status to finished.

Hopes that helps while solving this issue.

PS: deleting entries that are older than a few days can’t have any negative effect on the platform.

+1 for a fix on this.

This is rediculous !!
This should be considered a MAJOR issue - on our systems this is causing havoc.
Please fix this asap.

Hi Bart. As the OP I can change the initial report (for example, to major instead of minor) however I'd like to know what havoc this is causing on your Moodle installation. I know it's a problem, but I've not considered it major (just annoying) as we have a large capacity disk on our database and would take quite some time to fill.

If you can add detail to this report, showing in what ways this error is inconveniencing you, it is more likely to receive attention.

Paul.

It's an issue when migrating databases.

Hi Ray.

Is the issue only one of size? If so, it can easily be remedied by truncating the table immediately before migration. If there's something else, please detail here, where someone may be able to assist, and the devs will become aware of it.

Paul.

Yes.

We can empty the table beforehand but that shouldn't really be necessary.

I have run into this again with an update. Can you increase the priority?

As per requests, priority has been increased to major.

We're not running 2.2 in production yet so I cannot tell if this is still an issue. If anyone reading this is using 2.2 (or any version of 2.1 not listed above) please note it in the comments: the more people have the issue, the more traction this issue will gain and the quicker a solution will happen.

Recently we've upgraded our Moodle installations to "Moodle 2.2.1+ (Build: 20120119)" and we see the issue. On our university we have several Moodle installations, one per department. Some of them are actively used, some are in the works.

On one of the servers there are over 1500 empty courses (reflecting organizational structure), ready to be filled with content. And "backup_controllers" table already takes 13GB of hard drive space. On another server there are 253 courses, and "backup_controllers" is near 10GB. This is a big deal as our servers have disks of 80GB size, and we are nearing a couple of GB's of free space on some of them.

My quick search resulted in following:

• backup_controllers and backup_logs tables are related in DB schema, see: [$MOODLE]/lib/db/upgrade.php
  Important lines: "$table = new xmldb_table('backup_logs');" and "$table->add_key('backupid', XMLDB_KEY_FOREIGN, array('backupid'), 'backup_controllers', array('backupid'));"
• Records from both log and backup_controllers tables are removed in cron, see: [$MOODLE]/lib/cronlib.php
  Important lines: "// Delete old logs to save space (this might need a timer to slow it down...)" and "// Delete old backup_controllers and logs"
• The above removal is controlled by CFG->loglifetime setting. The default value of "loglifetime" setting is "Never remove logs".
• In case of our university this "loglifetime" setting is left default on our Moodle servers, and automatic course backup is turned on.

For us "never remove logs" is required in our situation, because students may repeat courses they've failed on later semesters/years of their studies, if their ECTS points allow them. It's useful to have long log of their history on courses, to see all their previous actions, not only grades.

I'm looking forward to help resolve this issue.

Wiktor Wandachowicz

Paul, we have Moodle 2.1.3 and have the same issue

We're on 2.1.3 and experiencing this issue. We are also a Moodle Partner and while this could be a problem for anybody, you can imagine how much more massive this problem becomes for us when we're hosting millions of courses across a large number of sites. Like other people have already said, this one table is over 75% of the entire database size in many cases. Databases are growing faster than anticipated, which could compromise site performance and reliability if left unchecked. We are truncating tables to mitigate this but doing so is only acceptable for the very short term. This issue needs a permanent resolution.

+1

Bumping this issue.

We're on 2.2, and we've definitely got this issue!

Just truncated 10.4Gb, which was 86% of the whole database.

Update: this is in progress and I wanted to have it ready to integration for today. But right now I've decided to target it for next week, to complete it properly. It's being fixed by:

1) the creation of one new setting, 100% separated from $CFG->loglifetime (that seems that a lot of sites have it set for unlimited/big periods), to control the deletion of backup_controllers and backup_logs. Will default to 30 days and it will explain clearly that it's not recommended to use BIG periods at all.

2) avoid saving the controllers fullinfo (serialized object) once the configuration phase is done. That's already implemented but have found some cases where it may be bypassed leading to BIG objects being inserted.

3) after each successful backup, delete the serialized object from DB completely. It won't be needed anymore. It implies that, for failed backups, the serialized info will be there (can be useful to reproduce a bug) but, will be small (because of 2) and only will persist for 30 days (because of 1).

And that is it. Once again, sorry for the delay and thanks for your patience. Ciao

No problem.

Small delay is nothing compared to the prospect of a big solution.

Adding our +1, this is making some very big DBs for us too.

Sending to integration (21, 22 and master).

It includes 3 commits to separate the changes:

1) The new setting to decide the ttl of backup logs (separating them from the main $CFG->loglifetime).

2) Changes in backup controller to clean the "controller" column after finishing execution without errors.

3) Changes in restore controller (100% copy of previous) to clean the column after finishing execution without errors.

It applies to all operations (normal backups, import, duplicate, automated...).

Also, on purpose, it's not allowed to configure the site to keep backup logs for > 1 year.

And the only drawback I can imagine is that the 1st execution of the cleaning in cron can consume a lot of time due to the number of records to delete, specially in sites running mysql and having the general $CFG->loglifetime set to 0 (never delete logs).

That's all. Ciao

Thanks, this has been integrated now. Lets have some great testing for this issue!

Note: One extra commit has been added (and integrated) to each branch in order to fix some E_STRICT messages in tests were some "mock" controller classes were left behind the changes in core. Tidying testing instructions to run unit tests.

I'm taking myself off testing this issue as I'm not going to get to it today.

Hi Eloy,
Results for master
All works as expected except step 18
In step 18, I had 9 new entries, where as I have only 8 courses.
PHP unit tests ran as expected

Results under 21
All works as expected except step 18
In step 18, I had 5 new entries, where as I have only 4 courses.
Output of simpletest

1/1 test cases complete: 6 passes, 0 fails and 0 exceptions.

Please suggest if the issue @step18 is worth failing this test?
Thanks

The extra entry was because of Front page which is treated as course.
Working in all versions as expected (21 22 and master)
Passing
Thanks

Thanks Ankit!

This has been near becoming rejected, because it's not the best code you are able to produce.

But, luckily, at the end, it has landed and has been spread to all repos out there.

Many thanks and, don't forget it, keep improving your skills, you can!

Closing, ciao