I think we have an underlying MySQL issue, arising from the time of the Moodle upgrade.
When we 'exported' our moodle database (using phpmyadmin), as per upgrade instructions, the process timed out and hung.
Theory: this may have left the my sql temp folder, or file(s) within it locked open, or alternatively it ran out of space(though the hidden temp file it was using must have grown enormous).
Intermittent/periodic subsequent access to this folder/file(s) causes a problem - or possibly because the temp file has outgrown space available.
This is supported by a similar error in our Mambo CMS.
If these ideas are impossible, we welcome correction of our understanding.
Steps taken so far:
PHP - in php.ini:
- increase script timeouts to 90 and memory_limit to 64M
- !question - is there a rule of thumb how big these values should be? (ideally, simultaneous user x applications x database tables / 'magic number' - but I realise there are many other variables)
Moodle -in lib/settings/php
- set memorylimit to 48M (based on http://moodle.org/mod/forum/discuss.php?d=11959 discussion)
- !question - a) should this be smaller than php.ini limit so Moodle does not hog all PHP memory resources? b) am I right in assuming moodle uses this value rather than php.ini value?
- stop MySQL service
- change mySQL configuration from medium to large (see mysql sample config files)
- create new filesystem folder to act as temp folder for mysql
- set filesystem ACLs on this folder to same as old one.
- change settings in environment variables (and also service start parameters) to point to new folder
- restart mysql
- repair users_admin in moodle database
- result - cannot find users_admin myd! This is now critical to managing moodle. All help gratefully received.
Good news -
- Moodle (and Mambo) performance now seems improved.
- Front page posts seem to have returned
- Course listings seem to have returned in categories, even for admin.
Incidental: In discussion http://moodle.org/mod/forum/discuss.php?d=11959 - have now seen comments re: 16M being adequate. I will tune down the settings.php in line with this comment.
[Martin/moderator - now we have dug deeper, perhaps the title of this topic should change (if possible) to 'SQL error during pre-upgrade backup' to help others. I will search again about user_admins, now that seems isolated, and post separately if necessary about that. TIA]