Metrics display when DB_PREFIX is set

[bwalker-telstra]

Howdy,

We are running cachet in docker and Ive noticed that when displaying metrics on the status page we get the following error in the logs

Next exception 'Illuminate\Database\QueryException' with message 'SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "metric_points" does not exist
LINE 1: ..._points.counter) as value FROM chq_metrics m JOIN metric_poi...
^ (SQL: select avg(metric_points.value * metric_points.counter) as value FROM chq_metrics m JOIN metric_points ON metric_points.metric_id = m.id WHERE metric_points.metric_id = :metricId AND to_char(metric_points.created_at, 'YYYYMMDDHH24') = :timeInterval GROUP BY to_char(metric_points.created_at, 'H'))' in /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:713
Stack trace:
#0 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php(669): Illuminate\Database\Connection->runQueryCallback('select avg(metr...', Array, Object(Closure))
#1 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php(342): Illuminate\Database\Connection->run('select avg(metr...', Array, Object(Closure))
#2 [internal function]: Illuminate\Database\Connection->select('select avg(metr...', Array)
#3 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/DatabaseManager.php(317): call_user_func_array(Array, Array)
#4 /var/www/html/vendor/laravel/framework/src/Illuminate/Support/Facades/Facade.php(219): Illuminate\Database\DatabaseManager->__call('select', Array)
#5 /var/www/html/vendor/laravel/framework/src/Illuminate/Support/Facades/Facade.php(219): Illuminate\Database\DatabaseManager->select('select avg(metr...', Array)
#6 /var/www/html/app/Repositories/Metric/PgSqlRepository.php(90): Illuminate\Support\Facades\Facade::__callStatic('select', Array)
#7 /var/www/html/app/Repositories/Metric/PgSqlRepository.php(90): Illuminate\Support\Facades\DB::select('select avg(metr...', Array)
#8 /var/www/html/app/Repositories/Metric/MetricRepository.php(88): CachetHQ\Cachet\Repositories\Metric\PgSqlRepository->getPointsByHour(Object(CachetHQ\Cachet\Models\Metric), 0)
#9 /var/www/html/app/Http/Controllers/StatusPageController.php(145): CachetHQ\Cachet\Repositories\Metric\MetricRepository->listPointsToday(Object(CachetHQ\Cachet\Models\Metric))
#10 [internal function]: CachetHQ\Cachet\Http\Controllers\StatusPageController->getMetrics(Object(CachetHQ\Cachet\Models\Metric))
#11 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Controller.php(80): call_user_func_array(Array, Array)
#12 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php(146): Illuminate\Routing\Controller->callAction('getMetrics', Array)
#13 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php(94): Illuminate\Routing\ControllerDispatcher->call(Object(CachetHQ\Cachet\Http\Controllers\StatusPageController), Object(Illuminate\Routing\Route), 'getMetrics')
#14 [internal function]: Illuminate\Routing\ControllerDispatcher->Illuminate\Routing{closure}(Object(Illuminate\Http\Request))
#15 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(52): call_user_func(Object(Closure), Object(Illuminate\Http\Request))
#16 [internal function]: Illuminate\Routing\Pipeline->Illuminate\Routing{closure}(Object(Illuminate\Http\Request))
#17 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(103): call_user_func(Object(Closure), Object(Illuminate\Http\Request))
#18 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php(96): Illuminate\Pipeline\Pipeline->then(Object(Closure))
#19 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php(54): Illuminate\Routing\ControllerDispatcher->callWithinStack(Object(CachetHQ\Cachet\Http\Controllers\StatusPageController), Object(Illuminate\Routing\Route), Object(Illuminate\Http\Request), 'getMetrics')
#20 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Route.php(174): Illuminate\Routing\ControllerDispatcher->dispatch(Object(Illuminate\Routing\Route), Object(Illuminate\Http\Request), 'CachetHQ\Cachet...', 'getMetrics')
#21 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Route.php(140): Illuminate\Routing\Route->runController(Object(Illuminate\Http\Request))
#22 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Router.php(724): Illuminate\Routing\Route->run(Object(Illuminate\Http\Request))
#23 [internal function]: Illuminate\Routing\Router->Illuminate\Routing{closure}(Object(Illuminate\Http\Request))
#24 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(52): call_user_func(Object(Closure), Object(Illuminate\Http\Request))
#25 /var/www/html/app/Http/Middleware/Localize.php(59): Illuminate\Routing\Pipeline->Illuminate\Routing{closure}(Object(Illuminate\Http\Request))
#26 [internal function]: CachetHQ\Cachet\Http\Middleware\Localize->handle(Object(Illuminate\Http\Request), Object(Closure))
#27 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(136): call_user_func_array(Array, Array)
#28 [internal function]: Illuminate\Pipeline\Pipeline->Illuminate\Pipeline{closure}(Object(Illuminate\Http\Request))
#29 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(32): call_user_func(Object(Closure), Object(Illuminate\Http\Request))
#30 /var/www/html/app/Http/Middleware/ReadyForUse.php(40): Illuminate\Routing\Pipeline->Illuminate\Routing{closure}(Object(Illuminate\Http\Request))
#31 [internal function]: CachetHQ\Cachet\Http\Middleware\ReadyForUse->handle(Object(Illuminate\Http\Request), Object(Closure))
#32 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(136): call_user_func_array(Array, Array)
#33 [internal function]: Illuminate\Pipeline\Pipeline->Illuminate\Pipeline{closure}(Object(Illuminate\Http\Request))
#34 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(32): call_user_func(Object(Closure), Object(Illuminate\Http\Request))
#35 /var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/VerifyCsrfToken.php(64): Illuminate\Routing\Pipeline->Illuminate\Routing{closure}(Object(Illuminate\Http\Request))
#36 [internal function]: Illuminate\Foundation\Http\Middleware\VerifyCsrfToken->handle(Object(Illuminate\Http\Request), Object(Closure))
#37 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(136): call_user_func_array(Array, Array)
#38 [internal function]: Illuminate\Pipeline\Pipeline->Illuminate\Pipeline{closure}(Object(Illuminate\Http\Request))
#39 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(32): call_user_func(Object(Closure), Object(Illuminate\Http\Request))
#40 /var/www/html/vendor/laravel/framework/src/Illuminate/View/Middleware/ShareErrorsFromSession.php(49): Illuminate\Routing\Pipeline->Illuminate\Routing{closure}(Object(Illuminate\Http\Request))
#41 [internal function]: Illuminate\View\Middleware\ShareErrorsFromSession->handle(Object(Illuminate\Http\Request), Object(Closure))
#42 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(136): call_user_func_array(Array, Array)
#43 [internal function]: Illuminate\Pipeline\Pipeline->Illuminate\Pipeline{closure}(Object(Illuminate\Http\Request))
#44 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(32): call_user_func(Object(Closure), Object(Illuminate\Http\Request))
#45 /var/www/html/vendor/laravel/framework/src/Illuminate/Session/Middleware/StartSession.php(62): Illuminate\Routing\Pipeline->Illuminate\Routing{closure}(Object(Illuminate\Http\Request))
#46 [internal function]: Illuminate\Session\Middleware\StartSession->handle(Object(Illuminate\Http\Request), Object(Closure))
#47 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(136): call_user_func_array(Array, Array)
#48 [internal function]: Illuminate\Pipeline\Pipeline->Illuminate\Pipeline{closure}(Object(Illuminate\Http\Request))
#49 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(32): call_user_func(Object(Closure), Object(Illuminate\Http\Request))
#50 /var/www/html/vendor/laravel/framework/src/Illuminate/Cookie/Middleware/AddQueuedCookiesToResponse.php(37): Illuminate\Routing\Pipeline->Illuminate\Routing{closure}(Object(Illuminate\Http\Request))
#51 [internal function]: Illuminate\Cookie\Middleware\AddQueuedCookiesToResponse->handle(Object(Illuminate\Http\Request), Object(Closure))
#52 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(136): call_user_func_array(Array, Array)
#53 [internal function]: Illuminate\Pipeline\Pipeline->Illuminate\Pipeline{closure}(Object(Illuminate\Http\Request))
#54 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(32): call_user_func(Object(Closure), Object(Illuminate\Http\Request))
#55 /var/www/html/vendor/laravel/framework/src/Illuminate/Cookie/Middleware/EncryptCookies.php(59): Illuminate\Routing\Pipeline->Illuminate\Routing{closure}(Object(Illuminate\Http\Request))
#56 [internal function]: Illuminate\Cookie\Middleware\EncryptCookies->handle(Object(Illuminate\Http\Request), Object(Closure))
#57 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(136): call_user_func_array(Array, Array)
#58 [internal function]: Illuminate\Pipeline\Pipeline->Illuminate\Pipeline{closure}(Object(Illuminate\Http\Request))
#59 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(32): call_user_func(Object(Closure), Object(Illuminate\Http\Request))
#60 [internal function]: Illuminate\Routing\Pipeline->Illuminate\Routing{closure}(Object(Illuminate\Http\Request))
#61 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(103): call_user_func(Object(Closure), Object(Illuminate\Http\Request))
#62 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Router.php(726): Illuminate\Pipeline\Pipeline->then(Object(Closure))
#63 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Router.php(699): Illuminate\Routing\Router->runRouteWithinStack(Object(Illuminate\Routing\Route), Object(Illuminate\Http\Request))
#64 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Router.php(675): Illuminate\Routing\Router->dispatchToRoute(Object(Illuminate\Http\Request))
#65 /var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(246): Illuminate\Routing\Router->dispatch(Object(Illuminate\Http\Request))
#66 [internal function]: Illuminate\Foundation\Http\Kernel->Illuminate\Foundation\Http{closure}(Object(Illuminate\Http\Request))
#67 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(52): call_user_func(Object(Closure), Object(Illuminate\Http\Request))
#68 /var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/CheckForMaintenanceMode.php(44): Illuminate\Routing\Pipeline->Illuminate\Routing{closure}(Object(Illuminate\Http\Request))
#69 [internal function]: Illuminate\Foundation\Http\Middleware\CheckForMaintenanceMode->handle(Object(Illuminate\Http\Request), Object(Closure))
#70 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(136): call_user_func_array(Array, Array)
#71 [internal function]: Illuminate\Pipeline\Pipeline->Illuminate\Pipeline{closure}(Object(Illuminate\Http\Request))
#72 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(32): call_user_func(Object(Closure), Object(Illuminate\Http\Request))
#73 /var/www/html/vendor/fideloper/proxy/src/TrustProxies.php(41): Illuminate\Routing\Pipeline->Illuminate\Routing{closure}(Object(Illuminate\Http\Request))
#74 [internal function]: Fideloper\Proxy\TrustProxies->handle(Object(Illuminate\Http\Request), Object(Closure))
#75 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(136): call_user_func_array(Array, Array)
#76 [internal function]: Illuminate\Pipeline\Pipeline->Illuminate\Pipeline{closure}(Object(Illuminate\Http\Request))
#77 /var/www/html/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(32): call_user_func(Object(Closure), Object(Illuminate\Http\Request))
#78 [internal function]: Illuminate\Routing\Pipeline->Illuminate\Routing{closure}(Object(Illuminate\Http\Request))
#79 /var/www/html/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(103): call_user_func(Object(Closure), Object(Illuminate\Http\Request))
#80 /var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(132): Illuminate\Pipeline\Pipeline->then(Object(Closure))
#81 /var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(99): Illuminate\Foundation\Http\Kernel->sendRequestThroughRouter(Object(Illuminate\Http\Request))
#82 /var/www/html/public/index.php(54): Illuminate\Foundation\Http\Kernel->handle(Object(Illuminate\Http\Request))
#83 {main} {"identification":{"id":"47698f35-33c0-4e83-b3f8-29052bf932a1"}}

