Drupal generates database error table cache_rules does not exist

Last updated on 06 Sep 2023, 13:16:48.
Category: Managed servers

Drupal InnoDB MySQL

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

The import can be done with SSH, or through phpMyAdmin. We prefer SSH, it's quicker. This can be done through SSH with the following command:
[~]# mysql -u username -p yournewdatabase < yourdatabasebackup.sql

4. Recreate the cache_rules table

The 'cache_rules' table can be created with the following query. This can be executed in the mysql client, or in phpMyAdmin.
 `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.

Related articles

Managed services in the spotlight

Our system administrators have lifted a couple of cargo ships filled with containers of work. (yes, that pun was on...

Read more

What service level guarantees (SLA) are available for a server housing (co-location)?

Server housing or co-location customers can fall back on an excellent service level guarantee. A Kinamo server housing service comes...

Read more

Discover here how we’ve fixed a blocking error when upgrading ESXi 6.5 to 7.0 update 2 through vSphere Lifecycle Manager.

Kinamo maintains multiple VMware vSphere clusters, not only for our own infrastructure, but also for a variety of customers. As...

Read more

Need extra help?

Were not all your questions answered?
Don't worry, we will be happy to help you via a support request!


Select your language

All languages: