Bug#49938: Failing assertion: inode or deadlock in fsp/fsp0fsp.c

Bug#54678: InnoDB, TRUNCATE, ALTER, I_S SELECT, crash or deadlock

- Incompatible change: truncate no longer resorts to a row by
row delete if the storage engine does not support the truncate
method. Consequently, the count of affected rows does not, in
any case, reflect the actual number of rows.

- Incompatible change: it is no longer possible to truncate a
table that participates as a parent in a foreign key constraint,
unless it is a self-referencing constraint (both parent and child
are in the same table). To work around this incompatible change
and still be able to truncate such tables, disable foreign checks
with SET foreign_key_checks=0 before truncate. Alternatively, if
foreign key checks are necessary, please use a DELETE statement
without a WHERE condition.

Problem description:

The problem was that for storage engines that do not support
truncate table via a external drop and recreate, such as InnoDB
which implements truncate via a internal drop and recreate, the
delete_all_rows method could be invoked with a shared metadata
lock, causing problems if the engine needed exclusive access
to some internal metadata. This problem originated with the
fact that there is no truncate specific handler method, which
ended up leading to a abuse of the delete_all_rows method that
is primarily used for delete operations without a condition.

Solution:

The solution is to introduce a truncate handler method that is
invoked when the engine does not support truncation via a table
drop and recreate. This method is invoked under a exclusive
metadata lock, so that there is only a single instance of the
table when the method is invoked.

Also, the method is not invoked and a error is thrown if
the table is a parent in a non-self-referencing foreign key
relationship. This was necessary to avoid inconsistency as
some integrity checks are bypassed. This is inline with the
fact that truncate is primarily a DDL operation that was
designed to quickly remove all data from a table.

mysql-test/suite/innodb/t/innodb-truncate.test:
  Add test cases for truncate and foreign key checks.
  Also test that InnoDB resets auto-increment on truncate.
mysql-test/suite/innodb/t/innodb.test:
  FK is not necessary, test is related to auto-increment.
  
  Update error number, truncate is no longer invoked if
  table is parent in a FK relationship.
mysql-test/suite/innodb/t/innodb_mysql.test:
  Update error number, truncate is no longer invoked if
  table is parent in a FK relationship.
  
  Use delete instead of truncate, test is used to check
  the interaction of FKs, triggers and delete.
mysql-test/suite/parts/inc/partition_check.inc:
  Fix typo.
mysql-test/suite/sys_vars/t/foreign_key_checks_func.test:
  Update error number, truncate is no longer invoked if
  table is parent in a FK relationship.
mysql-test/t/mdl_sync.test:
  Modify test case to reflect and ensure that truncate takes
  a exclusive metadata lock.
mysql-test/t/trigger-trans.test:
  Update error number, truncate is no longer invoked if
  table is parent in a FK relationship.
sql/ha_partition.cc:
  Reorganize the various truncate methods. delete_all_rows is now
  passed directly to the underlying engines, so as truncate. The
  code responsible for truncating individual partitions is moved
  to ha_partition::truncate_partition, which is invoked when a
  ALTER TABLE t1 TRUNCATE PARTITION p statement is executed.
  
  Since the partition truncate no longer can be invoked via
  delete, the bitmap operations are not necessary anymore. The
  explicit reset of the auto-increment value is also removed
  as the underlying engines are now responsible for reseting
  the value.
sql/handler.cc:
  Wire up the handler truncate method.
sql/handler.h:
  Introduce and document the truncate handler method. It assumes
  certain use cases of delete_all_rows.
  
  Add method to retrieve the list of foreign keys referencing a
  table. Method is used to avoid truncating tables that are
  parent in a foreign key relationship.
sql/share/errmsg-utf8.txt:
  Add error message for truncate and FK.
sql/sql_lex.h:
  Introduce a flag so that the partition engine can detect when
  a partition is being truncated. Used to give a special error.
sql/sql_parse.cc:
  Function mysql_truncate_table no longer exists.
sql/sql_partition_admin.cc:
  Implement the TRUNCATE PARTITION statement.
