[MXS-1527] SELECT with session var is not supported Created: 2017-11-16  Updated: 2017-12-27  Resolved: 2017-12-27

Status: Closed
Project: MariaDB MaxScale
Component/s: readwritesplit
Affects Version/s: 2.1.10
Fix Version/s: 2.1.13, 2.2.1

Type: Bug Priority: Major
Reporter: Wagner Bianchi (Inactive) Assignee: Johan Wikman
Resolution: Fixed Votes: 0
Labels: None
Environment:

[root@maxscale maxscale.cnf.d]# uname -r
3.10.0-327.4.5.el7.x86_64 #: centos 7.2

Issue Links:
Blocks
blocks MXS-1526 Malformed disk image error Closed

 Description   

Folks,

Testing a simple SELECT with @@hostname var, I'm seeing a strange behavior when targeting the query to get executed using Maxscale. It's not getting executed and is pointing that SELECT with session data modification is not supported if configuration parameter use_sql_variables_in=all.

#: query
 
[root@maxscale maxscale.cnf.d]# mysql -u wb -p123 -h 192.168.50.100 -P 4006 -e "select if(@@hostname='box02','prod_mariadb02','n')"
 
ERROR 1064 (42000) at line 1: Routing query to backend failed. See the error log for further details.
 
 
 
#: maxscale log event
 
2017-11-16 15:33:29   error  : (3) [readwritesplit] Can't route MYSQL_COM_QUERY:QUERY_TYPE_READ|QUERY_TYPE_GSYSVAR_WRITE:"select if(@@hostname='box02','prod_mariadb02','n')". SELECT with session data modification is not supported if configuration parameter use_sql_variables_in=all .

Adding the use_sql_variables_in=master to the services definition, I've got the same:

[rwsplit-service]
 
servers=prod_mariadb01,prod_mariadb02,prod_mariadb03
 
use_sql_variables_in=master
 
 
 
...maxscale restart...
 
 
 
17-11-16 15:33:29   warning: (3) [readwritesplit] The query can't be routed to all backend servers because it includes SELECT and SQL variable modifications which is not supported. Set use_sql_variables_in=master or split the query to two, where SQL variable modifications are done in the first and the SELECT in the second one.
 
2017-11-16 15:33:29   error  : (3) [readwritesplit] Can't route MYSQL_COM_QUERY:QUERY_TYPE_READ|QUERY_TYPE_GSYSVAR_WRITE:"select if(@@hostname='box02','prod_mariadb02','n')". SELECT with session data modification is not supported if configuration parameter use_sql_variables_in=all .

It's not checking that I added the use_sql_variables_in=master to service's definition?

To have the same environment I'm using for tests, please, refer to https://jira.mariadb.org/browse/MXS-1526

 Comments   
Comment by Johan Wikman [ 2017-12-27 ]

The problem is that the statement is incorrectly classified as QUERY_TYPE_READ|QUERY_TYPE_GSYSVAR_WRITE although it should be classifed as QUERY_TYPE_READ|QUERY_TYPE_SYSVAR_READ.

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