Waiting for table metadata lock on cache_field table

#2229013 is related and as you can see in the comment #6 I've done a delete by create time, that way it doesn't block new inserts. Access of ones due to delete AFAIK will get a deadlock error which is caught by the cache module and ignored.

The problem with renaming is you've put your entire caching offline for the time (despite it being quicker than a DELETE ALL). More recent mysql truncate table effectively do you what you've done in SQL.

Yes, I believe I've addressed the DELETE FROM by adding the date criteria.

In addition the the previous time based concerns mentioned above there is overheads of indexes #165061.

How about adding a serial column on the cache and then doing SELECT serialid FROM cache ORDER BY serialid DESC LIMIT 1; DELETE FROM cache WHERE serialid < {selected_id}? Mitigates time, metadata locks and allows new cache to be created on new items after this. (I'll wait until 2224847 is committed before doing a patch on this approach).

serial:big perhaps :-)

postgres has a CYCLE option. MySQL will fail once reached however it can be reset back with an alter table statement though it will need a test to see what happens with existing data there. However once this happens the logic on previous optimisation falls down quickly and though you end up with:

1. select MAX(id)...
2. insert into ... (dummy entry)
3. if last_ID < MAX_ID - range delete
4. else delete < last_ID

Time options

Given the availability of alternate higher performing caching options should we even worry about the index insert time for created?

I guess we need to assess all of these https://api.drupal.org/api/drupal/includes%21cache.inc/function/calls/ca...

Looking - most seem to be admin, cron or test related.

Autoincrement overflow handling

> Truncate cache on cron if max is within 1 billion of the max value should take care of that issue.

Or catch exception on reaching and process it:

renumber or better in a locking sense:

1. select count(id) as size from cache
2. alter table cache AUTO_INCREMENT = {size + safety margin before next begin occurs}
3. begin
4. create temporary table x as select id from cache
5. SET @newid=0
6. update cache,x SET cache.id=(@newid:=@newid+1) where cache.id=x.id;
7. delete from cache using cache,x where x.id=cache.id
8. drop table x
9. commit

> I think your approach (delete where serialid) will work out well for D8.

Works for me.

> I think it's too risky to do in D7 though; people access the cache_* tables directly instead of using the API

crazy.

> , thus doing something more conservative would be ideal (my vote is for D7 is the rename trick).

works for me. Looked at your patch. Seems fine with one fairly significant exception - create table like isn't in sqlite.

> Catching the exception when auto increment overflow happens could cause a major headache as that table can not be written to and will keep throwing exceptions.

for at least the time of an select count ; alter table

fair call though.

> . I do like the update cache id trick though; that should work instead of truncate. I think we should do both; run a check on cron in order to be proactive and if cron stops working then we will still catch the exception and deal with it.

how about instead of cron we grab last_insert_id in a cache set call?

throw a warning after $early_limit

perform renumber on a some low probably request after $high_limit

provide api / ui to do process?

I'd done part one (assuming my cache-hash patch gets accepted) that covers a basic delete.

I wanted to get the structure in place before we had to deal with database migration scripts (which would be horribly blocking for anyone doing running an early 8.x).

I'm still up for doing a renumber function. As a bonus, because cid/cidhash is no longer a unique key, the retry that is doSet probably can go away too.

locking proof of concept.

By doing a delete from cache where id <= max_id we don't block new inserts.

session1

MariaDB [drupal_dev]> describe cache_data;
+------------------------+---------------------+------+-----+----------------------+----------------+
| Field                  | Type                | Null | Key | Default              | Extra          |
+------------------------+---------------------+------+-----+----------------------+----------------+
| id                     | bigint(20) unsigned | NO   | PRI | NULL                 | auto_increment |
| cidhash                | binary(20)          | NO   | MUL |                      |                |
| cid                    | varchar(1024)       | NO   |     |                      |                |
| data                   | longblob            | YES  |     | NULL                 |                |
| expire                 | int(11)             | NO   | MUL | 0                    |                |
| created                | int(11)             | NO   | MUL | 0                    |                |
| serialized             | smallint(6)         | NO   |     | 0                    |                |
| tags                   | longtext            | YES  |     | NULL                 |                |
| checksum_invalidations | int(11)             | NO   |     | 0                    |                |
| checksum_deletions     | int(11)             | NO   |     | 0                    |                |

MariaDB [drupal_dev]> select max(id) from cache_data;
+---------+
| max(id) |
+---------+
|     127 |
+---------+
1 row in set (0.00 sec)

