MySQL table error 145

I found this error message in my sql logs today.

“MySQL error code 145 = Table was marked as crashed and should be repaired”

This message was being generated by two of the12 scripts that are on a schedule and back up the moodle db instances each night.  The errors are a result of the drive where the backups are written filling up last week.  The drive ran out of space, corrupting a couple tables in the dbs.

To be clear, neither the site nor the db are down, just the backup process is not working. I only noticed it when looking the on of the backup folders and noticing the backup files were 1kb.

I resolved the errors by opening the last good backup I had for either of the two db in question and following these steps.

Steps to resolve error in table

1. I checked the tables being reported as corrupted, I tried browsing them and saw the error message
2. Launched MySQL Query Browser
3. Opened the backup.sql file (120 MB - took 20 seconds to open)
4. Search the script, found the code responsible for dropping, adding and populating the failing table
5. Copied / pasted the code into a new script window
6. Ensured I was using the correct schema, typed USE SCHEMA_NAME
7. Executed the script
8. Browsed the table from step 1 that was reporting the error, should be gone now
9.  Ran the db backup .bat to test if it is working again.
10. Verified that the backup had written a complete backup.sql file

The backup files are just a bunch of sql DROP, CREATE and INSERT statements.  Most of the size of the script is from the data records written to the backup file for the insert statement.

This is the drop, create, insert code for a single moodle table, that does not have much data in it.

DROP TABLE IF EXISTS `mdl_block`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `mdl_block` (
  `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(40) NOT NULL DEFAULT '',
  `version` bigint(10) unsigned NOT NULL DEFAULT '0',
  `cron` bigint(10) unsigned NOT NULL DEFAULT '0',
  `lastcron` bigint(10) unsigned NOT NULL DEFAULT '0',
  `visible` tinyint(1) NOT NULL DEFAULT '1',
  `multiple` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8 COMMENT='to store installed blocks';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `mdl_block`
--

LOCK TABLES `mdl_block` WRITE;
/*!40000 ALTER TABLE `mdl_block` DISABLE KEYS */;
INSERT INTO `mdl_block` VALUES (1,'activity_modules',2007101509,0,0,1,0),(2,'admin',2007101509,0,0,1,0),(3,'admin_bookmarks',2007101509,0,0,1,0),(4,'admin_tree',2007101509,0,0,1,0),(5,'blog_menu',2007101509,0,0,1,0),(6,'blog_tags',2007101509,0,0,1,1),(7,'calendar_month',2007101509,0,0,1,0),(8,'calendar_upcoming',2007101509,0,0,1,0),(9,'course_list',2007101509,0,0,1,0),(10,'course_summary',2007101509,0,0,1,0),(11,'glossary_random',2007101509,0,0,1,1),(12,'html',2007101509,0,0,1,1),(13,'loancalc',2007101509,0,0,1,0),(14,'login',2007101509,0,0,1,0),(15,'mentees',2007101509,0,0,1,1),(16,'messages',2007101509,0,0,1,0),(17,'mnet_hosts',2007101509,0,0,1,0),(18,'news_items',2007101509,0,0,1,0),(19,'online_users',2007101510,0,0,1,0),(20,'participants',2007101509,0,0,1,0),(21,'quiz_results',2007101509,0,0,1,1),(22,'recent_activity',2007101509,0,0,1,0),(23,'rss_client',2007101511,300,1279028940,1,1),(24,'search',2008031500,1,0,1,0),(25,'search_forums',2007101509,0,0,1,0),(26,'section_links',2007101509,0,0,1,0),(27,'site_main_menu',2007101509,0,0,1,0),(28,'social_activities',2007101509,0,0,1,0),(29,'tag_flickr',2007101509,0,0,1,1),(30,'tag_youtube',2007101509,0,0,1,1),(31,'tags',2007101509,0,0,1,1);
/*!40000 ALTER TABLE `mdl_block` ENABLE KEYS */;
UNLOCK TABLES;

This experience highlights the importance of having multiple backups - I may have lost a little data, but not a major issue. 

I was reminded also about how difficult it is to work with backup scripts that are large.  The scripts were in the range of 100 - 150 MB, and they took a long time to load in the MySQL Query window.  What if the script was 1 GB? 

Have a tool, like phpmyadmin or MySQL Query Browser that will open the backup.sql file.

Do administrators of gigantic script files have systems with 10 GB of RAM?  Probably a more efficient way to deal with creating and reading/restoring scripts of that size.

Also, I was reminded today of the difference between MyISAM and InnoDB engines.  I spend about an hr. reading on forums on MySQL and moodle sites about the differences between.  MySQL has a couple specific commands that are supposed to help with rebuilding a table using the .FRM file.  This process is a little mysterious to me.  I need to understand this better. 

Finally, the third error solved today was an easier one, once I saw the error message, I knew what to do.

error message:
"mysqldump: Got error: 1044: Access denied for user 'username' to database `schema_name` when selecting the database".

 I had not given schema privileges for the username to the schema_name.

Steps to resolve schema privileges:

1. I remoted into the DB using MYSQL Workbench Server Administration 
2. clicked on the Accounts 'Manage Users' ta
3. selected the username being used in the backup command.  
4. Clicked on the schemas
5. Selected the schema name that was failing
6. Clicked on the privileges tab
7. Granted appropriate rights
8. Clicked apply

Moral of the story? 

When you run out of disk space and your automated backup solution crashes, check carefully the full extend of internal damages. Monitor more closely the day after.