MySQL Bugs: #54678: InnoDB, TRUNCATE, ALTER, I

Bug #54678	InnoDB, TRUNCATE, ALTER, I_S SELECT, crash or deadlock
Submitted:	21 Jun 2010 19:43	Modified:	7 Dec 2010 21:36
Reporter:	Matthias Leich	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S2 (Serious)
Version:	5.1,5.5.5-m3	OS:	Any
Assigned to:	Jimmy Yang	CPU Architecture:	Any
Tags:	crash, deadlock, information_schema, innodb

Description:
When running RQG with the grammar from "How to repeat" I get on

1. 5.1.49 (mysql-5.1 revno: 3410 2010-06-03)
--------------------------------------------
Id db Command  Time  State Info
1  test  Sleep 146      NULL
6  test  Query 134   Opening tables SELECT * FROM information_schema . tables WHERE table_schema = TRIM(' test ') AND table_name = TRIM(
7  test  Query 135   Waiting for table ALTER TABLE t1 ENGINE = InnoDB
8  test  Query 136   Waiting for table ALTER TABLE t1 ENGINE = InnoDB
9  test  Query 134   Waiting for table ALTER TABLE t1 ENGINE = InnoDB
10 test  Query 134   Waiting for table TRUNCATE TABLE t1
11 test  Query 135   Waiting for table TRUNCATE TABLE t1
12 test  Query 135   Waiting for table TRUNCATE TABLE t1
13 test  Query 134   Opening tables TRUNCATE TABLE t1
14 test  Query 134   init  TRUNCATE TABLE t1
15 test  Query 135   Waiting for table TRUNCATE TABLE t1
16 test  Query 134   Waiting for table ALTER TABLE t1 ENGINE = InnoDB
17 test  Query 134   closing tables TRUNCATE TABLE t1
18 test  Query 135   Waiting for table ALTER TABLE t1 ENGINE = InnoDB
19 test  Query 134   Waiting for table TRUNCATE TABLE t1
20 test  Query 135   Waiting for table ALTER TABLE t1 ENGINE = InnoDB
21 test  Query 134   Waiting for table TRUNCATE TABLE t1
22 test  Query 135   Waiting for table TRUNCATE TABLE t1
23 test  Query 135   Waiting for table ALTER TABLE t1 ENGINE = InnoDB
24 test  Query 134   Waiting for table TRUNCATE TABLE t1
25 test  Query 134   Waiting for table TRUNCATE TABLE t1
26 test  Query 135   Opening tables ALTER TABLE t1 ENGINE = InnoDB
27 test  Query 135   Waiting for table TRUNCATE TABLE t1
28 test  Query 134   Waiting for table ALTER TABLE t1 ENGINE = InnoDB
29 test  Query 135   Waiting for table ALTER TABLE t1 ENGINE = InnoDB
30 test  Query 134   Waiting for table ALTER TABLE t1 ENGINE = InnoDB
31 test  Query 135   Waiting for table ALTER TABLE t1 ENGINE = InnoDB
32 test  Query 134   NULL  SELECT * FROM information_schema . tables WHERE table_schema = TRIM(' test ') AND table_name = TRIM(
33 test  Query 135   Waiting for table TRUNCATE TABLE t1
34 test  Query 135   Waiting for table TRUNCATE TABLE t1
35 test  Query 135   Waiting for table ALTER TABLE t1 ENGINE = InnoDB
36 test  Query 134   Waiting for table TRUNCATE TABLE t1
37 test  Query 134   Waiting for table TRUNCATE TABLE t1
54 NULL  Query 0  NULL  SHOW PROCESSLIST

2.5.5.5-m3 (mysql-trunk-runtime revno: 3060 2010-06-18)
-------------------------------------------------------
Slight variations like less threads give a deadlock
Thread 1 (Thread 10610):
#0  0x000000382860bd02 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000000b5b149 in my_write_core (sig=6) at stacktrace.c:326
#2  0x0000000000699e71 in handle_segfault (sig=6) at mysqld.cc:2791
#3  <signal handler called>
#4  0x0000003827a30265 in raise () from /lib64/libc.so.6
#5  0x0000003827a31d10 in abort () from /lib64/libc.so.6
#6  0x00000000009f6aa1 in fseg_inode_get (header=0x2aaaab7e804a "", space=0, zip_size=0, mtr=0x495f2a10) at fsp/fsp0fsp.c:2103
#7  0x00000000009f720f in fseg_n_reserved_pages (header=0x2aaaab7e804a "", used=0x495f2eb8, mtr=0x495f2a10) at fsp/fsp0fsp.c:2437
#8  0x00000000009b8eef in btr_get_size (index=0x2aaab4076f88, flag=1) at btr/btr0btr.c:428
#9  0x00000000009e535d in dict_update_statistics_low (table=0x2aaab405c3e8, has_dict_mutex=0) at dict/dict0dict.c:4235
#10 0x00000000009e5454 in dict_update_statistics (table=0x2aaab405c3e8) at dict/dict0dict.c:4277
#11 0x000000000093db2d in ha_innobase::info (this=0x2aaab4028278, flag=84) at handler/ha_innodb.cc:7715
#12 0x0000000000838e2a in get_schema_tables_record (thd=0x2aaab403a528, tables=0x2aaab80081a0, table=0x2aaab4068df8, res=false, db_name=0x495f48f0, table_name=0x495f4900) at sql_show.cc:3841
#13 0x0000000000847dc5 in get_all_tables (thd=0x2aaab403a528, tables=0x16e57d58, cond=0x2aaab8007d18) at sql_show.cc:3608
#14 0x0000000000833cc3 in get_schema_tables_result (join=0x2aaab802e388, executed_place=PROCESSED_BY_JOIN_EXEC) at sql_show.cc:6666
#15 0x00000000007490e6 in JOIN::exec (this=0x2aaab802e388) at sql_select.cc:1872
#16 0x0000000000745add in mysql_select (thd=0x2aaab403a528, rref_pointer_array=0x2aaab403c690, tables=0x16e57d58, wild_num=1, fields=..., conds=0x16e58a60, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2684619520,
    result=0x16e59968, unit=0x2aaab403bec0, select_lex=0x2aaab403c4c0) at sql_select.cc:2549
#17 0x000000000074b207 in handle_select (thd=0x2aaab403a528, lex=0x2aaab403be18, result=0x16e59968, setup_tables_done_option=0) at sql_select.cc:290
#18 0x00000000006aae58 in execute_sqlcom_select (thd=0x2aaab403a528, all_tables=0x16e57d58) at sql_parse.cc:4882
#19 0x00000000006abf05 in mysql_execute_command (thd=0x2aaab403a528) at sql_parse.cc:2329
#20 0x00000000006b3577 in mysql_parse (thd=0x2aaab403a528, inBuf=0x16e57ac8 "SELECT * FROM information_schema . tables WHERE table_schema = TRIM(' test ') AND table_name = TRIM(' t1 ')", length=107, parser_state=0x495f69b0) at sql_parse.cc:5911
#21 0x00000000006b4b2a in dispatch_command (command=COM_QUERY, thd=0x2aaab403a528, packet=0x2aaab403d259 "SELECT * FROM information_schema . tables WHERE table_schema = TRIM(' test ') AND table_name = TRIM(' t1 ')", packet_length=107)
    at sql_parse.cc:1135
#22 0x00000000006b6072 in do_command (thd=0x2aaab403a528) at sql_parse.cc:807
#23 0x00000000006a3f81 in do_handle_one_connection (thd_arg=0x2aaab403a528) at sql_connect.cc:1196
#24 0x00000000006a4047 in handle_one_connection (arg=0x2aaab403a528) at sql_connect.cc:1135
#25 0x000000382860673d in start_thread () from /lib64/libpthread.so.0
#26 0x0000003827ad3d1d in clone () from /lib64/libc.so.6

I will upload an archive with the protocols soon.

How to repeat:
1.yy:
-----
ddl:
        ALTER TABLE t1 ENGINE = InnoDB |
        TRUNCATE TABLE t1 ;

query:
        SELECT * FROM information_schema . tables WHERE table_schema = TRIM(' test ') AND table_name = TRIM(' t1 ') |
        ddl;

query_init:
        CREATE TABLE IF NOT EXISTS t1 (col1 BIGINT, col2 BIGINT);

1.zz (content is not important):
--------------------------------
$tables = {
        # rows => [0, 1, 10 ],
        rows => [0, 1 ],
        pk => [ 'int' , 'int auto_increment' ]
};

$fields = {
        types => [ 'int' ],
        indexes => [ 'key', undef ]
};

$data = {
        numbers => [ 'digit', 'null', undef ],
        strings => [ 'letter', 'english' ],
        blobs => [ 'data' ],
        temporals => ['date', 'year', 'null', undef ]
}

My commandline:
----------------
nice -20 perl runall.pl --gendata=1.zz --duration=200 \
--queries=3000 --reporter=Deadlock,Backtrace,Shutdown \
 --basedir=/data0/mleich/repo/mysql-trunk-runtime/ \
--mysqld=--lock-wait-timeout=1 \
--mysqld=--innodb-lock-wait-timeout=2 \
--mysqld=--log-output=none --mysqld=--secure-file-priv=/tmp/ \
--mysqld=--loose-innodb-lock-wait-timeout=2 \
--grammar=1.yy --vardir=/dev/shm/var_1276601155 \
--seed=352 --threads=32

crash in mysql-trunk-runtime

Attachment: crash.prt (application/octet-stream, text), 57.65 KiB.

Deadlock in mysql-5.1

Attachment: deadlock_5.1.prt (application/octet-stream, text), 2.57 KiB.

The issue here is that MDL does not know if InnoDB truncate table would take either drop/recreate table approach or delete row-by-row approach. 

Ideally, we should take EXCLUSIVE lock if we are going through drop/recreate table approach as in this case, and SHARE lock if we are going through delete row by row approach. However, such communication is not available currently, and need to be devised. 

The conclusion is that we remove the assertion in this case, and work on a complete solution in 5.5.

<jyang> so kostja, let's conclude here, I will check in a fix to skip the assert and  pass 0 count to ::info in this case
<kostja> jyang: yes, in 5.1 it's the only option
<kostja> and in 5.5 we need a good fix that we develop together
<jyang> However, to resolve it completely, we need to device a way to block folks to poke into metadata while we are drop/recreating
<kostja> jyang: i agree.
<kostja> so we need a two-phase truncate

Pushed into mysql-5.1 5.1.52 (revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (version source revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (merge vers: 5.1.52) (pib:21)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/119465

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/119926

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/120041

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/120042

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/120103

3156 Davi Arnaut	2010-10-06
      Bug#49938: Failing assertion: inode or deadlock in fsp/fsp0fsp.c
      Bug#54678: InnoDB, TRUNCATE, ALTER, I_S SELECT, crash or deadlock
      
      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.
      
      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.
      
      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 is referenced to by a foreign key in another 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.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/120137

3156 Davi Arnaut	2010-10-06
      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.

Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (merge vers: 5.1.51-ndb-7.0.20) (pib:21)

Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (merge vers: 5.1.51-ndb-6.3.39) (pib:21)

Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (merge vers: 5.1.51-ndb-6.2.19) (pib:21)

Adding to change log:

A simultaneous TRUNCATE TABLE and examining the same table's
information in the INFORMATION_SCHEMA could cause a crash, in the
debug version of the server.

Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (merge vers: 5.5.7-rc) (pib:21)

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)

Added to change log for 5.1 and 5.5:

Issuing TRUNCATE TABLE and examining the same table's information in
the INFORMATION_SCHEMA database at the same time could cause a crash
in the debug version of the server.

Added to change log for 5.5 only, flagged as 'incompatible change':

As a result of this change,
InnoDB always uses the fast truncation technique, equivalent to DROP
TABLE and CREATE TABLE. It no longer performs a row-by-row delete for
tables with parent-child foreign key relationships. TRUNCATE TABLE
returns an error for such tables. Modify your SQL to issue DELETE
FROM table_name for such tables instead.

Added similar 'incompatible change' wording to the 5.1->5.5 upgrade instructions.

Removed reference to the row-by-row technique from the TRUNCATE TABLE syntax section.

How about NOT breaking truncate table when foreign keys are used? Please take the time and fix the issue(s) properly.

At a minimum I expect to be able to truncate all tables in a database if I do it in such an order that no rows exist referencing the table being truncated.

Of course, given that 'on delete cascade' may be defined on any foreign key it should be possible to truncate a table which does have rows referencing it, provided foreign keys are declared with 'on delete cascade'.

We do not need any more arbitrary restrictions stemming from poorly implemented functionality, and this is a regression.

Hi Francis,

> At a minimum I expect to be able to truncate all tables in a database
> if I do it in such an order that no rows exist referencing the table
> being truncated.

To do that we would need to check the rows and would make the truncate statement equivalent to a delete. Likewise for cascade.

> We do not need any more arbitrary restrictions stemming from
> poorly implemented functionality, and this is a regression.

The design intention behind truncate has always been to bypass integrity checks. This behavior is similar to those of other database products.