List:Commits« Previous MessageNext Message »
From:Mattias Jonsson Date:August 20 2010 6:26pm
Subject:bzr push into mysql-5.5-bugfixing branch (mattias.jonsson:3189 to 3190)
Bug#54747
View as plain text  
 3190 Mattias Jonsson	2010-08-20
      Bug#54747: Deadlock between REORGANIZE PARTITION and SELECT is not detected
      
      The ALTER PARTITION and SELECT seemed to be deadlocked
      when having innodb_thread_concurrency = 1.
      
      Problem was that there was unreleased latches
      in the ALTER PARTITION thread which was needed
      by the SELECT thread to be able to continue.
      
      Solution was to release the latches by commit 
      before requesting upgrade to exclusive MDL lock.
      
      Updated according to reviewers comments (3).
     @ mysql-test/r/partition_innodb.result
        updated test result
     @ mysql-test/t/partition_innodb.test
        added test
     @ sql/sql_partition.cc
        Moved implicit commit into mysql_change_partition
        so that if latches are taken, they are always released
        before waiting on exclusive lock.
     @ sql/sql_table.cc
        refactored the code to prepare and commit
        around copy_data_between_tables, to be able
        to reuse it in mysql_change_partitions
     @ sql/sql_table.h
        exporting mysql_trans_prepare/commit_alter_copy_data

    modified:
      mysql-test/r/partition_innodb.result
      mysql-test/t/partition_innodb.test
      sql/sql_partition.cc
      sql/sql_table.cc
      sql/sql_table.h
 3189 Christopher Powers	2010-08-20 [merge]
      merge

    removed:
      mysql-test/include/have_thread_concurrency.inc
      mysql-test/suite/rpl/t/rpl_mixed_row_innodb.test
    added:
      mysql-test/r/ctype_utf16_def.result
      mysql-test/suite/rpl_ndb/t/rpl_ndb_stm_innodb-slave.opt
      mysql-test/t/ctype_utf16_def-master.opt
      mysql-test/t/ctype_utf16_def.test
      sql/sql_reload.cc
      sql/sql_reload.h
    modified:
      .bzrignore
      client/mysql.cc
      config/ac-macros/plugins.m4
      configure.in
      include/my_pthread.h
      include/mysql/psi/mysql_thread.h
      libmysqld/CMakeLists.txt
      libmysqld/Makefile.am
      mysql-test/CMakeLists.txt
      mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test
      mysql-test/extra/binlog_tests/mix_innodb_myisam_side_effects.test
      mysql-test/extra/rpl_tests/rpl_drop_create_temp_table.inc
      mysql-test/extra/rpl_tests/rpl_drop_create_temp_table.test
      mysql-test/extra/rpl_tests/rpl_innodb.test
      mysql-test/include/check_no_concurrent_insert.inc
      mysql-test/include/ctype_numconv.inc
      mysql-test/include/handler.inc
      mysql-test/include/mix1.inc
      mysql-test/r/case.result
      mysql-test/r/ctype_binary.result
      mysql-test/r/ctype_cp1251.result
      mysql-test/r/ctype_cp932_binlog_stm.result
      mysql-test/r/ctype_latin1.result
      mysql-test/r/ctype_ucs.result
      mysql-test/r/ctype_utf32.result
      mysql-test/r/ctype_utf8.result
      mysql-test/r/events_trans.result
      mysql-test/r/flush.result
      mysql-test/r/func_str.result
      mysql-test/r/information_schema_inno.result
      mysql-test/r/mdl_sync.result
      mysql-test/r/mysql.result
      mysql-test/r/mysqlbinlog.result
      mysql-test/r/query_cache.result
      mysql-test/r/schema.result
      mysql-test/r/sp-lock.result
      mysql-test/r/sp-threads.result
      mysql-test/suite/binlog/r/binlog_database.result
      mysql-test/suite/binlog/r/binlog_innodb_row.result
      mysql-test/suite/binlog/r/binlog_row_binlog.result
      mysql-test/suite/binlog/r/binlog_row_drop_tbl.result
      mysql-test/suite/binlog/r/binlog_row_drop_tmp_tbl.result
      mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result
      mysql-test/suite/binlog/r/binlog_stm_binlog.result
      mysql-test/suite/binlog/r/binlog_stm_blackhole.result
      mysql-test/suite/binlog/r/binlog_stm_drop_tbl.result
      mysql-test/suite/binlog/r/binlog_stm_drop_tmp_tbl.result
      mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result
      mysql-test/suite/binlog/r/binlog_stm_row.result
      mysql-test/suite/binlog/t/binlog_stm_row.test
      mysql-test/suite/funcs_1/datadict/processlist_val.inc
      mysql-test/suite/funcs_1/r/processlist_val_no_prot.result
      mysql-test/suite/funcs_1/r/processlist_val_ps.result
      mysql-test/suite/funcs_2/charset/charset_master.test
      mysql-test/suite/funcs_2/r/innodb_charset.result
      mysql-test/suite/funcs_2/r/memory_charset.result
      mysql-test/suite/funcs_2/r/myisam_charset.result
      mysql-test/suite/funcs_2/t/disabled.def
      mysql-test/suite/innodb/t/innodb-lock.test
      mysql-test/suite/parts/t/partition_debug_sync_innodb.test
      mysql-test/suite/perfschema/r/binlog_mix.result
      mysql-test/suite/perfschema/r/binlog_row.result
      mysql-test/suite/perfschema/r/binlog_stmt.result
      mysql-test/suite/perfschema/r/dml_setup_instruments.result
      mysql-test/suite/perfschema/r/server_init.result
      mysql-test/suite/rpl/r/rpl_conditional_comments.result
      mysql-test/suite/rpl/r/rpl_drop_if_exists.result
      mysql-test/suite/rpl/r/rpl_mixed_drop_create_temp_table.result
      mysql-test/suite/rpl/r/rpl_mixed_implicit_commit_binlog.result
      mysql-test/suite/rpl/r/rpl_mixed_mixing_engines.result
      mysql-test/suite/rpl/r/rpl_non_direct_mixed_mixing_engines.result
      mysql-test/suite/rpl/r/rpl_non_direct_row_mixing_engines.result
      mysql-test/suite/rpl/r/rpl_non_direct_stm_mixing_engines.result
      mysql-test/suite/rpl/r/rpl_row_drop.result
      mysql-test/suite/rpl/r/rpl_row_drop_create_temp_table.result
      mysql-test/suite/rpl/r/rpl_row_implicit_commit_binlog.result
      mysql-test/suite/rpl/r/rpl_row_log.result
      mysql-test/suite/rpl/r/rpl_row_log_innodb.result
      mysql-test/suite/rpl/r/rpl_row_mixing_engines.result
      mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result
      mysql-test/suite/rpl/r/rpl_row_show_relaylog_events.result
      mysql-test/suite/rpl/r/rpl_server_id.result
      mysql-test/suite/rpl/r/rpl_sp.result
      mysql-test/suite/rpl/r/rpl_stm_drop_create_temp_table.result
      mysql-test/suite/rpl/r/rpl_stm_implicit_commit_binlog.result
      mysql-test/suite/rpl/r/rpl_stm_log.result
      mysql-test/suite/rpl/r/rpl_stm_mix_show_relaylog_events.result
      mysql-test/suite/rpl/r/rpl_stm_mixing_engines.result
      mysql-test/suite/rpl/r/rpl_temp_table_mix_row.result
      mysql-test/suite/rpl/t/rpl_savepoint.test
      mysql-test/suite/rpl/t/rpl_sp.test
      mysql-test/suite/rpl/t/rpl_stm_innodb.test
      mysql-test/suite/rpl/t/rpl_view_multi.test
      mysql-test/suite/rpl_ndb/r/rpl_ndb_dd_basic.result
      mysql-test/suite/rpl_ndb/r/rpl_ndb_mixed_implicit_commit_binlog.result
      mysql-test/suite/rpl_ndb/r/rpl_ndb_row_implicit_commit_binlog.result
      mysql-test/suite/rpl_ndb/t/disabled.def
      mysql-test/suite/rpl_ndb/t/rpl_ndb_2other-slave.opt
      mysql-test/suite/sys_vars/r/concurrent_insert_func.result
      mysql-test/suite/sys_vars/r/query_cache_wlock_invalidate_func.result
      mysql-test/suite/sys_vars/t/concurrent_insert_func.test
      mysql-test/suite/sys_vars/t/delayed_insert_limit_func.test
      mysql-test/suite/sys_vars/t/query_cache_wlock_invalidate_func.test
      mysql-test/suite/sys_vars/t/sql_low_priority_updates_func.test
      mysql-test/suite/sys_vars/t/thread_concurrency_basic.test
      mysql-test/t/case.test
      mysql-test/t/ctype_utf32.test
      mysql-test/t/ctype_utf8.test
      mysql-test/t/delayed.test
      mysql-test/t/disabled.def
      mysql-test/t/events_trans.test
      mysql-test/t/flush.test
      mysql-test/t/func_str.test
      mysql-test/t/information_schema.test
      mysql-test/t/information_schema_inno.test
      mysql-test/t/innodb_mysql_lock.test
      mysql-test/t/innodb_mysql_lock2.test
      mysql-test/t/insert_notembedded.test
      mysql-test/t/kill.test
      mysql-test/t/lock_multi.test
      mysql-test/t/lock_sync.test
      mysql-test/t/mdl_sync.test
      mysql-test/t/merge-big.test
      mysql-test/t/multi_update.test
      mysql-test/t/mysql.test
      mysql-test/t/query_cache.test
      mysql-test/t/query_cache_28249.test
      mysql-test/t/schema.test
      mysql-test/t/sp-lock.test
      mysql-test/t/sp_notembedded.test
      mysql-test/t/sp_sync.test
      mysql-test/t/status.test
      mysql-test/t/trigger_notembedded.test
      mysql-test/t/view.test
      mysys/thr_lock.c
      mysys/thr_rwlock.c
      scripts/CMakeLists.txt
      scripts/mysqlaccess.conf*
      sql/CMakeLists.txt
      sql/Makefile.am
      sql/datadict.cc
      sql/event_db_repository.cc
      sql/events.cc
      sql/ha_ndbcluster.cc
      sql/ha_ndbcluster_binlog.cc
      sql/ha_ndbcluster_binlog.h
      sql/handler.cc
      sql/handler.h
      sql/item.h
      sql/item_cmpfunc.cc
      sql/item_func.cc
      sql/item_strfunc.cc
      sql/item_strfunc.h
      sql/lock.cc
      sql/lock.h
      sql/log.cc
      sql/log_event.cc
      sql/mdl.cc
      sql/mdl.h
      sql/mysqld.cc
      sql/mysqld.h
      sql/sql_admin.cc
      sql/sql_base.cc
      sql/sql_base.h
      sql/sql_class.cc
      sql/sql_handler.cc
      sql/sql_insert.cc
      sql/sql_lex.cc
      sql/sql_lex.h
      sql/sql_parse.cc
      sql/sql_parse.h
      sql/sql_partition.cc
      sql/sql_plugin.cc
      sql/sql_rename.cc
      sql/sql_servers.cc
      sql/sql_show.cc
      sql/sql_table.cc
      sql/sql_table.h
      sql/sql_test.cc
      sql/sql_trigger.cc
      sql/sql_truncate.cc
      sql/sql_view.cc
      sql/sql_yacc.yy
      sql/sys_vars.cc
      sql/table.cc
      sql/table.h
      storage/innobase/plug.in
      storage/myisam/ft_stopwords.c
