Error with drush clear-cache: Base table or view not found

Question

There are similar questions out there, but I think this warrants a new thread.

I am getting the following error when running drush clear-cache all

PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'dbname.cache_rules' doesn't exist: TRUNCATE {cache_rules} ;"

However, when I run the show tables command on the database, it clearly lists the cache_rules table.

Any ideas what is going on here and what I can do to fix it?

Answer 1

Here is how to reset your cache_rules table, using a stable Drupal installation.

Get cache_rules structure from clean database

You can get SQL text to drop/add the cache_rules table via the following steps:

1. Log in to phpMyAdmin on a functional Drupal site.
2. Click on the columns section under the cache_rules table
3. Click the Export button
4. Click 'Custom' to show all options
5. Under 'Output' click 'view output as text'
6. Under 'Format-specific options' choose 'structure'
7. Under 'Object creation options' check 'Add DROP TABLE / TRIGGER statement'
8. Copy the text displayed, or see below (sorry for the formatting)

Here is the SQL code to drop and add the table (thanks duckx!).

DROP TABLE cache_rules ;
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.';

Drop/add the cache_rules table

Now navigate to the other database, using phpMyAdmin.

1. Make a full backup of the database
2. After the backup has been saved, click the SQL tab
3. Paste the text into the text box (removing any existing text)
4. Click 'Go'

Answer 2

Here's the code to run where it drops and adds the table back:

REPLACE XX WITH YOUR OWN TABLE NAME....

DROP TABLE XX_cache_rules ;
CREATE TABLE IF NOT EXISTS `XX_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.';

Answer 3

Based on the two comments above, I ran mysqlcheck and confirmed the table was missing, even though the "show columns" command displayed it.

Then I went into /var/lib/mysql and removed all references to the table.

Logged into mysql and ran the drop table command for good measure.

And finally recreated the table. I referenced a working database to correctly model the table.