feature request: mysql: multiple statement execution support

[hailong1004]

summary by @gabor the grafana mysql plugin currently only support running a single sql statement. this does not allow certain use-cases, like using user-defined variables. to allow it, we need to add something to the mysql connection string, question is, should we always add it, add a config-checkbox for it, etc.

---

What happened:
I use mysql data source. When I need to define a variable in the sql, it prompts a syntax error. If it is normal to execute directly in the mysql client, my sql as follows

SET @csum := 0;
SELECT 
    date, 
    profit, 
    (@csum := @csum + profit) AS sum_profit
FROM daily_pnl_view;

What you expected to happen:
Can output the result normally as in mysql client

How to reproduce it (as minimally and precisely as possible):
Refer to my sql above. In fact, I want to calculate the monthly profit accumulation. In mysql, you need to use set to define a variable before using it.

Anything else we need to know?:

Environment:

• Grafana version: v6.7.3 (a04ef6c)
• Data source type & version: mysql
• OS Grafana is installed on: docker images grafana/grafana
• User OS & Browser: macOS 10.15.4
• Grafana plugins:
• Others:

[baur]

similar error was in mssql if complex query, but I can't reproduce it ...

[baur]

try

SET @csum := 0;
SELECT 
    date, 
    profit, 
    (@csum + profit) AS sum_profit
FROM daily_pnl_view;

[hailong1004]

In mysql client

mysql> select * from daily_pnl_view;
+------------+--------+
| date       | profit |
+------------+--------+
| 2020-05-11 |   0.46 |
| 2020-05-13 |   0.52 |
+------------+--------+
2 rows in set (0.00 sec)

mysql> SET @csum := 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT 
    ->     date, 
    ->     profit, 
    ->     (@csum := @csum + profit) AS sum_profit
    -> FROM daily_pnl_view;
+------------+--------+---------------------+
| date       | profit | sum_profit          |
+------------+--------+---------------------+
| 2020-05-11 |   0.46 | 0.46000000834465027 |
| 2020-05-13 |   0.52 |  0.9799999892711639 |
+------------+--------+---------------------+
2 rows in set (0.00 sec)

mysql> SET @csum := 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT 
    ->     date, 
    ->     profit, 
    ->     (@csum + profit) AS sum_profit
    -> FROM daily_pnl_view;
+------------+--------+---------------------+
| date       | profit | sum_profit          |
+------------+--------+---------------------+
| 2020-05-11 |   0.46 | 0.46000000834465027 |
| 2020-05-13 |   0.52 |  0.5199999809265137 |
+------------+--------+---------------------+
2 rows in set (0.00 sec)

 mysql> 

but it's syntax error when run in grafana

[zuchka]

I can reproduce this on 8.5.0-pre, but I did find a super hacky workaround: if you query the DB with just the SET function, it will actually set the User-Defined variable. Then you can write a query using that @var. But you can't do both at the same time, or split them across two queries:

CleanShot.2022-02-13.at.03.59.54.mp4

of course this isn't very stateful. I also wonder if template variables cannot resolve these situations more often than not.

Also, I think this is a problem with the request, not the response. I get a 400 error, and when I tail the MySQL logs I don't see the query making it into the DB. 🤷

[lamdaik1412]

Has anyone got a solution yet? :(

[exul]

The underlying issue is that MySQL's Multiple Statement Execution Support is not enabled by default.

A minimal example to reproduce the issue: https://github.com/exul/xorm-multi-statements

Relevant connection string: https://github.com/grafana/grafana/blob/main/pkg/tsdb/mysql/mysql.go#L100 (there is a similar connection string here: https://github.com/grafana/grafana/blob/main/pkg/services/sqlstore/sqlstore.go#L284 but that one isn't used when adding a data source, so probably no need to update it).

Steps in the code when a MySQL Query is executed from a dashboard using MySQL as a data source (leaving this here as a reminder for myself and hoping it helps others too):

• Parse API request: https://github.com/grafana/grafana/blob/main/pkg/api/metrics.go#L59C15-L59C15
• Get MySQL data source handler and query data: https://github.com/grafana/grafana/blob/main/pkg/tsdb/mysql/mysql.go#L188
• SQL Engine layer executes the query: https://github.com/grafana/grafana/blob/main/pkg/tsdb/sqleng/sql_engine.go#L217 and https://github.com/grafana/grafana/blob/main/pkg/tsdb/sqleng/sql_engine.go#L290
• The engine used is from xorm: https://github.com/grafana/grafana/blob/main/pkg/services/sqlstore/sqlstore.go#L51

I don't know the Grafana code base well enough to judge how a fix for this should look like. Is it ok to set multiStatements=true on the relevant connection strings directly? If so that would be an easy change/PR. Otherwise, I'd need some guidance on how/if this should be fixed.

Video from a successful execution
from my local instance with a modified DSN:

grafana-multi-statements.mp4

[exul]

After some further investigation the best way to deal with this seems to be to add a new config flag to the MySQL data source (similar to Skip TLS Verification). This would allow users to decide if they want to enable multiStatements when connecting to MySQL.