Max_allowed_packets & Backup/Restore/Import cannot write to Database

Max_allowed_packets & Backup/Restore/Import cannot write to Database

by Jeff White -
Number of replies: 23

I've read through many people's posts receiving the same error I'm receiving: max_allowed_packets exceeded for my attempts to restore a course. 

I followed the advice and increased the value, incrementally up finally all the way to 1000M (a gig). No good at any stop. My overall backup file is only 7.7MB. 

So knowing the value is not really the issue, what else might trigger that particular message? We cannot back up (make a copy) of any course--nor restore a course or import a course. Each tells us that we cannot write to the db. However, quizzes, completions, and feedback are all being stored in the DB for active courses. So it's tied somehow to backing up or restoring a (model) past backup. 

When I click "restore" and select a backup.mbz file, I see that the content is there (on step 2 of restoration), but I get the Error msg after that. 

I've tried several things: I removed any users from one backup, hoping that it was tied to duplicate accounts; I also (as said above), increased the max_allowed_packets size far, far beyond what is reasonable. 

Anyone have a suggestion for other things to look into?

Average of ratings: -
In reply to Jeff White

Re: Max_allowed_packets & Backup/Restore/Import cannot write to Database

by Ken Task -
Picture of Particularly helpful Moodlers

I know this is gonna sound 'doh!', but ... did you restart the MySQL service after increasing max_packets_allowed?  I get in a hurry to try tweaks and fixes sometimes and forget to do that.  The other item that I've ended up tweaking for restores one time was number of open files.

'spirit of sharing', Ken

Average of ratings: -
In reply to Ken Task

Re: Max_allowed_packets & Backup/Restore/Import cannot write to Database

by Jeff White -

Yessir. I did a restart each time.

I am at a loss with this problem. We recently "discovered" a group in our company using Moodle when they came to us for IT help. So I've been trying to work with them and to learn this along the way.

We had a backup which worked 1 time to restore it out as a new class. So I didn't hear from them for a while. The next class they wanted to restore off the original one failed.

Now we cannot backup either class (original or copy 1), cannot import them into new class shell, and cannot restore. I upgraded to 2.4 (from 2.3) hoping it was somehow there. Then I did a fresh install of another 2.3.4, thinking it was possibly the build. In the upgraded 2.4 and the new 2.3, I can create NEW courses, back them up, and restore.

However, the course with all the content and work they did won't write to the db b/c (it says, it exceeds max packets). So...here I am. with my max packet set at 500MB or higher, and still the error.

All that said, forgetting to restart MySQL *would* often be something I'm likely to do...but not this time.

--Jeff

 

Average of ratings: -
In reply to Jeff White

Re: Max_allowed_packets & Backup/Restore/Import cannot write to Database

by Rob Johnson -

Have turn on debugging to see if you get more information when it throws the error?

Average of ratings: -
In reply to Rob Johnson

Re: Max_allowed_packets & Backup/Restore/Import cannot write to Database

by Jeff White -

Rob--thanks. Yes, I had Debugging for Developer on in the 2.4.1 when trying this, and it did not give anything beyond "Error writing to Database."

In the 2.3.4 new build I have, I get the Max_allowed_packets error with a stack trace which I have seen posted, and which I tried the suggested fixes/approaches for, but they did not help. Namely, I increased the mysql M_A_Packets value and I changed $cfg dbsessions to false. 

Same error arose. I could copy the stack trace, but it is already posted here--exactly--and I didn't find a solution that helped.

Average of ratings: -
In reply to Jeff White

Re: Max_allowed_packets & Backup/Restore/Import cannot write to Database

by Rob Johnson -

Did you have any add on modules in the backup that are not in the installation anymore?  I have seen database errors if I take a backup with plugin data to another Moodle that does not have that plugin installed.  That said, the stack trace would likely tell you if that was the case.

Average of ratings: -
In reply to Rob Johnson

Re: Max_allowed_packets & Backup/Restore/Import cannot write to Database

by Jeff White -

