Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.5.8
-
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
- duplicates
-
MDEV-24452 ALTER TABLE event take infinite time which for example breaks mysql_upgrade
- Closed