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

Default behavior of use_sql_variables_in is unnecessarily strict

    XMLWordPrintable

    Details

    • Sprint:
      MXS-SPRINT-162

      Description

      By default, any SELECT statement that modifies a user variable causes the following warning to be logged.

      MXS_WARNING("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.");
      

      For example, a common use of user variables in SELECT statements is as row numbers :

      SET @rownum := 1;
      SELECT @rownum := @rownum + 1 FROM my_table;
      

      With modern MariaDB versions, this is less common now that the ROW_NUMBER window function exists. However, the method still sees some use.

      Another example can be seen in the MariaDB ODBC connector where it uses user variables to store information that is used elsewhere in the query.


      The proposed change is to simply remove the error generation code and allow SELECT statements to be stored in the session command history if they modify a user variable. This usually will end up executing somewhat redundant queries on the other servers but it is necessary in order to guarantee that the variables are defined on all servers. The one problem that does remain is that if the result is not deterministic, the value of the variable can end up being different on different servers. However, this is already the case with statements like the following which are allowed even now:

      SET @myvar = (SELECT @@server_id + COUNT(*) FROM mysql.user)
      

      The historical reason for the whole use_sql_variables_in was the inability to correctly handle multiple concurrent resultsets. With the improvements done to MaxScale, this is no longer a problem. The variable use_sql_variables_in should remain as it allows the end-user to select whether they want the "correct" behavior of routing the SELECT statements to all backends or the more efficient method of routing them only to the current master server (use_sql_variables_in=master).

        Attachments

          Activity

            People

            Assignee:
            markus makela markus makela
            Reporter:
            markus makela markus makela
            Votes:
            0 Vote for this issue
            Watchers:
            1 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.