Metrics MySQL Schema Error with Official Docker Image

[davidkazuhiro]

Issue Summary

When using the Official Docker Image, I get schema errors when trying to view metrics on the status page, and the metrics graph does not render.

How to reproduce

1. Use official Cachet docker image (cachet/docker:2.3.13) with the official mysql docker image (mysql:5.7.21)
2. Create Metric and Metric Points
3. View Dashboard

(See "How to Reproduce (details)" section below for details)

Expected behavior

1. Metrics are displayed in graph on dashboard
2. No errors are thrown

Actual Behavior

1. Metrics are not displayed in graph on dashboard

2. Errors are thrown

2018/02/20 10:40:45 [error] 122#122: *19 FastCGI sent in stderr: "PHP message: [2018-02-20 10:40:45] development.ERROR: PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'cachet.metric_points' doesn't exist in /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:333

How to Reproduce (Details)

Start Cachet

Open a shell and do the following

Create docker-compose.yml

cat << EOT > docker-compose.yml
version: "3"
  
services:
  mysql:
    image: mysql:5.7.21
    environment:
      - MYSQL_DATABASE=cachet
      - MYSQL_USER=cachet
      - MYSQL_PASSWORD=cachet
      - MYSQL_RANDOM_ROOT_PASSWORD=yes
    restart: always
  cachet:
    image: cachethq/docker:2.3.13
    ports:
      - 8000:8000
    links:
      - mysql:mysql
    environment:
      - DB_DRIVER=mysql
      - DB_HOST=mysql
      - DB_PORT=3306
      - DB_DATABASE=cachet
      - DB_USERNAME=cachet
      - DB_PASSWORD=cachet
      - DB_PREFIX=chq_
      - APP_KEY=Idgz1PE3zO8iNc0E3oeH3CHDPX9MzZe3
      - APP_LOG=errorlog
      - DEBUG=true
EOT

Start containers

docker-compose up

Set up Cachet

1. Open http://localhost:8000/ in your browser
2. Fill in required fields in the form. Below is what I set but nearly anything is fine as input
   • "Environment Setup" Page:
     • Mail From Address: "null"
     • Mail Username: "null"
     • Mail Password: "null"
     • Click "Next"
   • "Status Page Setup" Page:
     • Site Name: "Test"
     • Select your timezone: "Abidjan - 9:44"
     • Click "Next"
   • "Administrator Account" Page:
     • Username: "admin"
     • Email: "admin@example.org"
     • Password: "admin"
     • Click "Complete Setup"
   • "Complete Setup" Page:
     • Click "Go to dashboard"
