Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-5576

Server stalls because ALTER TABLE on partitioned table holds dict mutex

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 5.7.25-28, 8.0.15-5
    • Fix Version/s: 5.7.27-30
    • Component/s: None

      Description

      TRUNCATE PARTITION blocks completely unrelated queries.

      This was originally reported at https://bugs.mysql.com/bug.php?id=94610 However I have simpler test case for MTR.

      How to Repeat.

      Modify code:

      1. sveta@delly:~/src/percona-server$ git diff
        diff --git a/storage/innobase/row/row0mysql.cc b/storage/innobase/row/row0mysql.cc
        index 45c0e629fd9..be2d878690b 100644
        --- a/storage/innobase/row/row0mysql.cc
        +++ b/storage/innobase/row/row0mysql.cc
        @@ -3494,6 +3494,8 @@ row_mysql_lock_data_dictionary_func(
                trx->dict_operation_lock_mode = RW_X_LATCH;
         
                mutex_enter(&dict_sys->mutex);
        +
        +       DBUG_EXECUTE_IF("bug94610", {my_sleep(60000000); fprintf(stderr, "bug94610\n");});
         }
        

      Run test case:

      $ cat ~/src/tests/t/bug94610.test
      --source include/have_innodb.inc
      --source include/have_debug.inc
      --source include/have_debug_sync.inc
      create table t1(f1 int) engine=innodb;
      insert into t1 values(1);
      insert into t1 values(2);
      CREATE TABLE `t2` (
        `id` bigint(20) NOT NULL,
        `p` json NOT NULL,
        `m` tinyint(2) NOT NULL,
        `ho` char(64) COLLATE utf8_bin GENERATED ALWAYS AS (json_unquote(json_extract(`p`,'$.ho'))) VIRTUAL,
        `hr` char(64) COLLATE utf8_bin GENERATED ALWAYS AS (json_unquote(json_extract(`p`,'$.hr'))) VIRTUAL,
        PRIMARY KEY (`id`,`m`),
        KEY `originator_idx` (`ho`(16)),
        KEY `recipient_idx` (`hr`(16))
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
      PARTITION BY RANGE (`m`)
      (PARTITION p1 VALUES LESS THAN (2) ENGINE = InnoDB,
       PARTITION p2 VALUES LESS THAN (3) ENGINE = InnoDB,
       PARTITION p3 VALUES LESS THAN (4) ENGINE = InnoDB,
       PARTITION p4 VALUES LESS THAN (5) ENGINE = InnoDB,
       PARTITION p5 VALUES LESS THAN (6) ENGINE = InnoDB,
       PARTITION p6 VALUES LESS THAN (7) ENGINE = InnoDB,
       PARTITION p7 VALUES LESS THAN (8) ENGINE = InnoDB,
       PARTITION p8 VALUES LESS THAN (9) ENGINE = InnoDB,
       PARTITION p9 VALUES LESS THAN (10) ENGINE = InnoDB,
       PARTITION p10 VALUES LESS THAN (11) ENGINE = InnoDB,
       PARTITION p11 VALUES LESS THAN (12) ENGINE = InnoDB,
       PARTITION p12 VALUES LESS THAN (13) ENGINE = InnoDB);
      set debug="d,bug94610";
      --send alter table t2 truncate partition p3
      --connect(con1,localhost,root,,test)
      --connection con1
      --sleep 5
      select now();
      insert into t1 values(3);
      select now();
      --connection default
      --reap
      set debug='';
      drop table t1, t2;
      

      Notice that INSERT command took 55 seconds (should return immediately).

        Smart Checklist

          Attachments

          1. s1.st
            40 kB
          2. s2.st
            38 kB
          3. s3.st
            44 kB
          4. s4.st
            38 kB

            Issue Links

              Activity

                People

                Assignee:
                satya.bodapati Satya Bodapati
                Reporter:
                sveta.smirnova Sveta Smirnova
                Votes:
                1 Vote for this issue
                Watchers:
                4 Start watching this issue

                  Dates

                  Created:
                  Updated:
                  Resolved:

                    Time Tracking

                    Estimated:
                    Original Estimate - Not Specified
                    Not Specified
                    Remaining:
                    Remaining Estimate - Not Specified
                    Not Specified
                    Logged:
                    Time Spent - 1 week, 4 days, 6 hours, 16 minutes
                    1w 4d 6h 16m