Looks to be a simple case of not prefixing the DB_PREFIX when generating the sql?

[bwalker-telstra]

Here are our important env settings

env {
HTTP_PROXY = "{{ proxy_server }}"
HTTPS_PROXY = "{{ proxy_server }}"
DB_DRIVER = "pgsql"
DB_HOST = "postgresql-cachet.service.consul"
DB_DATABASE = "postgres"
DB_USERNAME = "postgres"
DB_PASSWORD = "postgres"
DB_PREFIX = "chq_"
DEBUG = "true"
MAIL_DRIVER = "smtp"
MAIL_HOST = ""
MAIL_NAME = "AutomationEngineering"
MAIL_PORT = "25"
MAIL_USERNAME = ""
MAIL_ADDRESS = ""
APP_DEBUG = "true"
APP_KEY = "SomeRandomStringSomeRandomString"
}

[bwalker-telstra]

Closing this out, looking at the history of the PgSqlRepository.php file this issue appears to have been addressed. Ill try and get the docker side of the project to update to a later version of cachet.

[SimonVillage]

Hi there,

is there an update? We have the same problem

[jbrooksuk]

The issue is fixed under v2.4 :)

[mz52arsi]

I am using cachet docker. I am facing the same issue of metric_points not available.
I am trying to display the graph using metrics api, data is getting inserted via api but not available on status dashboard.

