Hi,
grafana supports mysql as its own DB. I wanted to try to connect it to one of our memsql cluster but it fails right from the get go with: Error 1749: Feature ‘Correlated subselect that can not be transformed and does not match on shard keys’ is not supported by MemSQL Distributed.
Is that something that can be fixed or enhanced?
Let me know if you want more information.
Thanks,
Sam.
Grafana should work with MemSQL and we actually use Grafana with MemSQL as part of our monitoring solution (SingleStoreDB Cloud · SingleStore Documentation). If you can send us the specific query that’s being generated and throwing that error we can take a look.
This is trying to make grafana use memsql as its own internal DB though, to store dashboards, users etc… (As opposed to the sqllite default). Not to use a memsql DB as a data source in grafana.
If that makes sense.
This is an interesting use case! Can you share the schema that you have created for it? As well as the query that doesn’t work. It might have been fixed in the latest release
What I did is convert an existing grafana sqllite DB to mysql and then imported the resulting file into a blank memsql DB. That seems to work fine but I therefore don’t have a schema for it. I have not actually tried to do this with a brand new grafana installation (Which would have no existing DB). I assume grafana would create the relevant tables at the first startup. Something to confirm though.
Find below the grafana log file showing the query sent and the rejection from the DB. I am on memsql 7.1.4.
t=2020-08-28T15:54:58+0000 lvl=info msg="[SQL] SELECT\n\t\t\tdashboard.id,\n\t\t\tdashboard.uid,\n\t\t\tdashboard.title,\n\t\t\tdashboard.slug,\n\t\t\tdashboard_tag.term,\n\t\t\tdashboard.is_folder,\n\t\t\tdashboard.folder_id,\n\t\t\tfolder.uid AS folder_uid,\n\t\t\tfolder.slug AS folder_slug,\n\t\t\tfolder.title AS folder_title\n\t\tFROM ( SELECT dashboard.id FROM dashboard WHERE dashboard.org_id=? AND (SELECT count(*)\n\t\t\t FROM star\n\t\t\t WHERE star.dashboard_id = dashboard.id AND star.user_id = ?) > 0 ORDER BY dashboard.title ASC LIMIT 30 OFFSET 0) AS ids\n\t\tINNER JOIN dashboard ON ids.id = dashboard.id\nLEFT OUTER JOIN dashboard AS folder ON folder.id = dashboard.folder_id\n\t\tLEFT OUTER JOIN dashboard_tag ON dashboard.id = dashboard_tag.dashboard_id\n ORDER BY dashboard.title ASC []interface {}{1, 1} - took: 63.701754ms" logger=sqlstore.xorm
t=2020-08-28T15:54:58+0000 lvl=eror msg=“Search failed” logger=context userId=1 orgId=1 uname=admin error=“Error 1749: Feature ‘Correlated subselect that can not be transformed and does not match on shard keys’ is not supported by MemSQL Distributed.” remote_addr=82.11.147.103
Can you include the output of SHOW CREATE TABLE dashboard; and SHOW CREATE TABLE dashboard_tag;? ETA: Also SHOW CREATE TABLE star;.
it’s possible you can recreate the table with a different shard key and the query will go through.
SHOW CREATE TABLE dashboard
CREATE TABLE dashboard ( id tinyint(4) DEFAULT NULL, version tinyint(4) DEFAULT NULL, slug varchar(43) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, title varchar(43) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, data text CHARACTER SET utf8 COLLATE utf8_general_ci, org_id tinyint(4) DEFAULT NULL, created varchar(0) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, updated varchar(0) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, updated_by tinyint(4) DEFAULT NULL, created_by tinyint(4) DEFAULT NULL, gnet_id tinyint(4) DEFAULT NULL, plugin_id varchar(0) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, folder_id tinyint(4) DEFAULT NULL, is_folder tinyint(4) DEFAULT NULL, has_acl tinyint(4) DEFAULT NULL, uid varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL /*!90618 , SHARD KEY () / ) /!90623 AUTOSTATS_CARDINALITY_MODE=PERIODIC, AUTOSTATS_HISTOGRAM_MODE=CREATE / /!90623 SQL_MODE=‘STRICT_ALL_TABLES’ */
SHOW CREATE TABLE dashboard_tag
CREATE TABLE dashboard_tag ( id varchar(0) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, dashboard_id varchar(0) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, term varchar(0) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL /*!90618 , SHARD KEY () / ) /!90623 AUTOSTATS_CARDINALITY_MODE=PERIODIC, AUTOSTATS_HISTOGRAM_MODE=CREATE / /!90623 SQL_MODE=‘STRICT_ALL_TABLES’ */
SHOW CREATE TABLE star
CREATE TABLE star ( id varchar(0) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, user_id varchar(0) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, dashboard_id varchar(0) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL /*!90618 , SHARD KEY () / ) /!90623 AUTOSTATS_CARDINALITY_MODE=PERIODIC, AUTOSTATS_HISTOGRAM_MODE=CREATE / /!90623 SQL_MODE=‘STRICT_ALL_TABLES’ */
hi celer-tech.
i was able to change the schema to the following, and the query went through. in general, i think, MySQL to memSQL import probably tries to use keyless sharding, but in this case it will trip up every sub-select like the one you encountered.
CREATE TABLE dashboard (
id tinyint(4) not NULL,
version tinyint(4) DEFAULT NULL,
slug varchar(43) DEFAULT NULL,
title varchar(43) DEFAULT NULL,
data text,
org_id tinyint(4) DEFAULT NULL,
created datetime DEFAULT NULL,
updated datetime DEFAULT NULL,
updated_by tinyint(4) DEFAULT NULL,
created_by tinyint(4) DEFAULT NULL,
gnet_id tinyint(4) DEFAULT NULL,
plugin_id tinyint(4) DEFAULT NULL,
folder_id tinyint(4) DEFAULT NULL,
is_folder tinyint(4) DEFAULT NULL,
has_acl tinyint(4) DEFAULT NULL,
uid varchar(10) DEFAULT NULL,
SHARD KEY (id) ) ;
CREATE TABLE dashboard_tag (
id tinyint(4) DEFAULT NULL,
dashboard_id tinyint(4) DEFAULT NULL,
term varchar(43) DEFAULT NULL ,
SHARD KEY (dashboard_id) );
CREATE TABLE star (
id tinyint(4) NOT NULL,
user_id tinyint(4) NOT NULL,
dashboard_id tinyint(4) not null,
SHARD KEY (dashboard_id) );
Likely do not want varchar(0) columns, unless we expect every value to be the empty string.
memsql> insert ttt values ('');
Query OK, 1 row affected (0.082 sec)
memsql> insert ttt values ('a');
Query OK, 1 row affected (0.030 sec)
memsql> select * from ttt;
+------+
| a |
+------+
| |
| |
+------+
Database changed
memsql> show create table ttt;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ttt | CREATE TABLE `ttt` (
`a` varchar(0) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL
/*!90618 , SHARD KEY () */
) /*!90623 AUTOSTATS_CARDINALITY_MODE=OFF, AUTOSTATS_HISTOGRAM_MODE=OFF */ /*!90623 SQL_MODE='STRICT_ALL_TABLES' */ |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)yep good catch on that.
additionally, we recommend you make the tables columnstore, rather than in-memory. it’s unlikely that you’ll hit the number of dashboards and stars that make a huge difference, but it’s better not to dedicate cluster resources to the presentation layer.