MariaDB [drupal_dev]> set autocommit =0; set tx_isolation='READ-COMMITTED'; start transaction; delete from cache_data where id <=127;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 74 rows affected (0.00 sec)

As this isn't committed (with a commit) the locks are still active as we can see by the record locks in session 2 there lock the pages of the primary key. We can also see inserts in session 2 aren't locked by the delete.

session2

MariaDB [drupal_dev]> show engine innodb status;
...
---TRANSACTION 290E1E, ACTIVE 75 sec
10 lock struct(s), heap size 1248, 68 row lock(s), undo log entries 68
MySQL thread id 7435, OS thread handle 0x7f379610b700, query id 3298598 localhost debian-sys-maint sleeping
TABLE LOCK table `drupal_dev`.`cache_data` trx id 290E1E lock mode IX
RECORD LOCKS space id 82546 page no 7 n bits 88 index `PRIMARY` of table `drupal_dev`.`cache_data` trx id 290E1E lock_mode X locks rec but not gap
RECORD LOCKS space id 82546 page no 8 n bits 80 index `PRIMARY` of table `drupal_dev`.`cache_data` trx id 290E1E lock_mode X locks rec but not gap
RECORD LOCKS space id 82546 page no 9 n bits 72 index `PRIMARY` of table `drupal_dev`.`cache_data` trx id 290E1E lock_mode X locks rec but not gap
RECORD LOCKS space id 82546 page no 11 n bits 80 index `PRIMARY` of table `drupal_dev`.`cache_data` trx id 290E1E lock_mode X locks rec but not gap
RECORD LOCKS space id 82546 page no 12 n bits 88 index `PRIMARY` of table `drupal_dev`.`cache_data` trx id 290E1E lock_mode X locks rec but not gap
RECORD LOCKS space id 82546 page no 30 n bits 80 index `PRIMARY` of table `drupal_dev`.`cache_data` trx id 290E1E lock_mode X locks rec but not gap
RECORD LOCKS space id 82546 page no 33 n bits 72 index `PRIMARY` of table `drupal_dev`.`cache_data` trx id 290E1E lock_mode X locks rec but not gap
RECORD LOCKS space id 82546 page no 35 n bits 72 index `PRIMARY` of table `drupal_dev`.`cache_data` trx id 290E1E lock_mode X locks rec but not gap
RECORD LOCKS space id 82546 page no 36 n bits 80 index `PRIMARY` of table `drupal_dev`.`cache_data` trx id 290E1E lock_mode X locks rec but not gap

MariaDB [drupal_dev]> insert into cache_data(cidhash,cid,data,expire,created,serialized,tags,checksum_invalidations,checksum_deletions) VALUES ('sdd','dddd','data',NOW(),NOW(),4,'super tag',3,4);
Query OK, 1 row affected, 2 warnings (0.00 sec)

MariaDB [drupal_dev]> insert into cache_data(cidhash,cid,data,expire,created,serialized,tags,checksum_invalidations,checksum_deletions) VALUES ('sdd','dddd','data',NOW(),NOW(),4,'super tag',3,4);
Query OK, 1 row affected, 2 warnings (0.01 sec)

MariaDB [drupal_dev]> insert into cache_data(cidhash,cid,data,expire,created,serialized,tags,checksum_invalidations,checksum_deletions) VALUES ('sdd','dddd','data',NOW(),NOW(),4,'super tag',3,4);
Query OK, 1 row affected, 2 warnings (0.00 sec)

MariaDB [drupal_dev]> insert into cache_data(cidhash,cid,data,expire,created,serialized,tags,checksum_invalidations,checksum_deletions) VALUES ('sdd','dddd','data',NOW(),NOW(),4,'super tag',3,4);
Query OK, 1 row affected, 2 warnings (0.00 sec)

ps. warnings are the double use of NOW() to an int field.

(which means I need to get transaction isolation level support done otherwise it would only not block with < max).

Only core maintainers should set the beta blocker tag and only critical issues should be beta-blocking; did you speak to catch about this issue?

If TRUNCATE isn't safe, we should change the implementation in the MySQL driver, not work around it in the cache backend.

Since this will need backport to 7.x. which will require an update, I don't see why it should block an 8.x beta. The update can drop and recreate all the cache tables if necessary rather than trying to actually update.

I don't understand where the discussion is going here. The fact that the database cache is transactional is a feature, not a bug, we should not try to workaround it.