sql/sql_truncate.cc:
  Change the truncate table implementation to use the new truncate
  handler method and to not rely on row-by-row delete anymore.
  
  The truncate handler method is always invoked with a exclusive
  metadata lock. Also, it is no longer possible to truncate a
  table that is parent in some non-self-referencing foreign key.
storage/archive/ha_archive.cc:
  Rename method as the description indicates that in the future
  this could be a truncate operation.
storage/blackhole/ha_blackhole.cc:
  Implement truncate as no operation for the blackhole engine in
  order to remain compatible with older releases.
storage/federated/ha_federated.cc:
  Introduce truncate method that invokes delete_all_rows.
  This is required to support partition truncate as this
  form of truncate does not implement the drop and recreate
  protocol.
storage/heap/ha_heap.cc:
  Introduce truncate method that invokes delete_all_rows.
  This is required to support partition truncate as this
  form of truncate does not implement the drop and recreate
  protocol.
storage/ibmdb2i/ha_ibmdb2i.cc:
  Introduce truncate method that invokes delete_all_rows.
  This is required to support partition truncate as this
  form of truncate does not implement the drop and recreate
  protocol.
storage/innobase/handler/ha_innodb.cc:
  Rename delete_all_rows to truncate. InnoDB now does truncate
  under a exclusive metadata lock.
  
  Introduce and reorganize methods used to retrieve the list
  of foreign keys referenced by a or referencing a table.
storage/myisammrg/ha_myisammrg.cc:
  Introduce truncate method that invokes delete_all_rows.
  This is required in order to remain compatible with earlier
  releases where truncate would resort to a row-by-row delete.

15 files changed, 238 insertions, 50 deletions

diff --git a/mysql-test/r/mdl_sync.result b/mysql-test/r/mdl_sync.result
index de57e3859b7..d2a32c25201 100644
--- a/mysql-test/r/mdl_sync.result
+++ b/mysql-test/r/mdl_sync.result
@@ -2632,7 +2632,8 @@ DROP TABLE IF EXISTS t1;
 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
 INSERT INTO t1 VALUES (1),(2),(3);
 # Connection: con1
-SET debug_sync='lock_table_for_truncate SIGNAL parked_truncate WAIT_FOR go_truncate';
+LOCK TABLES t1 WRITE;
+SET debug_sync='upgrade_lock_for_truncate SIGNAL parked_truncate WAIT_FOR go_truncate';
 TRUNCATE TABLE t1;
 # Connection: default
 SET debug_sync='now WAIT_FOR parked_truncate';
@@ -2647,10 +2648,11 @@ FLUSH TABLES t1;
 # Connection: default
 SET debug_sync='now WAIT_FOR parked_flush';
 SET debug_sync='now SIGNAL go_truncate';
-# Connection: con1
-# Reaping...
-# Connection: default
+# Ensure that truncate waits for a exclusive lock
 SET debug_sync= 'now SIGNAL go_show';
+# Connection: con1 (TRUNCATE)
+# Reaping...
+UNLOCK TABLES;
 # Connection: con2 (SHOW FIELDS FROM t1)
 # Reaping...
 Field	Type	Null	Key	Default	Extra
diff --git a/mysql-test/r/sp_trans.result b/mysql-test/r/sp_trans.result
index 4fa91121f50..4163725a196 100644
--- a/mysql-test/r/sp_trans.result
+++ b/mysql-test/r/sp_trans.result
@@ -585,3 +585,20 @@ UPDATE t1_aux SET f2 = 2 WHERE f1 = f1_two_inserts()|
 ERROR 23000: Column 'f2' cannot be null
 DROP TABLE t1_aux, t1_not_null|
 DROP FUNCTION f1_two_inserts|
+#
+# Bug#49938: Failing assertion: inode or deadlock in fsp/fsp0fsp.c
+#
+DROP PROCEDURE IF EXISTS p1|
+DROP TABLE IF EXISTS t1|
+CREATE TABLE t1 (a INT) ENGINE=INNODB|
+CREATE PROCEDURE p1()
+BEGIN
+TRUNCATE TABLE t1;
+END|
+LOCK TABLES t1 WRITE|
+CALL p1()|
+FLUSH TABLES;
+UNLOCK TABLES|
+CALL p1()|
+DROP PROCEDURE p1|
+DROP TABLE t1|
diff --git a/mysql-test/r/trigger-trans.result b/mysql-test/r/trigger-trans.result
index 2c4e355af9d..722ac79854d 100644
--- a/mysql-test/r/trigger-trans.result
+++ b/mysql-test/r/trigger-trans.result
@@ -151,9 +151,14 @@ CREATE TRIGGER t1_ad AFTER DELETE ON t1 FOR EACH ROW SET @b = 1;
 SET @a = 0;
 SET @b = 0;
 TRUNCATE t1;
+ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `test`.`t1` (`a`))
 SELECT @a, @b;
 @a	@b
 0	0
+DELETE FROM t1;
+SELECT @a, @b;
+@a	@b
+1	1
 INSERT INTO t1 VALUES (1);
 DELETE FROM t1;
 SELECT @a, @b;
diff --git a/mysql-test/suite/innodb/r/innodb-truncate.result b/mysql-test/suite/innodb/r/innodb-truncate.result
new file mode 100644
index 00000000000..f63e9272850
--- /dev/null
+++ b/mysql-test/suite/innodb/r/innodb-truncate.result
@@ -0,0 +1,68 @@
+#
+# TRUNCATE TABLE
+#
+# Truncating is disallowed for parent tables unless such table
+# participates in self-referencing foreign keys only.
+#
+CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=INNODB;
+CREATE TABLE t2 (fk INT NOT NULL, FOREIGN KEY (fk) REFERENCES t1 (pk)) ENGINE=INNODB;
+TRUNCATE TABLE t1;
+ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `test`.`t1` (`pk`))
+# Truncation of child should succeed.
+TRUNCATE TABLE t2;
+DROP TABLE t2;
+DROP TABLE t1;
+CREATE TABLE t1 (pk INT PRIMARY KEY, fk INT,
+FOREIGN KEY (fk) REFERENCES t1 (pk)) ENGINE=INNODB;
+# Truncation of self-referencing table should succeed.
+TRUNCATE TABLE t1;
+DROP TABLE t1;
+#
+# Also, truncating such tables is allowed if foreign key
+# checks are disabled.
+#
+SET @old_foreign_key_checks = @@SESSION.foreign_key_checks;
+CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=INNODB;
+CREATE TABLE t2 (fk INT NOT NULL, FOREIGN KEY (fk) REFERENCES t1 (pk)) ENGINE=INNODB;
+CREATE TABLE t3 (pk INT PRIMARY KEY, fk INT,
+FOREIGN KEY (fk) REFERENCES t1 (pk)) ENGINE=INNODB;
+SET @@SESSION.foreign_key_checks = 0;
+TRUNCATE TABLE t1;
+TRUNCATE TABLE t2;
+TRUNCATE TABLE t3;
+SET @@SESSION.foreign_key_checks = 1;
+TRUNCATE TABLE t1;
+ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `test`.`t1` (`pk`))
+TRUNCATE TABLE t2;
+TRUNCATE TABLE t3;
+LOCK TABLES t1 WRITE;
+SET @@SESSION.foreign_key_checks = 0;
+TRUNCATE TABLE t1;
+SET @@SESSION.foreign_key_checks = 1;
+TRUNCATE TABLE t1;
+ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `test`.`t1` (`pk`))
+UNLOCK TABLES;
+DROP TABLE t3,t2,t1;
+SET @@SESSION.foreign_key_checks = @old_foreign_key_checks;
+#
+# Test that TRUNCATE resets auto-increment.
+#
+CREATE TABLE t1 (a INT PRIMARY KEY NOT NULL AUTO_INCREMENT);
+INSERT INTO t1 VALUES (NULL), (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't1';
+AUTO_INCREMENT
+3
+SELECT * FROM t1 ORDER BY a;
+a
+1
+2
+TRUNCATE TABLE t1;
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't1';
+AUTO_INCREMENT
+1
+INSERT INTO t1 VALUES (NULL), (NULL);
+SELECT * FROM t1 ORDER BY a;
+a
+1
+2
+DROP TABLE t1;
diff --git a/mysql-test/suite/innodb/r/innodb.result b/mysql-test/suite/innodb/r/innodb.result
index 2e5585ee7af..09a2c5b9ef4 100644
--- a/mysql-test/suite/innodb/r/innodb.result
+++ b/mysql-test/suite/innodb/r/innodb.result
@@ -2424,10 +2424,6 @@ drop table t1,t2;
 CREATE TABLE t1 (
 id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)
 ) ENGINE=InnoDB;
