|Image Source: http://media.smashingmagazine.com/cdn_smash/images/404-error-pages/simp.gif|
Earlier today, one of our Moodle instances was unavailable to users–several thousand–because the MySQL database upon which Moodle is based has grown corrupt. What apparently was corrupted was the mdl_user table and who knows how many others. When one tried to access the database using phpMyAdmin, it would shown an error stating that the database needed to be repaired.
Having encountered this previously, I found that phpMyAdmin, as wonderful a tool as it is for optimizing MySQL tables, lacked the tools I thought I needed to have to fix the problem. To get around the issue, I decided to take advantage of the REPAIR tools in Navicat Lite for Mac, a free, non-commercial MySQL tool.
Using Navicat Lite for MySQL (available for Windows as well), I was able to quickly repair the Moodle database affected.
Here are the steps taken:
1) Connect to the Server and choose the affected Moodle Database (e.g. “sandbox” in this example) and then SELECT ALL the tables that appear.
2) After selecting all the tables–they will appear highlighted–go to FILE and choose Maintain Table, then Repair Table–>Extended. This action, if all goes well, should fix your affected MySQL database.
3) When Navicat Lite for MySQL is done repairing your database, a window similar to the one below should appear with the message “Finished.” Note that if you adjust the width of the QUERY column, you’ll potentially see the total number of tables fixed.
One of the questions folks ask is, “What caused the corruption in the first place?” My opinion is that corruption can be caused by a variety of factors, including failure to regularly optimize the database tables that have overhead. So, for that reason, I encourage folks to optimize their tables regularly so as to prevent problems.
Of course, another possibility is that the server was restarted, or something happened, to interfere with how the data was being stored in the database tables, resulting in corruption. Some would also point out that this tends to occur with MyISAM type databases in MySQL as opposed to INNODB, which is why Moodle 2.x is switching to INNODB.
Whatever the reason, it’s nice to know how to repair the database using a GUI interface. Note that there are other MySQL GUI tools–aside from Navicat Lite for MySQL–you can use, such as SQLYog for Windows and/or Sequel Pro for Mac…both of those are free, open source and no-cost as well.
Everything posted on Miguel Guhlin’s blogs/wikis are his personal opinion and do not necessarily represent the views of his employer(s) or its clients. Read Full Disclosure