SQL errors with Metrics rendering in Cachet 2.4-dev and Postgres 10.

[Pahanda]

Fresh Cachet 2.4-dev install with PostgreSQL 10 (and a table prefix of cachet_). Any attempts to have a Metric item's graph displayed have an image loading placeholder, and the Laravel log fills up with this:

LINE 1: SELECT to_char(cachet_metric_points.created_at, 'YYYY-MM-DD ...
                       ^ (SQL: SELECT to_char(cachet_metric_points.created_at, 'YYYY-MM-DD HH24:00') AS key, sum(cachet_metric_points.value * cachet_metric_points.counter) AS value FROM cachet_metrics INNER JOIN cachet_metric_points ON cachet_metrics.id = cachet_metric_points.metric_id WHERE cachet_metrics.id = :metricId AND cachet_metric_points.created_at >= (NOW() - INTERVAL '12' HOUR) AND cachet_metric_points.created_at <= NOW() GROUP BY to_char(cachet_metric_points.created_at, 'HH24:00') ORDER BY to_char(cachet_metric_points.created_at, 'HH24:00')) in /var/www/Cachet/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664

There is a full stack trace, but the problem is in the SQL, so I'm omitting it.

There's two separate problems here.

The first is that with recent PostgreSQL versions, it's not quite smart enough to identify the to_char() call as a key, so you have to explicitly state it in the GROUP BY clause, like this:

SELECT to_char(cachet_metric_points.created_at, 'YYYY-MM-DD HH24:00') AS key, sum(cachet_metric_points.value * cachet_metric_points.counter) AS value FROM cachet_metrics INNER JOIN cachet_metric_points ON cachet_metrics.id = cachet_metric_points.metric_id WHERE cachet_metrics.id = <INSERT_ID_HERE> AND cachet_metric_points.created_at >= (NOW() - INTERVAL '12' HOUR) AND cachet_metric_points.created_at <= NOW() GROUP BY to_char(cachet_metric_points.created_at, 'HH24:00'), cachet_metric_points.created_at ORDER BY to_char(cachet_metric_points.created_at, 'HH24:00');

That's an easy fix.

But there's another problem which is preventing it from returning data. This system that I'm running it on is freshly installed, and I only just created data this morning, so the created_at values are very recent:

SELECT * FROM cachet_metric_points;
 id | metric_id | value  |     created_at      |     updated_at      | counter
----+-----------+--------+---------------------+---------------------+---------
  1 |         1 | 42.000 | 2018-09-24 18:15:30 | 2018-09-24 12:15:57 |       3
  2 |         1 | 38.000 | 2018-09-24 18:50:30 | 2018-09-24 12:50:43 |       1
  3 |         1 | 37.000 | 2018-09-24 18:50:30 | 2018-09-24 12:50:46 |       2
  4 |         1 | 40.000 | 2018-09-24 18:51:00 | 2018-09-24 12:50:47 |       1
  5 |         1 | 35.000 | 2018-09-24 18:51:00 | 2018-09-24 12:50:48 |       1
(5 rows)

created_at appears stored in UTC and updated_at is stored at local time, which makes the <= NOW() part of the WHERE clause clobber those data points. This may be because I'm passing in data points with a local epoch time (that's Central, while the server is Mountain)... but I don't think that would (should?) cause issues like that. Either way, the results of my query that I added the GROUP BY fix to are blank, and that doesn't seem right.

Eventually, of course, time will move past that created_at value, and it should start returning results, but this seems a bit off to me.

[welcome]

👋 Thank you for opening your first issue. I'm just an automated bot that's here to help you get the information you need quicker, so please ignore this message if it doesn't apply to your issue.
If you're looking for support, you should try the Slack group by registering your email address at https://cachethq-slack.herokuapp.com. Alternatively, email support@alt-three.com for our Professional support service (please note, this a paid service.)
If you're issue is with documentation, you can suggest edits by clicking the Suggest Edits link on any page, or open an issue at https://github.com/CachetHQ/Docs

[jbrooksuk]

@Pahanda apologies for the delay! Do you have a dump of your metrics and metric_points tables so I can test with, please?

[jbrooksuk]