Why not simply doing a DELETE unconditionally, instead of trying to do a TRUNCATE if outside a transaction, a DELETE otherwise?

> I don't understand where the discussion is going here. The fact that the database cache is transactional is a feature, not a bug, we should not try to workaround it.

Yep. We agree.

> Why not simply doing a DELETE unconditionally, instead of trying to do a TRUNCATE if outside a transaction, a DELETE otherwise?

An unconditional DELETE in repeatable-read will lock all inserts and take a while. A read-committed level will delete new cache entries. A range based delete will only lock the records that exist and still allow new cache to be inserted. #2238203: DatabaseCacheBackend/CacheBackendInterface get method should have timeout should stop existing records in the process of being deleted from undergoing a long lock timeout (which I'm working on test cases for at the moment).

> Why not simply doing a DELETE unconditionally, instead of trying to do a TRUNCATE if outside a transaction, a DELETE otherwise?

An unconditional DELETE in repeatable-read will lock all inserts and take a while.

We don't want repeatable-read, but that's another question. I guess what you are saying is only true for statement-based replication, in which InnoDB will add a gap-lock?

A read-committed level will delete new cache entries. A range based delete will only lock the records that exist and still allow new cache to be inserted. #2238203: DatabaseCacheBackend/CacheBackendInterface get method should have timeout should stop existing records in the process of being deleted from undergoing a long lock timeout (which I'm working on test cases for at the moment).

From the perspective of Drupal 8 (that only has tag-based mass deletion), what's actually desirable is to delete all the entries matching the tag *and* prevent new entries from being created.

From the perspective of Drupal 7, it seems desirable to delete any entries matching the wildcard *and* prevent new entries matching the wildcard from being created. Of course, that last one is not possible with standard SQL tools, but that what sounds desirable from a transactional perspective.

So, as a second best, locking the whole table doesn't sound too bad from this perspective.

@mikeytown2:

Locking the table is an awful idea when you have a lot of action on a site; that's the reason for using InnoDB, row based locks instead of table based.

The burden of proof bears on you. If you have a full wildcard flushing of a cache bin, it's desirables that it blocks further inserts in the table until the transaction is committed. See #23.

If it's a performance problem, the solution is to not do such full wildcard flushing...

I'll work on a patch monday that does the truncate of the spare cache table in a shutdown request and I'll make sure the spare table is empty before promoting it to be the cache bin.

That will make the cache non-transactional, which is an horrible idea.

Drupal barely supports concurrency properly right now. The only thing that saves us from horrible inconsistencies is that the default cache implementation is transactional. Start using memcache as the field cache, for example, and everything falls apart. See #1679344: Race condition in node_save() when not using DB for cache_field.

I think in many ways you are looking at the wrong end of the problem. Look at why/where those cache flushings are done and fix them.

Btw. TRUNCATE itself is implemented in that way (rename, create like, drop, rename) and had bugs before MySQL 5.5 (still buggy in 5.1):

http://bugs.mysql.com/bug.php?id=42643

I thought about the whole patch again and it totally makes sense.

It is pretty simple to get into a performance situation with this during a revert of field_instances. In show processlist you see:

TRUNCATE cache_field
1
2
TRUNCATE cache_token
1
2

So it takes up to 2s to execute the truncate. The reason is the global metadata lock, because there is new SELECTS going on during the TRUNCATE.

This patch and the rename tricks solves it because:

The 2nd TRUNCATE on the table__truncated will be _fast_ as nothing is trying to select from it (besides other processes doing a count query in the same function), but because it is fast, this should still improve things very much.

And we know that consistency is not a problem with the cache here, so it is okay if INSERTS already scheduled fail.

The only thing I don't get is the !inTransaction() part.

Would that not mean that the rename is called but the table __truncated could not exist, yet?

If the first to hit this code path would be in a transcation? (unlikely, but possible)

Because I think this double-buffers the table, right?

EDIT:

I think it should then look like:

if (inTransaction()) {
  $sql = TRUNCATE TABLE
} else {
  // Do the rename
}

EDIT 2:

What I outlined above is basically single-threaded, that means I had no concurrency issues, which means that the TRUNCATE was running in the background anyway. Which might mean it has no effect on the run time performance. I am gonna check that.

I see now, we also had query cache lock problems.

A co-worker pointed out also that SELECT * from TABLE LIMIT 1 should return 0 when empty and something else when not and that count can be a performance bottleneck by itself.

JFYI

