Multi-query SQL create table and add to cluster doesn't actually add

General Discussions - English
1

I’m trying to create a table and add it to a cluster in a single HTTP request – this is necessary for our use case because we’re running a HA multi-master setup. In order to add a table to the cluster you need to create it locally and then add it, so if we run those in separate queries they can get sent to different masters which will of course cause it to fail.

The docs state the mode=raw SQL HTTP endpoint supports any valid SQL, including multi-queries, and I can run a simple multi-query (e.g. query=SHOW TABLES&query=SELECT * FROM test) just fine. However, when running CREATE TABLE…ALTER CLUSTER the table gets created but isn’t actually added to the cluster.

mysql> SHOW TABLES;
mysql> CREATE CLUSTER test_cluster;
mysql> exit
# curl localhost:9308/sql?mode=raw -d 'query=CREATE TABLE test(name string)&query=ALTER CLUSTER test_cluster ADD test'
[{"total":0,"error":"","warning":""}]# 
# mysql
mysql> SHOW TABLES;
+-------+------+
| Index | Type |
+-------+------+
| test  | rt   |
+-------+------+
mysql> ALTER CLUSTER test_cluster DROP test;
ERROR 1064 (42000): table 'test' is not in cluster 'test_cluster'

Does anyone know if there’s a way to get this to work, or a reason why it can’t?

We’re running Manticore 6.0.4 - Manticore 6.0.4 1a3a4ea82@230314 (columnar 2.0.4 5a49bd7@230306) (secondary 2.0.4 5a49bd7@230306)

2

you can not run multi query via HTTP interface.

I sure only search queries and insert \ replace supports multi-queries. You can not issue other different queries together.

3

Interesting. So when the /sql docs say

The /sql endpoint also includes a special “raw” mode, which allows you to send any valid SQL queries, including multi-queries .

That means just straight SQL, and not sphinxql? I guess that makes some sense, though it’s disappointing and a little confusing.

We can set up a primary master for us to send all our schema modifications to, we were hoping to avoid that as it creates a single point of failure. But if there’s no way in manticore to run 2 sequential queries on the same machine then I guess we have to live with it.

4

you also can not issue multi-queries via SphinxQL interface but only search queries or search query + meta as daemon returns error for all other multi-queries via SphinxQL interface.