-CREATE TABLE t2 (
-id INTEGER NOT NULL,
-FOREIGN KEY (id) REFERENCES t1 (id)
-) ENGINE=InnoDB;
 INSERT INTO t1 (id) VALUES (NULL);
 SELECT * FROM t1;
 id
@@ -2443,7 +2439,7 @@ INSERT INTO t1 (id) VALUES (NULL);
 SELECT * FROM t1;
 id
 1
-DROP TABLE t2, t1;
+DROP TABLE t1;
 CREATE TABLE t1
 (
 id INT PRIMARY KEY
@@ -2621,13 +2617,15 @@ ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fail
 update t4 set a=2;
 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t3` (`a`))
 truncate t1;
-ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`))
+ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `test`.`t1` (`a`))
 truncate t3;
-ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t3` (`a`))
+ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `test`.`t3` (`a`))
 truncate t2;
 truncate t4;
 truncate t1;
+ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `test`.`t1` (`a`))
 truncate t3;
+ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `test`.`t3` (`a`))
 drop table t4,t3,t2,t1;
 create table t1 (a varchar(255) character set utf8,
 b varchar(255) character set utf8,
diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result
index 14eb3f0a6a8..5a52ba0ee54 100644
--- a/mysql-test/suite/innodb/r/innodb_mysql.result
+++ b/mysql-test/suite/innodb/r/innodb_mysql.result
@@ -1941,7 +1941,7 @@ INSERT INTO t2 VALUES (3,2);
 SET AUTOCOMMIT = 0;
 START TRANSACTION;
 TRUNCATE TABLE t1;
-ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`))
+ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `test`.`t1` (`id`))
 SELECT * FROM t1;
 id
 1
@@ -1953,7 +1953,7 @@ id
 2
 START TRANSACTION;
 TRUNCATE TABLE t1;
-ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`))
+ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `test`.`t1` (`id`))
 SELECT * FROM t1;
 id
 1
@@ -1971,7 +1971,7 @@ id
 2
 COMMIT;
 TRUNCATE TABLE t1;
-ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`))
+ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `test`.`t1` (`id`))
 SELECT * FROM t1;
 id
 1
@@ -1983,9 +1983,12 @@ id
 1
 2
 TRUNCATE TABLE t1;
+ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `test`.`t1` (`id`))
 ROLLBACK;
 SELECT * FROM t1;
 id
+1
+2
 TRUNCATE TABLE t2;
 DROP TABLE t2;
 DROP TABLE t1;
@@ -2077,9 +2080,9 @@ i	i
 ** error handling inside a row iteration.
 **
 DROP TRIGGER trg;
-TRUNCATE TABLE t1;
-TRUNCATE TABLE t2;
-TRUNCATE TABLE t3;
+DELETE FROM t1;
+DELETE FROM t2;
+DELETE FROM t3;
 INSERT INTO t1 VALUES (1),(2),(3),(4);
 INSERT INTO t3 VALUES (1),(2),(3),(4);
 INSERT INTO t4 VALUES (3,3),(4,4);
@@ -2105,9 +2108,9 @@ DROP TRIGGER trg;
 **
 ** Induce an error midway through an AFTER-trigger
 **
-TRUNCATE TABLE t4;
-TRUNCATE TABLE t1;
-TRUNCATE TABLE t3;
+DELETE FROM t4;
+DELETE FROM t1;
+DELETE FROM t3;
 INSERT INTO t1 VALUES (1),(2),(3),(4);
 INSERT INTO t3 VALUES (1),(2),(3),(4);
 CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW
