PDOException: SQLSTATE[HY000]: General error: 1030 Got error -1 from storage engine: TRUNCATE {cache_field} ; Array ( ) in cache

Database table cannot be repaired or deleted and only solution is a workaround such as creating a new table with a prefix

You should be able to 'drop table x' and recreate it as what you have looks like the same effect as 'alter table x discard tablespace'.

What do you have innodb_file_per_table variable set to? (should be ON as will reduce the truncate time a lot, however you can't just change it on a running system).

> Are other people experiencing this problem since upgrading to later versions of MySQL?

If you're right about the cause then probably. MariaDB seems to be the same https://mariadb.com/kb/en/truncate-table/.

> Should drupal make the permanent change from TRUNCATE to DELETE FROM?

Tables that need to be written to while a truncate operation is in progress so in theory you should probably use a DELETE FROM statement, however in a REPEATABLE-READ isolation level (the default) will block or DEADLOCK error on other inserts. Having a cache insert block while DELETE FROM is going isn't going to go well. Lower isolation levels will DELETE a cache entry added after after the delete has started but won't block or deadlock which also isn't ideal.

Looking at the current uses:

https://api.drupal.org/api/drupal/includes%21database%21database.inc/fun...

DrupalDatabaseCache::clear - I'd change the delete from to delete from xxx where created <= REQUEST_TIME;

test cases - leave as is.

_menu_router_save - given this is done within a transaction DELETE FROM menu_router is the most appropriate way to go. Transaction isolation for dropping/creating tables isn't there in mysql but I'm not sure if TRUNCATE TABLE fakes this since 5.6 has some online DDL is possible.

Drupal 8 does https://api.drupal.org/api/drupal/core%21modules%21locale%21locale.compa...

Given how this is used it probably should be in a transaction and use then use DELETE FROM... instead of a truncate.

In general given the way its implemented in mysql-5.6 whatever uses db_truncate should probably be doing DELETE FROM but also with some criteria, or some other transaction or more thought out use.

On Drupal 8 implementation, the current session's transaction status is useful as it can't roll back if it does a truncate table, however no other session can use the database in a transactional manner when a truncate tables is performed.

locale_translation_flush_projects is getting re-written in http://drupal.org/node/1842362

Semi-Related
#2222635: Waiting for table metadata lock on cache_field table

Oh no... Changing truncate to delete is no option at all. This goes back to my case #643382: Clear cache all with TRUNCATE TABLE or update.php times out! and was a serious issue.

Ignore my patch - #2222635: Waiting for table metadata lock on cache_field table has perhaps current patch for D7.

> Oh no... Changing truncate to delete is no option at al

By making the delete based on time changed the locking profile a lot. Make a test the same as I've done in #2222635-16: Waiting for table metadata lock on cache_field table and you'll see it actually works. It was only the time based arguments that mike put forward that made me consider dropping endorcing this patch.

Attached patch should at least warn implementers foolishuninformed enough to attempt using db_truncate assuming it doesn't have consequences.

Database cache implementation discussion can be continued in [2222635].

I think for mysql - a variant of miketown-2 patch [2222635-3] in core/lib/Drupal/Core/Database/Driver/mysql/Truncate.php would make it less impacting for all those foolish enough to use it.

Any comments/objections on my menu_router_save patch?

opps. prev was D8 patch

That looks useless to me. We need more likely a version check and innodb and change truncate to delete only in this case.

> That looks useless to me

The doco patch, the rename and truncate that miketown2 concept, or the menu_router save patch?

And can you explain "useless" a bit more?

Actually the menu_router should delete all the paths not in the its list and then do a merge.

We provide an API that makes the database type more or less hidden to the developer. If you use db_truncate you expect it works on all db servers and versions supported by Drupal. If there is a specific problem in newer versions we need to add workarounds to the database backend. Documenting something without implementing a workaround is useless as it does not help anybody. If this is an all time issue we must add code that change logic based on the mysql version.

my bad with previous menu router patch. There was a db_transaction at a higher function call. However relevant to the original topic of the thread, truncate in mysql is an implicit commit with no rollback. This could leave you without any menu_router table if other errors occurred. Though the merge here isn't as good as a big replace (#1800286: Update, rather than Delete and insert - for significant database performance improvement) at least you'll have the data if something fails.

> We provide an API that makes the database type more or less hidden to the developer. If you use db_truncate you expect it works on all db servers and versions supported by Drupal.

Is rollback capability and transaction isolation part of the expectation?

> If there is a specific problem in newer versions we need to add workarounds to the database backend.

Actually the implicit commit is as old as 5.0.8.

Postgres seems to be implemented with similar constraints. Sqlite does a DELETE all as its implementation.

> Documenting something without implementing a workaround is useless as it does not help anybody.

Though there are now two patches that removes all non-test uses of db_truncate in core, there's probably other uses.

> If this is an all time issue we must add code that change logic based on the mysql version.

The non-transactional implementation of db_transaction affects all supported mysql versions.

At the moment I see the options for db_transaction as:
a) accept it isn't transactional as the status quo (and document this more);
b) changing the implementation to an implicit delete for mysql and postgres like postgres and get long delete times; or
c) some version of mikeytown2 "rename around" patch that interacts with db_transaction in some invisible (and fairly tricky) way to provide rollback.

