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 |
I agree it's a serious bug. event scheduler should close the transaction after reading the event table.
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 
Fix pushed