The Ultimate Guide to High Availability with MariaDB

Cannot use JSON_EXTRACT() on Spider Node that connecting to another Spider Node

You are viewing an old version of this question. View the current version here.

As I have a spider node (SA) connecting to 10 tables in data node, and might keep increasing in the future.

And I want to do some query in another spider node (SB) which need to use the data that stored in that 10 tables, but if I directly connect to those 10 tables, there might have a maintenance problem in the future as it grows.

So I decided to directly connect to (SA) since this spider node already have those 10 tables' data, it has no problem on normal query, but when I use JSON_EXTRACT function, it prompts me the following error message

Error Message

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 'json_extract _utf8'$.id') `JSON_EXTRACT(params, '$.id')` from `DB_HK' at line 1 SQL.sql 3 1

params is stored as LONGTEXT and save a json string.

I tried several things,

First, if I put the connection string in table comment field, the error message will prompt, Second, if I create a single partition and put the connection string in partition comment field, the error message will prompt

But, if I have at least 2 partitions and put the connection string in partition comment field, it works. Event those 2 partition are pointing to the same server, database and table, the (SA) spider node

Could you help to fix this issue? Coz there is no way for keep a spider node connecting to all partitions in all data nodes, to make the network simple, we should connect to the spider node. And we just maintain the partitions in one spider node so that all the others that connecting to it will take effective.

Or if my idea is wrong, please correct me. Thanks.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.