I could do with some help on the Postgres side of things.

[J-tt]

Experiencing the same issue currently, do you still need that db dump @jbrooksuk ?

[skny5]

@jbrooksuk - any updates on this?

[Puyodead1]

Same issue occuring for me as well on a fresh install, ubuntu 16.04 with docker

[Puyodead1]

Same issue occuring for me as well on a fresh install, ubuntu 16.04 with docker

[Puyodead1]

an update (if it helps):
sql statment seems to work from pgAdmin if i change:
to_char(chq_metric_points.created_at, 'HH24:MI')
to:
to_char(chq_metric_points.created_at, 'YYYY-MM-DD HH24:MI')
Old statment that produces the error:

SELECT to_char(chq_metric_points.created_at, 'YYYY-MM-DD HH24:MI') AS key, avg(chq_metric_points.value) AS value FROM chq_metrics INNER JOIN chq_metric_points ON chq_metrics.id = chq_metric_points.metric_id WHERE chq_metrics.id = :metricId AND chq_metric_points.created_at >= (NOW() - INTERVAL '71' MINUTE) AND chq_metric_points.created_at <= NOW() GROUP BY to_char(chq_metric_points.created_at, 'HH24:MI') ORDER BY chq_metric_points.created_at

final statment i got:

SELECT to_char(chq_metric_points.created_at, 'YYYY-MM-DD HH24:MI') AS key, avg(chq_metric_points.value) AS value FROM chq_metrics INNER JOIN chq_metric_points ON chq_metrics.id = chq_metric_points.metric_id WHERE chq_metrics.id = 1 AND chq_metric_points.created_at >= (NOW() - INTERVAL '71' MINUTE) AND chq_metric_points.created_at <= NOW() GROUP BY to_char(chq_metric_points.created_at, 'YYYY-MM-DD HH24:MI') ORDER BY to_char(chq_metric_points.created_at, 'YYYY-MM-DD HH24:MI')

(ofc i changed :metricid to 1 to test the sql)

[Puyodead1]

an update (if it helps):
sql statment seems to work from pgAdmin if i change:
to_char(chq_metric_points.created_at, 'HH24:MI')
to:
to_char(chq_metric_points.created_at, 'YYYY-MM-DD HH24:MI')
Old statment that produces the error:

SELECT to_char(chq_metric_points.created_at, 'YYYY-MM-DD HH24:MI') AS key, avg(chq_metric_points.value) AS value FROM chq_metrics INNER JOIN chq_metric_points ON chq_metrics.id = chq_metric_points.metric_id WHERE chq_metrics.id = :metricId AND chq_metric_points.created_at >= (NOW() - INTERVAL '71' MINUTE) AND chq_metric_points.created_at <= NOW() GROUP BY to_char(chq_metric_points.created_at, 'HH24:MI') ORDER BY chq_metric_points.created_at

final statment i got:

SELECT to_char(chq_metric_points.created_at, 'YYYY-MM-DD HH24:MI') AS key, avg(chq_metric_points.value) AS value FROM chq_metrics INNER JOIN chq_metric_points ON chq_metrics.id = chq_metric_points.metric_id WHERE chq_metrics.id = 1 AND chq_metric_points.created_at >= (NOW() - INTERVAL '71' MINUTE) AND chq_metric_points.created_at <= NOW() GROUP BY to_char(chq_metric_points.created_at, 'YYYY-MM-DD HH24:MI') ORDER BY to_char(chq_metric_points.created_at, 'YYYY-MM-DD HH24:MI')

(ofc i changed :metricid to 1 to test the sql)

Editing app/Repositories/Metric/SqliteRepository.php and editing the sql statment starting on line 35 and adding YYYY-MM-DD to the group_by and wrapping the order_by in a to_char() method solved the issue and metrics are now working!

[dirtycajunrice]

@Puyodead1 For those of us not as PHP savvy, could you paste the modified block? :D

[joe-eklund]

Just wanted to post that I got this working with Cachet 2.4.0 dev and Postgres 12 all in docker containers. See my diff here.

Please note I am not a php developer, like at all. I did these changes through trial and error based off @Puyodead1's comment above. I will lastly mention my "Last Hour" graph is delayed by 10 minutes, but that's a separate issue (expected? Idk...).

