Drush cache clear corrupting database?

Question

I just moved to a new computer: Windows 10 with XAMPP. As far as I know I set everything up the same as it was on my old Windows 7 computer. But now, for some reason, much of the time when I run drush cc all or run update.php, the database gets corrupted. Most frequently, some cache tables' *.idb files (tablespaces) are deleted from my mysql directory, which makes those tables appear as "in use" in phpMyAdmin and prevents them from being edited or emptied. They are always cache tables and never tables that actually get modified by my update hooks. Restoring the database is the only way to fix it then. This is a mature site that I've been working on for a long time, and have never seen this until the new computer. What's happening here?

A sample error message when using update.php, caused by a missing *.idb file:

PDOException: SQLSTATE[HY000]: General error: 1812 Tablespace is missing
for table '(null)': DELETE FROM {cache_field} WHERE
(cid = :db_condition_placeholder_0) ; Array (
[:db_condition_placeholder_0] => field:message_type:6 )
in cache_clear_all() (line 168 of
C:\<path>\includes\cache.inc).

My most recent drush cc all error message, which Google has never even heard of, was:

exception 'PDOException' with message 'SQLSTATE[HY000]: General      [error]
error: 1030 Got error -1 "Internal error < 0 (Not system error)" from
storage engine InnoDB'

Answer 1

In general Drush can't corrupt your database, as it's a software which just sends the SQL queries.

However this error can be related to newer version of MySQL, as of 5.5.7, InnoDB always uses the fast truncation technique (which is equivalent to DROP TABLE and CREATE TABLE). So it no longer performs a row-by-row delete for tables with parent-child foreign key relationships, therefore TRUNCATE TABLE returns an error for such tables.

See: 2.11.1.1 Changes Affecting Upgrades to 5.5

This already has been reported in #2229013 for Drupal core.

So you should either:

• consider using different data structures:

  • non-InnoDB,
  • use redis/memcached for your cache management (so this won't happen),

• try clearing your caches in different way, e.g.

  echo "SHOW TABLES LIKE 'cache%'" | drush sqlc --extra=--skip-column-names | xargs -L1 -I% echo "DELETE FROM %;" | drush sqlc --extra=-v 
  

• upgrade your Drupal to 8 and see if the same happens (drush cr),

• use different database (maybe will work in MariaDB?),
• downgrade your MySQL to <5.5.7,

• patch the Drupal core by replacing TRUNCATE with DELETE statement,

  You may also consider the condition like if ($mysqlversion > 5.57 AND $cache_table_type = 'innodb') { // DELETE } else { // TRUNCATE }.

• or wait until the fix is provided for Drupal core (#2229013).