MySQL Syntax errors with multi_query

[NielsH]

Hi,

We are using ProxySQL v2.0.10 on Debian 10. It is configured with very simple query rules to split reads/writes to master-slaves. There is 1 master and 2 slaves, all running MariaDB 10.4.12, also on Debian 10.

We are talking to ProxySQL from a Debian 8 server using PHP. The PHP version is:
PHP 7.1.33-14+0~20200320.35+debian8~1.gbpb15fae

We are seeing different behaviour when routing queries through ProxySQL, compared to directly talking with MariaDB itself. We have a script to reproduce this:

<?php

include 'config.php'; // defines $host, $user, $password, $schema, $port

function run($func, $query) {
  global $db;

  echo "Run using {$func}: {$query}\n";
  $res = $db->{$func}($query);

  // Success
  if (TRUE === $res) {
    echo "=> No errors\n";
    do {
      if ($result = $db->store_result()) {
        while ($row = $result->fetch_row()) {
          printf("%s\n", $row[0]);
        }
        $result->free();
      }
    } while ($db->next_result());
  // Error
  } elseif (FALSE === $res) {
    echo "=> Error: [{$db->errno}]\n";
    var_dump($db->error);

  // Result pointer?
  } else {
    echo "=> No errors\n";
    while ($row = $res->fetch_row()) {
      printf("%s\n", $row[0]);
    }
    $res->free();
  }

  echo "-----\n";
}

function single($query) { run('query', $query); }
function multi($query)  { run('multi_query', $query); }

$db = new mysqli($host, $user, $password, $schema, $port);

single('SELECT @@report_host');
multi('SELECT 1; SELECT 2;');

Interestingly, when we change:
single('SELECT @@report_host'); --> single('SELECT 1');
it does work correctly. It appears to be related to queries with @ prior to the multi_query function.

See:

$ php test.php
Run using query: SELECT @@report_host
=> No errors
acc-sql002c
-----
Run using multi_query: SELECT 1; SELECT 2;
=> Error: [1064]
string(156) "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT 2' at line 1"
-----

This is affecting some of our migrations as well where of course we use different queries however this was the most minimal example we were able to make to show/reproduce this issue.

These are our query rules:

MySQL [(none)]> SELECT rule_id, active, match_pattern, destination_hostgroup, cache_ttl, apply FROM runtime_mysql_query_rules;
+---------+--------+-----------------------+-----------------------+-----------+-------+
| rule_id | active | match_pattern         | destination_hostgroup | cache_ttl | apply |
+---------+--------+-----------------------+-----------------------+-----------+-------+
| 100     | 1      | ^SELECT .* FOR UPDATE | 1                     | NULL      | 1     |
| 200     | 1      | ^SELECT .*            | 2                     | NULL      | 1     |
| 300     | 1      | .*                    | 1                     | NULL      | 1     |
+---------+--------+-----------------------+-----------------------+-----------+-------+

I have also attached a list of all the proxysql variables.
proxysql-vars.log

Is this a setting we have wrong or is something else the cause? Thank you!

[renecannao]

Verified.
Note:
if replacing single('SELECT @@report_host'); with single('SELECT 1'); , it works successfully.
This seems a regression of a fix introduced in the past , #2449

[castaf]

Same here with ProxySQL 2.0.10, 2.0.9, 2.0.8, 2.0.7 and MariaDB 10.4.12 as backend : multi_query() returns a SQL syntax error (Error 1064). Tested with Moodle 3.8 DDL statements and the script above.

[flavianc]

Even I'm facing this issue, we are sending bulk. " UPDATE x set t1=2; UPDATE x set t2=2; UPDATE x set t3=2;" getting string(156) "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE x' at line 1"

Can we have a fix sooner for this? It has been breaking our application for bulk UPDATE.

[renecannao]

This is now fixed in upcoming 2.0.11 .
Closing.

[castaf]

Well done ! Thanks !

[flavianc]

Thanks, tested new build and works. Cheers.