0

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?

Floobinator
  • 388
  • 2
  • 11
  • I tested on MySQL 8.0.31 on my local instance, but I didn't get the error. I guess it's something specific to AWS RDS. I suggest you open a support ticket with AWS. – Bill Karwin Nov 05 '22 at 18:20
  • Thanks @BillKarwin, I opened a ticket with AWS. I'll let you know what they say! – Floobinator Nov 06 '22 at 04:24
  • Hey @BillKarwin. AWS can't figure out what's going ON LOL. I'm starting to wonder if this is a MySQL error. As mentioned it ONLY happens when we use a function call (that only reads a database value) in the main call, but when we hardcode a number it works fine. Makes me wonder if there's some weird setting that writes to a log when a function is used as a parameter of a stored procedure? – Floobinator Nov 09 '22 at 16:17
  • No, MySQL doesn't do that. First it calls the function, gets the result, then uses that value as the argument to the procedure. Just like if you use any other expression as the argument to a function. As I said, I tested your code on my own local instance of MySQL and I got no error. I'm not surprised that AWS support is useless, that seems to be everyone's experience. – Bill Karwin Nov 09 '22 at 16:56
  • @BillKarwin AWS is STILL investigating. The issue happens when we call any Stored Procedure on the RR with any function. I'll let you know what they come up with, but my team is stumped. – Floobinator Nov 11 '22 at 15:37
  • You may have to do the procedure call in two steps: first get the value returned by that function, then use that value as a parameter to the procedure. A few lines more code in your app, but maybe it won't fail with an error. – Bill Karwin Nov 11 '22 at 15:53
  • Hey @BillKarwin I posted an update above. I think you're right - only workaround is to pass variables. – Floobinator Nov 15 '22 at 18:49

1 Answers1

0

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.

Floobinator
  • 388
  • 2
  • 11