Moodle Database Corruption

Moodle Database Corruption

by Donna Smith -
Number of replies: 17

The isp that housed our Moodle installations had server problems. In the move, from one server to another one of the databases of one of our Moodle sites got corrupted so we were told by this isp. They said the database crashed like on a Monday but we were working in the site on Friday with no problems. The move was on Saturday and after that the database was corrupted. We were paying them to do our backups and all backups are corrupted. The database is missing every table pass the mdl_tab table. Needless, to say we are leaving this isp. But wondering what would be some options that we might try to save this site. We have a whole site backup just not a database backup.

This is what this isp said:

It appears the innoDB tables for users are missing, but are inside the backup. Upon looking the InnoDB system was crashed on the other server and the logins were most likely not working prior to maintenance either.

There is not a way to easily get this back (as its not as simple as restoring a file), a complete database would have to be reconstructed from the files which is outside our scope of support. I can provide you a copy of the files, however if you wish for us to spend the hours it will take to reconstruct these tables from the backup files, it would be billable. I will start preparing a environment for reconstruction in our lab in case you wish to go that direction.

Any help would be greatly appreciated.
Average of ratings: -
In reply to Donna Smith

Re: Moodle Database Corruption

by Ken Task -
Picture of Particularly helpful Moodlers

Ouch!

Moodle has a jobs site:
https://moodle.org/mod/data/view.php?d=54

But before you go, please describe what 'files' you do have ... moodle code by itself, cannot re-construct a database.  Moodle data for the site cannot re-construct a database.

Is there a chance that one might be able to get a hold of the raw database files for the site?   Or at least view those raw files.   Viewing a database with some client app, like PHPMyAdmin or Moodle Adminer is something to interact with the DB server and DB, but if one were to actually see the files in a DB

The last raw DB files of a moodle look like:

-rw-r-----. 1 mysql mysql   8720 May 17 11:57 mdl_workshop_aggregations.frm
-rw-r-----. 1 mysql mysql  73728 May 17 11:58 mdl_workshop_aggregations.ibd
-rw-r-----. 1 mysql mysql  12998 May 17 11:57 mdl_workshopallocation_scheduled.frm
-rw-r-----. 1 mysql mysql  65536 May 17 11:58 mdl_workshopallocation_scheduled.ibd
-rw-r-----. 1 mysql mysql   9260 May 17 11:57 mdl_workshop_assessments.frm
-rw-r-----. 1 mysql mysql  73728 May 17 11:58 mdl_workshop_assessments.ibd
-rw-r-----. 1 mysql mysql   8640 May 17 11:57 mdl_workshopeval_best_settings.frm
-rw-r-----. 1 mysql mysql  65536 May 17 11:58 mdl_workshopeval_best_settings.ibd
-rw-r-----. 1 mysql mysql   8794 May 17 11:57 mdl_workshopform_accumulative.frm
-rw-r-----. 1 mysql mysql  65536 May 17 11:58 mdl_workshopform_accumulative.ibd
-rw-r-----. 1 mysql mysql   8728 May 17 11:57 mdl_workshopform_comments.frm
-rw-r-----. 1 mysql mysql  65536 May 17 11:58 mdl_workshopform_comments.ibd
-rw-r-----. 1 mysql mysql   8884 May 17 11:57 mdl_workshopform_numerrors.frm
-rw-r-----. 1 mysql mysql  65536 May 17 11:58 mdl_workshopform_numerrors.ibd
-rw-r-----. 1 mysql mysql   8672 May 17 11:57 mdl_workshopform_numerrors_map.frm
-rw-r-----. 1 mysql mysql  65536 May 17 11:58 mdl_workshopform_numerrors_map.ibd
-rw-r-----. 1 mysql mysql   8632 May 17 11:57 mdl_workshopform_rubric_config.frm
-rw-r-----. 1 mysql mysql  65536 May 17 11:58 mdl_workshopform_rubric_config.ibd
-rw-r-----. 1 mysql mysql   8728 May 17 11:57 mdl_workshopform_rubric.frm
-rw-r-----. 1 mysql mysql  65536 May 17 11:58 mdl_workshopform_rubric.ibd
-rw-r-----. 1 mysql mysql   8728 May 17 11:57 mdl_workshopform_rubric_levels.frm
-rw-r-----. 1 mysql mysql  65536 May 17 11:58 mdl_workshopform_rubric_levels.ibd
-rw-r-----. 1 mysql mysql  14382 May 17 11:57 mdl_workshop.frm
-rw-r-----. 1 mysql mysql   8816 May 17 11:57 mdl_workshop_grades.frm
-rw-r-----. 1 mysql mysql  65536 May 17 11:58 mdl_workshop_grades.ibd
-rw-r-----. 1 mysql mysql  65536 May 17 11:58 mdl_workshop.ibd
-rw-r-----. 1 mysql mysql   9306 May 17 11:57 mdl_workshop_submissions.frm
-rw-r-----. 1 mysql mysql  73728 May 17 11:58 mdl_workshop_submissions.ibd

