Query only success when adding database name

Grafana MySQL
,
1

Hello,
I just installed grafana and add a connection to an existing database with values based on mariadb 10.3.32-1040.

When adding the first simple query to a table the following error occurs:

db query error: 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 ‘Load WHERE Timestamp BETWEEN FROM_UNIXTIME(1661854754) AND FROM_UNIXTIME(16…’ at line 4

The generated sql is:

SELECT
  Timestamp AS "time",
  Power
FROM  Load
WHERE
  $__timeFilter(Timestamp)
ORDER BY Timestamp

When I manually change this to the following, a graph will be displayed:

SELECT
  Timestamp AS "time",
  Power
FROM  `PowerDb`.`Load`
WHERE
  $__timeFilter(Timestamp)
ORDER BY Timestamp

Is there any solution to fix this error without change the query manually? When switching to query builder the database statement is missing and the query fails again.

2

Hi @mttkrb,

Welcome to the :grafana: community support forums !!

I do not understand your question clearly. So your post title says that your query only works when mentioning the database name inside the SQL query.

But when looking into your SQL, I see that you are using 2 different tables i.e. PowerDB and Load to make it work, while using only a single table (i.e. Load) gave you an error as cannot show the results due to an incorrect query.

So where exactly did you mention the database name in your query manually?

OR I make a mistake in understanding your question?

3

Hi,
PowerDb is the database and load the table in both queries. The query without setting the database produces an error.

Another Example:
Error:

Select * from table

Success:

Select * from `database`.`table`
4

Thanks for the detailed reply.

Ok, I think I know this. Basically its the syntax thingy. So for e.g. I was doing this MySQL learning and on Grafana it does need to work like that i.e. db_name.table_name in the query.

So this is somewhat required for the query analyzer.

5

There could be multiple databases in a db server.
So you do have ti specify the database.

Can you please show us what your datasource settings are?

6

Thanks for your reply and possible solutions.
Here is my datasource:

grafik
grafik629×689 25.9 KB

7

thanks. And what version of grafana are you on?

1 Like
8

V9.1.1 installed with the latest docker image on Docker Hub
Should I use another version?

2 Likes
9

nope, it just helps us test it out on our side since we do not have access to your grafana :wink:
Some in this forum have power to read mind of people across the globe. I no longer have that capability. :laughing:

and your mariadb is in docker also?

2 Likes
10

no, It’s installed on my synology nas in version

mysql Ver 15.1 Distrib 10.3.32-MariaDB, for Linux (x64) using readline 5.1

1 Like
11

I bet you don’t have correct DB config in the connection. What is a result for SELECT DATABASE(); in the Grafana?

12

is this is really your data source config and you have specified the Database PowerDb in your datasource, I am unable to duplicate this issue.

do you by any chance have other datasources to mariadb without specifying the Database name

13

the result is

PowerDb

14

Got it. ‘LOAD’ is a keyword. See LOAD DATA INFILE - MariaDB Knowledge Base
You didn’t choose suitable table name, so you have to always specify this long db.table format explicitly, so DB knows that LOAD is a table and not a SQL command.

3 Likes
15

what if OP used

`Load`

instead without specifying the db name

16

Thanks a lot. After change the table name the issue no longer occurs.