I believe the only addon we have in here is the "jumptonavigation" block. I had added, but then removed, the Certificate module b/c it did affect backups. However, after removing it things had been fine (this was months ago, when I first started working with the group in our comany who were using Moodle).

Average of ratings: -
In reply to Jeff White

Re: Max_allowed_packets & Backup/Restore/Import cannot write to Database

by Ken Task -
Picture of Particularly helpful Moodlers

Wish I had a solution for ya, but all I can offer is some ideas/things to check ...

Check the status of tables ... ie, are any in need of repair?

Is there a 'trashdir' in the data folder?
When was the last running of cron? (that should 'empty' the 'trash')

Are there any backups stored in the course?
With each manual backup, the size of the backup could increase dramatically because the new backups don't exclude the old backups (old issue with =< 1.9's still exist with 2.4.1+).  Download them all, then remove.  Then attempt backup.

When backing up, Step 1 initial settings what items are checked?
Suggest unchecking ALL but activities and blocks.
IF you get to Step 2, un-check any item known to be heavy processing … like quiz.  If that is successful and one can restore the backup.  Then think it's possible to come back to the same course and backup only the quiz activities.  With that backup one could restore to the original course thus getting quizes back into the course.

In System Admin Courses Backups General Backup Defaults
What is the setting of Keep Logs?
(set that lower - the default is 30 days)

