From: Mattias Jonsson Date: August 17 2010 3:54pm Subject: bzr commit into mysql-5.5-bugfixing branch (mattias.jonsson:3183) Bug#54747 List-Archive: http://lists.mysql.com/commits/115991 X-Bug: 54747 Message-Id: <201008171554.o7H71gse026352@acsinet15.oracle.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============5334059279105758650==" --===============5334059279105758650== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///Users/mattiasj/mysql-bzr/test-55-bugfixing/ based on revid:mattias.jonsson@stripped 3183 Mattias Jonsson 2010-08-17 Bug#54747: Deadlock between REORGANIZE PARTITION and SELECT is not detected 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 before requesting upgrade to exclusive MDL lock. @ mysql-test/r/partition_debug_sync.result updated result @ mysql-test/t/partition_debug_sync.test added test @ sql/sql_base.cc New DEBUG_SYNC point @ 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 Clarifying the comment. modified: mysql-test/r/partition_debug_sync.result mysql-test/t/partition_debug_sync.test sql/sql_base.cc sql/sql_partition.cc sql/sql_table.cc === modified file 'mysql-test/r/partition_debug_sync.result' --- a/mysql-test/r/partition_debug_sync.result 2010-07-01 13:53:46 +0000 +++ b/mysql-test/r/partition_debug_sync.result 2010-08-17 15:54:04 +0000 @@ -1,6 +1,38 @@ DROP TABLE IF EXISTS t1, t2; SET DEBUG_SYNC= 'RESET'; # +# Bug#54747: Deadlock between REORGANIZE PARTITION and +# SELECT is not detected +# +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)); +LOAD DATA LOCAL INFILE '/Users/mattiasj/mysql-bzr/test-55-bugfixing/b/mysql-test/var/t_part_range.load' +INTO TABLE t1 (user_num, hours); +# Start a ALTER PARTITION and wait between the copy and rename of table. +# con1 +SET DEBUG_SYNC= 'wait_while_table_is_used SIGNAL run_select WAIT_FOR select_started'; +ALTER TABLE t1 +REORGANIZE PARTITION hour_003, hour_004 INTO +(PARTITION oldest VALUES LESS THAN (4)); +# Start a concurrent SELECT +SET DEBUG_SYNC= 'now WAIT_FOR run_select'; +SET DEBUG_SYNC= 'after_lock_tables_takes_lock SIGNAL select_started'; +SELECT COUNT(*) FROM t1; +COUNT(*) +10000 +SET DEBUG_SYNC= 'RESET'; +SET GLOBAL innodb_thread_concurrency = 0; +DROP TABLE t1; +# # Bug#42438: Crash ha_partition::change_table_ptr # Test when remove partitioning is done while drop table is waiting # for the table. === modified file 'mysql-test/t/partition_debug_sync.test' --- a/mysql-test/t/partition_debug_sync.test 2010-07-01 13:53:46 +0000 +++ b/mysql-test/t/partition_debug_sync.test 2010-08-17 15:54:04 +0000 @@ -12,6 +12,68 @@ SET DEBUG_SYNC= 'RESET'; --enable_warnings --echo # +--echo # Bug#54747: Deadlock between REORGANIZE PARTITION and +--echo # SELECT is not detected +--echo # + +let $thread_concurrency = `SELECT @@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)); + +--perl +open( DATA, ">$ENV{MYSQLTEST_VARDIR}/t_part_range.load" ) + || die "Could not open file $ENV{MYSQLTEST_VARDIR}/t_part_range.load for writing: $!"; +for ( 1..10000 ) +{ + print DATA ( int(rand(9000000000))+1000000000 ), "\t", ( int(rand(5)) ), "\n"; +} +close( DATA ); +EOF + +eval LOAD DATA LOCAL INFILE '$MYSQLTEST_VARDIR/t_part_range.load' + INTO TABLE t1 (user_num, hours); + + +--echo # Start a ALTER PARTITION and wait between the copy and rename of table. +--echo # con1 + +--connect (con1,localhost,root,,) +SET DEBUG_SYNC= 'wait_while_table_is_used SIGNAL run_select WAIT_FOR select_started'; +--send +ALTER TABLE t1 +REORGANIZE PARTITION hour_003, hour_004 INTO +(PARTITION oldest VALUES LESS THAN (4)); + +--echo # Start a concurrent SELECT + +--connection default +#START TRANSACTION; +SET DEBUG_SYNC= 'now WAIT_FOR run_select'; +SET DEBUG_SYNC= 'after_lock_tables_takes_lock SIGNAL select_started'; +SELECT COUNT(*) FROM t1; + +--connection con1 +--reap + +--disconnect con1 + +--connection default + +SET DEBUG_SYNC= 'RESET'; +eval SET GLOBAL innodb_thread_concurrency = $thread_concurrency; +DROP TABLE t1; + +--echo # --echo # Bug#42438: Crash ha_partition::change_table_ptr --echo # Test when remove partitioning is done while drop table is waiting --echo # for the table. === modified file 'sql/sql_base.cc' --- a/sql/sql_base.cc 2010-08-13 07:50:25 +0000 +++ b/sql/sql_base.cc 2010-08-17 15:54:04 +0000 @@ -2197,6 +2197,8 @@ bool wait_while_table_is_used(THD *thd, table->s->table_name.str, (ulong) table->s, table->db_stat, table->s->version)); + DEBUG_SYNC(thd, "wait_while_table_is_used"); + if (thd->mdl_context.upgrade_shared_lock_to_exclusive( table->mdl_ticket, thd->variables.lock_wait_timeout)) DBUG_RETURN(TRUE); === modified file 'sql/sql_partition.cc' --- a/sql/sql_partition.cc 2010-08-13 07:50:25 +0000 +++ b/sql/sql_partition.cc 2010-08-17 15:54:04 +0000 @@ -4377,7 +4377,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 +4385,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,9 +5527,20 @@ 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); + + /* + 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); + if ((error= file->ha_change_partitions(lpt->create_info, path, &lpt->copied, &lpt->deleted, lpt->pack_frm_data, lpt->pack_frm_len))) @@ -5545,6 +5548,21 @@ static bool mysql_change_partitions(ALTE file->print_error(error, MYF(error != ER_OUTOFMEMORY ? 0 : ME_FATALERROR)); DBUG_RETURN(TRUE); } + + if (ha_enable_transaction(thd, TRUE)) + DBUG_RETURN(TRUE); + + /* + Ensure that the new table is saved properly to disk + before installing new .FRM and releasing InnoDB's internal latches. + */ + error= trans_commit_stmt(thd); + if (trans_commit_implicit(thd)) + error= 1; + + if (error) + DBUG_RETURN(TRUE); /* The error has been reported */ + DBUG_RETURN(FALSE); } === modified file 'sql/sql_table.cc' --- a/sql/sql_table.cc 2010-08-16 12:58:38 +0000 +++ b/sql/sql_table.cc 2010-08-17 15:54:04 +0000 @@ -6902,7 +6902,9 @@ copy_data_between_tables(TABLE *from,TAB /* Ensure that the new table is saved properly to disk so that we - can do a rename + can do a rename. + And release InnoDB's internal latches, to avoid deadlock + when waiting on other tables before rename. */ if (trans_commit_stmt(thd)) error=1; --===============5334059279105758650== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/mattias.jonsson@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: mattias.jonsson@stripped\ # 8gqc71huukaekukv # target_branch: file:///Users/mattiasj/mysql-bzr/test-55-bugfixing/ # testament_sha1: 89c9fa0d05feda3cc0c5c41fcd1fa2f6ffb61baa # timestamp: 2010-08-17 17:54:28 +0200 # base_revision_id: mattias.jonsson@stripped\ # 7m7wm1k2nqrvkrxt # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWQkx6V0ABqX/gEJwEAB8//// f///9P////5gDr2qfc+9y73Xs0eqkgnQ97rweU7vbmx3ezN2VHbEzLWzrlo468JRJpNT9UfqTJtR 6g9RpkxMRpoNNAaAGgaAAAEkiYRkJqeVPaU9U/JMTUxPSaNHqAAAAADT0mnqHGhoGjTI00aZAYmC AAGgNAaZAYEyBIiRpNU2VPZNMmpP0nppPUT9U09qjT1D1PQeqNPUaeoAaNBoAikiZDQ0I8jRJ5T1 NT9EwmQaTJk000DTQ2oDQAEkQTIBGiZGmplP0JgjSj1PTRD1AA0NBoA0GUqkelwMqn/j1rPni16d EPyeqb2u398Fbtex7eR3xfA+bL+Oi77fMf4dd9c6dC7+Dfv4sYPiRMcdzzkd4UO1IuJa3G8vLxXF 0FXS6sCtno4dGCa6LcDUj04oghU7hQ1PcuMoPNsMBhGtXAdrII2F6dXDXLKN1sPJd5Ht2SLT17K/ xl4c6S3S4EB1DwAsxbCRlva1JdM974/IMD+q9fYIt/U7TXH8kGzqzdN/gzHzeyCqPWjwobY222Ez Bx2f5S1yy+UfXEeL+NijA5T8fGEpJvF1JqFjg0LYK6jMOJsUOEsDmWW4v4FIW1dKlEplgiJhJJKL LLCBWSrTIA6iMzif8W9KwIW9EmRp3CkxGeZKkhYicwujy4KjCdh7ysTTZo83IZTQd8kcRBdAeKy2 PDuffvJDayTEW3K0eNua53pkoG+BFdsHwNr3vZkRtp13htGF1OWq9qHILe41E4dCYys2Q6xqyy5n +6hrjtsHC3SBs0JmUNlqP21za+91durCgo5nIsYcwj2C4MNwxbAdClBGD636VHFmTRd3ddLX0dK5 nPba1OksIV8/UyBs++zQta1bVZr0eIyS6GymFc4kHwvmzVdkiF+N1kGiIM8TY22xt6E4Po3wXjGF 5zEi3jXd4IKJimwzFQ+ZOdrBUPKhCYwfE9Tcj37YHdMeJ5GPYdPm5bG66psiIwHB4qlS+kWOnoXS qBTO0xp9cF2BmIyHDizW8sWq5njGCEL4pgGbyNBoZmwWaRH6gUJYPx+Rme2pRe6K6kFy8BdV1y2/ O5t/N5zlpxsNGEwMMMhEXKoRANwclM/YyO/xpsoiv2/b7TTMhcaj6jy2E5QzceoyADa1MJaVHYBC 0dgvYBMY+KkJDCqwCsFAUpj9anOYRFwIqsQ5VEqvAeLo6ylaEpFqgOCJCsofAHiK6YrhQtpAZPht JRoKgvvnAv7K6EXE47uMVjGB4d/b++QJfSyWAubibtugt6ke1hhSdu8Wdk4brvf1njsUBk7EsxKJ SvWOFgfVlbaWhLnEvPALyJDpURKGfP5ROUj3blZzSFuKzGv4jMNtRaMZER1TNDHjO04WIKCXtVcb q9BudmbjIRTU4kZaW79BXq7g8rB+khRVVk8ChbUXExTnFJzoPJi1sA3lph4W4rkXarulcq0UVjoy o4lLrHlBQYy02LMYiKZDWPPAraQv4kBo2rIrvCfEbItuW+GPqHu5mHyKXWrWxicJki4ranWpcF7A rBdC1UGRduBxLPEzIOJTSws16lkxkRntRZeXcJympNcaGdSOsxy8BVHc4klTFpHP0DEhVhSZZkY8 kBrKSH9q+MXiVl2yleF71uTxUWQykQTHMWmrEgKTYuOZ+C3EHgFW+pWHeKKIkyeaHNLYSVrmMRSi bMRclce/recqY0LHaT3IZnFHDtkiFAzMTIkRgRhcz/C1LjfQuGCWbJS7VTjkRMTE9FvL2JTUomxY UyjPmFArPSTCtEpJy8S8HXWsCw11HTKgxVmrs1YnOURsRZ04ye+Sb2goKgmOA3a53W0vW9x1vseZ pM+OyMjPE6QytBGZvn0ERYjlO84nQxaRKG+hCJDnLb8LErB99pMs38ChEibRJzhFrTiqEJQkLlVR FHWQyOut9prL/AV3riJxa0trcxssxepiF5eTZXXRYU3ctbMmi3FpzIARE0RTdoJSSeTOlPWQsqBS k3sug5FFkYxTYGFHEaNC8v8tQbrCZ5wALqKg9ks00JsZ0IsUjAav8yHs13+q5CLjzoXb9p/YcJ3w tD2nbibD+wfgJ6j4JEzk7QT8U9pUflMGqqIBg2PIGE+wCxXCJGzrxiLSSrGWIK2+3jkij2wqnCMl hcPX6l5mf8jMsA7voFcZiNIIIIKH7DRRNExDiIh6iJPX+mO3HE+4PdeSRYHnHgwVQqJHEqHE0JGL mZx2ZTQjRMCNwXoiKDxQYLyoHBBERQgOlQHDxehMCzMmNh8RHkNew71oiYItTUDzCMggbBkcRqKI ESu0NYEOwvJoQxvCKIrA2GpGsZOzMzjvopokFZF2KqQVD3HBURfmmN5VCLQuOcvEYCHZYHlxHecZ wDMpDhbU4R97LIRCAZoiC2uDlyWSJAYOYAQSMQRZQEWBE2qqmFVHqLKjJhHFCY5lovT/pQRqWI4Z lTEwE1tacp4xMYbEYfnH3HUvWJ7DnSUHlSGISMBaVgRQKSiEU7bQlJdgh2ItXaVPSiRiQpek5VQX heCXx18dAchEdEA8oDjjO55OTFUQjIbx25bETK+nGQwvZPIaz7cBaL5TANIHuXG/YZK3646iKB3j Y4DC2DBjtrI0QuFQcGBgC6t4OhFTYhk9h+uaZ0eqiIdjCRI4nDTH61mpOr2sBUTvxwpo/AMpJAVS UkQq5VtBg4jGCpcXaVDeaS5qAOWHvvf3CfcYYzsgTkdtskOUSRZhGVdZBbcLpxGttO07TAUFxB2l Bo1fYrn8N2XUb9Dhea/G3bzptWcrHCQygkibLTKqXzAVyD0TLBzczULgQF6xAEDbGmzDMLndAZLg ggYYHOSOpGszumiJHkFpEbmGVUoXMqpcSNxFCql4+4iki84ROqplmIdN5wjsHq9NeLR3UueQSVV9 3DG6aZM+6NtuaQlzahjIsZxFNVkF2qctCk81SFLTBiR7oNDqy44oNveIiMZerefDpvc5rZDmst4l RjBGaPXjg6HzQr0DIqa0GmP5VlGaIiOCUN0RmFenTLmm7m7W7wjjulEH0PmMCJaWEka9NU/nI+Cb eRb2PKthgWIu1xEuS/na9ZeZ+1FgGO2d44dUs28vqX4dkJkYQtv1bZmkCc3SqJ129/reUrqLPk5m QO7MWRggdsc1MkuhDLvIgcPpY1FUhsLsVgvBu9x+1B2I6FYszzGIHI9R7C4V1Q3NJX/idNqQkyZj mCwvT0pBRvNwy4XFfTHB0puOxQFAYR3JwtF0sA5RSKTVV1LVI4Q2reLz6KW+3jkNjrY6mJONn4wd 5IuoegqZI9kOrok0pGWYL2ssaF5akduMkW1Ibxdw0IzOk6CR3u7JU4pYjbs5yyBZziZubBmSQ4Ln YKYmgowxw6eQCO5OGpf44+AeAPzeJmNrRZt1YpSwtDExDx2C1LTgA3rOPm03IIytAIWm4uHRtbe6 yOSEa9DsaawdrhfiZjcLkczzbDQBz6WsW+LpcmjCFglAUMN4CymZ8FA0wJ68RqS/h07DjegekIhb USSsmGDIjAbZZOk3mKbgaKpse0P3szkMZxhjO9/NURXaY4qhh2T9nb0YXBsYKCRgutPNc4vgfATI fvfvxQdshcTioBmrke5TsYVheo6XSG37DBc+JXwm4viKNR/0QNiyjAzJoQQElqYYEYxF2CqCxKBv e0qAU50VUNSCoLg3KxPSF5KKUID0Gtra63EGEUweqKyCCISCaJMQsbgIfOPju09S75IZ44yo6jY3 6A3hmgGSIzBJu96Sm/aCWTC1YNB+w6IaKIQ0iEGeLo/9NCH6i0r6ORM3Zl86EbAIAZDrVt0Dj7j3 eOwQZ8NS7lpmEr9rQ0tIjb0TEQ4bhGuoqy2DKzzmDu8E87772Drb2XiajO27xdj1um0FnytLmqbn HN3NHCSkZAnEOhFWA9PiVDMI1YsZ6PDFBv2hiyPUgohP1e923wgKphPTfEwVyxgvFfZlTrrQ2Bag tBhNeBVgcwA6Ycq9RPgw0JDux7B/BH3GduqUCYOOhD3rWRM2LFyieHWLOk63mJixjeQKh3ZA0fe1 LViLLcAFSiJnq4Usmxa1y34xI8KNjxREREe0uQIUyqUmVMQok4hZhIYzG2MBBtYMNUB15Dx7lIRm T8KzWIQOAbU6qi9ziceuJMlwnFelWMWygtBD3U9bpwgicwlkHvEZBKLK5F4DMMxA9wMFqNdEyoqx YMZzfp9DEj4ZbuhZ1JOIe9DryR+P/UEG9kbA/iN8lSWpJGmJuNjaNtXcw6SmAUnggsZwHU844EaY W/iVPC6UxFj57Ii4Z2abuPJVfb3T9D8Y+QHIXmoXrFudNdBpcYEHCFt0TXqQZevUhMNzQah0O46X sFzI6Tj2FVd7OzScPNfA4Gm9MUEGxrPEboFfyI885V0rvrQqb7tKfBzGI5DMcSkRlG36DSjwE053 qpHmaDjdg11Pg4BJda3GUm0MzxTjtGu4zhQBtz0P3eg7D/F3JFOFCQCTHpXQ --===============5334059279105758650==--