MySQL Bugs: #109128: MySQL 8.0.31 Read Replica Procedure Call Bug

Bug #109128	MySQL 8.0.31 Read Replica Procedure Call Bug
Submitted:	17 Nov 2022 19:53	Modified:	18 Nov 2022 15:53
Reporter:	David Allen	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	8.0.31	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	ER_OPTION_PREVENTS_STATEMENT, Read Replica

Description:
MYSQL is giving the error "Error: ER_OPTION_PREVENTS_STATEMENT: The MySQL server is running with the --read-only option so it cannot execute this statement" when we execute a stored procedure that does not execute a write but ONLY if we pass a function result as a parameter.

How to repeat:
Whenever we call a Stored Procedure on a Read Replica (AWS RDS) and pass a FUNCTION (any kind) as a parameter (e.g. one that just returns an INT or something), IF the stored procedure has a @@read_only flag check before doing an UPDATE or INSERT, the system always provides the error "Error: ER_OPTION_PREVENTS_STATEMENT: The MySQL server is running with the --read-only option so it cannot execute this statement" even though no write is committed.

If we pass the parameter as a variable or a static value, we do not get this error (using the exact same values).

This call WORKS:

CALL test_rr_sp(1,1,'yay',@out1,@out2);

This call results in the ER_OPTION_PREVENTS_STATEMENT error:

CALL test_rr_sp(1,test_rr_func(),'yay',@out1,@out2);

====

Note it doesn't matter what table the insert is for.

Here is the code to reproduce the issue:

CREATE DEFINER=`root`@`localhost` PROCEDURE `test_rr_sp`(IN in_account_main_id int, IN in_object_base_c smallint, IN in_SQL text, OUT out_result bool, OUT out_result_details json)
    READS SQL DATA
    COMMENT 'XXX'
BEGIN
    IF (@@read_only = FALSE) THEN
        INSERT INTO system_log_error 
        (
            date_created,
            error_source_c,
            error_type_c,
            error_action_c,
            error_details
        )
        VALUES 
        (
            NOW(), 1, 1, 1, JSON_OBJECT('test','test')
        );
    END IF;

    SET out_result = TRUE;
    SET out_result_details = JSON_OBJECT('message','yay!');
END

CREATE DEFINER=`root`@`localhost` FUNCTION `test_rr_func`() RETURNS int
    DETERMINISTIC
BEGIN

RETURN 1;
END

Suggested fix:
The ER_OPTION_PREVENTS_STATEMENT error should not be triggered. Fix the bug! :)

Hi David,

I verified the bug report as I cannot find this limitation documented anywhere but the limitation is there because in case you are pushing the function as a parameter we do not know if that function changes anything or not at that time so we drop the error. Not sure if this is going to be changed in near future or just the limitation will be properly documented, we'll see what the team will decide on.

Thank you for the report

My understanding of the way Functions work with MySQL is they can never write to or update the database, or execute any action that would result in such an action. For clarity and my own understanding, could you explain a circumstance where a user defined function could execute a statement that would result in some sort of write or lock, etc.?

Hi David,

Well it is a bit of a stretch (that is why I verified the bug) but since you can write UDF in C you can for all intents and purposes format a hard drive if you want to, let alone change some data in a database. If there would be a simple/common way of doing it than this would be a "not a bug" situation. Since it is not, I verified a bug. We'll see what will the appropriate team decide :)

all best