Can't use a separate schema for aggregate tables

[vojta]

I have a schema called "archive" (shard1), where are the large tables I want to query.
I don't want to create any temporary tables in this schema (because it's replicated), but want to use another schema called "temp", which is excluded from replication.

I've set this in bootstrap:

[config]
coord_shard=temp

[shard1]
db=archive

[temp]
db=temp

But I get an error:

While creating coordinator table: 1146Table 'temp.mytable' doesn't exist

At line:
https://github.com/greenlion/swanhart-tools/blob/master/shard-query/include/shard-query.php#L288

Am I doing anything wrong? I have tried find it in the docs, but there don't seem to be any for that situation.

[greenlion]

All shards (including coord shards) have to have the schema loaded, even just empty tables will work. I'll think about support for coord shards without schema though.

[vojta]

OK, so can I force SQ to use the "temp" schema for aggregate tables? My use case is simple: just one server, two schemas - "archive" with data and "temp" is empty. Can I define a single shard to use that two schemas? I'm sorry, but the config is a little bit confusing for me :)

Thank you, Vojtech

[greenlion]

No, that isn't supported right now. The coordinator shard has to have the schema of the other shard. I can add code to use a different shard than the coordinator shard for metadata, but that will take some time.

[vojta]

OK, I get it, thanks

[greenlion]

The underlying reasons are:
CREATE TABLE .. AS SELECT is used to create the coordinator table
Looking up partition information is done on the coord shard (requires schema)
Looking up shard-column information (does a table have the shard key?) is done on the coordinator
...
Probably more

[vojta]

I think all these operations can be done in a cross-schema fashion:
CREATE TABLE temp.aggr.... .. AS SELECT .... FROM mydb.mytable .....

I have the schemas on the same mysqld instance, I just need to keep all the temporary tables in the 'temporary' schema to keep my production schema clean and to setup the replication filters easily. I don't want the "aggregate...." tables to be replicated, it doesn't make any sense to me.

It's just a feature request :)