Are any items there 'locked' (can't change them when manually backing up a course).

'spirit of sharing', Ken

Average of ratings: -
In reply to Ken Task

Re: Max_allowed_packets & Backup/Restore/Import cannot write to Database

by Jeff White -

Ken--thank you for all you posted here and on my question yesterday. I see you overflow with the "spirit of sharing," and whether the suggestions you posted here fix it or not, your taking the time to help is very much appreciated.

The "back ups within a course" issue...that hits home...I'm assuming that the first time I back up a class, I get the class; second time, I get the class AND the first backup; third time...and beyond...all Hell starts breaking loose in Exponentville. So I'm going to try that first. I think we are good on the other two suggestions, but I'll follow up on them too. 

Again--thank you!

Jeff

Average of ratings: -
In reply to Ken Task

Re: Max_allowed_packets & Backup/Restore/Import cannot write to Database

by Jeff White -

Do "private user backups" get stored in the Course or in the User's acct? 

Average of ratings: -
In reply to Ken Task

Re: Max_allowed_packets & Backup/Restore/Import cannot write to Database

by Jeff White -

Well, Ken, I did all the above--ran cron to ensure trash was cleaned, removed all the backups from the class (we had at least 5 there), and reduced the Log keeping # of day.

I don't even get to step 1 in the backup process. If I click Backup, I get the writing to db error (but I do not get the debugging text, even though I have it turned on for Developer). I'd assume this was a permissions/rights thing, except that I can create a new class, back it up, and restore it--with the same user acct I used to back-up the originally successful course.

At this point, I'm assuming the actual backup file is corrupted.

Average of ratings: -
In reply to Jeff White

Re: Max_allowed_packets & Backup/Restore/Import cannot write to Database

by Ken Task -
Picture of Particularly helpful Moodlers

I do hope you downloaded them all before deleting them. :|

Are we absolutely certain the DB user can write to the DB?
User should have access rights to create temp tables as well.

The fact that you cannot backup anything and that when you click on 'backup' it immediately
hangs could indicate some issues with files/permissions
If that button is going to backup.php located in /moodle/backup/backup.php
That file has 3 required references:

require_once('../config.php');
require_once($CFG->dirroot . '/backup/util/includes/backup_includes.php');
require_once($CFG->dirroot . '/backup/moodle2/backup_plan_builder.class.php'

Are those files there?  And what are ownerships/permissions on them?

There are 5 tables related to backup in  2.4.1+

mdl_backup_controllers
mdl_backup_courses
mdl_backup_files_template
mdl_backup_ids_template
mdl_backup_logs

select * from `mdl_backup_logs` where `message` like "%unable%"
select * from `mdl_backup_logs` where `message` like "%error%"

One can find backup files in what area:

select * from `mdl_files` where `component` like "backup"

The above should show an area.

From the output of above one can actually locate the physical file associated by using the contenthash column.

Example:
A backup found in course file area has:
name of:      backup-moodle2-course-1-mdl24-20121204-1741.mbz
contenthash of: c20151cef6e8607f501e970be52eb48d6fe11335

In datafolder/filesdir/c2/01/ (the first 2 characters of the contenthash is a directory … as is the 2nd 2 characters of the contenthash)

one will find a file named: c20151cef6e8607f501e970be52eb48d6fe11335

You can test to see if the file is corrupted (the above name is a representation of a .mbz which is really a .zip).

mkdir /home/test/
cp c20151cef6e8607f501e970be52eb48d6fe11335 /home/test/
cd /home/test/
mv c20151cef6e8607f501e970be52eb48d6fe11335 somecoursebackup.zip
unzip somecoursebackup.zip

The last command should result in something like the following:

completion.xml    files.xml      moodle_backup.log  questions.xml  users.xml
course            gradebook.xml  moodle_backup.xml  roles.xml
somecoursebackup.zip  groups.xml     outcomes.xml       scales.xml

'spirit of sharing', Ken

Average of ratings: -
In reply to Ken Task

Re: Max_allowed_packets & Backup/Restore/Import cannot write to Database

by Jeff White -

Ken,

The files you referenced for the backup are there. Each is set at 644 permissions...I'm wondering if they should be 755 so I can execute? In fact, it looks as if all the actual files in our Moodle are 644, while the folders containing them are set at 755.

I'm going to go through the rest of your post items now.

Many thanks, yet again.

Average of ratings: -
In reply to Ken Task

Re: Max_allowed_packets & Backup/Restore/Import cannot write to Database

by Jeff White -

The DB user does have all the rights necessary--including temporary tables writing.

My queries of the backup_log for both "unable" and "error" pulled back what appears to be the same entries. All tied to "unsupported activity module questionnaire." However, these were all recorded back in September of 2012. We have had successful backups that worked in October. And I see no errors or "unable" msgs since September...

Suggesting (to me, at least) that the failures recently here aren't even counting as backup failures.

Average of ratings: -
In reply to Jeff White

Re: Max_allowed_packets & Backup/Restore/Import cannot write to Database

by Ken Task -
Picture of Particularly helpful Moodlers

Let's backup here a little (no pun intended) ... you mentioned in this thread you posted in another forum the trace.  Which posting?

Are you remotely hosted?  If so who is provider?  If not, what operating system ... we could be looking in the wrong area.

'spirit of sharing', Ken

Average of ratings: -
In reply to Ken Task

Re: Max_allowed_packets & Backup/Restore/Import cannot write to Database

by Jeff White -

 

Ken--To initiate my part in this snipe-hunt, the main user of our Moodle site sent me this message from Moodle when he first had an issue. I posted it to the Moodle Forum a week or so ago. I was out of the office when he first got this message, and by the time I'd returned, I think he was at the point of getting the Database error messages at the start of the current thread.

I searched around for information about this message--but didn't find anything that seemed to address it in layman's terms. My next move was to upgrade to 2.4.1, thinking that perhaps that would fix it--or that a fix for it would require the upgrade as a component of fixing it. (I do realize it says it affects 2.4 as well as earlier versions).  


MSA-13-0003: Potential server file access through backup restoration
   Description: Paths in backups to restorable files were not being sufficiently validated and could be manipulated to gain access to files on the server.
Issue summary: moodle1 backup converter path not properly validated
Severity/Risk: Serious
Versions affected: 2.4, 2.3 to 2.3.3+, 2.2 to 2.2.6+, 2.1 to 2.1.9+
Reported by: Dan Poltawski
Issue no.: MDL-36977
CVE Identifier: CVE-2012-6099

Average of ratings: -
In reply to Jeff White

Re: Max_allowed_packets & Backup/Restore/Import cannot write to Database

by Ken Task -
Picture of Particularly helpful Moodlers

That was a security issue.  The solution concerning max_packets did solve

the other posters issue.  You've said you've tried that and still no go.

In all of this, no info regarding system has been shared - what OS?  Am guessing Linux, but what flavor.  And how did you take the system from it's 2.3.x to the 2.4.1+?

'spirit of sharing', Ken

Average of ratings: -
In reply to Ken Task

Re: Max_allowed_packets & Backup/Restore/Import cannot write to Database

by Jeff White -

Ken--Sorry, I forgot to provide that information. We are using a hosted Linux server--I believe it's Cent OS 5. I work with it through the cPanel interface and ftp.

To upgrade--the first time, I took the 2.4.1 download onto my windows machine, unzipped it. I renamed the existing moodle file folders and FTPd the new version in--and copied the old config file into it.

I later, re-did the 2.4.1 install by putting the .zip into the public_html area and uncompressing it there. And again, copying the config file in. That's the version we have in place now.

-Jeff

Average of ratings: -
In reply to Jeff White

Re: Max_allowed_packets & Backup/Restore/Import cannot write to Database

by Ken Task -
Picture of Particularly helpful Moodlers

Thanks, now one more ... who is the provider?  They are not all equal and some run different stuff which does affect Moodle.

Folks have reported issues in things just to find out that when they xfered files via FTP either files got corrupted or not all files got transferred.  Am assuming no files are missing.

Now this could be habit, dunno, but if one is hosting with a Linux server, one should download the .tar.gz version and not the .zip.  If you notice the .zip is larger and probably contains stuff really meant for Winders.

All workstation OS's now a days should be able to un-tar, gunzip a .tar.gz file.

public_html indicates a shared host which means shared MySQL server also.

so if I am correct about that am wondering what your caps are.  Some providers not setup to handle a DB hungry app like Moodle might have caps in place that is involved in this issue.  Only folks that would know would be provider.  Sometimes they provide an FAQ or a forum for customers, which, thank goodness, they leave open to the public to read (can't participate).  Have found some interesting comments from hosting providers tech support in those.  Their web pages that describe available packages sometimes don't disclose all caps - unless they involve $$ of course.

I run CentOS on several standalone (dedicated) servers an have never experienced what you are describing.  So me helping you is also, for me, self-preservation in case I ever do experience it.  See, if there was an issue with versions, surely someone would have reported this problem before ... immediately after clicking that button is not one of the max_allowed_packets issue as before.

There is something very strange about this ... so I keep asking questions.

'spirit of sharing', Ken

Average of ratings: -
In reply to Ken Task

Re: Max_allowed_packets & Backup/Restore/Import cannot write to Database

by Jeff White -

Our host is Verio.com we're going to check on the caps.

I keep coming back to the fact that I can create a new class and back it up fine--but in the class we have going with our full content in it, we cannot even get past...or TO...the first step in backing up.  Since we have two classes running from that same initial class (created in 2.3.1 before I updated to 2.4) and all our backups giving me problems pertain to that class (like patient zero), I feel like I  must have something "inconsistent" between the backup class and the environment we're trying to open it into.

I checked the modules and blocks (as Rob suggested), but maybe I missed something. If the backup has jump-to-navigation block in the course must I have that in any Moodle I try to restore into? I had it on 2.4.1, but then along the way removed it.

--thanks, Jeff 

 

Average of ratings: -
In reply to Jeff White

Re: Max_allowed_packets & Backup/Restore/Import cannot write to Database

by Ken Task -
Picture of Particularly helpful Moodlers

There is more than one way to begin a backup of a course.  In the course itself is one and then in the course listings there is an icon for backing up.  Both, however, do get to the same screen.  But  wonder if trying a different path makes any diff?

Ok, one other method to see just how messed up it might be ... automated backups.  What happens to the problem courses if that is turned on?

Something to check ... in moodledata/temp/backup/ Is there anything in there other than hashnamed.log files with 0 byte size?  Any hash named directories? If there are hashnamed directories, what's contained therein.  Is there anything contained therein that relates to the troubled course?

We've been looking at just backup ... what of the course itself?

You might have already said this, but .... what blocks/activities are being used?

Does it have a Section 0 at the top of the course with any user input image or intro or something like that?  How about HTML blocks?  Can one do any editing in that  course?

Looked over Verio's site ... they don't have any customer forums?  Did see where it suggested they had installer scripts.  And in looking at their offerings thought the memory a little low for Moodle - 4 Gig their highest listed?

Will go study some tables related to backup to see if I can see anything, but in just observing how that process starts, it presents a screen listing the activities, etc. that are to be backed up.   That list takes direction from settings in Courses, Backups, General Backup defaults.  That list has check boxes for 'locked'.  Are there any items 'locked'?  Since it kicks out immediately, the first item in the settings is the number of days, etc.for logs, then users.  Have you tried changing any of those (IMS Cartridge Package is the first in the list) to see if the first screen is then presented to you?  Total guess, of course.

'spirit of sharing', Ken

 

 

Average of ratings: -
In reply to Ken Task

Re: Max_allowed_packets & Backup/Restore/Import cannot write to Database

by Jeff White -

Ken, thank you very much for sticking with this, your "spirit of sharing" is pretty spirit-ful!

Okay--both approaches to backup lead to the quick error.

No hashnamed.log file directories and all files show 0 byte size.

Class is a "topics format" with 13 topics.

Activities used in the course are only 3 types: Quizes, Pages, and Feedback. We have completion tracking turned on to require previous pages be read prior to taking a quiz, and a "pass" on quiz required before moving to new Topic/section.

Blocks in use (those showing with 1 or more instances in block management): Admin bookmarks, calendar, course overview, Latest news, Login, Main menu, My private files, Navigation, Online users, Quiz results, Recent activity, Search forums, Settings, and upcoming events.

However, we also had "Jump-to-Navigation" in the course that was backed up. I deleted it after moving to 2.4.1 b/c I could not get it to show correctly on the page...hmmm. If it is not installed in the current 2.4.1 environment, could that cause an issue with unpacking the backup? I actually think I re-installed it and tried without success to restore. But I will try that again.

Top section shows a "News Forum" which must be default b/c the guys using this do not use a forum of any sort in the class.

Users can add avatars, but nobody has done so.

It uses the TinyMCE editor. The only people who can edit content are the instructors. Students' input is only through the Quiz and Feedback modules, and on their individual user profile pages.

We contacted Verio after the question you asked about caps. They were not particularly helpful--they provide the hosting space and set up the accounts, but do not provide any support in terms of specific applications or installs.

I will look at the backup-defaults. I was in there and did go through them once, thinking that they *seemed* okay to me. I don't believe anything is locked.

Also, I will turn on Automated backups to see what occurs.

-Jeff

 

Average of ratings: -
In reply to Ken Task

Re: Max_allowed_packets & Backup/Restore/Import cannot write to Database

by Jeff White -

Automated backups were enabled already--weekly, with it set to keep one. It is supposed to save the backup in the default course backup area. Today, I had a message that cron hadn't run, so I clicked to run it, and it tells me it successfully backed up one class (one made off the initial image we had); however, when I go into a course restore page, nothing is there. The cron report then tells me it failed backing up the other class (we have two built off the same original).

When I click on "restore" for an older backup, it goes to the first page and shows a Section 0 with a News Forum and a Page of text from our guys. It also says the backup includes student info. Once I finish confirming the backup (which does show all the topics of the class), it hits the DB error. 

So the backup file does have the content (or at least some) in it, it just cannot push it into the DB to make the NEW class off the backup.

 

Average of ratings: -
In reply to Ken Task

Re: Max_allowed_packets & Backup/Restore/Import cannot write to Database

by Jeff White -

I realize my other response is not a 'stack trace'--however, I went through this forum prior to posting and found the same stack trace as the one I received posted in several threads--and typically getting the response to increase the setting in my.cnf. 

In this thread, for example, the exact same stack trace I recieve is posted. https://moodle.org/mod/forum/discuss.php?d=209066

Average of ratings: -