The patch from #26 helped us a lot. How can we get some more eyes on this? I'm looking forward to see this patch in a future D7/D8 release! Great work!

Not totally sure if this is the right place for this, but this seemed the most relevant of all the related threads:

About three weeks ago, began to experience seemingly random site crashes. Looking through the logs, we saw PDOException errors, and realized we had a database lockup. Kicking the database server restored the site, but did not, of course, eliminate the problem. Over the next couple weeks, we installed a number of patches to Drupal, and made some changes to our database set up. The number of PDOException errors dramatically decreased, but we still had intermittent site failures.

We did everything on this list: "Fixes for MySQL Deadlocks in D7" (https://groups.drupal.org/node/415883) and also applied the patch from this thread: https://www.drupal.org/node/930876.

* Use READ COMMITTED for MySQL transactions
* Do not use InnoDB for the semaphore table, use Memory
* Deadlocks occur in cache_field table (patch to modules/field/field.info.class.inc and modules/field/field.info.inc)
* Use asynchronous MySQLi connection to avoid RECORD LOCKS (Deadlocks) on cache tables (patch to includes/cache.inc and includes/database/mysql/database.inc)
* PDOException: SQLSTATE[08004] [1040] Too many connections in lock_may_be_available() (patch to lock.inc)
* Waiting for table metadata lock on cache_field table (patch to includes/database/query.inc)

Having eliminated most of the other PDOExceptions we were able to see that the exact sequence of events that led to the site going down was the generation of some PDOException and *then* clearing the cache.

That last patch to the query.inc file is intended to help fix the issue of the website locking up in the middle of a cache clear. But applying that patch did not solve our issue. Clearing cache alone does not drop the site. However, we have another (related or not, no idea) issue where you cannot unblock a user using the admin UI. Attempting to unblock a user throws a PDO Exception - and if you clear cache after that, the site goes down. (If you use drush first, it works. If you attempt in the UI first, it won't work in the UI or in Drush.)

Here are the two errors, drush and from the dlog:

***

root@CNS0904:/var/www# drush uublk dsherman
WD user: PDOException: SQLSTATE[HY000]: General error: 1205 Lock wait[error]
timeout exceeded; try restarting transaction: DELETE FROM
{cache_entity_user}
WHERE (cid IN (:db_condition_placeholder_0)) ; Array
(
[:db_condition_placeholder_0] => 1252
)
in cache_clear_all() (line 165 of /var/www/includes/cache.inc).
WD php: PDOException: SQLSTATE[HY000]: General error: 1205 Lock wait [error]
timeout exceeded; try restarting transaction: DELETE FROM
{cache_entity_user}
WHERE (cid IN (:db_condition_placeholder_0)) ; Array
(
[:db_condition_placeholder_0] => 1252
)
in cache_clear_all() (line 165 of /var/www/includes/cache.inc).
PDOException: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction: DELETE FROM {cache_entity_user}
WHERE (cid IN (:db_condition_placeholder_0)) ; Array
(
[:db_condition_placeholder_0] => 1252
)
in cache_clear_all() (line 165 of /var/www/includes/cache.inc).
Drush command terminated abnormally due to an unrecoverable error. [error]

***

Type php
Date Monday, November 3, 2014 - 6:45am
User Anonymous (not verified)
Location http://default/index.php
Referrer
Message PDOException: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction: DELETE FROM {cache_entity_user}

WHERE (cid IN (:db_condition_placeholder_0)) ; Array ( [:db_condition_placeholder_0] => 1252 ) in cache_clear_all() (line 165 of /var/www/includes/cache.inc).
Severity error
Hostname 127.0.0.1

***

Once the site went down after those errors and a clear cache, I restarted the database server and the site came back up. Went to drush and was able to unblock the user successfully.

So:

* Need to know why using the UI to unblock a user throws that error. Searching on the error leads to all the fixes listed above.
* I think I have an idea of why the database locks up when we clear cache, but why have all the previous fixes and patches not prevented it from going down in this case?
* And what changed three weeks ago such that PDOExceptions suddenly started locking up the database and killing the site? Our logs show random PDOExceptions as far back as we've looked, but we weren't dropping the site.

Other relevant information:

* We updated to 7.31 on the 10/16, and saw the first site crash on the 21st. (That could be because no one clicked the clear cache button, possibly.)
* We installed the SQL Injection vulnerability patch (not the whole 7.32 update, just the patch), also on the 16th.
* The production site is load-balanced, two servers synced with unison. PDO errors were mostly on the primary but all the cache errors were on the secondary.
* We have been completely unable to replicate the error on any of our development servers. (Not load balanced, but otherwise pretty close to production.)
* Site sits on Ubuntu 14.04 lts, and the mysql server is version: 5.5.40-0ubuntu0.14.04.1 (Ubuntu)

Any guidance would be greatly appreciated.

@mikeytown2

Thanks for replying so quickly. We're talking to our client to arrange bringing the site down for a few minutes to gather the information you requested. That will happen sometime later today, and we'll post our findings as soon as we have them.

@mikeytown2

We captured the information you asked about, attached.

@mikeytown2, thanks

Just to clarify in my own head - the first transaction is coming from your truncate patch, correct? Also, are you saying that this thread 188 is a sort of zombie thread that has been causing our deadlocks all along, or just in this particular case? (And by extension, is killing this thread the solution, or just treating a symptom?)

We've been rebooting the mysql server whenever it deadlocks - this brings our site back, and I would have thought that it would have killed any threads, because reboot. Of course, I know next to nothing about mysql.

Wow, thanks mikeytown2 for a _lot_ of background information here.

mikeytown2, thanks for all the information so far. So, we now have a manual option for restoring database function with the kill thread, so we don't have to reboot the database server every time we get a deadlock. However, manually performing that is not a long term plan, especially as we've asked our clients to refrain from clicking on the clear cache button or modifying user accounts until this is resolved.

We're planning on triggering a deadlock intentionally again tomorrow morning, with the query logs turned on. Is there any other information that we should gather that would be helpful in diagnosing the actual cause of the deadlocks?

You've mentioned adjusting the innodb_lock_wait_timeout from the 2 seconds, and the wait_timeout variable from 90 - which of these do you recommend, and why? If the problem is, as you suggest, in drupal, what can we do to stop the the user modify / cache clear / deadlock situation?

@mikeytown2:

This morning we took down the site again, and gathered yet more info, attached. (The two query files are the results of your queries in the previous post. The two process list files are from during the deadlock, before and after killing thread 61. Qyery 61 is an excerpt from the query log for thread 61. If you need more of the query log, just say so, we have over 500MB for the five minutes we had the query logging turned on.) Also, we watched closely what was happening in phpmyadmin as the site went down. So, we knew before we saw the results of your custom queries that thread 61 was the initial problem. We can take the site down - cause a deadlock in the database by clicking "save" on a user edit page, and then clearing the cache. This morning, as soon as I clicked save, thread 61 appeared and immediately went to sleep / null:

61 dbuser xxx.xx.xxx.xxx:38286 database Sleep 448 NULL

We waited about 30 seconds, then clicked the clear cache button. Within a couple seconds, thread 112 collided with thread 61.

112 dbuser xxx.xx.xxx.xxx:38292 database Query 393 Waiting for table metadata lock RENAME TABLE cache_entity_user TO cache_entity_use...

And then we had the big pile up of threads "Waiting for table metadata lock " right after it:

185	dbuser	xxx.xx.xxx.xxx:49720	database	Query	377	Waiting for table metadata lock	SELECT cid, data, created, expire, serialized FROM...
186	dbuser	xxx.xx.xxx.xxx:49721	database	Query	377	Waiting for table metadata lock	SELECT cid, data, created, expire, serialized FROM...
187	dbuser	xxx.xx.xxx.xxx:49722	database	Query	378	Waiting for table metadata lock	SELECT cid, data, created, expire, serialized FROM...
188	dbuser	xxx.xx.xxx.xxx:49723	database	Query	377	Waiting for table metadata lock	SELECT cid, data, created, expire, serialized FROM...

etc.

We waited until everything was fully locked up, and then killed thread 61. We saw the results in "show_process_list_2" for a few seconds, and then it all cleared up and the site was back to normal.

Next, we put in place the changes to settings.php and created a new module, and installed/enabled that. Ran the test again with results that were functionally identical to the above, just with different thread ID numbers.

Looking back over the last couple weeks, the only things of note that happened before the site started going down were:

* Updating core from 7.28 to 7.31 (five days previous)
* Ubuntu updates to libmysqlclient18 mysql-client-5.5 mysql-client-core-5.5 mysql-common mysql-server mysql-server-5.5 mysql-server-core-5.5 (five days previous)
* Ubuntu updates to libssl1.0.0 openssl (one day previous)

I haven't seen any issues related to being unable to save changes to users. So maybe I need to post that as a new bug report - but the problem certainly feels related to all these other PDOException type issues.

We waited almost 400 seconds in the second test, after we installed the patched settings.php and module, we wanted to be sure we gave it a chance to work.

Glad we could help testing your queries, just wish we didn't have the opportunity. We'll look more into field_attach_update, and thanks once more for your help with this.

I would strongly suggest to not use entitycache module with DB cache backend.

Do you have the option to have at least that bin in memcache or redis?

I see in #48 something very similar to what I am having now in D7, described here more
http://drupal.stackexchange.com/questions/137979/user-import-from-other-...
maybe it is somehow related so my problem could bring some light to it.

Thanx Mikey. I'll just add that my issue was with something else. I hade a cURL connection that was slow and it made site timeout. Sorry for this.

Somewhat related: #2347867: Race conditions with lock/cache using non-database storage - add a non-transactional database connection

Belated update:

@fabienx: We uninstalled the entitycache module, and attempted to recreate the error. The error occurred anyway. We inherited this site from another company, and the cache modules like many other aspects of the site were apparently poorly thought out. The site is now consolidated on default drupal cache, and all the various contrib cache modules are disabled.

@mikeytown2: PHP version is:

PHP 5.5.9-lubuntu4.5 (cli) (built: Oct 29 2014 11:59:10)
Zend Engine v2.5.0 with Zend OPcache v7.0.3

We waited until our weekly maintenance window to do some more testing, seeing as the site was up so long as no one clicked on the save user button. What we found was strange - previously, we had been completely unable to replicate the error in any of our other environments, despite our best efforts to make those other environments match production as closely as possible.

The one difference that was most difficult to replicate was the fact that our production environment is load-balanced, with two servers hitting the same database, and files replicated between the two servers with unison. Outside users would hit one of the two servers randomly, but all administrative functions went through just one, the primary (server 9). We'd checked, and the pdo errors all occurred on server 9, which you'd expect since that's where the user save and cache clear would originate.

So, we rewired things a bit so that the secondary server (server 10) could be used for drupal admin as well. Since all the files and settings are constantly synced from 9 to 10, we expected that when we hit the save user then cleared cache, we'd get the database lockup. But we didn't. Hitting the same database as server 10, we could not recreate the database lockup at all. So far as we can determine, all settings - php, mysql, apache, ubuntu, drupal, whatever - were identical between the two servers.

As best we can now figure, there is some obscure difference, some setting, on server 9 that uniquely causes the error condition. Some research showed that server 9 was built by a different individual than server 10, but aside from that, we have no clue as to what the difference might be.

Having isolated the issue to one server - we made the decision to just blow the server away, and rebuild using the same procedure as we used for server 10, then restore the site. Since that time, we have not experienced any issues with PDO errors, or anything else. Our db performance is significantly better, though, thanks to all the tuning patches from mikeytown2.

Thanks to everyone for all their help with this issue - though it seems at this point that we'll not have any answer for why, exactly, the error was happening. The various patches that we applied were essential, though, in narrowing down the issue - without clearing away many of the errors we were seeing through the successive patches and without the sql scripts that allowed us to watch what was happening on the sql server in real time, it would have been much harder if not impossible to see that the issue was coming from that one server.

From patch in 26 I am experiencing a number of these __truncate tables not being defined in the database for Drupal 7:
cache__truncate
cache_views__truncate
cache_variable__truncate
cache_rules__truncate
cache_libraries__truncate
cache_field__truncate

If you uninstall a module that has a truncated table, the truncated table does not get uninstalled. Shouldn't it?

So I'm a little late to this issue. There are some known issues w.r.t. MySQL:
- 5.6 and above has a 'fast' DROP TABLE (but not truncate)
- 5.7 and above has a 'fast' TRUNCATE

See this MySQL bug for more details. I am simplifying 'fast' a little, but essentially with a large buffer pool MySQL can take some time to remove the pages from the deleted table. The new approach is to do this in a lazy way.

Drupal supports MySQL 5.5+ so that technically means neither statement is safe for large buffer pools.

I think this problem will be fixed in #2684359: The truncate method is does different things on different databases and depending if in a transation.

Drupal requires MySQL 5.7 now, so I believe this is resolved by https://bugs.mysql.com/bug.php?id=68184

Closing as outdated.

#2684359: The truncate method is does different things on different databases and depending if in a transation is still open for some of the other issues.