1

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'
  • increase your local server max_execution time – Adi Mar 8 '16 at 7:20
  • My max_execution_time in php.ini is already set to 60000. That's over 16 hours. More than that? Or somewhere else? – John Alexander Mar 8 '16 at 7:23
2

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).

  • Thanks for the advice! This is a large company project and I don't really have the option of changing data structures, using a different DB, or upgrading Drupal. Wasn't able to successfully downgrade my local MySQL. So I guess I'm stuck for now. – John Alexander Mar 9 '16 at 20:05
  • @JohnAlexander You can do some core patching and change all DELETE * FROM {table} into TRUNCATE TABLE {table}. – kenorb Mar 9 '16 at 20:14
  • Same error happens on MariaDB 10.1.13. – Randell Jul 5 '16 at 7:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.