PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'cache_rules' doesn't exist

Rules table. ;-)

Here is the code which I ran to recreate the broken table:

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.';

Much appreciated!

Thank you @aeremeev: simple and easy copy/paste solution (with removal of XX_ table prefixes) that worked for me.

Thanks Solved it for me too

I had the same problem, and I tried to create the table, but I always get the following message :

#1005 - Can't create table 'ID139639_calico.cache_rules' (errno: -1) ()

I also tried to export the table from my local site and import it into the site online, but that doesn't work either.

I'm sorry, but I don't know much about mysql.

I'm also having problems recreating the table. I get the message: #1005 - Can't create table 'textbook_d7.cache_rules' (errno: -1)
Supports transactions, row-level locking, and foreign keys

Any help?

Thank you aeremeev, that did the trick for me!

I was just wondering: How did you know what the table should look like? Did you check an existing (working) installation?

I used a simpler syntax, but based on #4, big ups for that

I had to actually do this a few times to missing cache_ tables after a mysqldump and migration from one Aegir server to the next

mysql -u root
USE databaseblahblah1

DROP TABLE cache_metatag;
CREATE TABLE IF NOT EXISTS cache_metatag (
  cid varchar(255) NOT NULL DEFAULT '',
  data longblob,
  expire int(11) NOT NULL DEFAULT '0',
  created int(11) NOT NULL DEFAULT '0',
  serialized smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (cid),
  KEY expire (expire)
);

This is a recurring issue, and affects many sites. It has been documented several times, and is worth investigating the underlying cause.

http://drupal.stackexchange.com/questions/107918/error-with-drush-clear-...
https://www.ostraining.com/blog/drupal/broken-drupal-database/
http://drupal.stackexchange.com/questions/118643/corrupted-mysql-cache-r...

In my case, it seems related to running out of disk space on my /var/ partition, where the databases reside.

Just FYI in case this can help anyone. To solve my problem I had to
1. do a mysql dump of the database and then recreate it under a different name
2. make sure my settings.php file reflects the change in database name
3. Run the following 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.';

For some reason it would not allow me to drop and recreate the cache_rules table without completely recreating the database.

I also am getting this error: (errno: -1)
Supports transactions, row-level locking, and foreign keys

i changed the storage type to blackhole and was able to then create the table. I don't know if this really matters or not, but it did the trick and allowed me to update the site.

Same here with erno:-1.

"solved" it with ENGINE=Blackhole

Thanks Aeremeev! #4 worked for me. Just copied and pasted it in phpmyadmin console minus the XX_ part and it fixed the problem ;) I'm forever grateful for your help!!!

#4 worked for me also. MAKE sure you remove the XX_ in the example as i left it in without looking.

Initially #4 didn't work as I still got the error when trying to recreate the table.

I had to do an ENGINE=blackhole and that worked.

What does the engine=blackhole mean? anyone?

Also how can this be prevented? What is the cause of this problem? is it drupal or is it mysql?

We also experienced this problem on one of our websites all of a sudden. When trying to recreate the table we experienced the errno: -1 as well. The latter could be circumvented using a different storage engine than InnoDB, but the premise of ENGINE=blackhole does not sound good (see https://dev.mysql.com/doc/refman/5.0/en/blackhole-storage-engine.html):

The BLACKHOLE storage engine acts as a “black hole” that accepts data but throws it away and does not store it.

It seems data will not be stored in the cache_rules table and caches will be built over and over again. We could not switch back to InnoDB, but were able to switch to MyISAM.

Corrupted database

I guess the db has become corrupted and some InnoDB dependent orphan files prevent us from switching back to InnoDB. On our CentOS server I assume one of the files is /var/lib/mysql/NAME_OF_DATABASE/cache_rules.ibd.

How to fix the table

We could manually remove this file according to http://dev.mysql.com/doc/refman/5.0/en/innodb-error-codes.html#c9663, but to play safe we chose not to. After we recreated the cache_rules table using MyISAM, we dumped the full db, used this file to create a new db with it and changed the table's storage engine to InnoDB, with success. Now we removed the old db and use the new one.

Bits and pieces

The odd thing is that according to all other issues this always seems to happen to the cache_rules table...

Extensive background info can be found at http://dba.stackexchange.com/questions/69656/cant-create-table-but-table....

And since there is no patch to review, I set the status back to Active.

The real question here is WHAT is causing the cache_rules table to become corrupted when using InnoDB? I have also successfully used MyISAM rather than blackhole, but this still isn't an actual solution.

this happened again to another site. im curious to what is the reason for this to happen so we can stop this from coming up all the time?

Doesn't this smell like a MySQL bug? Whatever the Rules module does with the database, a table should never get corrupted. The server we encountered the problem on runs 5.5.36-cll-lve - MySQL Community Server (GPL) by Atomicorp.

Version info can be found:

1. by executing SHOW VARIABLES LIKE "%version%"; (more info) or
2. at phpMyAdmin's home screen.

Corrupted InnoDB tables do not seem to appear seldomly:

• https://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
• http://dba.stackexchange.com/questions/42495/how-to-recover-restore-corr...

Seems we're not the only ones having this issue: https://www.drupal.org/node/2097037

I get this too. I can drop and recreate the table but after some time the error comes back and I need to do it again.

#4 worked for me also. Two thumbs up for this. Thank you.

while clearing the cache i got the same error,creating the table manually fixed the issue.Thnx.

Hi everyone, I'm super new with all of this and learning as I go...I was hoping someone could help me in locating the phpmyadmin console. I'm trying to add code #4 but am having trouble locating it. It might be super easy, but I'm not sure how. Thanks.

reyna130, if on a local machine, you have to manually install it (I suggest installing it under your website root folder). You may already have it on your Web development stack. If you install it under your web root, you should be able to get to phpmyadmin at localhost\phpmyadmin.

If on a webhosting service, it's normally already installed and you can get to phpmyadmin from cpanels under the databases section (cPanel->PhpMyAdmin).

obviously this worked for me, for clear cache, i dont know how to make this, because i install rules but i dont know why fail if i dont use rules, but this works

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.';

Thanks,
#13 worked for me but I had to create a new db because else I received the tablespace error when recreating the cache_rules table

Corrected the related issue. The other one is a duplicate.

The way I solved the "(errno: -1)" was to create a table cache_rules_1 following indications from #28 (or #4 which looks the same to me but the XX_ prefix).

You need of course to delete the old table cache_rules as well, so don't rename/remove: DROP TABLE cache_rules ;

Once cache_rules_1 was created I renamed the table to cache_rules.

It went all fine with my case. Not sure what's causing that error or what's causing the table corruption.

P.S.: I did the above from phpMyAdmin. I think I logged off and logged on after having deleted the cache_rules table.

Just thought I'd chime in that I just went through this yesterday, a small nervous break down is all. But the #4 fortunately worked for me. Thank you aeremeev and all here, I got my site going again. But would be nice to know how/why this table pukes.

I also had the same issue today on a production site. I did execute what is described in #28, with the additional error when recreating the table
ERROR 1813 (HY000): Tablespace for table '`congress`.`cache_rules`' exists. Please DISCARD the tablespace before IMPORT.
fixed with http://stackoverflow.com/questions/15694168/error-tablespace-for-table-x...
It's a bit frustrating to not know the root of this issue.

I've experienced the same issue. Table cache_rules didn't exist and I get:
#1813 - Tablespace for table '`cache_rules`' exists. Please DISCARD the tablespace before IMPORT.

#32 solved the problem and now it seems to be fixed. Although I'd like to unerstand root cause.

I am glad that #4 worked for me. uf...

I was getting the following error while trying to create table as mentioned in #13:

#1813 - Tablespace for table '`cache_rules`' exists. Please DISCARD the tablespace before IMPORT.

I navigated to xampp/mysql/data/ folder & deleted the cache_rules.ibd file.
Then I run the query as in #13 & it worked fine.

Hope this helps someone.

Regards,
Supriya Rajgopal

This is a much larger issue and is not the fault of Rules. It is being addressed in Core: https://www.drupal.org/node/2229013. Even though that's a D8 issue, it still applies for D7.

The solution in #4 works for me too when I get the cache_rules problem, but only temporarily. The next time I Clear Cache (even 10 seconds later), bam it happens again.

If I turn Rules off and Clear Cache, it will happen for another module that uses cache, including Features, Metatag, Libraries.

fyi, I'm running MariaDB 10.1.6

Thanks #5

Thank you so much for this. Much much appreciated.

I got the (error -1) as well when trying to create the table again - using #32 fixed it - code for renaming table:

RENAME TABLE cache_rules_1 TO cache_rules

Thanks @supriyarajgopal #37

What @jarodms said in #38 is correct - this is not specifically a Rules issue, but it is something that affects all Drupal modules. Search for "PDOException base table or view not found" and you will find hundreds of issues that are the same as this, but all happening for different tables - it's not just cache_rules. All those other issues have the same discussions and same suggestions for fixes and workarounds.

The issue #2229013: PDOException: SQLSTATE[HY000]: General error: 1030 Got error -1 from storage engine: TRUNCATE {cache_field} ; Array ( ) in cache mentioned by @jarodms seems to be the key - there was a change in the underlying DB behavior and Drupal core has not been changed to account for that.

While this issue is useful as a way for people experiencing this problem to figure out how to recover, there's nothing that can be done in Rules to avoid the situation so I don't see any value in leaving this issue open any longer. This information is still available for those who search the issue queue, but there's really nothing more to say.

Here are some of the other threads, for those who might be interested:
https://www.drupal.org/forum/support/post-installation/2013-11-26/pdoexc...
https://www.drupal.org/forum/support/post-installation/2012-07-20/sqlsta...
https://www.drupal.org/forum/support/post-installation/2016-01-23/pdo-ex...
https://www.drupal.org/project/workspace/issues/2688929
https://www.drupal.org/project/commerce_discount/issues/2824161