diff --git a/mysql-test/suite/innodb/t/innodb-truncate.test b/mysql-test/suite/innodb/t/innodb-truncate.test
new file mode 100644
index 00000000000..7629eb1a980
--- /dev/null
+++ b/mysql-test/suite/innodb/t/innodb-truncate.test
@@ -0,0 +1,65 @@
+--source include/have_innodb.inc
+
+--echo #
+--echo # TRUNCATE TABLE
+--echo #
+--echo # Truncating is disallowed for parent tables unless such table
+--echo # participates in self-referencing foreign keys only.
+--echo #
+CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=INNODB;
+CREATE TABLE t2 (fk INT NOT NULL, FOREIGN KEY (fk) REFERENCES t1 (pk)) ENGINE=INNODB;
+--error ER_TRUNCATE_ILLEGAL_FK
+TRUNCATE TABLE t1;
+--echo # Truncation of child should succeed.
+TRUNCATE TABLE t2;
+DROP TABLE t2;
+DROP TABLE t1;
+CREATE TABLE t1 (pk INT PRIMARY KEY, fk INT,
+                 FOREIGN KEY (fk) REFERENCES t1 (pk)) ENGINE=INNODB;
+--echo # Truncation of self-referencing table should succeed.
+TRUNCATE TABLE t1;
+DROP TABLE t1;
+
+--echo #
+--echo # Also, truncating such tables is allowed if foreign key
+--echo # checks are disabled.
+--echo #
+
+SET @old_foreign_key_checks = @@SESSION.foreign_key_checks;
+CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=INNODB;
+CREATE TABLE t2 (fk INT NOT NULL, FOREIGN KEY (fk) REFERENCES t1 (pk)) ENGINE=INNODB;
+CREATE TABLE t3 (pk INT PRIMARY KEY, fk INT,
+                 FOREIGN KEY (fk) REFERENCES t1 (pk)) ENGINE=INNODB;
+SET @@SESSION.foreign_key_checks = 0;
+TRUNCATE TABLE t1;
+TRUNCATE TABLE t2;
+TRUNCATE TABLE t3;
+SET @@SESSION.foreign_key_checks = 1;
+--error ER_TRUNCATE_ILLEGAL_FK
+TRUNCATE TABLE t1;
+TRUNCATE TABLE t2;
+TRUNCATE TABLE t3;
+LOCK TABLES t1 WRITE;
+SET @@SESSION.foreign_key_checks = 0;
+TRUNCATE TABLE t1;
+SET @@SESSION.foreign_key_checks = 1;
+--error ER_TRUNCATE_ILLEGAL_FK
+TRUNCATE TABLE t1;
+UNLOCK TABLES;
+DROP TABLE t3,t2,t1;
+SET @@SESSION.foreign_key_checks = @old_foreign_key_checks;
+
+--echo #
+--echo # Test that TRUNCATE resets auto-increment.
+--echo #
+
+CREATE TABLE t1 (a INT PRIMARY KEY NOT NULL AUTO_INCREMENT);
+INSERT INTO t1 VALUES (NULL), (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't1';
+SELECT * FROM t1 ORDER BY a;
+TRUNCATE TABLE t1;
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't1';
+INSERT INTO t1 VALUES (NULL), (NULL);
+SELECT * FROM t1 ORDER BY a;
+DROP TABLE t1;
+
diff --git a/mysql-test/suite/innodb/t/innodb.test b/mysql-test/suite/innodb/t/innodb.test
index a283cd26ccb..8c24457e07c 100644
--- a/mysql-test/suite/innodb/t/innodb.test
+++ b/mysql-test/suite/innodb/t/innodb.test
@@ -1471,11 +1471,6 @@ CREATE TABLE t1 (
 id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)
 ) ENGINE=InnoDB;
 
-CREATE TABLE t2 (
-id INTEGER NOT NULL,
-FOREIGN KEY (id) REFERENCES t1 (id)
-) ENGINE=InnoDB;
-
 INSERT INTO t1 (id) VALUES (NULL);
 SELECT * FROM t1;
 TRUNCATE t1;
