Drupal throws a 'cache_rules' does not exist error

Unfortunately the Drupal cache system drags along a nasty bug. The Drupal database engine uses a transactional InnoDB storage engine. This has advantages over the classic MyISAM storage engine, but one of the bigger disadvantages is the dreadful recovery of InnoDB data after a database crash.

If you did manage however, Drupal can still give you the famous PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'cache_rules' doesn't exist error.

More information on this error can be found at https://www.drupal.org/node/2160645

There are several reasons why an InnoDB database may crash, but one of the most unfortunate and common is the lack of diskspace. This results in the fact that data written to both table and transaction log is wrong, resulting in a corrupt table that's probably not even visible in phpMyAdmin or through the MySQL client.

If you think adding a 'cache_rules' table is a quick fix, you're in for a real surprise. You will get the error:

ERROR 1005 (HY000): Can't create table 'cache_rules' (errno: -1)

The reason why you get the error is because on disk, there is a cache_rules.idb file, but the table metadata is missing. Simply removing the cache_rules.idb file is no solution and the pretty lame suggestion to do a mysqlcheck -r on the database will not help either since the MySQL database repair functions are only applicable to the MyISAM storage engine!

How can I fix this issue?

To successfully recover from this error and recreate the cache_rules table you will need to process a few steps:

1. Make a backup of the current database (both structure AND data)
2. Create a new MySQL database
3. Do an import of the backup to the newly created database
4. Cretae the cache_rules table in the new database

And finally:

1. Change the database connection in Drupal so you use the new database
2. Remove the old database, if your site works fine

This is quite a job, but unfortunately the only workable, and quickest option.

1. Create a MySQL database backup with mysqldump

The quickest way to create a MySQL database backup is by using the mysqldump command, but you will need SSH access to your hosting / database server:

[~]# mysqldump -u username -p myolddatabase > mydatabasebackup.sql

If you have no SSH access you can still generate an export with phpMyAdmin, but don't forget to get both structure AND data!

2. Create a new database

The quickest way is by using the My Kinamo website (if you're a Kinamo customer), if you have SSH access however you can execute the following commands:

[~]# mysql -u username -p
mysql> CREATE DATABASE IF NOT EXISTS yournewdatabase;

3. Import your database backup into the new MySQL database

[~]# mysql -u username -p yournewdatabase < yourdatabasebackup.sql

4. Recreate the cache_rules table

CREATE TABLE IF NOT EXISTS `cache_rules` (
 `cid` varchar(255) NOT NULL DEFAULT '' COMMENT 'Primary Key: Unique cache ID.',
 `data` longblob COMMENT 'A collection of data to cache.',
 `expire` int(11) NOT NULL DEFAULT '0' COMMENT 'A Unix timestamp indicating when the cache entry should expire, or 0 for never.',
 `created` int(11) NOT NULL DEFAULT '0' COMMENT 'A Unix timestamp indicating when the cache entry was created.',
 `serialized` smallint(6) NOT NULL DEFAULT '0' COMMENT 'A flag to indicate whether content is serialized (1) or not (0).',
 PRIMARY KEY (`cid`),
 KEY `expire` (`expire`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Cache table for the rules engine to store configured items.';

The final step is adjusting your connection details in the settings.php file, and removing the old database.