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

FTWL failed when event scheduler ON

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.5.8
    • 10.5.9
    • Backup, Events
    • None

    Description

      Hello,

      Investigating a backup failure here is what we found out :

      585222 root localhost NULL Query 39 Waiting for backup lock FLUSH TABLES WITH READ LOCK 0.000

      SELECT * FROM information_schema.metadata_lock_info;
      -----------------------------------------------------------------------------------+

      THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME

      -----------------------------------------------------------------------------------+

      2 MDL_BACKUP_SYS_DML NULL Backup lock    
      2 MDL_SHARED_WRITE NULL Table metadata lock mysql event

      -----------------------------------------------------------------------------------+
      2 rows in set (0.000 sec)

      Thead Id 2

      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      Id User Host db Command Time State Info Progress

      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      2 event_scheduler localhost NULL Daemon 269218 Waiting for next activation NULL

      A workaround is to disable event scheduler before taking backup

      looking at the content of events we have purging process like
      PROCEDURE `p_purge_syslog`(IN max_rows_deleted int unsigned)
      COMMENT 'Remove rows older than 3 months'
      BEGIN
      IF (get_lock('p_purge_syslog', 0) = 1) THEN
      DELETE FROM syslog.logs WHERE date < current_date - INTERVAL 3 MONTH LIMIT max_rows_deleted;
      SELECT release_lock('p_purge_syslog');
      END IF;

      syslog.logs is engine innodb

      PROCEDURE `p_purge_slow_query_log`(max_rows_deleted int unsigned)
      COMMENT 'Remove rows older than 3 week'
      BEGIN
      IF (get_lock('p_purge_slow_query_log', 0) = 1) THEN

      DELETE FROM global_query_review_history WHERE ts_min < current_date - INTERVAL 3 WEEK LIMIT max_rows_deleted;

      SELECT release_lock('p_purge_slow_query_log');

      END IF;

      slow_query_log.global_query_review_history is innodb engine

      Is that expected to have MDL lock on mysql.event when the scheduler is running or is this something inside the procedure code that is blocking backups ?

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              stephane@skysql.com VAROQUI Stephane
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.