#12107 closed Bug/Something is broken (fixed)

Need help with corrupt table in Commonbound Drupal database

Reported by: https://id.mayfirst.org/jackaponte Owned by: https://id.mayfirst.org/jaimev
Priority: High Component: Tech
Keywords: ossie Cc: eli@…, rene@…
Sensitive: no

Description

Today I was applying routine module updates and doing maintenance on commonbound.org. While clearing caches the site completely timed out; it recovered by itself, but now the cache table is missing. I can't recreate it:

mysql> CREATE TABLE `cache` (   `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='Generic cache table for caching things not separated out...';
ERROR 1005 (HY000): Can't create table 'commonbound_drupal.cache' (errno: -1)

In phpMyAdmin I've actually been able to get the cache table to appear in the sidebar tree of the database tables and was once able to navigate into the cache table only to receive errors about the table not existing.

This sounds an awful lot like what's described on this StackExchange issue, which makes it seem like we'll need MF/PL assistance to get it fixed!

Otherwise, Drupal site caches can't be cleared, and I imagine other problems will begin to crop up on site as well.

Please advise!

Change History (7)

comment:1 Changed 20 months ago by https://id.mayfirst.org/jaimev

Looking into this now.

comment:2 Changed 20 months ago by https://id.mayfirst.org/jaimev

  • Owner set to https://id.mayfirst.org/jaimev
  • Status changed from new to assigned

I can see there is a leftover cache.ibd file in /var/lib/mysql/commonbound_drupal This also looks similar to the problem that came up in #12050. There I ultimately had to delete the existing db create a new one and import a dump before running the commands to recreate the missing tables.

Should I proceed with the same solution for you? I've just created a new dump in commonbound.org/users/commonbound/sql-dump/commonbound_drupal.bak.sql

comment:3 Changed 20 months ago by https://id.mayfirst.org/jackaponte

Hi--sorry that I wasn't able to respond to this earlier! I'd like to double-check with Commonbound/NEC that they're OK with that approach; I'll get back to you ASAP.

To confirm--it sounds like with this approach, restoring from a recent sql-dump with only the cache table being missing, we're unlikely to encounter any/much data loss. Would you say that's right, Jaime?

Thanks so much!

comment:4 Changed 20 months ago by https://id.mayfirst.org/jaimev

So the current problem is you won't be able to create the missing cache table if the cache.ibd file is still present. The surest way I've found to remove it is to delete the entire database and then restore. The missing table won't be restored from the current dump, but it can then be recreated. You shouldn't lose any other data as the dump should restore the same data you have now.

There are some other tricks for removing lingering .ibd files mentioned in that ticket I referenced but I couldn't get them to work.

comment:5 Changed 20 months ago by https://id.mayfirst.org/jackaponte

Thanks so much Jaime--please go ahead with this as soon as you are able to, I've gotten the go-ahead from Eli at NEC!

comment:6 Changed 20 months ago by https://id.mayfirst.org/jaimev

  • Resolution set to fixed
  • Status changed from assigned to feedback

I believe this is done. I first created a new dump of the existing db. I then deleted the commonbound_drupal database from the control panel which resulted in a soft-error because of the lingering cache.ibd file. After manually removing that file I was able to cleanly delete the db from the control panel. I then proceeded to recreate the db again with the same name. I re-established privileges to this db for the commonbound_drup user using the same password. I then restored the dump I'd created over the new db and ran the query you included above to recreate the cache table. Can you give it a try and let me know if it is responding correctly now?

comment:7 Changed 20 months ago by https://id.mayfirst.org/jackaponte

  • Status changed from feedback to closed

Thanks for the work and the explanation, Jaime--that seems to have fixed it! The last error in the logs about the cache table being missing is dated 2016-09-14 03:36, since then no errors and I'm able to clear all caches fine.

Please login to add comments to this ticket.

Note: See TracTickets for help on using tickets.