Multi-Statement execution behaves differently between MySQL / HTTP

[pavelnemirovsky]

Guys,

Can you explain how to execute multi-statement via HTTP interface?

mysql -P9306 -h0 -e 'select 1; select 2;'
+------+
| 1    |
+------+
| 1    |
+------+
+------+
| 2    |
+------+
| 2    |
+------+

But via HTTP I am getting an error:

curl --location 'http://localhost:9308/sql?mode=raw' \
--header 'Content-Type: application/json' \
--data 'query=select 1;select 2;'

Response:

{
    "error": "sphinxql: syntax error, unexpected SELECT, expecting $end near 'select 2;'"
}

It looks like it should work according to the documentation described in here

[klirichek]

Not sure that first statement is really 'multistatement'.
Notice, mysql cli itself breaks statements by ';'. So, 'select 1; select 2;' would be as 2 separate statements, and also 1 multi-statement, depending from mysql delimiter.

$ mysql -h0 -P9315
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 6.0.3 DEADBEEF@220512 devmode developer version

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select 1; select 2;
+------+
| 1    |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

+------+
| 2    |
+------+
| 2    |
+------+
1 row in set (0.00 sec)

mysql> delimiter '|';
mysql> select 1; select 2;|
ERROR 1064 (42000): (P01): syntax error, unexpected SELECT, expecting $end near 'select 2;'

[sanikolaev]

Can you explain how to execute multi-statement via HTTP interface?

Multi-queries is not working for queries like select 1, so use real queries and don't forget about urlencoding since that's required.

For example:

➜  ~ curl -s --location 'http://localhost:9308/sql?mode=raw' \
--header 'Content-Type: application/json' \
--data 'query=select%20%2A%20from%20t%20where%20match%28%27abc%27%29%3B%20select%20%2A%20from%20t%20where%20a%3D123%3B%20show%20meta%3B'|jq .

[
  {
    "columns": [
      {
        "id": {
          "type": "long long"
        }
      },
      {
        "f": {
          "type": "string"
        }
      },
      {
        "a": {
          "type": "long"
        }
      }
    ],
    "data": [
      {
        "id": 1,
        "f": "abc",
        "a": 0
      },
      {
        "id": 2,
        "f": "abc",
        "a": 123
      }
    ],
    "total": 2,
    "error": "",
    "warning": ""
  },
  {
    "columns": [
      {
        "id": {
          "type": "long long"
        }
      },
      {
        "f": {
          "type": "string"
        }
      },
      {
        "a": {
          "type": "long"
        }
      }
    ],
    "data": [
      {
        "id": 2,
        "f": "abc",
        "a": 123
      }
    ],
    "total": 1,
    "error": "",
    "warning": ""
  },
  {
    "columns": [
      {
        "Variable_name": {
          "type": "string"
        }
      },
      {
        "Value": {
          "type": "string"
        }
      }
    ],
    "data": [
      {
        "Variable_name": "total",
        "Value": "1"
      },
      {
        "Variable_name": "total_found",
        "Value": "1"
      },
      {
        "Variable_name": "total_relation",
        "Value": "eq"
      },
      {
        "Variable_name": "time",
        "Value": "0.000"
      }
    ],
    "total": 4,
    "error": "",
    "warning": ""
  }
]

[pavelnemirovsky]

@sanikolaev thank you so much for clarification.