MySQL query works directly in MySQL but not via Grafana

Grafana MySQL
1

I have the following MySQL query for my dashboard penal:

> SET @ts = $__timeFrom();
> SELECT now() as time, COUNT(*) from (SELECT * FROM genmsgs WHERE date_stored > @ts ORDER BY msgdate) as records where msgtext NOT LIKE '% 200 %';

This generates the following SQL query:

SET @ts = FROM_UNIXTIME(1592501076); SELECT now() as time, COUNT(*) from (SELECT * FROM genmsgs WHERE date_stored > @ts ORDER BY msgdate) as records where msgtext NOT LIKE ‘% 200 %’;

When run via Grafana, I get and error:

Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT now() as time, COUNT(*) from (SELECT * FROM genmsgs WHERE date_stored > @' at line 2"

However, when I run the generated query directly in MySQL, it returns the results I want without and error:

mysql> SELECT now() as time, COUNT(*) from (SELECT * FROM genmsgs WHERE date_stored > @ts ORDER BY msgdate) as records where msgtext LIKE '% 200 %';
+---------------------+----------+
| time                | COUNT(*) |
+---------------------+----------+
| 2020-06-18 18:45:31 |   563407 |
+---------------------+----------+
1 row in set (7.65 sec)

What’s the difference in how Grafana is presenting the query to MySQL vs. running it directly ?

FYI, Using the MySQL variable (@ts) prepopulated with the $__timeFrom() result, allows the query to use the index for the table. As opposed to using ‘date_stored > $__timeFrom()’ which bypasses the index and causes the query to go through millions of records.

2

Your query is actually two statements, a set and a select and I think Grafana only supports/accepts a single statement.

Have you tried applying index hints to your query instead?
https://dev.mysql.com/doc/refman/5.7/en/index-hints.html

You can check/compare sql statement performance direct from Grafana by putting your query in a table panel (Format as Table) and changing the query to an explain.

3

What do you want to achieve from that query into Grafana Dashboard?
Did you want to use TimeRange that provided by Grafana? Or just build dashboard using specific time of the data?
Grafana provided workable time range and also interval that suitable for time series dashboard.
It will help if you mention what you want to display…

Regards,
Fadjar Tandabawana

4

The query is meant to operate on the records that correspond the the time frame select on the dashboard. While __timeFilter() includes this as part of the query, it is submitted in a way the causes the index for the table to be disregarded. This causes poor performance as there are millions of entries in the table. By formatting the Select the way I presented in my post, the index is utilized. However, the only way I can think of to grab the values for timeframe [__timeFrom(), $__timeTo()], so they can be used in the SELECT, is to use MySQL variables that are pre-populated with the correct values.
As stated in the original post, the SQL query that is generated does not work when submitted by Grafana (Error 1064). However, it works when submitted directly via MySQL. So this is a matter of how Grafana submits the query.

5

Using Grafana dashboard variables that stored the values of the ‘__timeFrom()' and '__timeTo()’ with a refresh set for anytime the dashboard timeframe is changed allows me to utilize my SELECT without using MySQL variables. With the query being reduced back down to the single SELECT statement, everything works.