Bottom line, without any sql or 'raw files' and the InnoDB settings for your DB didn't have those that help recover a crashed InnoDB DB, afraid there isn't much anyone can do.

sadly ...

Ken



Average of ratings: -
In reply to Ken Task

Re: Moodle Database Corruption

by Dan Marsden -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers Picture of Plugins guardians Picture of Testers Picture of Translators

I probably wouldn't recommend using the jobs db for this. I would suggest you find someone reputable to help (Maybe a Moodle Partner that has an experienced DBA on staff)  - otherwise whoever you engage may just make the problem worse. You might consider moving your site to managed hosting with a Moodle Partner to improve the overall reliability of your site too.

How you approach this depends on where the corruption is - as Ken mentions - do you actually have all the raw DB files? - Make sure they don't have a 0 byte file size. If the files are missing or have a 0 byte size you will need to find a way to get the original files off the old server.

Mysql have some info here on repairing tables that might help too:
https://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html


Average of ratings: -
In reply to Dan Marsden

Re: Moodle Database Corruption

by Donna Smith -

Thank you for your suggestions. Not interested in a Moodle partner, used one several years ago and didn't like the restrictions placed on the site. Believe it or not we do pretty well on our own for such a small company...been using Moodle for a long time. It was our mistake to not make local backups, even though we were paying to have them done. Lesson learned.

Average of ratings: -
In reply to Donna Smith

Re: Moodle Database Corruption

by Dan Marsden -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers Picture of Plugins guardians Picture of Testers Picture of Translators

Not all Moodle Partners are equal and provide the same services and the partner you were using before (looking at your previous posts) is no longer a Moodle Partner! - If restrictions were the issue, I'd highly recommend you fire off a few e-mails to the other local Moodle partners describing your requirements and some of them may be able to help. You have done pretty well on your own ... but now you're dealing with a relatively catastrophic event that should have been avoidable if your site was being managed properly.

Average of ratings: -
In reply to Dan Marsden

Re: Moodle Database Corruption

by Donna Smith -

Thanks for the advice. It would of been catastrophic if it was one of our other Moodle sites, but lucky for us it was our smallest. We are a very small company with limited resources and we have 7 Moodle sites.  I doubt that we could afford a Moodle partner again, but I will research them again. I know they offer a lot more services now than years ago.

Average of ratings: -
In reply to Ken Task

Re: Moodle Database Corruption

by Donna Smith -

I think we have all the files we need to reconstruct from the site backup. 2 tar files. We can see all the tables and that they have data. They are just buried deep in the backup. We are researching all of our options.

We actually have a cpanel backup of this site from last November. If we exported the missing tables from it and imported them into this database, do you think that might have a chance of working? It's a small site with less than 15 users that would be in the November backup.

Average of ratings: -
In reply to Donna Smith

Re: Moodle Database Corruption

by Dan Marsden -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers Picture of Plugins guardians Picture of Testers Picture of Translators

If all the tables before the mdl_user table are ok, and you don't use the wiki or workshop plugins on your site that could potentially work. 

You would probably need to be careful with the mdl_url and mdl_user_enrolments tables which are after the mdl_tag tables and may contain some stuff that needs to be restored too.

ideally you should really have an experienced Moodle dev doing this for you.

Average of ratings: -
In reply to Dan Marsden

Re: Moodle Database Corruption

by Donna Smith -

Thanks for your feedback. We use very few plugins...two at the most. I'll check the tables you mentioned. An experienced Moodle dev would be the ideal. We will give it a shot and let you know the results.

Average of ratings: -
In reply to Donna Smith

Re: Moodle Database Corruption

by Ken Task -
Picture of Particularly helpful Moodlers

What version of MySQL?

What version of Moodle?  in the /var/lib/mysql/ directory on a CentOS 7 box running MySQL 5.7 each database has it's own directory.  At the top of each directory is a file ... db.opt ... it tells you the character set and collation of that database.

[root@sos moodle33]# cat db.opt
default-character-set=utf8mb4
default-collation=utf8mb4_unicode_ci

"buried deep in the backup" ... what does that mean?

What does a raw listing of files look like?   Have asked this now twice.   Yes, know you are an experienced Moodle Master, but DB Admin?   Not I ... for sure.

IF I were to attempt to do anything with raw files of a DB, the DB server/service should not be running.

