Cannot use JSON_EXTRACT() on Spider Node that connecting to another Spider Node
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
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.