=== modified file 'mysql-test/r/partition_innodb.result'
--- a/mysql-test/r/partition_innodb.result	2010-08-13 07:50:25 +0000
+++ b/mysql-test/r/partition_innodb.result	2010-08-20 17:15:48 +0000
@@ -1,5 +1,41 @@
 drop table if exists t1, t2;
 #
+# Bug#54747: Deadlock between REORGANIZE PARTITION and
+#            SELECT is not detected
+#
+SET @old_innodb_thread_concurrency:= @@innodb_thread_concurrency;
+SET GLOBAL innodb_thread_concurrency = 1;
+CREATE TABLE t1
+(user_num BIGINT,
+hours SMALLINT,
+KEY user_num (user_num))
+ENGINE = InnoDB   
+PARTITION BY RANGE COLUMNS (hours)
+(PARTITION hour_003 VALUES LESS THAN (3),
+PARTITION hour_004 VALUES LESS THAN (4),
+PARTITION hour_005 VALUES LESS THAN (5),
+PARTITION hour_last VALUES LESS THAN (MAXVALUE));
+INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
+BEGIN;
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+5
+# con1
+# SEND a ALTER PARTITION which waits on the ongoing transaction.
+ALTER TABLE t1
+REORGANIZE PARTITION hour_003, hour_004 INTO
+(PARTITION oldest VALUES LESS THAN (4));
+# Connection default wait until the ALTER is in 'waiting for table...'
+# state and then continue the transaction by trying a SELECT
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+5
+COMMIT;
+# con1, reaping ALTER.
+# Disconnecting con1 and switching to default. Cleaning up.
+SET GLOBAL innodb_thread_concurrency = @old_innodb_thread_concurrency;
+DROP TABLE t1;
+#
 # Bug#50418: DROP PARTITION does not interact with transactions
 #
 CREATE TABLE t1 (

=== modified file 'mysql-test/t/partition_innodb.test'
--- a/mysql-test/t/partition_innodb.test	2010-08-13 07:50:25 +0000
+++ b/mysql-test/t/partition_innodb.test	2010-08-20 17:15:48 +0000
@@ -9,6 +9,63 @@ drop table if exists t1, t2;
 let $MYSQLD_DATADIR= `SELECT @@datadir`;
 
 --echo #
+--echo # Bug#54747: Deadlock between REORGANIZE PARTITION and
+--echo #            SELECT is not detected
+--echo #
+
+SET @old_innodb_thread_concurrency:= @@innodb_thread_concurrency;
+SET GLOBAL innodb_thread_concurrency = 1;
+
+CREATE TABLE t1
+(user_num BIGINT,
+ hours SMALLINT,
+ KEY user_num (user_num))
+ENGINE = InnoDB   
+PARTITION BY RANGE COLUMNS (hours)
+(PARTITION hour_003 VALUES LESS THAN (3),
+ PARTITION hour_004 VALUES LESS THAN (4),
+ PARTITION hour_005 VALUES LESS THAN (5),
+ PARTITION hour_last VALUES LESS THAN (MAXVALUE));
+
+INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
+
+BEGIN;
+SELECT COUNT(*) FROM t1;
+
+--echo # con1
+--connect (con1,localhost,root,,)
+--echo # SEND a ALTER PARTITION which waits on the ongoing transaction.
+--send
+ALTER TABLE t1
+REORGANIZE PARTITION hour_003, hour_004 INTO
+(PARTITION oldest VALUES LESS THAN (4));
+
+--echo # Connection default wait until the ALTER is in 'waiting for table...'
+--echo # state and then continue the transaction by trying a SELECT
+--connection default
+let $wait_condition =
+SELECT COUNT(*) = 1
+FROM information_schema.processlist
+WHERE INFO like 'ALTER TABLE t1%REORGANIZE PARTITION hour_003, hour_004%'
+AND STATE = 'Waiting for table metadata lock';
+--source include/wait_condition.inc
+SELECT COUNT(*) FROM t1;
+COMMIT;
+
+--echo # con1, reaping ALTER.
+--connection con1
+--reap
+
+--echo # Disconnecting con1 and switching to default. Cleaning up.
+--disconnect con1
+
+--connection default
+
+SET GLOBAL innodb_thread_concurrency = @old_innodb_thread_concurrency;
+DROP TABLE t1;
+
+
+--echo #
 --echo # Bug#50418: DROP PARTITION does not interact with transactions
 --echo #
 CREATE TABLE t1 (

=== modified file 'sql/sql_partition.cc'
--- a/sql/sql_partition.cc	2010-08-18 11:29:04 +0000
+++ b/sql/sql_partition.cc	2010-08-20 17:15:48 +0000
@@ -63,6 +63,7 @@
 #include "sql_table.h"                  // build_table_filename,
                                         // build_table_shadow_filename,
                                         // table_to_filename
+                                        // mysql_*_alter_copy_data
 #include "opt_range.h"                  // store_key_image_to_rec
 #include "sql_analyse.h"                // append_escaped
 
@@ -4377,7 +4378,6 @@ static int fast_end_partition(THD *thd, 
                               ALTER_PARTITION_PARAM_TYPE *lpt,
                               bool written_bin_log)
 {
-  int error;
   char tmp_name[80];
   DBUG_ENTER("fast_end_partition");
 
@@ -4386,13 +4386,6 @@ static int fast_end_partition(THD *thd, 
   if (!is_empty)
     query_cache_invalidate3(thd, table_list, 0);
 
-  error= trans_commit_stmt(thd);
-  if (trans_commit_implicit(thd))
-    error= 1;
-
-  if (error)
-    DBUG_RETURN(TRUE);                      /* The error has been reported */
-
   if ((!is_empty) && (!written_bin_log) &&
       (!thd->lex->no_write_to_binlog) &&
     write_bin_log(thd, FALSE, thd->query(), thd->query_length()))
@@ -5535,17 +5528,25 @@ static bool mysql_change_partitions(ALTE
   char path[FN_REFLEN+1];
   int error;
   handler *file= lpt->table->file;
+  THD *thd= lpt->thd;
   DBUG_ENTER("mysql_change_partitions");
 
   build_table_filename(path, sizeof(path) - 1, lpt->db, lpt->table_name, "", 0);
+
+  if(mysql_trans_prepare_alter_copy_data(thd))
+    DBUG_RETURN(TRUE);
+
   if ((error= file->ha_change_partitions(lpt->create_info, path, &lpt->copied,
                                          &lpt->deleted, lpt->pack_frm_data,
                                          lpt->pack_frm_len)))
   {
     file->print_error(error, MYF(error != ER_OUTOFMEMORY ? 0 : ME_FATALERROR));
-    DBUG_RETURN(TRUE);
   }
-  DBUG_RETURN(FALSE);
+
+  if (mysql_trans_commit_alter_copy_data(thd))
+    DBUG_RETURN(TRUE);                      /* The error has been reported */
+
+  DBUG_RETURN(test(error));
 }
 
 

=== modified file 'sql/sql_table.cc'
--- a/sql/sql_table.cc	2010-08-20 08:24:32 +0000
+++ b/sql/sql_table.cc	2010-08-20 17:15:48 +0000
@@ -6740,6 +6740,54 @@ err_with_mdl:
 }
 /* mysql_alter_table */
 
+
+
+/**
+  Prepare the transaction for the alter table's copy phase.
+*/
+
+bool mysql_trans_prepare_alter_copy_data(THD *thd)
+{
+  DBUG_ENTER("mysql_prepare_alter_copy_data");
+  /*
+    Turn off recovery logging since rollback of an alter table is to
+    delete the new table so there is no need to log the changes to it.
+    
+    This needs to be done before external_lock.
+  */
+  if (ha_enable_transaction(thd, FALSE))
+    DBUG_RETURN(TRUE);
+  DBUG_RETURN(FALSE);
+}
+
+
+/**
+  Commit the copy phase of the alter table.
+*/
+
+bool mysql_trans_commit_alter_copy_data(THD *thd)
+{
+  bool error= FALSE;
+  DBUG_ENTER("mysql_commit_alter_copy_data");
+
+  if (ha_enable_transaction(thd, TRUE))
+    DBUG_RETURN(TRUE);
+  
+  /*
+    Ensure that the new table is saved properly to disk before installing
+    the new .frm.
+    And that InnoDB's internal latches are released, to avoid deadlock
+    when waiting on other instances of the table before rename (Bug#54747).
+  */
+  if (trans_commit_stmt(thd))
+    error= TRUE;
+  if (trans_commit_implicit(thd))
+    error= TRUE;
+
+  DBUG_RETURN(error);
+}
+
+
 static int
 copy_data_between_tables(TABLE *from,TABLE *to,
 			 List<Create_field> &create,
@@ -6766,14 +6814,7 @@ copy_data_between_tables(TABLE *from,TAB
   ulonglong prev_insert_id;
   DBUG_ENTER("copy_data_between_tables");
 
-  /*
-    Turn off recovery logging since rollback of an alter table is to
-    delete the new table so there is no need to log the changes to it.
-    
-    This needs to be done before external_lock
-  */
-  error= ha_enable_transaction(thd, FALSE);
-  if (error)
+  if (mysql_trans_prepare_alter_copy_data(thd))
     DBUG_RETURN(-1);
   
   if (!(copy= new Copy_field[to->s->fields]))
@@ -6932,20 +6973,8 @@ copy_data_between_tables(TABLE *from,TAB
   }
   to->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY);
 
-  if (ha_enable_transaction(thd, TRUE))
-  {
+  if (mysql_trans_commit_alter_copy_data(thd))
     error= 1;
-    goto err;
-  }
-  
-  /*
-    Ensure that the new table is saved properly to disk so that we
-    can do a rename
-  */
-  if (trans_commit_stmt(thd))
-    error=1;
-  if (trans_commit_implicit(thd))
-    error=1;
 
  err:
   thd->variables.sql_mode= save_sql_mode;

=== modified file 'sql/sql_table.h'
--- a/sql/sql_table.h	2010-08-20 02:59:58 +0000
+++ b/sql/sql_table.h	2010-08-20 17:15:48 +0000
@@ -143,6 +143,8 @@ bool mysql_create_table_no_lock(THD *thd
 bool mysql_prepare_alter_table(THD *thd, TABLE *table,
                                HA_CREATE_INFO *create_info,
                                Alter_info *alter_info);
+bool mysql_trans_prepare_alter_copy_data(THD *thd);
+bool mysql_trans_commit_alter_copy_data(THD *thd);
 bool mysql_alter_table(THD *thd, char *new_db, char *new_name,
                        HA_CREATE_INFO *create_info,
                        TABLE_LIST *table_list,


Attachment: [text/bzr-bundle] bzr/mattias.jonsson@oracle.com-20100820171548-6lx5keun4ovejysz.bundle
Thread
bzr push into mysql-5.5-bugfixing branch (mattias.jonsson:3189 to 3190)Bug#54747Mattias Jonsson20 Aug