@@ -1488,7 +1483,7 @@ DELETE FROM t1;
 TRUNCATE t1;
 INSERT INTO t1 (id) VALUES (NULL);
 SELECT * FROM t1;
-DROP TABLE t2, t1;
+DROP TABLE t1;
 
 # Test that foreign keys in temporary tables are not accepted (bug #12084)
 CREATE TABLE t1
@@ -1723,13 +1718,15 @@ update t2 set a=2;
 update t3 set a=2;
 -- error 1452
 update t4 set a=2;
--- error 1451
+-- error ER_TRUNCATE_ILLEGAL_FK
 truncate t1;
--- error 1451
+-- error ER_TRUNCATE_ILLEGAL_FK
 truncate t3;
 truncate t2;
 truncate t4;
+-- error ER_TRUNCATE_ILLEGAL_FK
 truncate t1;
+-- error ER_TRUNCATE_ILLEGAL_FK
 truncate t3;
 
 drop table t4,t3,t2,t1;
diff --git a/mysql-test/suite/innodb/t/innodb_mysql.test b/mysql-test/suite/innodb/t/innodb_mysql.test
index 8925f538c38..052db7d789a 100644
--- a/mysql-test/suite/innodb/t/innodb_mysql.test
+++ b/mysql-test/suite/innodb/t/innodb_mysql.test
@@ -151,14 +151,14 @@ INSERT INTO t2 VALUES (3,2);
 SET AUTOCOMMIT = 0;
 
 START TRANSACTION;
---error ER_ROW_IS_REFERENCED_2
+--error ER_TRUNCATE_ILLEGAL_FK
 TRUNCATE TABLE t1;
 SELECT * FROM t1;
 COMMIT;
 SELECT * FROM t1;
 
 START TRANSACTION;
---error ER_ROW_IS_REFERENCED_2
+--error ER_TRUNCATE_ILLEGAL_FK
 TRUNCATE TABLE t1;
 SELECT * FROM t1;
 ROLLBACK;
@@ -170,13 +170,14 @@ START TRANSACTION;
 SELECT * FROM t1;
 COMMIT;
 
---error ER_ROW_IS_REFERENCED_2
+--error ER_TRUNCATE_ILLEGAL_FK
 TRUNCATE TABLE t1;
 SELECT * FROM t1;
 DELETE FROM t2 WHERE id = 3;
 
 START TRANSACTION;
 SELECT * FROM t1;
+--error ER_TRUNCATE_ILLEGAL_FK
 TRUNCATE TABLE t1;
 ROLLBACK;
 SELECT * FROM t1;
@@ -275,9 +276,9 @@ SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
 --echo ** error handling inside a row iteration.
 --echo **
 DROP TRIGGER trg;
-TRUNCATE TABLE t1;
-TRUNCATE TABLE t2;
-TRUNCATE TABLE t3;
+DELETE FROM t1;
+DELETE FROM t2;
+DELETE FROM t3;
 
 INSERT INTO t1 VALUES (1),(2),(3),(4);
 INSERT INTO t3 VALUES (1),(2),(3),(4);
@@ -304,9 +305,9 @@ DROP TRIGGER trg;
 --echo **
 --echo ** Induce an error midway through an AFTER-trigger
 --echo **
-TRUNCATE TABLE t4;
-TRUNCATE TABLE t1;
-TRUNCATE TABLE t3;
+DELETE FROM t4;
+DELETE FROM t1;
+DELETE FROM t3;
 INSERT INTO t1 VALUES (1),(2),(3),(4);
 INSERT INTO t3 VALUES (1),(2),(3),(4);
 delimiter ||;