-Joe

[joe-eklund]

To follow up on my above comment, it looks like my metrics are not showing up in the "Last Hour" graph, but they are in the the "12 hours" one. The graph does still show up (just with default values) so this may be an issue with how I am trying to get around the timezone issue. That issue is uploaded metrics in epoch UTC time are displayed in such a time (as in they Cachet doesn't use the timezone set in the settings to change the metric timezones). To get around that I edit the timezones of the metric to my local timezone before I upload them, that way correctly displaying them in the graph.

If Cachet ever changes this in the future I can just change back to uploading in UTC, but for now this does work for at least 12 hour graph. I will let Cachet run to see if I can get metrics to show up in the Last Hour graph. I do see there are some other issues related to timezones or time related metrics, so that may be my current issue.

[Puyodead1]

Just wanted to post that I got this working with Cachet 2.4.0 dev and Postgres 12 all in docker containers. See my diff here.

Please note I am not a php developer, like at all. I did these changes through trial and error based off @Puyodead1's comment above. I will lastly mention my "Last Hour" graph is delayed by 10 minutes, but that's a separate issue (expected? Idk...).

-Joe

Glad you got it working.

To follow up on my above comment, it looks like my metrics are not showing up in the "Last Hour" graph, but they are in the the "12 hours" one. The graph does still show up (just with default values) so this may be an issue with how I am trying to get around the timezone issue. That issue is uploaded metrics in epoch UTC time are displayed in such a time (as in they Cachet doesn't use the timezone set in the settings to change the metric timezones). To get around that I edit the timezones of the metric to my local timezone before I upload them, that way correctly displaying them in the graph.

If Cachet ever changes this in the future I can just change back to uploading in UTC, but for now this does work for at least 12 hour graph. I will let Cachet run to see if I can get metrics to show up in the Last Hour graph. I do see there are some other issues related to timezones or time related metrics, so that may be my current issue.

I had an issue with the time also. I changed stuff in the code to display the metric time differently, but that didn't go as I had expected.

[joe-eklund]

Yes I tried to upload my metrics in my current time zone, but that had adverse affects. My Last hour graph displayed nothing, and my 12 hours graph only displayed the most recent 7 hours (though they were correct). This probably has something to do with UTC and my timezone being in PDT (7 hours behind).

My current "fix", which isn't really a fix, is to just upload everything in UTC and display in UTC. That seems to work just fine. I will look more into getting the timezone to display correctly and see if I can figure it out.

[viraja1]

We faced the same issue and the following fix worked for us :- https://github.com/CachetHQ/Cachet/pull/3830/files

[si458]

sorry to tag onto this but i get this error using postgres 9.5 and the latest docker image
is the a fix in the works?

[joe-eklund]

@si458 If you are asking for a fix in the official docker image (which isn't unreasonable), that I don't know. I'll defer to the Cachet repo members for that one.

If you would like something now that somewhat works, the fix I posted above does seem to work for me (see the diff I posted). You can fork Cachet and add it yourself. Then change your Dockerfile to point to your forked repo.

One caveat with this is I only got everything working with uploading in my local timezone. I could not get upload in UTC working with displaying in my local timezone. I also ran into a snag with daylight savings during the beginning of November, but that was my fault. I also had to setup my local timezone to work in all my containers.

So if you use the fix I posted above you should at least be able to display in whatever timezone you upload. This is currently sufficient for my needs, but does involve a bit of work to get working (namely the fix in my diff and passing through my local timezone) and I understand if you would like to just have it "work".

[exherb]

any progress on this one?

[Maelstrom96]

We're using the fix from @viraja1 #3830 and it seems to work great, would it be possible to merge this so we don't have to actually run a fork?

[jbrooksuk]

Thank you for your input on Cachet 2.x. We are shifting our attention and resources to Cachet 3.x and will no longer be supporting the 2.x version. If your feedback or issue is relevant to the 3.x series, we encourage you to engage with the new branch.

For more information on the Cachet rebuild and our plans for 3.x, you can read the announcement here.

We appreciate your understanding and look forward to your contributions to the new version.