[MXS-4147] multi-statement detection is not working for create procedure Created: 2022-05-30  Updated: 2022-06-09  Resolved: 2022-06-09

Status: Closed
Project: MariaDB MaxScale
Component/s: N/A
Affects Version/s: 2.4
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: danielforever Assignee: markus makela
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

select 1; CREATE PROCEDURE doiterate(p1 INT)
BEGIN
label1: LOOP
SET p1 = p1 + 1;
IF p1 < 10 THEN ITERATE label1; END IF;
LEAVE label1;
END LOOP label1;
END;

The create procedure has multiple statements, and there's `if else end if` clause.
The current logic seems not correct in handling SQL above.

 Comments   
Comment by danielforever [ 2022-06-07 ]

@markus any update on this?

Comment by markus makela [ 2022-06-07 ]

daniel_xu_forever seems to work as expected on the latest 2.4 version:

2022-06-07 19:37:41   info   : (3) [readwritesplit] Servers and router connection counts:
 
2022-06-07 19:37:41   info   : (3) [readwritesplit] current operations : 0 in 	[127.0.0.1]:3000 Master, Running
 
2022-06-07 19:37:41   info   : (3) [readwritesplit] current operations : 0 in 	[127.0.0.1]:3001 Slave, Running
 
2022-06-07 19:37:41   info   : (3) [readwritesplit] current operations : 0 in 	[127.0.0.1]:3002 Slave, Running
 
2022-06-07 19:37:41   info   : (3) [readwritesplit] current operations : 0 in 	[127.0.0.1]:3003 Slave, Running
 
2022-06-07 19:37:41   info   : (3) [readwritesplit] Connected to 'server1'
 
2022-06-07 19:37:41   info   : (3) [readwritesplit] Selected Master: server1
 
2022-06-07 19:37:41   info   : (3) [readwritesplit] Connected to 'server2'
 
2022-06-07 19:37:41   info   : (3) [readwritesplit] Selected Slave: server2
 
2022-06-07 19:37:41   info   : (3) [readwritesplit] Connected to 'server3'
 
2022-06-07 19:37:41   info   : (3) [readwritesplit] Selected Slave: server3
 
2022-06-07 19:37:41   info   : (3) [readwritesplit] Connected to 'server4'
 
2022-06-07 19:37:41   info   : (3) [readwritesplit] Selected Slave: server4
 
2022-06-07 19:37:41   info   : (3) Started RW-Split-Router client session [3] for 'maxuser' from ::ffff:127.0.0.1
 
2022-06-07 19:37:41   info   : (3) Connected to 'server1' with thread id 221
 
2022-06-07 19:37:41   info   : (3) Connected to 'server2' with thread id 140
 
2022-06-07 19:37:41   info   : (3) Connected to 'server3' with thread id 180
 
2022-06-07 19:37:41   info   : (3) Connected to 'server4' with thread id 178
 
2022-06-07 19:37:41   info   : (3) > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 37, type: QUERY_TYPE_READ|QUERY_TYPE_SYSVAR_READ, stmt: select @@version_comment limit 1 
2022-06-07 19:37:41   info   : (3) [readwritesplit] Route query to slave: server2 	[127.0.0.1]:3001 <
 
2022-06-07 19:37:41   info   : (3) [readwritesplit] Reply complete, last reply from server2
 
2022-06-07 19:37:42   info   : (3) Multi-statement query or stored procedure call, routing all future queries to master.
 
2022-06-07 19:37:42   info   : (3) > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 161, type: QUERY_TYPE_READ, stmt: select 1; CREATE PROCEDURE doiterate(p1 INT) BEGIN label1: LOOP SET p1 = p1 + 1; IF p1 < 10 THEN ITERATE label1; END IF; LEAVE label1; END LOOP label1; END; 
2022-06-07 19:37:42   info   : (3) [readwritesplit] Route query to master: server1 	[127.0.0.1]:3000 <
 
2022-06-07 19:37:42   info   : (3) [readwritesplit] Reply not yet complete. Waiting for 1 replies, got one from server1
 
2022-06-07 19:37:42   info   : (3) [readwritesplit] Reply complete, last reply from server1

Comment by markus makela [ 2022-06-07 ]

Please attach the configuration you used (remove any passwords or other sensitive information) and which client program you used to execute the SQL.

Comment by danielforever [ 2022-06-08 ]

@markus My bad, I didn't describe the issue clearly.
The issue now is that the current implementation falsely claims that a create procedure SQL as multistatement.

Using the following sql as an example, the algorithm works like this:

It jumps to the first `;` and tries to skip the procedure (with multi-sql procedure, it fails to skip it). Then it starts to check whether the remaining tokens are comments or non-sql (in this case, they aren't) so it will be regarded as multi-statement.

CREATE PROCEDURE doiterate(p1 INT)
BEGIN
label1: LOOP
SET p1 = p1 + 1;
IF p1 < 10 THEN ITERATE label1; END IF;
LEAVE label1;
END LOOP label1;
END;

Comment by markus makela [ 2022-06-08 ]

The fact that it claims the CREATE PROCEDURE is a multi-statement might be wrong but the way it is routed is correct: to the current master server. All multi-statement SQL queries (CREATE PRODECURE and BEGIN NOT ATOMIC blocks) are all treated as if they were writes as the parser in MaxScale does not parse those fully.

Is there some practical problem that this behavior causes?

Comment by markus makela [ 2022-06-09 ]

I'll close this as Not a Bug since it's expected and correct behavior to treat compound statements the same way multi-statements are treated.

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