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.

2 files changed, 59 insertions, 35 deletions

diff --git a/storage/example/ha_example.cc b/storage/example/ha_example.cc
index 306f8eaeccd..6af471494af 100644
--- a/storage/example/ha_example.cc
+++ b/storage/example/ha_example.cc
@@ -356,14 +356,14 @@ int ha_example::close(void)
   is happening. buf() is a byte array of data. You can use the field
   information to extract the data from the native byte array type.
 
-    @details
+  @details
   Example of this would be:
-    @code
+  @code
   for (Field **field=table->field ; *field ; field++)
   {
     ...
   }
-    @endcode
+  @endcode
 
   See ha_tina.cc for an example of extracting all of the data as strings.
   ha_berekly.cc has an example of how to store it intact by "packing" it
@@ -375,7 +375,7 @@ int ha_example::close(void)
   Called from item_sum.cc, item_sum.cc, sql_acl.cc, sql_insert.cc,
   sql_insert.cc, sql_select.cc, sql_table.cc, sql_udf.cc, and sql_update.cc.
 
-    @see
+  @see
   item_sum.cc, item_sum.cc, sql_acl.cc, sql_insert.cc,
   sql_insert.cc, sql_select.cc, sql_table.cc, sql_udf.cc and sql_update.cc
 */
@@ -400,19 +400,19 @@ int ha_example::write_row(uchar *buf)
   Keep in mind that the server can do updates based on ordering if an ORDER BY
   clause was used. Consecutive ordering is not guaranteed.
 
-    @details
+  @details
   Currently new_data will not have an updated auto_increament record, or
   and updated timestamp field. You can do these for example by doing:
-    @code
+  @code
   if (table->timestamp_field_type & TIMESTAMP_AUTO_SET_ON_UPDATE)
     table->timestamp_field->set_time();
   if (table->next_number_field && record == table->record[0])
     update_auto_increment();
-    @endcode
+  @endcode
 
   Called from sql_select.cc, sql_acl.cc, sql_update.cc, and sql_insert.cc.
 
-    @see
+  @see
   sql_select.cc, sql_acl.cc, sql_update.cc and sql_insert.cc
 */
 int ha_example::update_row(const uchar *old_data, uchar *new_data)
@@ -507,10 +507,10 @@ int ha_example::index_prev(uchar *buf)
   @brief
   index_first() asks for the first key in the index.
 
-    @details
+  @details
   Called from opt_range.cc, opt_sum.cc, sql_handler.cc, and sql_select.cc.
 
-    @see
+  @see
   opt_range.cc, opt_sum.cc, sql_handler.cc and sql_select.cc
 */
 int ha_example::index_first(uchar *buf)
@@ -528,10 +528,10 @@ int ha_example::index_first(uchar *buf)
   @brief
   index_last() asks for the last key in the index.
 
-    @details
+  @details
   Called from opt_range.cc, opt_sum.cc, sql_handler.cc, and sql_select.cc.
 
-    @see
+  @see
   opt_range.cc, opt_sum.cc, sql_handler.cc and sql_select.cc
 */
 int ha_example::index_last(uchar *buf)
@@ -551,11 +551,11 @@ int ha_example::index_last(uchar *buf)
   scan. See the example in the introduction at the top of this file to see when
   rnd_init() is called.
 
-    @details
+  @details
   Called from filesort.cc, records.cc, sql_handler.cc, sql_select.cc, sql_table.cc,
   and sql_update.cc.
 
-    @see
+  @see
   filesort.cc, records.cc, sql_handler.cc, sql_select.cc, sql_table.cc and sql_update.cc
 */
 int ha_example::rnd_init(bool scan)
@@ -578,11 +578,11 @@ int ha_example::rnd_end()
   The Field structure for the table is the key to getting data into buf
   in a manner that will allow the server to understand it.
 
-    @details
+  @details
   Called from filesort.cc, records.cc, sql_handler.cc, sql_select.cc, sql_table.cc,
   and sql_update.cc.
 
-    @see
+  @see
   filesort.cc, records.cc, sql_handler.cc, sql_select.cc, sql_table.cc and sql_update.cc
 */
 int ha_example::rnd_next(uchar *buf)
@@ -602,11 +602,11 @@ int ha_example::rnd_next(uchar *buf)
   position() is called after each call to rnd_next() if the data needs
   to be ordered. You can do something like the following to store
   the position:
-    @code
+  @code
   my_store_ptr(ref, ref_length, current_position);
-    @endcode
+  @endcode
 
-    @details
+  @details
   The server uses ref to store data. ref_length in the above case is
   the size needed to store current_position. ref is just a byte array
   that the server will maintain. If you are using offsets to mark rows, then
@@ -615,7 +615,7 @@ int ha_example::rnd_next(uchar *buf)
 
   Called from filesort.cc, sql_select.cc, sql_delete.cc, and sql_update.cc.
 
-    @see
+  @see
   filesort.cc, sql_select.cc, sql_delete.cc and sql_update.cc
 */
 void ha_example::position(const uchar *record)
@@ -632,10 +632,10 @@ void ha_example::position(const uchar *record)
   ref. You can use ha_get_ptr(pos,ref_length) to retrieve whatever key
   or position you saved when position() was called.
 
-    @details
+  @details
   Called from filesort.cc, records.cc, sql_insert.cc, sql_select.cc, and sql_update.cc.
 
-    @see
+  @see
   filesort.cc, records.cc, sql_insert.cc, sql_select.cc and sql_update.cc
 */
 int ha_example::rnd_pos(uchar *buf, uchar *pos)
