MySQL running delete trigger on truncate

Question

I have a table that has an on delete trigger that updates another table. THe table I'm trying to truncate has 130,000 rows. I have duplicate schema and data on my dev server as well as the client's deployment stating server. (5,5,49)

On my dev server (5.5.29) when I truncate the table, it's instant, as in by the time I type show full processlist, the truncate is finished.

When I run the same truncate on the staging server I see this in the process list:

| 3377 | root | localhost:46410 | database | Query   |  533 | Sending data                    | DELETE FROM PriceCode44      |
| 3378 | root | localhost:46800 | database | Query   |  521 | Waiting for table metadata lock | truncate table `PriceCode44` |

There are no foreign keys in the database related to this table.

My solution was to drop the table and recreate it, but I'm concerned that at some point the client is going to need to truncate the table, and since the delete trigger is updating a lot of data it takes a long time.

What could be the possibly cause of this, and how can I avoid it?

Adding if it's not clear - the documented truncate behavior is the desired behavior. I just want to whack the data in the table.

Here is the create table:

CREATE TABLE `InsynchPriceCode44` (
  `PriceCodeRecord` varchar(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `PriceCode` varchar(4) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `ItemCode` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `CustomerPriceLevel` varchar(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `ARDivisionNo` varchar(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `CustomerNo` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `Description` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Method` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL,
  `BreakQty1` decimal(9,0) DEFAULT NULL,
  `BreakQty2` decimal(9,0) DEFAULT NULL,
  `BreakQty3` decimal(9,0) DEFAULT NULL,
  `BreakQty4` decimal(9,0) DEFAULT NULL,
  `BreakQty5` decimal(9,0) DEFAULT NULL,
  `DiscountMarkupPriceRate1` decimal(12,3) DEFAULT NULL,
  `DiscountMarkupPriceRate2` decimal(12,3) DEFAULT NULL,
  `DiscountMarkupPriceRate3` decimal(12,3) DEFAULT NULL,
  `DiscountMarkupPriceRate4` decimal(12,3) DEFAULT NULL,
  `DiscountMarkupPriceRate5` decimal(12,3) DEFAULT NULL,
  PRIMARY KEY (`PriceCodeRecord`,`PriceCode`,`ItemCode`,`CustomerPriceLevel`,`ARDivisionNo`,`CustomerNo`),
  KEY `PriceCodeRecord_2` (`PriceCodeRecord`,`PriceCode`,`CustomerPriceLevel`) USING BTREE,
  KEY `PriceCodeRecord_3` (`PriceCodeRecord`,`ItemCode`,`CustomerPriceLevel`) USING BTREE,
  KEY `PriceCodeRecord` (`PriceCodeRecord`,`ItemCode`,`CustomerNo`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT

This is identical to my dev server.

Answer 1

TRUNCATE does not fire ON DELETE triggers in any 5.x version. But it does have to lock the table and have to wait for any other open transactions that hold locks on the table to release them.

That's what the process list is showing. There is a (transaction with a) delete query that is running and has some lock on the table and is blocking the TRUNCATE (which is Waiting for table metadata lock). As soon as the other transaction commits (or rolls back) and the lock is released, TRUNCATE will manage to get the metadata lock and continue.

---

There are some differences across the minor 5.x versions regarding the existence of any foreign key constraints that reference the table that affect how TRUNCATE is processed. See this question “TRUNCATE doesn't run ON DELETE trigger” true or false for details but this seems irrelevant in your case, since you are on 5.5, where the TRUNCATE would raise an error if there were any foreign key references from other tables.

---

On relevant matters, you should upgrade to the latest 5.5 version (5.5.49). Your servers are 4 years and 22 releases behind the latest 5.5 version.

Since you edited that the staging server is 5.5.49 (that's good!) it would be best to update your dev servers to the same version.

I wouldn't entirely dismiss the possibility of a bug but we'll need more evidence to be convinced, like the output of SHOW ENGINE INNODB STATUS; when the issue appears (the Transactions part would show relevant info about locks and blocking).