Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17809

Query execution was interrupted and BF lock wait long for trx

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.3.11
    • Fix Version/s: 10.3, 10.4
    • Component/s: Galera, wsrep
    • Labels:
      None
    • Environment:
      Redhat el6, cluster with 2 nodes and 1 arbitrator

      Description

      I am using 10.3.11. I have a table:
      CREATE TABLE `tmp_app_to_db_status_report` (
      `site` char(2) NOT NULL,
      `project` varchar(7) NOT NULL,
      `app_server` varchar(15) NOT NULL,
      `instance` varchar(20) NOT NULL,
      `status` varchar(7) NOT NULL,
      `check_time` datetime NOT NULL,
      PRIMARY KEY (`site`,`project`,`app_server`,`instance`,`status`,`check_time`),
      KEY `idx_tmp_app_to_db_status_report` (`site`,`project`,`app_server`,`instance`,`check_time`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

      The table contains data of Node1 and Node2 and it have the column 'site' to indicate the data coming from which node. Therefore Node1 only inserts, deletes, updates data of Node1 and Node2 only inserts, deletes, updates data of Node2.

      I have a stored procedure which loops for more than 10,000 times and read the data from the table 'tmp_app_to_db_status_report' for processing. It will takes several minutes to finish.

      On Node1, I start the stored procedure to read from 'tmp_app_to_db_status_report' and process Node1's data (i.e. where site='Node1'). During the execution of the stored procedure , on Node2, I delete/insert data of Node2 only (where site='Node2') from/to 'tmp_app_to_db_status_report'. The execution of the stored procedure on Node1 sometimes is terminated with error "ERROR 1317 (70100): Query execution was interrupted" and the massive "BF lock wait long for trx" messages are logged in the mysqld.err of Node1.

      I have attachment the mysqld.err for reference.

      Please advise and thanks in advanced.

        Attachments

        1. my.cnf
          4 kB
        2. mysqld.err
          375 kB
        3. mysqld.zip
          105 kB

          Activity

            People

            Assignee:
            sysprg Julius Goryavsky
            Reporter:
            ktchow_ogcio Chow King Tak
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated: