memc_delete called from a MySQL trigger loop fails randomly

Question

I'm calling memc_delete from a MySQL trigger's code. The call is inside a loop. Logging the results, I see that memc_delete sometimes returns 1 (ok, memcached entry deleted) and sometimes returns 0 (entry is not deleted). I suspect the cause is too many calls in a row. If one call is raised before the previous one ends, it causes an error. Does it make any sense?

If yes, Is there any way to 'enqueue' calls, or set a 'wait until finish' mode?

Anyway, I'm trying to figure out the error's cause. I see here that the C/C++ memcached interface allows to translate the error code into a printable string with memcached_strerror, but an equivalent function callable from SQL doesn't seem to exist

Any ideas?

Thanks in advance

Answer 1

DELIMITER $$

CREATE TRIGGER my_trigger BEFORE DELETE ON my_table
FOR EACH ROW
BEGIN
    DECLARE memcached_result INT;
    
    -- Attempt to delete the key from memcached
    SET memcached_result = memc_delete('your_key');
    
    -- Check the result and handle errors
    IF memcached_result = 1 THEN
        -- Key successfully deleted
        -- Perform other trigger actions
    ELSE
        -- Handle the error (e.g., log it)
        INSERT INTO error_log (message) VALUES ('Memcached deletion failed');
        -- Optionally, you can raise an error or take other actions
    END IF;
END$$

DELIMITER ;