Uploaded image for project: 'MariaDB MaxScale'
  1. MariaDB MaxScale
  2. MXS-1527

SELECT with session var is not supported

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.1.10
    • Fix Version/s: 2.1.13, 2.2.1
    • Component/s: readwritesplit
    • Labels:
      None
    • Environment:
      [root@maxscale maxscale.cnf.d]# uname -r
      3.10.0-327.4.5.el7.x86_64 #: centos 7.2

      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

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              johan.wikman Johan Wikman
              Reporter:
              wagnerbianchi Wagner Bianchi (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.