Not All Mysql data displaying in graphs when displaying short time scales

ve6sar · November 30, 2017, 6:59pm

I’m trying to graph a few different data sets and they work over large time scales but as soon as I select a shorter time scale the data isn’t displayed, yet it shows data in the query inspector.

If I pick a time scale greater then 30 days it will display the last third or so data. At >90 days all the data is displayed.

My server install is Version 4.6.2 (commit: 8db5f08).

Any suggestions?

Here’s a sample from the query inspector:
{
“xhrStatus”: “complete”,
“request”: {
“method”: “POST”,
“url”: “api/tsdb/query”,
“data”: {
“from”: “1512024771416”,
“to”: “1512067971417”,
“queries”: [
{
“refId”: “A”,
“intervalMs”: 30000,
“maxDataPoints”: 1235,
“datasourceId”: 1,
“rawSql”: “SELECT\n unixEpoch as time_sec,\n NHPI as value,\n ‘NHPI’ as metric\nFROM Auroral_Power\nWHERE $__timeFilter(Date_Time)\nORDER BY Date_Time ASC\n\n”,
“format”: “time_series”
}
]
}
},
“response”: {
“results”: {
“A”: {
“refId”: “A”,
“meta”: {
“rowCount”: 46,
“sql”: “SELECT\n unixEpoch as time_sec,\n NHPI as value,\n ‘NHPI’ as metric\nFROM Auroral_Power\nWHERE Date_Time >= FROM_UNIXTIME(1512024771) AND Date_Time <= FROM_UNIXTIME(1512067971)\nORDER BY Date_Time ASC\n\n”
},
“series”: [
{
“name”: “NHPI”,
“points”: [
[
22,
1514616900000
],
[
21,
1514617200000
],
[
20,
1514617500000
],
[
18,
1514618100000
],
[
17,
1514618400000
],
[
17,
1514618700000
],
[
16,
1514619000000
],
[
16,
1514619300000
],
[
15,
1514619900000
],
[
15,
1514620200000
],
[
14,
1514620500000
],
[
14,
1514620800000
],
[
13,
1514621100000
],
[
13,
1514621400000
],
[
12,
1514621700000
],
[
12,
1514622300000
],
[
12,
1514622600000
],
[
12,
1514622900000
],
[
12,
1514623200000
],
[
12,
1514623500000
],
[
11,
1514623800000
],
[
11,
1514624100000
],
[
10,
1514624400000
],
[
10,
1514625000000
],
[
10,
1514625300000
],
[
9,
1514625600000
],
[
9,
1514625900000
],
[
9,
1514626200000
],
[
9,
1514626500000
],
[
9,
1514626800000
],
[
9,
1514627100000
],
[
9,
1514627400000
],
[
10,
1514627700000
],
[
10,
1514628000000
],
[
10,
1514628300000
],
[
11,
1514628600000
],
[
11,
1514628900000
],
[
10,
1514629500000
],
[
9,
1514629800000
],
[
14,
1514658000000
],
[
14,
1514658300000
],
[
13,
1514658600000
],
[
13,
1514658900000
],
[
13,
1514659200000
],
[
13,
1514659500000
],
[
13,
1514659800000
]
]
}
],
“tables”: null
}
}
}
}

mefraimsson · December 5, 2017, 9:24am

Hi,

If you display the graph as points instead of lines, does that display all the data you get back in query inspector?

Marcus

ve6sar · December 5, 2017, 4:33pm

Hi Marcus,

There’s no change when I switch to points the behaviour is still the same.

Other data sets are working fine, is there maybe something to do with the dataset?

Sean

mefraimsson · December 6, 2017, 9:02am

Hi,

If you try and change the dashboard settings -> General tab -> Timezone to UTC. Does that make all data show in graph?

Marcus

ve6sar · December 6, 2017, 4:16pm

It was already on UTC, I tried it on Local and no difference.

I switched the log level to debug, and there’s nothing showing up in there that seems like an error.

Sean

mefraimsson · December 8, 2017, 8:52am

Hi,

If you do a raw query from mysql, like

SELECT TOP 10 * FROM Auroral_Power ORDER BY Date_Time DESC

Can you share the result of that query?

Other things to verify - timezone of the computers/servers running Grafana and mysql?

Marcus

ve6sar · December 8, 2017, 3:29pm

Hi,

Here is the result of the query;

