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

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Won't Fix
    • 10.3.11
    • N/A
    • Galera, wsrep
    • None
    • 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
          Chow King Tak
        2. mysqld.err
          375 kB
          Chow King Tak

        Activity

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.