[MDEV-31388] Error in PROCEDURE in v.10.6.13 with RELEASE_LOCK Created: 2023-06-02  Updated: 2023-06-15  Resolved: 2023-06-02

Status: Closed
Project: MariaDB Server
Component/s: Events, Galera, Locking
Affects Version/s: 10.6.13
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Moises Garcia Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Environment:

Description: Ubuntu 22.04.2 LTS
Release: 22.04
Codename: jammy


Issue Links:
Duplicate
duplicates MDEV-31325 Can not run db patches using flyway w... Closed
Problem/Incident
is caused by MDEV-30473 Do not allow GET_LOCK() / RELEASE_LOC... Closed

 Description   

After upgrading MariaDB from 10.6.12 to 10.6.13 we started to get these kinds of errors:

2023-06-02 12:01:21 5510 [ERROR] Event Scheduler: [sa@%][my_table.session_scheduler_event] Failed to load routine my_table.event_scheduler_update (internal code -6). For more details, run SHOW WARNINGS
2023-06-02 12:01:21 5510 [Note] Event Scheduler: [sa@%][my_table.session_scheduler_event] At line 1 in my_table.session_scheduler_event
2023-06-02 12:01:21 5510 [Note] Event Scheduler: [sa@%].[my_table.session_scheduler_event] event execution failed.
2023-06-02 12:01:21 5511 [ERROR] Event Scheduler: [sa@%][my_table.session_scheduler_event] This version of MariaDB doesn't yet support 'RELEASE_LOCK in cluster (WSREP_ON=ON)'

This is how is defined the EVENT in the table

CREATE DEFINER=`sa`@`%` EVENT `session_scheduler_event`
	ON SCHEDULE
		EVERY 30 SECOND STARTS '2023-06-01 01:04:11'
	ON COMPLETION NOT PRESERVE
	ENABLE
	COMMENT ''
	DO CALL event_scheduler_update()

And the PROCEDURE event_scheduler_update

CREATE DEFINER=`sa`@`%` PROCEDURE `event_scheduler_update`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN 
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
                BEGIN
                    DO RELEASE_LOCK("my_table_event_lock");
                END;
            
                BEGIN
                    DECLARE inqueue_count INT;
                    DECLARE scheduled_count INT;
                    
                    IF IS_FREE_LOCK("my_table_event_lock") AND GET_LOCK("my_table_event_lock", 0) THEN
                        CALL main_queue_update(1822);
                    END IF;
                    
                    DO RELEASE_LOCK("my_table_event_lock");
                END;
END



 Comments   
Comment by Sergei Golubchik [ 2023-06-02 ]

it's caused by MDEV-30473 and was already reported as MDEV-31325. Please, watch MDEV-31325 instead.

Comment by Moises Garcia [ 2023-06-02 ]

Perfect!! Thank you very much, I will keep an eye on it.
I'm running 10.11.3 in my local environment, and it does not have that issue.

Comment by Jan Lindström [ 2023-06-15 ]

moik78 GET_LOCK is not recommended on a cluster see https://mariadb.com/kb/en/mariadb-galera-cluster-known-limitations/ if you really need to use it you could do SET SESSSION wsrep_on = OFF.

Generated at Thu Feb 08 10:23:29 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.