mysql> SELECT * FROM Auroral_Power ORDER BY Date_Time DESC LIMIT 10;
±----------±--------------------±-----------±-----+
| Record_ID | Date_Time | unixEpoch | NHPI |
±----------±--------------------±-----------±-----+
| 106700 | 2017-11-30 23:50:00 | 1514677800 | 22 |
| 106699 | 2017-11-30 23:40:00 | 1514677200 | 23 |
| 106698 | 2017-11-30 23:35:00 | 1514676900 | 23 |
| 106697 | 2017-11-30 23:30:00 | 1514676600 | 23 |
| 106696 | 2017-11-30 23:25:00 | 1514676300 | 23 |
| 106695 | 2017-11-30 23:20:00 | 1514676000 | 24 |
| 106694 | 2017-11-30 23:15:00 | 1514675700 | 24 |
| 106693 | 2017-11-30 23:10:00 | 1514675400 | 24 |
| 106692 | 2017-11-30 23:05:00 | 1514675100 | 24 |
| 106691 | 2017-11-30 23:00:00 | 1514674800 | 24 |
±----------±--------------------±-----------±-----+
10 rows in set (0.00 sec)

Grafana and MySQL are on the same server and the server is set to UTC.

Here’s the query as entered into Grafana;

SELECT
unixEpoch as time_sec,
NHPI as value,
‘NHPI’ as metric
FROM Auroral_Power
WHERE $__timeFilter(Date_Time)
ORDER BY Date_Time ASC

Sean

mefraimsson · December 8, 2017, 4:59pm

Hi,

I think I know what you’re problem are. You’re mixing and matching between Date_Time and unixEpoch which don’t seems to correlate to the same values.

In your initial example you have the following query:

SELECT unixEpoch as time_sec,NHPI as value, ‘NHPI’ as metric FROM Auroral_Power WHERE $__timeFilter(Date_Time) ORDER BY Date_Time ASC

So you’re selecting the unixEpoch column, but filter on and order by the Date_Time column. If you fix this error all should work.

Marcus

ve6sar · December 12, 2017, 5:15am

Sorry for the slow reply,

So I tried two different queries tonight the first
SELECT UNIX_TIMESTAMP(Date_Time) as time_sec, NHPI as value, ‘NHPI’ as metric FROM Auroral_Power WHERE $__timeFilter(Date_Time) ORDER BY Date_Time

Gives an error “Found row with no time value” when i run the generated query in mysql all values are there.

The second query I tried is:
SELECT unixEpoch as time_sec, NHPI as value, ‘NHPI’ as metric FROM Auroral_Power WHERE $__timeFilter(unixEpoch) ORDER BY unixEpoch ASC

Returns “No Data Points” but when I run the generated query in mysql I get the exact same amount of results as the first query.

Is there a way to by pass the $__timeFilter routine and just have it enter the start / end unix times via template maybe?

Sean

mefraimsson · December 22, 2017, 3:08pm

Hi,

Sorry for late answer. Now I’ve tried creating your table and inserted the 10 records you included above.

