Last updated on 06 Sep 2023, 13:16:48.
Category: Managed servers
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!
To successfully recover from this error and recreate the cache_rules table you will need to process a few steps:
This is quite a job, but unfortunately the only workable, and quickest option.
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!
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;
[~]# mysql -u username -p yournewdatabase < yourdatabasebackup.sql
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.
Were not all your questions answered?
Don't worry, we will be happy to help you via a support request!