If you are on a shared server, doubt that your hosting provider would shutdown the DB service so they could acquire the raw files for your DB.  Not sure how their cPanel thang works.  Do know if I do a command line mysqldump of a DB to get an sql file the site isn't accessible ... code not talking to DB while the mysqldump is running ... which makes sense.

LIke Dan mentions, might get away with missing files IF something wasn't used, but then again ... do know there is lots of contextual stuff and it is a relational DB.

So if you have ssh and it's Linux and you can get to the directory where the DB files live:

cd /var/lib/mysql/

cd moodle

ls -l > /path/to/my/account/someplace/moodle-db-raw-listing.txt

Here's a for example:

-rw-rw----. 1 mysql mysql    8766 Sep  3  2017 mdl_stats_daily.frm
-rw-rw----. 1 mysql mysql   73728 Sep  3  2017 mdl_stats_daily.ibd
-rw-rw----. 1 mysql mysql    8766 Sep  3  2017 mdl_stats_monthly.frm
-rw-rw----. 1 mysql mysql   73728 Sep  3  2017 mdl_stats_monthly.ibd
-rw-rw----. 1 mysql mysql    8822 Sep  3  2017 mdl_stats_user_daily.frm
-rw-rw----. 1 mysql mysql   81920 Sep  3  2017 mdl_stats_user_daily.ibd
-rw-rw----. 1 mysql mysql    8822 Sep  3  2017 mdl_stats_user_monthly.frm
-rw-rw----. 1 mysql mysql   81920 Sep  3  2017 mdl_stats_user_monthly.ibd
-rw-rw----. 1 mysql mysql    8822 Sep  3  2017 mdl_stats_user_weekly.frm
-rw-rw----. 1 mysql mysql   81920 Sep  3  2017 mdl_stats_user_weekly.ibd
-rw-rw----. 1 mysql mysql    8766 Sep  3  2017 mdl_stats_weekly.frm
-rw-rw----. 1 mysql mysql   73728 Sep  3  2017 mdl_stats_weekly.ibd

I don't have to explain, but ... each table in the DB has a .frm file and an .ibd file.

About Dan's comment concerning jobs ... hmmmm ... wouldn't it be nice if Moodle had something similar for 'consultants' ... and have some way to check credentials/claims of expertise, etc.?

But then again, have known of 8th grades students who could do amazing things.

'spirit of sharing', Ken



Average of ratings: -
In reply to Ken Task

Re: Moodle Database Corruption

by Donna Smith -

This particular database mysql version is now 10.2.16-MariaDB, but this is after the switch to the new server. It was mysql version 5.5.59-cll. We don't know if the change in versions corrupted the tables or not. I think the Moodle version was 3.0 or 3.2.

I'm not at work so I don't have the file to look at. I can answer your questions better tomorrow when I'm at work and my associate is there who does the Moodle upgrading, etc. Buried deep means that in the tar file we had to go down several levels to get to the tables. These tar files that we were given by the isp are not what we are use to seeing. I don't know if they are "raw file." I do know we don't have access to everything, there are some things only the isp can see or do.

We have a vps, if that helps. We can see the .frm file and .ibd file for each table in the tar file.

Thanks for your help. We are not experts in Moodle, started with Moodle 1.2,  been Moodling for a while now, but still so much to learn just not enough time.

Average of ratings: -
In reply to Donna Smith

Re: Moodle Database Corruption

by Ken Task -
Picture of Particularly helpful Moodlers

Following shared in hopes of understanding what you are working with ...

MariaDB is supposed to be a 'drop in replacement' for MySQL.  From command line, all the mysql commands are the same.  For moodle, the only difference is in config.php and the line for DB type.

MariaDB: $CFG->dbtype    = 'mariadb';

This reference might help:

https://mariadb.com/kb/en/library/upgrading/

Normally, after an upgrade to MariaDB or MySQL a command is run to upgrade the tables ...

https://mariadb.com/kb/en/library/upgrading-from-mysql-to-mariadb/

"You should run mysql_upgrade (just as you would with MySQL) to finish the upgrade. This is needed to ensure that your mysql privilege and event tables are updated with the new fields MariaDB uses. Note that if you use a MariaDB package, mysql_upgrade is usually run automatically".

Your version of Moodle can be found in the code root in version.php.

vps - with most hosting providers that means a dedicated server to just one customer.  Customer has full run of the server meaning they can get into every area ... including database and config files for db server.

Check your config.php file for $CFG->dbhost    = 'localhost'; ... if it says localhost, then the DB server was/is on the same machine.

tar - common for Linux archiving tool ... back in the day when the 'standard' method of backing up was to a tape device ... can still be used with files with the -f switch.  WinDoz equiv. - Zip and Unzip.

raw files are located in /var/lib/mysql/ and that should be the same location for MariaDB as well as MySQL.  What looks to be folders/directories are really all the 'raw files' for the database ... database name is the same as the folder/directory.

It's important that, if you work with raw files manipulation, the DB server be shut down ... ie, not running.  DB servers are doing stuff all the time with files contained in /var/lib/mysql/.  Work only with the moodle database files.   Do not touch etc. other files/directories you see in /var/lib/mysql/

If, what you have, is a backup from Nov. of this year, make sure you copy files ... don't move.  If your attempts to fix fail you've not lost what backups you do have.

Also consider that even it it means loosing data from Nov. til today, at least the site is running.

What are doing is very risky.   Even if it appears the site is running ok, you might discover all things don't work.

Question: how large is this tar file?

That question might have an offer with it on follow up via PM.

'spirit of sharing', Ken


Average of ratings: -
In reply to Ken Task

Re: Moodle Database Corruption

by Donna Smith -

I have attached a copy that shows one of the archive files. It is 21 mb and includes all the tables. The capture just shows some of the tables but shows the tree levels at the top.

Attachment archiveDB.png
Average of ratings: -
In reply to Donna Smith

Re: Moodle Database Corruption

by Ken Task -
Picture of Particularly helpful Moodlers

Is 7-26 a date reference?  IF so, that was not that far back in time.

Also note the dates on some of the .ibd/.frm files ... indicate when they were modified.

database name is reolimit_moodle

21 MB doesn't look like it has all files - that's a guess.

On another server that has sandbox moodles - with few users ... just for comparison:

[root@server mysql]# du -h ./moodle34fc
96M    ./moodle34fc
[root@server mysql]# du -h ./moodle33
61M    ./moodle33
[root@server mysql]# du -h ./moodle32
134M    ./moodle32
[root@server mysql]# du -h ./moodle31
192M    ./moodle31
[root@server mysql]# du -h ./moodle30
76M    ./moodle30
[root@server mysql]# du -h ./moodle27
62M    ./moodle27

See PM message from me.

'spirit of sharing', Ken


Average of ratings: -
In reply to Donna Smith

Re: Moodle Database Corruption

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

So they screwed your site up and screwed the backups up and they want to charge you?

You probably need a lawyer wink

Average of ratings: -
In reply to Howard Miller

Re: Moodle Database Corruption

by Donna Smith -

tongueout Believe me, I felt the same way. But it's a small company so I don't suppose we'd get much.

Average of ratings: -
In reply to Donna Smith

Re: Moodle Database Corruption

by Ken Task -
Picture of Particularly helpful Moodlers

Well, Donna and a co-worker must have higher powers smiling on them ... they have been success in recovering the database in an unorthodox method.  Will let her share back the details/descriptions.

Congrats, Donna!

Hopefully, a full backup ... sql dump, tar ball of code, and tar ball of moodledata has been made and archived off the server.  And as long as the sites are hosted where they are, backups will be taken on a regular basis.

It was kind of you, Donna, not to mention hosting provider, but if you'd care to share, am sure others that might be seeking moodle hosting would be interested in not only reading the about the issue but ... maybe ... what provider to avoid.   Think they call that 'full and honest disclosure'.   Granted, every provider has 'acts of a higher power' clause but ....

'spirit of sharing', Ken


Average of ratings: -
In reply to Ken Task

Re: Moodle Database Corruption

by Donna Smith -

Thanks, Ken! I wanted to thank everyone who responded especially, Ken. You are a wealth of  Moodle information.

Our database was missing all the tables past mdl_tag after a major maintenance by our isp. This was the isp when I became owner of our company and when we just had web sites probably okay. But once we got heavy into Moodle and Wordpress, it just wasn't working anymore. Change is hard even when you need. it. We had no backups from the isp that were usable but we did have a cpanel backup of our own from Nov 2017. This site was small and the users hadn't changed since that time, so I thought why not just export the missing tables from that backup and import them into the current database with the missing tables. So we installed the backup and exported the missing tables to a file that we were going to import into the current database. But when we reinstalled the current database the missing tables were there. We didn't have to import them. We know just enough about databases to be dangerous so this was a big surprise. We guess that when you install a database over another it just replaces the tables, since those tables were missing they weren't replaced. But everything worked with just a few hiccups. For some reason, we couldn't get by the plugin check so we deleted the 3 offending plugins and manually installed them. Then the site updated and is working fine. Some things are off with users but easily fixed.

We used WebbyTech which is a very small isp that I doubt is on any Moodle radar. We just outgrew them. We have since gone to SiteGround and are much happier.

Been a Moodle user for a long time and this is the worse we've seen, but hopefully we have learned from this experience. Backup, backup, backup . . .locally. Love Moodle and this community.

 

Average of ratings: -