How do I get a MySQL/MariaDB user defined variable working in grafana?

Question

Using MariaDB 5.5 (our asterisk cdr database) and trying to build a time series by date out of existing data in the database, but want to join to a column of all dates in order to account for days that have no data. The following query (simplified) works in MySQL Workbench to get a list of all dates in the last 180 days, but in grafana I get a syntax error.

SET @i = -1;

SELECT DATE(DATE_ADD(DATE_ADD(CURRENT_DATE(), INTERVAL -180 DAY), INTERVAL @i:=@i+1 DAY)) AS date
FROM queuestats
HAVING 
@i < DATEDIFF(CURRENT_DATE(), DATE_ADD(CURRENT_DATE(), INTERVAL -180 DAY))
;

The error I'm getting is as follows:

Error 1064: 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 'SELECT DATE(DATE_ADD(DATE_ADD(CURRENT_DATE(), INTERVAL -180 DAY), INTERVAL @i:=@' at line 3

It seems to be taking issue with the 'SET' keyword as I get no error, but also no data returned, if I simply remove the first line from the query. I've tested this syntax against both this MariaDB, and a separate MySQL database and the same thing happens. To simplify even further, I get the same error even with the following query:

SET @i = -1;
SELECT CURRENT_DATE() AS time;

Does anybody have any idea as to why the grafana doesn't like the SET keyword? Or how to get a list of dates between two dates without using a variable?

Thanks in advance.

Answer 1

Due to security reasons (mainly SQL injection) by default MariaDB (and also MySQL) doesn't accept multiple statements which are separated by a semicolon unless you explicitly enable this option (in C-API you can do that by setting the CLIENT_MULTI_STATEMENTS flag, in PHP/mysqli there is a separate API call mysqli_multi_query()) - however I don't know if Grafana has an option to support multiple statements.