Bug #42643 InnoDB does not support replication of TRUNCATE TABLE
Submitted: 6 Feb 11:28
Reporter: Mats Kindahl
Status: Verified
Category:Server: InnoDB Severity:S2 (Serious)
Version:5.1 OS:Any
Assigned to: Marko Mäkelä Target Version:5.4+
Tags: disabled
Triage: Triaged: D2 (Serious)

[6 Feb 11:28] Mats Kindahl 
Description:
TRUNCATE TABLE is a DDL roughly equivalent to the following sequence of statements:

    LOCK TABLE t1 WRITE;
    CREATE TEMPORARY TABLE __tt1 LIKE t1;
    DROP TABLE t1;
    CREATE TABLE t1 LIKE __tt1;
    DROP TABLE __tt1;
    UNLOCK TABLES;

However, InnoDB implements this in a way so that the statement is not supported under
transaction isolation level READ COMMITTED and READ UNCOMMITTED, which gives an error
when executed under BINLOG_MODE=STATEMENT.

However, according to comment by Heikki in BUG#36763, TRUNCATE TABLE is not safe for
replication at all, and should therefore not be allowed to replicate by the InnoDB
storage engine.

How to repeat:
CREATE TABLE t1 (a INT) ENGINE=INNODB;
SET BINLOG_FORMAT=STATEMENT;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
TRUNCATE TABLE t1;

Suggested fix:
Block TRUNCATE TABLE until any INSERT threads have finished executing their inserts or,
alternatively, abort any threads executing INSERT and then execute the TRUNCATE TABLE.
[25 Feb 19:42] Michael Izioumtchenko 
assigning to Marko who worked on bug#36763. I suspect the proper idea 
would be to wait until this problem resolves itself when we have transactional
DDL. regarding

Suggested fix:
Block TRUNCATE TABLE until any INSERT threads have finished executing their inserts 

### this would violate the spirit of TRUNCATE being a lightweight form of DELETE

alternatively, abort any threads executing INSERT and then execute the TRUNCATE TABLE.

### this is better, could probably be implemented without the 'then' part. Existing DML
threads on the table would just get an error like 'sorry, the table is being truncated'
and rollback, the replication would sort it out somehow. But this is obviously more work.
Could probably be done by synchronizing TRUNCATE and transaction ending statements, feels
like transactional DDL again.
[27 Mar 14:56] Michael Izioumtchenko 
see also bug#43832