CREATE TABLE `Auroral_Power` (
  `Record_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Date_Time` datetime DEFAULT NULL,
  `unixEpoch` int(11) DEFAULT NULL,
  `NHPI` int(11) DEFAULT NULL,
  PRIMARY KEY (`Record_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

###
INSERT INTO `grafana`.`Auroral_Power` (`Date_Time`, `unixEpoch`, `NHPI`) VALUES ('2017-11-30 23:50:00', 1514677800, 22);
INSERT INTO `grafana`.`Auroral_Power` (`Date_Time`, `unixEpoch`, `NHPI`) VALUES ('2017-11-30 23:40:00', 1514677200, 23);
INSERT INTO `grafana`.`Auroral_Power` (`Date_Time`, `unixEpoch`, `NHPI`) VALUES ('2017-11-30 23:35:00', 1514676900, 23);
INSERT INTO `grafana`.`Auroral_Power` (`Date_Time`, `unixEpoch`, `NHPI`) VALUES ('2017-11-30 23:30:00', 1514676600, 23);
INSERT INTO `grafana`.`Auroral_Power` (`Date_Time`, `unixEpoch`, `NHPI`) VALUES ('2017-11-30 23:25:00', 1514676300, 23);
INSERT INTO `grafana`.`Auroral_Power` (`Date_Time`, `unixEpoch`, `NHPI`) VALUES ('2017-11-30 23:20:00', 1514676000, 24);
INSERT INTO `grafana`.`Auroral_Power` (`Date_Time`, `unixEpoch`, `NHPI`) VALUES ('2017-11-30 23:15:00', 1514675700, 24);
INSERT INTO `grafana`.`Auroral_Power` (`Date_Time`, `unixEpoch`, `NHPI`) VALUES ('2017-11-30 23:10:00', 1514675400, 24);
INSERT INTO `grafana`.`Auroral_Power` (`Date_Time`, `unixEpoch`, `NHPI`) VALUES ('2017-11-30 23:05:00', 1514675100, 24);
INSERT INTO `grafana`.`Auroral_Power` (`Date_Time`, `unixEpoch`, `NHPI`) VALUES ('2017-11-30 23:00:00', 1514674800, 24);

Then I created a new dashboard and panel according to screenshot below.

Nothing here that’s strange. Seems to work as expected. Please note that I did try this on latest commit from master.

For further investigation, please include screenshots of your dashboard that underline your problems, shows your graph panel, metric tab and selected time range.

Thanks in advance

Marcus

ve6sar · January 10, 2018, 6:38pm

Ok Life has finally slowed down so I have time to do my volunteer work.

Here’s a screenshot of a 2 month range you will notice it doesn’t display anything before 11-30-2017 23:00:00 even though there is data for that time.

Now here is the display for 12-01-2017 - 12-31-2017 the range which data was displayed on the first picture.

Here’s my metrics tab

Here’s a shot of my original dash board with other data from the same database

Thanks again for helping me figure this out.

Sean

mefraimsson · January 13, 2018, 8:36am

Hi,

I can still see that you’re mixing usage of unixEpoch and Date_Time columns. As I wrote above they don’t correlate.

If you try my query that I used:

SELECT
  UNIX_TIMESTAMP(Date_Time) as time_sec,
  NHPI as value,
  'NHPI' as metric
FROM Auroral_Power
WHERE $__timeFilter(Date_Time)
ORDER BY time_sec ASC

Does that work better?

Marcus

ve6sar · January 13, 2018, 3:08pm

Hi Marcus,

I figured it out in my database the DateTime column type wasn’t set as DateTime I changed that and it seems to be working.

Thanks for all your help

Sean

alvianno · January 15, 2018, 3:54am

helo

why issue

thanks
alvianno

mefraimsson · January 15, 2018, 10:35am

Hi,

Did you have a question?

Marcus

shon711 · February 13, 2020, 8:06pm

Hello,
i have the same issue with my table.
so i tried to setup as you did:
created the table and inserted the rows.

CREATE TABLE `Auroral_Power` (

Record_ID int(11) NOT NULL AUTO_INCREMENT,
Date_Time datetime DEFAULT NULL,
unixEpoch int(11) DEFAULT NULL,
NHPI int(11) DEFAULT NULL,
PRIMARY KEY (Record_ID)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO grafana.Auroral_Power (Date_Time, unixEpoch, NHPI) VALUES (‘2017-11-30 23:50:00’, 1514677800, 22);
INSERT INTO grafana.Auroral_Power (Date_Time, unixEpoch, NHPI) VALUES (‘2017-11-30 23:40:00’, 1514677200, 23);
INSERT INTO grafana.Auroral_Power (Date_Time, unixEpoch, NHPI) VALUES (‘2017-11-30 23:35:00’, 1514676900, 23);
INSERT INTO grafana.Auroral_Power (Date_Time, unixEpoch, NHPI) VALUES (‘2017-11-30 23:30:00’, 1514676600, 23);
INSERT INTO grafana.Auroral_Power (Date_Time, unixEpoch, NHPI) VALUES (‘2017-11-30 23:25:00’, 1514676300, 23);
INSERT INTO grafana.Auroral_Power (Date_Time, unixEpoch, NHPI) VALUES (‘2017-11-30 23:20:00’, 1514676000, 24);
INSERT INTO grafana.Auroral_Power (Date_Time, unixEpoch, NHPI) VALUES (‘2017-11-30 23:15:00’, 1514675700, 24);
INSERT INTO grafana.Auroral_Power (Date_Time, unixEpoch, NHPI) VALUES (‘2017-11-30 23:10:00’, 1514675400, 24);
INSERT INTO grafana.Auroral_Power (Date_Time, unixEpoch, NHPI) VALUES (‘2017-11-30 23:05:00’, 1514675100, 24);
INSERT INTO grafana.Auroral_Power (Date_Time, unixEpoch, NHPI) VALUES (‘2017-11-30 23:00:00’, 1514674800, 24);

db query after insert:

SELECT * FROM Auroral_Power;
Record_ID, Date_Time, unixEpoch, NHPI
‘10850322’, ‘2017-11-30 23:50:00’, ‘1514677800’, ‘22’
‘10850325’, ‘2017-11-30 23:40:00’, ‘1514677200’, ‘23’
‘10850328’, ‘2017-11-30 23:35:00’, ‘1514676900’, ‘23’
‘10850331’, ‘2017-11-30 23:30:00’, ‘1514676600’, ‘23’
‘10850334’, ‘2017-11-30 23:25:00’, ‘1514676300’, ‘23’
‘10850337’, ‘2017-11-30 23:20:00’, ‘1514676000’, ‘24’
‘10850340’, ‘2017-11-30 23:15:00’, ‘1514675700’, ‘24’
‘10850343’, ‘2017-11-30 23:10:00’, ‘1514675400’, ‘24’
‘10850346’, ‘2017-11-30 23:05:00’, ‘1514675100’, ‘24’
‘10850349’, ‘2017-11-30 23:00:00’, ‘1514674800’, ‘24’

now… on Grafana i created the below panel with 1 query and i can see the data 10 hours ahead. and if i changing the time range to > 01:00:00 i cant see the points anymore

the dashboard timezone is local browser changing to UTC change it to 8 hours ahead which is make sense …

any idea?

Thanks,
Shon