It looks like this patch does not fix db_truncate() on API level.

This needs multiple solutions in order to improve the situation. Refactoring code in order to eliminate truncate operations is good. Making truncate work better is good as well.

danblack's current patch might belong in here #512962: Optimize menu_router_build() / _menu_router_save()

ok. menu_router moved to #512962-227: Optimize menu_router_build() / _menu_router_save(). thanks mikeytown2 for finding it.

So as far as I can tell you want good performance and no errors in a db_truncate function that even database implementers haven't implemented.

Which trades off do you wish to make?

@danblack

More results from my rename truncate patch #2222635-20: Waiting for table metadata lock on cache_field table. Async Truncate (rename and then truncate in another request OR at end of request and don't wait for DB response) is what I think will work.

@miketown2 - nice - so you want that to be the mysql implementation of db_truncate for all? Does this work in test cases?

I thought "core/lib/Drupal/Core/Database/Query/Truncate.php" requires something like (pseudo code)

if ($mysqlversion > 5.57 AND $cache_table_type = 'innodb') {
  DELETE * FROM {table}
}
else {
  TRUNCATE TABLE {table}
}

We do not need watchdogs or documentation in code. The issue need to be fixed. However I have no idea why MySQL guys made truncate incompatible.

> We do not need watchdogs

Probably, but it wouldn't hurt identifying modules and add-ons that are using it unsafely.

> or documentation in code.

Documentation comes up in https://api.drupal.org/api/drupal/core%21includes%21database.inc/functio...

> The issue need to be fixed.

Mike's got a tested an measured solution. Might even work for Postgresql.

> However I have no idea why MySQL guys made truncate incompatible.

Truncate in mysql and postresql has never been fully transactional because of rollback and isolation as I mentioned earlier if you'd actually read the previous reference links.

Probably, but it wouldn't hurt identifying modules and add-ons that are using it unsafely.

It hurts as it causes performance penalties.

This is an API, we need to make it consistent over all db types. Thats why we using PDO and the API. Developers should not add conditional database code to their modules.

Developers should not add conditional database code to their modules.

I agree with this statement but in reality sometimes it's necessary when using obscure features like group_concat which can really speed things up in the right places.
http://drupalcode.org/project/advagg.git/blob/05e3dde2351cecada4c5ff2dcf...

On line 209 I implement a slower version of group_concat that works across all DBs. Having a graceful fallback is how to handle different databases.

New Zeal: I'm having the same problems. There are at least two of us on the planet. Replacing truncate with delete from helps. Have yet to fully test though. Cost many hours so far.

Computers suck.

New Zeal: I'm having this issue too. Was working on display settings on a content type and: bang.
So there are three of us now. Time to start a club.

Edit: my solution was the following two SQL statements.

DROP TABLE cache_field;

CREATE TABLE IF NOT EXISTS `cache_field` (
`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...';

Good afternoon,

I am having the same problem :-(

got the error now all so

error disappears after drop and create table, but after sometime it comes back again.

clean install with drupal 7.34, omega3 and sub-theme, the modules necessary for omega and extra modules are admin-menu, ckeditor and views

Server: 127.0.0.1 via TCP/IP
Servertype: MySQL
Serverversie: 5.6.21 - MySQL Community Server (GPL)
Protocolversie: 10
Apache/2.4.10 (Win32) OpenSSL/1.0.1i PHP/5.6.3

this time the error occurred when i updated the views module and wanted to run the database update

I created an alt db cache backend for core that should take care of issues like this one
https://www.drupal.org/project/apdqc

Related: Drush cache clear corrupting database? at DA

There is a patch in #2684359: The truncate method is does different things on different databases and depending if in a transation to fix this issue and more.