diff --git a/mysql-test/suite/parts/inc/partition_check.inc b/mysql-test/suite/parts/inc/partition_check.inc
index 19d548cc8ef..235764a034f 100644
--- a/mysql-test/suite/parts/inc/partition_check.inc
+++ b/mysql-test/suite/parts/inc/partition_check.inc
@@ -177,7 +177,7 @@ let $any_unique= `SELECT @my_errno IN ($ER_DUP_KEY,$ER_DUP_ENTRY)`;
 #                   @my_errno AS sql_errno;
 if (`SELECT @my_errno NOT IN (0,$ER_DUP_KEY,$ER_DUP_ENTRY)`)
 {
-   --echo #      The last command got an unexepected error response.
+   --echo #      The last command got an unexpected error response.
    --echo #      Expected/handled SQL codes are 0,$ER_DUP_KEY,$ER_DUP_ENTRY
    SELECT '#      SQL code we got was: ' AS "", @my_errno AS "";
    --echo #      Sorry, have to abort.
@@ -219,7 +219,7 @@ if ($any_unique)
    #                   @my_errno AS sql_errno;
    if (`SELECT @my_errno NOT IN (0,$ER_DUP_KEY,$ER_DUP_ENTRY)`)
    {
-      --echo #      The last command got an unexepected error response.
+      --echo #      The last command got an unexpected error response.
       --echo #      Expected/handled SQL codes are 0,$ER_DUP_KEY,$ER_DUP_ENTRY
       SELECT '#      SQL code we got was: ' AS "", @my_errno AS "";
       --echo #      Sorry, have to abort.
@@ -255,7 +255,7 @@ if ($any_unique)
    #                   @my_errno AS sql_errno;
    if (`SELECT @my_errno NOT IN (0,$ER_DUP_KEY,$ER_DUP_ENTRY)`)
    {
-      --echo #      The last command got an unexepected error response.
+      --echo #      The last command got an unexpected error response.
       --echo #      Expected/handled SQL codes are 0,$ER_DUP_KEY,$ER_DUP_ENTRY
       SELECT '#      SQL code we got was: ' AS "", @my_errno AS "";
       --echo #      Sorry, have to abort.
@@ -503,7 +503,7 @@ if ($no_debug)
 eval SET @my_errno = $mysql_errno;
 if (`SELECT @my_errno NOT IN (0,$ER_SAME_NAME_PARTITION,$ER_NO_PARTITION_FOR_GIVEN_VALUE)`)
 {
-   --echo #      The last command got an unexepected error response.
+   --echo #      The last command got an unexpected error response.
    --echo #      Expected/handled SQL codes are 0,$ER_SAME_NAME_PARTITION,$ER_NO_PARTITION_FOR_GIVEN_VALUE
    SELECT '#      SQL code we got was: ' AS "", @my_errno AS "";
    --echo #      Sorry, have to abort.
@@ -566,7 +566,7 @@ eval SET @my_errno = $mysql_errno;
 let $run= `SELECT @my_errno = 0`;
 if (`SELECT @my_errno NOT IN (0,$ER_BAD_NULL_ERROR)`)
 {
-   --echo #      The last command got an unexepected error response.
+   --echo #      The last command got an unexpected error response.
    --echo #      Expected/handled SQL codes are 0,$ER_BAD_NULL_ERROR
    SELECT '#      SQL code we got was: ' AS "", @my_errno AS "";
    --echo #      Sorry, have to abort.
diff --git a/mysql-test/suite/sys_vars/r/foreign_key_checks_func.result b/mysql-test/suite/sys_vars/r/foreign_key_checks_func.result
index 9b1736541c1..398a2a76eb8 100644
--- a/mysql-test/suite/sys_vars/r/foreign_key_checks_func.result
+++ b/mysql-test/suite/sys_vars/r/foreign_key_checks_func.result
@@ -26,7 +26,7 @@ INSERT INTO t2 values (20,22);
 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk` FOREIGN KEY (`b`) REFERENCES `t1` (`a`))
 '---Check when foreign_key_checks is disabled---'
 TRUNCATE t1;
-ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk` FOREIGN KEY (`b`) REFERENCES `t1` (`a`))
+ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `fk` FOREIGN KEY (`b`) REFERENCES `test`.`t1` (`a`))
 SET @@session.foreign_key_checks = 0;
 TRUNCATE t1;
 TRUNCATE t2;
diff --git a/mysql-test/suite/sys_vars/t/foreign_key_checks_func.test b/mysql-test/suite/sys_vars/t/foreign_key_checks_func.test
index 5786b9283be..14134a5fb95 100644
--- a/mysql-test/suite/sys_vars/t/foreign_key_checks_func.test
+++ b/mysql-test/suite/sys_vars/t/foreign_key_checks_func.test
@@ -76,7 +76,7 @@ INSERT INTO t2 values (20,22);
 --echo '---Check when foreign_key_checks is disabled---'
 #===========================================================
 
---Error ER_ROW_IS_REFERENCED_2
+--Error ER_TRUNCATE_ILLEGAL_FK
 TRUNCATE t1;
 
 SET @@session.foreign_key_checks = 0;
diff --git a/mysql-test/t/mdl_sync.test b/mysql-test/t/mdl_sync.test
index f7780d2003a..5c4fc20b428 100644
--- a/mysql-test/t/mdl_sync.test
+++ b/mysql-test/t/mdl_sync.test
@@ -3969,7 +3969,8 @@ INSERT INTO t1 VALUES (1),(2),(3);
 
 --echo # Connection: con1
 connection con1;
-SET debug_sync='lock_table_for_truncate SIGNAL parked_truncate WAIT_FOR go_truncate';
+LOCK TABLES t1 WRITE;
+SET debug_sync='upgrade_lock_for_truncate SIGNAL parked_truncate WAIT_FOR go_truncate';
 send TRUNCATE TABLE t1;
 
 connection default;
@@ -3994,15 +3995,17 @@ connection default;
 --echo # Connection: default
 SET debug_sync='now WAIT_FOR parked_flush';
 SET debug_sync='now SIGNAL go_truncate';
+--echo # Ensure that truncate waits for a exclusive lock
+let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist
+  WHERE state='Waiting for table metadata lock' AND info='TRUNCATE TABLE t1';
+--source include/wait_condition.inc
+SET debug_sync= 'now SIGNAL go_show';
 
 connection con1;
---echo # Connection: con1
+--echo # Connection: con1 (TRUNCATE)
 --echo # Reaping...
 reap;
-
-connection default;
---echo # Connection: default
-SET debug_sync= 'now SIGNAL go_show';
+UNLOCK TABLES;
 
 connection con2;
 --echo # Connection: con2 (SHOW FIELDS FROM t1)
diff --git a/mysql-test/t/sp_trans.test b/mysql-test/t/sp_trans.test
index c4915bdb87a..c114d397e43 100644
--- a/mysql-test/t/sp_trans.test
+++ b/mysql-test/t/sp_trans.test
@@ -636,6 +636,30 @@ UPDATE t1_aux SET f2 = 2 WHERE f1 = f1_two_inserts()|
 DROP TABLE t1_aux, t1_not_null|
 DROP FUNCTION f1_two_inserts|
 
+--echo #
+--echo # Bug#49938: Failing assertion: inode or deadlock in fsp/fsp0fsp.c
+--echo #
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS p1|
+DROP TABLE IF EXISTS t1|
+--enable_warnings
+
+CREATE TABLE t1 (a INT) ENGINE=INNODB|
+
+CREATE PROCEDURE p1()
+BEGIN
+  TRUNCATE TABLE t1;
+END|
+
+LOCK TABLES t1 WRITE|
+CALL p1()|
+FLUSH TABLES;
+UNLOCK TABLES|
+CALL p1()|
+
+DROP PROCEDURE p1|
+DROP TABLE t1|
 
 #
 # BUG#NNNN: New bug synopsis
diff --git a/mysql-test/t/trigger-trans.test b/mysql-test/t/trigger-trans.test
index 4d6e82dedcb..82bee7aa224 100644
--- a/mysql-test/t/trigger-trans.test
+++ b/mysql-test/t/trigger-trans.test
@@ -148,10 +148,15 @@ CREATE TRIGGER t1_ad AFTER DELETE ON t1 FOR EACH ROW SET @b = 1;
 SET @a = 0;
 SET @b = 0;
 
+--error ER_TRUNCATE_ILLEGAL_FK
 TRUNCATE t1;
 
 SELECT @a, @b;
 
+DELETE FROM t1;
+
+SELECT @a, @b;
+
 INSERT INTO t1 VALUES (1);
 
 DELETE FROM t1;