@@ -655,15 +655,15 @@ int ha_example::rnd_pos(uchar *buf, uchar *pos)
   ::info() is used to return information to the optimizer. See my_base.h for
   the complete description.
 
-    @details
+  @details
   Currently this table handler doesn't implement most of the fields really needed.
   SHOW also makes use of this data.
 
   You will probably want to have the following in your code:
-    @code
+  @code
   if (records < 2)
     records = 2;
-    @endcode
+  @endcode
   The reason is that the server will optimize for cases of only a single
   record. If, in a table scan, you don't know the number of records, it
   will probably be better to set records to two so you can return as many
@@ -682,7 +682,7 @@ int ha_example::rnd_pos(uchar *buf, uchar *pos)
   sql_select.cc, sql_select.cc, sql_show.cc, sql_show.cc, sql_show.cc, sql_show.cc,
   sql_table.cc, sql_union.cc, and sql_update.cc.
 
-    @see
+  @see
   filesort.cc, ha_heap.cc, item_sum.cc, opt_sum.cc, sql_delete.cc, sql_delete.cc,
   sql_derived.cc, sql_select.cc, sql_select.cc, sql_select.cc, sql_select.cc,
   sql_select.cc, sql_show.cc, sql_show.cc, sql_show.cc, sql_show.cc, sql_table.cc,
@@ -716,14 +716,14 @@ int ha_example::extra(enum ha_extra_function operation)
   Used to delete all rows in a table, including cases of truncate and cases where
   the optimizer realizes that all rows will be removed as a result of an SQL statement.
 
-    @details
+  @details
   Called from item_sum.cc by Item_func_group_concat::clear(),
   Item_sum_count_distinct::clear(), and Item_func_group_concat::clear().
   Called from sql_delete.cc by mysql_delete().
   Called from sql_select.cc by JOIN::reinit().
   Called from sql_union.cc by st_select_lex_unit::exec().
 
-    @see
+  @see
   Item_func_group_concat::clear(), Item_sum_count_distinct::clear() and
   Item_func_group_concat::clear() in item_sum.cc;
   mysql_delete() in sql_delete.cc;
@@ -739,17 +739,40 @@ int ha_example::delete_all_rows()
 
 /**
   @brief
+  Used for handler specific truncate table.  The table is locked in
+  exclusive mode and handler is responsible for reseting the auto-
+  increment counter.
+
+  @details
+  Called from Truncate_statement::handler_truncate.
+  Not used if the handlerton supports HTON_CAN_RECREATE, unless this
+  engine can be used as a partition. In this case, it is invoked when
+  a particular partition is to be truncated.
+
+  @see
+  Truncate_statement in sql_truncate.cc
+  Remarks in handler::truncate.
+*/
+int ha_example::truncate()
+{
+  DBUG_ENTER("ha_example::truncate");
+  DBUG_RETURN(HA_ERR_WRONG_COMMAND);
+}
+
+
+/**
+  @brief
   This create a lock on the table. If you are implementing a storage engine
   that can handle transacations look at ha_berkely.cc to see how you will
   want to go about doing this. Otherwise you should consider calling flock()
   here. Hint: Read the section "locking functions for mysql" in lock.cc to understand
   this.
 
-    @details
+  @details
   Called from lock.cc by lock_external() and unlock_external(). Also called
   from sql_table.cc by copy_data_between_tables().
 
-    @see
+  @see
   lock.cc by lock_external() and unlock_external() in lock.cc;
   the section "locking functions for mysql" in lock.cc;
   copy_data_between_tables() in sql_table.cc.
@@ -767,7 +790,7 @@ int ha_example::external_lock(THD *thd, int lock_type)
   should be needed for the table. For updates/deletes/inserts we get WRITE
   locks, for SELECT... we get read locks.
 
-    @details
+  @details
   Before adding the lock into the table lock handler (see thr_lock.c),
   mysqld calls store lock with the requested locks. Store lock can now
   modify a write lock to a read lock (or some other lock), ignore the
@@ -790,12 +813,12 @@ int ha_example::external_lock(THD *thd, int lock_type)
 
   Called from lock.cc by get_lock_data().
 
-    @note
+  @note
   In this method one should NEVER rely on table->in_use, it may, in fact,
   refer to a different thread! (this happens if get_lock_data() is called
   from mysql_lock_abort_for_thread() function)
 
-    @see
+  @see
   get_lock_data() in lock.cc
 */
 THR_LOCK_DATA **ha_example::store_lock(THD *thd,
@@ -816,7 +839,7 @@ THR_LOCK_DATA **ha_example::store_lock(THD *thd,
   shared references released). The variable name will just be the name of
   the table. You will need to remove any files you have created at this point.
 
-    @details
+  @details
   If you do not implement this, the default delete_table() is called from
   handler.cc and it will delete all files with the file extensions returned
   by bas_ext().
@@ -825,7 +848,7 @@ THR_LOCK_DATA **ha_example::store_lock(THD *thd,
   during create if the table_flag HA_DROP_BEFORE_CREATE was specified for
   the storage engine.
 
-    @see
+  @see
   delete_table and ha_create_table() in handler.cc
 */
 int ha_example::delete_table(const char *name)
diff --git a/storage/example/ha_example.h b/storage/example/ha_example.h
index 12e088f5f05..5555a10930b 100644
--- a/storage/example/ha_example.h
+++ b/storage/example/ha_example.h
@@ -245,6 +245,7 @@ public:
   int extra(enum ha_extra_function operation);
   int external_lock(THD *thd, int lock_type);                   ///< required
   int delete_all_rows(void);
+  int truncate();
   ha_rows records_in_range(uint inx, key_range *min_key,
                            key_range *max_key);
   int delete_table(const char *from);