[MXS-1295] MaxScale's readwritesplit router does not take into account the fact that stored procedure call may change the value of a user variable Created: 2017-06-23  Updated: 2021-10-13  Resolved: 2017-09-20

Status: Closed
Project: MariaDB MaxScale
Component/s: readwritesplit
Affects Version/s: 2.1.3
Fix Version/s: 2.1.9

Type: Bug Priority: Major
Reporter: Valerii Kravchuk Assignee: markus makela
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Duplicate
duplicates MDEV-26788 Does Maxscale 6.1.3 prevent the reas... Closed
Relates
relates to MXS-1406 Expose more operations Closed
relates to MXS-1407 Bind session to master after CALL. Closed
Sprint: 2017-42

 Description   

MaxScale's ReadWriteSplit router is not smart enough to assume that stored procedure call may modify a user variable. If we have a procedure, p1, defined like this:

delimiter //
 
create procedure p1(OUT p_id int(11) unsigned) 
MODIFIES SQL DATA 
BEGIN 
  ...
 
  SET p_id = some_value; -- last_insert_id(), for example 
END//

then for the following sequence of statements:

SET @id = NULL;
 
CALL p1(@id);
 
SELECT @id;

we may still get NULL from the last statement, even if the value is changed inside the procedure and this is clear when we connect to master directly.

MaxScale should be smarter and either assume that any procedure call may change data, including user variables, or parse the definition of the procedure called to check if this is the case, before decide what node to route queries to.

 Comments   
Comment by Johan Wikman [ 2017-09-11 ]

Parsing the procedure to figure out whether it requires subsequent statements to be routed to the master is not feasible. In practice, what can be done is to pin the session to master after a CALL statement taking user variables as argument, or simply after every CALL statement.

The effect of that can currently be achieved by using the CCR filter with

match=.*CALL.*

and a sufficiently large value for time (larger than the length of the longest session).

Comment by markus makela [ 2017-09-20 ]

The CALL statements can now be configured to "lock" the session to the master node.

This new feature is disabled by default and can be enabled by adding strict_sp_calls=true to the readwritesplit configuration.

Generated at Thu Feb 08 04:05:40 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.