I see that v2.4 is available but release cannot be found for docker
https://github.com/CachetHQ/Cachet/releases
When I try to use
cachet_ver=v2.4.0 or v2.4 instead of cachet_ver=v2.3.10 it gives me 404

cachet_1 | 172.24.0.1 - - [22/Jul/2017:14:47:16 +0000] "GET / HTTP/1.1" 200 3387 "http://127.0.0.1:8003/dashboard/metrics/1/edit" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/59.0.3071.115 Safari/537.36" "-" postgres_1 | ERROR: relation "metric_points" does not exist at character 90 postgres_1 | STATEMENT: select avg(metric_points.value * metric_points.counter) as value FROM chq_metrics m JOIN metric_points ON metric_points.metric_id = m.id WHERE m.id = $1 AND to_char(metric_points.created_at, 'YYYYMMDDHH24MI') = $2 GROUP BY to_char(metric_points.created_at, 'HHMI') cachet_1 | 172.24.0.1 - - [22/Jul/2017:14:47:16 +0000] "GET /metrics/1?filter=last_hour HTTP/1.1" 500 3175 "http://127.0.0.1:8003/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/59.0.3071.115 Safari/537.36" "-"

[SimonVillage]

@jbrooksuk can we get the docker also updated to 2.4.0?

[mz52arsi]

@jbrooksuk Any ETA for this?

[bwalker-telstra]

Hi All,

I worked around this but dropping the DB_PREFIX variable in my env vars.

I did need to go into my postgres instance and rename the tables, but it worked.

I would try and assist on this issue but I can't understand why the latest docker image doesnt have the problem solved because it should be bringing in the master version of cachet.

Cheers,
Byron

[mz52arsi]

@bwalker-telstra Thanks for the suggestion. It worked for me by dropping DB_PREFIX.
Just for FYI I am using master only but its not working for me.

Thanks for your help :)

[jbrooksuk]

You'll need to speak with @djdefi, @TAiS46 😄

[MrCskncn]

Facing the same issue. Looking forward for the 2.4 release for Docker.

[djdefi]

We won't release a new Docker image until 2.4 is officially released, however if you change master to 2.4 in https://github.com/CachetHQ/Docker/blob/master/docker-compose.yml#L16 then run docker-compose build it will build you a Docker image containing the Cachet 2.4 branch.

[MrCskncn]

@djdefi I've tried but I couldn't compile v2.4. Perhaps because of my Dockerfile?

--2017-08-27 17:57:57-- https://github.com/cachethq/Cachet/archive/v2.4.0.tar.gz
Resolving github.com... 192.30.253.113, 192.30.253.112
Connecting to github.com|192.30.253.113|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://codeload.github.com/CachetHQ/Cachet/tar.gz/v2.4.0 [following]
--2017-08-27 17:57:58-- https://codeload.github.com/CachetHQ/Cachet/tar.gz/v2.4.0
Resolving codeload.github.com... 192.30.253.121, 192.30.253.120
Connecting to codeload.github.com|192.30.253.121|:443... connected.
HTTP request sent, awaiting response... 404 Not Found
2017-08-27 17:57:58 ERROR 404: Not Found.

ERROR: Service 'cachet' failed to build: The command '/bin/sh -c wget https://github.com/cachethq/Cachet/archive/${cachet_ver}.tar.gz && tar xzvf ${cachet_ver}.tar.gz --strip-components=1 && chown -R www-data /var/www/html && rm -r ${cachet_ver}.tar.gz && php /bin/composer.phar global require "hirak/prestissimo:^0.3" && php /bin/composer.phar install --no-dev -o && rm -rf bootstrap/cache/*' returned a non-zero code: 8

[djdefi]

Try with just 2.4 not 2.4.0 (there is not 2.4.0 release yet)