UPDATE 11.15.2022
I have conducted extensive testing and found the pattern of problem here. Once again, what's strange is this ONLY happens if you pass a function as a parameter to the originating Stored Procedure; passing a hardcoded value or variable works just fine.
The issue is when the Stored Procedure calls another Stored Procedure that checks @@read_only to see if it can WRITE to the database. I confirmed removing any code that writes data fixes the issue -- so ultimately it appears passing a STATIC value to the SP causes the procedure execution to bypass any writing (as expected) because of the IF @@read_only = FALSE THEN ...write...
It seems passing a function somehow causes MySQL to compile a "tree" of calls and subcalls to see if they CAN write rather than if they DO write.
It appears the only way to work around this is to pass the parameters as variables rather than function calls. We can do this, but it will require substantial refactoring.
I just wonder why MySQL is doing this - why passing a function is causing the system to look ahead and see IF it COULD write rather than if it does.
We have a Read Replica that's up and running just fine. We can execute reads against it without a problem.
We can do this:
CALL get_table_data(1, 1, "SELECT * from PERSON where ID=1;", @out_result, @out_result_value);
And it executes fine. Note it's READS SQL DATA tagged. It does not write anything out.
We can also do this:
SELECT get_value("OBJECT_BASE", "NAME");
Which is SELECT function that is READ ONLY.
However, if we try to execute this:
CALL get_table_data(1, get_value("OBJECT_BASE", "NAME"), "SELECT * from PERSON where ID=1;", @out_result, @out_result_value);
We get the error:
Error: ER_OPTION_PREVENTS_STATEMENT: The MySQL server is running with the --read-only option so it cannot execute this statement
We're baffled at what could cause this. Both the SP and function are read-only and execute individually just fine, but the second we embed the function result in the call of the SP, the system chokes.
Any ideas?
So AWS cannot figure this out. The issue only happens when a function is passed as a parameter to a stored procedure that calls another stored procedure (not even passing the value of the function) that has a @@read_only check before doing an INSERT or UPDATE. So for some reason, the system is doing a pre-scan check when a function is passed vs. a variable or hardcoded value.
The workaround is to pass the function value as a variable.
I'm going to report this issue to Oracle as it might be some sort of bug, especially given the function is DETERMINISTIC.