Cannot get the most basic query in Mysql to work in Grafana (solved)

Update: I was able to fix this issue by changing the column names in the database table from series-A, series-B to A and B respectively.
It might be a good idea to alert new users to the pickiness of the query builder regarding field names in the data. Apparently the hyphen in the name confuses it.

  • What Grafana version and what operating system are you using?
    MacOS Grafana v8.1.3

  • What are you trying to achieve?
    display a simple graph from a table in MariaDb (mysql)

  • How are you trying to achieve it?
    1 Adding the data source OK
    2 Create a new dashboard OK
    3 add empty panel OK
    edit query NOT OK

  • What happened?
    When editing the query with the query editor various errors are displayed

  • What did you expect to happen?
    The graph to be displayed

  • Can you copy/paste the configuration(s) that you are having problems with?
    No

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
    The table I want to graph is a simple table with 4 fields:

+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id        | int(11) unsigned  | NO   | PRI | NULL   | auto_increment |
| time     | datetime         | YES  |     | NULL   |                |
| series-A | float              | YES  |     | NULL   |                |
| series-B | float              | YES  |     | NULL   |                |
+----------+------------------+------+-----+---------+----------------+

incidentally: this table was populated by exporting the random walk twice, and combining the two csv files in a text editor. The resulting file was imported into the MariaDb table.

When I add an empty panel to a new dashboard, the query builder has the following pre-selected:
From: testdata (the table name)
Time column: time
Metric column: none
Select: Column: id
Where: Macro: $__timeFilter
Format as: Time series

The graph area shows a graph of a straight line rising from left to right (I assume it’s graphing the id value).
When I click on the plus icon next to “Column: id” and choose “Column” this adds a second column id. I Click on the second column and change it to “series-A”.
The result: the graph now displays: “Data does not have a time field” and an alert icon top-left.
When I edit the sql (click the button “edit sql”) to read:
SELECT
time AS “time”,
series-A,
series-B
FROM testdata
ORDER BY time
Nothing changes.

Hi @markchagers and welcome to the forum.

Glad you got unblocked on this. Is there a particular place in the docs that you found confusing?

Also, sharing your actual query will help users a lot. Many here are super good at querying the SQL datasources through Grafana

There is no particular place I found confusing, but I read nowhere that having field names with hyphens in your table is problematic.
As stated in the title, this query was super simple, and it failed with ‘series-A’ and ‘series-B’ as field names in the table. When I renamed those fields in the database to ‘A’ and ‘B’ respectively, the (accordingly modified) query worked as expected.

1 Like