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

ALTER TABLE event take infinite time which for example breaks mysql_upgrade

Details

    • Bug
    • Status: Closed (View Workflow)
    • Blocker
    • Resolution: Fixed
    • 10.5.8, 10.5
    • 10.5.9
    • Events, Locking
    • None

    Description

      Upgrade from mariadb 10.3 to 10.5.8
      mysql_upgrade scripts executes:
      ALTER TABLE event DROP PRIMARY KEY, ADD PRIMARY KEY(db, name)
      this however takes infinite time if there are any events in table event. Its the same if query is executed manualy on mysql database. If events are removed then query is executed ok and mysql_upgrade works fine.

      Attachments

        Issue Links

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24185 Mariabackup is getting stuck when event_scheduler is running and acquired lock on mysql.event

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24459 FTWL failed when event scheduler ON

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24682 event_scheduler lock problem

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova added a comment - - edited

            Thanks for the report.
            I am raising the priority, because it is likely to affect all users who use event scheduler.

            Starting from this commit in 10.5

            commit 4102f1589c23309de968a5bf9511d3228a1b9319 
            Author: Monty
            		 
            Date:   Sat May 2 13:19:53 2020 +0300
            		 
             
            		 
                Aria will now register it's transactions

            after the first event is executed, the server acquires and keeps holding backup/metadata locks on mysql.event table – seemingly forever, as long as the event scheduler is enabled. It doesn't prevent further creation of events, but it blocks DDL on the table, hence the problem.

            I cannot tell whether this new locking routine was intentional; but even if it is, mysql_upgrade should somehow take it into account, maybe temporarily disable event_scheduler while it handles system tables.

            The test case below is dirty and shouldn't go as is into the regression suite, it's just a demonstration of the problem.

            install soname 'metadata_lock_info';
            		 
            --let $ts= `SELECT DATE_ADD(CURRENT_TIMESTAMP(), INTERVAL 3 SECOND)`
            		 
            eval create event ev1 on schedule every 1 minute starts '$ts' do set @a=1;
            		 
            set global event_scheduler= on;
            		 
            sleep 2;
            		 
            show status like 'Executed_events';
            		 
            select * from information_schema.metadata_lock_info;
            		 
            sleep 2;
            		 
            show status like 'Executed_events';
            		 
            select * from information_schema.metadata_lock_info;
            		 
             
            		 
            echo #;
            		 
            echo # If you now run ALTER TABLE mysql.event <something non-void>, it will hang;
            		 
            echo #;

            10.5 8de233af

            		 
            set global event_scheduler= on;
            		 
            show status like 'Executed_events';
            		 
            Variable_name	Value
            		 
            Executed_events	0
            		 
            select * from information_schema.metadata_lock_info;
            		 
            THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
            		 
            show status like 'Executed_events';
            		 
            Variable_name	Value
            		 
            Executed_events	1
            		 
            select * from information_schema.metadata_lock_info;
            		 
            THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
            		 
            5	MDL_BACKUP_SYS_DML	NULL	Backup lock		
            5	MDL_SHARED_WRITE	NULL	Table metadata lock	mysql	event

            elenst Elena Stepanova added a comment - - edited Thanks for the report. I am raising the priority, because it is likely to affect all users who use event scheduler. Starting from this commit in 10.5 commit 4102f1589c23309de968a5bf9511d3228a1b9319 Author: Monty Date: Sat May 2 13:19:53 2020 +0300   Aria will now register it's transactions after the first event is executed, the server acquires and keeps holding backup/metadata locks on mysql.event table – seemingly forever, as long as the event scheduler is enabled. It doesn't prevent further creation of events, but it blocks DDL on the table, hence the problem. I cannot tell whether this new locking routine was intentional; but even if it is, mysql_upgrade should somehow take it into account, maybe temporarily disable event_scheduler while it handles system tables. The test case below is dirty and shouldn't go as is into the regression suite, it's just a demonstration of the problem. install soname 'metadata_lock_info' ; --let $ts= `SELECT DATE_ADD(CURRENT_TIMESTAMP(), INTERVAL 3 SECOND)` eval create event ev1 on schedule every 1 minute starts '$ts' do set @a=1; set global event_scheduler= on ; sleep 2; show status like 'Executed_events' ; select * from information_schema.metadata_lock_info; sleep 2; show status like 'Executed_events' ; select * from information_schema.metadata_lock_info;   echo #; echo # If you now run ALTER TABLE mysql.event <something non-void>, it will hang; echo #; 10.5 8de233af set global event_scheduler= on ; show status like 'Executed_events' ; Variable_name Value Executed_events 0 select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME show status like 'Executed_events' ; Variable_name Value Executed_events 1 select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME 5 MDL_BACKUP_SYS_DML NULL Backup lock 5 MDL_SHARED_WRITE NULL Table metadata lock mysql event
            serg Sergei Golubchik added a comment -

            I agree it's a serious bug. event scheduler should close the transaction after reading the event table.

            serg Sergei Golubchik added a comment - I agree it's a serious bug. event scheduler should close the transaction after reading the event table.
            monty Michael Widenius added a comment -

            The problem was that update_timing_fields_for_event() didn't release all MDL locks it took.
            (No problem with reading the event table or closing transactions

            monty Michael Widenius added a comment - The problem was that update_timing_fields_for_event() didn't release all MDL locks it took. (No problem with reading the event table or closing transactions
            monty Michael Widenius added a comment -

            Fix pushed

            monty Michael Widenius added a comment - Fix pushed

            People

              monty Michael Widenius
              naox naox
              Votes:
              0 Vote for this issue
              Watchers:
              8 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.