3. Get your API token
   • On the "Welcome to your new Status page!" dialogue
     • Click "Generate API Token" (or just go directly to http://localhost:8000/dashboard/user)
     • Copy the value of "API Token"
4. Set metrics to display on status page
   • Go to http://localhost:8000/dashboard/settings/setup
   • Make sure "Display graphs on status page?" is checked
   • Click "Save"

Generate some metrics

Open a new shell (leave the docker-compose one open) and do the following

Create Metric

Create environment variable for token

TOKEN=whateveryoucopiedfromcachet

Create data.json file

cat << EOT > data.json
{ 
    "name"          : "Visitors",
    "description"   : "How many visitors",
    "suffix"        : "Visitors per hour",
    "default_value" : "0",
    "display_chart" : "1"
}
EOT

Submit post request

curl --request POST \
     --header "X-Cachet-Token: $TOKEN" \
     --header "Content-Type: application/json" \
     --url http://localhost:8000/api/v1/metrics \
     --data @data.json

Add a metric point

curl --request POST \
     --header "X-Cachet-Token: $TOKEN" \
     --header "Content-Type: application/json" \
     --url http://localhost:8000/api/v1/metrics/1/points \
     --data '{"value": 1,"timestamp": "1519122129"}'

Reproduce Error

View dashboard

Using your browser or wget, access the following page

http://localhost:8000/

Results

Metrics do not render

You will see the metrics show an empty box as follows

Cachet throws errors

Open your shell where docker-compose is running, and you will see the following errors

cachet_1  | 172.20.0.1 - - [20/Feb/2018:10:40:45 +0000] "GET / HTTP/1.1" 200 3387 "http://localhost:8000/dashboard/settings/setup" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/64.0.3282.167 Safari/537.36" "-"
cachet_1  | [20-Feb-2018 10:40:45] WARNING: [pool www] child 135 said into stderr: "NOTICE: PHP message: [2018-02-20 10:40:45] development.ERROR: PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'cachet.metric_points' doesn't exist in /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:333"
cachet_1  | [20-Feb-2018 10:40:45] WARNING: [pool www] child 135 said into stderr: "Stack trace:"
cachet_1  | [20-Feb-2018 10:40:45] WARNING: [pool www] child 135 said into stderr: "#0 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php(333): PDO->prepare('SELECT SUM(mp.`...')"
cachet_1  | [20-Feb-2018 10:40:45] WARNING: [pool www] child 135 said into stderr: "#1 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php(706): Illuminate\Database\Connection->Illuminate\Database\{closure}(Object(Illuminate\Database\MySqlConnection), 'SELECT SUM(mp.`...', Array)"
cachet_1  | [20-Feb-2018 10:40:45] WARNING: [pool www] child 135 said into stderr: "#2 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php(669): Illuminate\Database\Connection->runQueryCallback('SELECT SUM(mp.`...', Array, Object(Closure))"
cachet_1  | [20-Feb-2018 10:40:45] WARNING: [pool www] child 135 said into stderr: "#3 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php(342): Illuminate\Database\Connection->run('SELECT SUM(mp.`...', Array, Object(Closure))"
cachet_1  | [20-Feb-2018 10:40:45] WARNING: [pool www] child 135 said into stderr: "#4 [internal function]: Illuminate\Database\Connection-..."
cachet_1  | 172.20.0.1 - - [20/Feb/2018:10:40:45 +0000] "GET /metrics/1?filter=today HTTP/1.1" 500 3175 "http://localhost:8000/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/64.0.3282.167 Safari/537.36" "-"
cachet_1  | 2018/02/20 10:40:45 [error] 122#122: *19 FastCGI sent in stderr: "PHP message: [2018-02-20 10:40:45] development.ERROR: PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'cachet.metric_points' doesn't exist in /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:333
cachet_1  | Stack trace:
cachet_1  | #0 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php(333): PDO->prepare('SELECT SUM(mp.`...')
cachet_1  | #1 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php(706): Illuminate\Database\Connection->Illuminate\Database\{closure}(Object(Illuminate\Database\MySqlConnection), 'SELECT SUM(mp.`...', Array)
cachet_1  | #2 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php(669): Illuminate\Database\Connection->runQueryCallback('SELECT SUM(mp.`...', Array, Object(Closure))
cachet_1  | #3 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php(342): Illuminate\Database\Connection->run('SELECT SUM(mp.`...', Array, Object(Closure))
cachet_1  | #4 [internal function]: Illuminate\Database\Connection->select(" while reading response header from upstream, client: 172.20.0.1, server: localhost, request: "GET /metrics/1?filter=today HTTP/1.1", upstream: "fastcgi://127.0.0.1:9000", host: "localhost:8000", referrer: "http://localhost:8000/"

[jgimenez]

This looks like this bug, fixed in 2.4 but not released yet: #2104

I fixed it by creating a view: create view metric_points (id, metric_id, value, created_at, updated_at, counter) AS SELECT * from chq_metric_points;

[davidkazuhiro]

Thanks @jgimenez that workaround totally worked!

Weird that @jbrooksuk closed #2104 saying that this bug isn't in v2.3, because it totally is.

Any chance we we can get a docker image of the unreleased v2.4?

[jbrooksuk]

@davidkazuhiro dude, it gets so confusing to maintain multiple versions etc. Also, that was in 2016...

[jbrooksuk]

Prefixes should be supported now though, right?

[jbrooksuk]

Closed by #2960 - I just need to tag a new release.