javaface commented 9 months ago

Description

In the DevLake grafana Dora Dashboard I see data for Monthly deployments and Median Lead Time for Changes

However, devlake-go api calls return zero for all values..

curl -X 'GET' 'http://localhost:10666/dora/api/metric?type=df_count&aggregation=monthly' -H 'accept: application/json' {"aggregation":"monthly","dataPoints":[{"key":"23/09","value":0},{"key":"23/10","value":0},{"key":"23/11","value":0},{"key":"23/12","value":0},{"key":"24/01","value":0},{"key":"24/02","value":0}]}

curl -X 'GET' 'http://localhost:10666/dora/api/metric?type=mltc&aggregation=weekly' -H 'accept: application/json' {"aggregation":"weekly","dataPoints":[{"key":"202407","value":0},{"key":"202406","value":0},{"key":"202405","value":0},{"key":"202404","value":0},{"key":"202403","value":0},{"key":"202402","value":0},{"key":"202401","value":0},{"key":"202353","value":0},{"key":"202352","value":0},{"key":"202351","value":0},{"key":"202350","value":0},{"key":"202349","value":0},{"key":"202348","value":0},{"key":"202347","value":0},{"key":"202346","value":0},{"key":"202345","value":0},{"key":"202344","value":0},{"key":"202343","value":0},{"key":"202342","value":0},{"key":"202341","value":0},{"key":"202340","value":0},{"key":"202339","value":0},{"key":"202338","value":0},{"key":"202337","value":0},{"key":"202336","value":0},{"key":"202335","value":0},{"key":"202334","value":0},{"key":"202333","value":0}]}

So it's not surprising the Backstage plugin is also getting zero counts...

I do NOT have cors errors or any issues in my logs except for a couple I believe are unrelated and not critical:

devlake-v0190-config-ui-1 | 172.30.0.1 - - [15/Feb/2024:21:11:20 +0000] "GET /grafana/api/live/ws HTTP/1.1" 400 12 "-" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36" "-"
devlake-v0190-grafana-1 | logger=grafana.update.checker t=2024-02-15T21:06:33.886450311Z level=error msg="Update check failed" error="failed to get latest.json repo from github.com: Get \"https://raw.githubusercontent.com/grafana/grafana/main/latest.json\": tls: failed to verify certificate: x509: certificate signed by unknown authority" duration=149.73419ms

Expected behavior

I expect to see devlake-go dora api returning values other than zeroes where applicable. For example, the month of Feb. 2024 should be showing counts for df_count and mltc.

Actual Behavior

Zeroes for all values returned. See Description.

Reproduction steps

version: "3" services: opendora:

profiles:

  # - dev
container_name: opendora-api
# latest 0.2.2
#image: ghcr.io/devoteamnl/opendora/opendora-api:sha-4d8a961
# 0.1.1
#image: ghcr.io/devoteamnl/opendora/opendora-api:sha-a5e1fb1
#i made this from the latest source
image: devlake-go:latest
restart: unless-stopped
ports:
  - "10666:10666"
environment:
  BACKSTAGE_URL: ${BACKSTAGE_URL-http://backstage:7007/}
  DEVLAKE_URL: ${DEVLAKE_URL-http://config-ui:4000/}
  DEVLAKE_DBUSER: ${DEVLAKE_DBUSER-merico}
  DEVLAKE_DBPASS: ${DEVLAKE_DBPASS-merico}
  DEVLAKE_DBADDRESS: ${DEVLAKE_DBADDRESS-mysql:3306}
  DEVLAKE_DBNAME: ${DEVLAKE_DBNAME-lake}
depends_on:
  mysql:
    condition: service_healthy

backstage:

profiles:

#   - dev
container_name: backstage-backend-container
image: backstage-backend-image
restart: unless-stopped
ports:
  - "7007:7007"
environment:
  - REDACTED

backstage-frontend-image: container_name: backstage-frontend-container image: backstage-frontend-image ports:

"4040:4040" db: container_name: backstage-postgres-container image: artifactory.farmersinsurance.com/fadops-docker-hub-proxy/postgres restart: always ports:
5432:5432 environment: POSTGRES_HOST_AUTH_METHOD: trust

mysql: container_name: mysql image: mysql:8 volumes:
mysql-storage:/var/lib/mysql restart: always ports:
3306:3306 environment: MYSQL_ROOT_PASSWORD: admin MYSQL_DATABASE: lake MYSQL_USER: merico MYSQL_PASSWORD: merico TZ: UTC command: --character-set-server=utf8mb4 --collation-server=utf8mb4_bin --skip-log-bin healthcheck: test: ["CMD", "mysqladmin" ,"ping", "-h", "localhost"] timeout: 20s retries: 10

grafana: image: devlake.docker.scarf.sh/apache/devlake-dashboard:v0.19.0 ports:
3002:3000 volumes:
grafana-storage:/var/lib/grafana environment: GF_SERVER_ROOT_URL: "http://localhost:4000/grafana" GF_USERS_DEFAULT_THEME: "light" MYSQL_URL: mysql:3306 MYSQL_DATABASE: lake MYSQL_USER: merico MYSQL_PASSWORD: merico TZ: UTC restart: always depends_on: mysql: condition: service_healthy

devlake: image: devlake.docker.scarf.sh/apache/devlake:v0.19.0 ports:
8080:8080 restart: always volumes:
devlake-log:/app/logs env_file:
./.env environment: LOGGING_DIR: /app/logs TZ: UTC

DB_URL: ${DEVLAKE_MYSQL_CONNECTION_URL:-mysql://merico:merico@mysql:3306/lake?charset=utf8mb4&parseTime=True}

ENCRYPTION_SECRET: "REDACTED"

depends_on: mysql: condition: service_healthy

config-ui: image: devlake.docker.scarf.sh/apache/devlake-config-ui:v0.19.0 ports:
4000:4000 env_file:
./.env environment: DEVLAKE_ENDPOINT: devlake:8080 GRAFANA_ENDPOINT: grafana:3000 TZ: UTC
ADMIN_USER: devlake

ADMIN_PASS: merico

depends_on:
devlake

volumes: mysql-storage: grafana-storage: devlake-log:

Your Environment

Please see complete docker-compose.yaml file contents in Reproduction steps.

This issue is for the opendora devlake-go dora api not Backstage but providing my Backstage environment details anyway: OS: Linux 5.15.133.1-microsoft-standard-WSL2 - linux/x64 node: v18.18.0 yarn: 1.22.19 cli: 0.25.1 (installed) backstage: 1.22.2

Dependencies: @backstage/app-defaults 1.4.7 @backstage/backend-app-api 0.5.10 @backstage/backend-common 0.20.1 @backstage/backend-dev-utils 0.1.3 @backstage/backend-openapi-utils 0.1.2 @backstage/backend-plugin-api 0.6.9 @backstage/backend-tasks 0.5.14 @backstage/catalog-client 1.5.2 @backstage/catalog-model 1.4.3 @backstage/cli-common 0.1.13 @backstage/cli-node 0.2.2 @backstage/cli 0.25.1 @backstage/config-loader 1.6.1 @backstage/config 1.1.1 @backstage/core-app-api 1.11.3 @backstage/core-compat-api 0.1.1 @backstage/core-components 0.11.2, 0.13.10 @backstage/core-plugin-api 1.8.2 @backstage/dev-utils 1.0.26 @backstage/e2e-test-utils 0.1.0 @backstage/errors 1.2.3 @backstage/eslint-plugin 0.1.4 @backstage/frontend-plugin-api 0.5.0 @backstage/integration-aws-node 0.1.8 @backstage/integration-react 1.1.23 @backstage/integration 1.8.0 @backstage/plugin-adr-backend 0.4.6 @backstage/plugin-adr-common 0.2.19 @backstage/plugin-adr 0.6.12 @backstage/plugin-api-docs 0.10.3 @backstage/plugin-app-backend 0.3.57 @backstage/plugin-app-node 0.1.9 @backstage/plugin-auth-backend-module-atlassian-provider 0.1.1 @backstage/plugin-auth-backend-module-gcp-iap-provider 0.2.3 @backstage/plugin-auth-backend-module-github-provider 0.1.6 @backstage/plugin-auth-backend-module-gitlab-provider 0.1.6 @backstage/plugin-auth-backend-module-google-provider 0.1.6 @backstage/plugin-auth-backend-module-oauth2-provider 0.1.6 @backstage/plugin-auth-backend-module-oauth2-proxy-provider 0.1.1 @backstage/plugin-auth-backend-module-okta-provider 0.0.2 @backstage/plugin-auth-backend 0.20.3 @backstage/plugin-auth-node 0.4.3 @backstage/plugin-catalog-backend-module-github 0.4.7 @backstage/plugin-catalog-backend-module-scaffolder-entity-model 0.1.6 @backstage/plugin-catalog-backend 1.16.2 @backstage/plugin-catalog-common 1.0.20 @backstage/plugin-catalog-graph 0.3.3 @backstage/plugin-catalog-import 0.10.5 @backstage/plugin-catalog-node 1.6.1 @backstage/plugin-catalog-react 1.9.3 @backstage/plugin-catalog 1.16.1 @backstage/plugin-events-node 0.2.18 @backstage/plugin-github-actions 0.6.10 @backstage/plugin-github-deployments 0.1.60 @backstage/plugin-home-react 0.1.7 @backstage/plugin-home 0.6.1 @backstage/plugin-jenkins-backend 0.3.3 @backstage/plugin-jenkins-common 0.1.23 @backstage/plugin-jenkins 0.9.4 @backstage/plugin-kubernetes-backend 0.14.1 @backstage/plugin-kubernetes-common 0.7.3 @backstage/plugin-kubernetes-node 0.1.3 @backstage/plugin-kubernetes-react 0.2.1 @backstage/plugin-kubernetes 0.11.4 @backstage/plugin-org 0.6.19 @backstage/plugin-permission-common 0.7.12 @backstage/plugin-permission-node 0.7.20 @backstage/plugin-permission-react 0.4.19 @backstage/plugin-proxy-backend 0.4.7 @backstage/plugin-scaffolder-backend-module-azure 0.1.1 @backstage/plugin-scaffolder-backend-module-bitbucket 0.1.1 @backstage/plugin-scaffolder-backend-module-gerrit 0.1.1 @backstage/plugin-scaffolder-backend-module-github 0.1.1 @backstage/plugin-scaffolder-backend-module-gitlab 0.2.12 @backstage/plugin-scaffolder-backend 1.20.0 @backstage/plugin-scaffolder-common 1.4.5 @backstage/plugin-scaffolder-node 0.2.10 @backstage/plugin-scaffolder-react 1.7.1 @backstage/plugin-scaffolder 1.17.1 @backstage/plugin-search-backend-module-catalog 0.1.13 @backstage/plugin-search-backend-module-pg 0.5.18 @backstage/plugin-search-backend-module-techdocs 0.1.13 @backstage/plugin-search-backend-node 1.2.13 @backstage/plugin-search-backend 1.4.9 @backstage/plugin-search-common 1.2.10 @backstage/plugin-search-react 1.7.5 @backstage/plugin-search 1.4.5 @backstage/plugin-sonarqube-backend 0.2.11 @backstage/plugin-sonarqube-react 0.1.12 @backstage/plugin-sonarqube 0.7.11 @backstage/plugin-tech-insights-backend-module-jsonfc 0.1.41 @backstage/plugin-tech-insights-backend 0.5.23 @backstage/plugin-tech-insights-common 0.2.12 @backstage/plugin-tech-insights-node 0.4.15 @backstage/plugin-tech-insights 0.3.21 @backstage/plugin-tech-radar 0.6.12 @backstage/plugin-techdocs-backend 1.9.2 @backstage/plugin-techdocs-module-addons-contrib 1.1.4 @backstage/plugin-techdocs-node 1.11.1 @backstage/plugin-techdocs-react 1.1.15 @backstage/plugin-techdocs 1.9.3 @backstage/plugin-todo-backend 0.3.7 @backstage/plugin-todo 0.2.33 @backstage/plugin-user-settings 0.8.0 @backstage/release-manifests 0.0.11 @backstage/test-utils 1.4.7 @backstage/theme 0.2.19, 0.4.4, 0.5.0 @backstage/types 1.1.1 @backstage/version-bridge 1.0.7

Context of the bug

No errors in logs except for two I believe to be unrelated to this bug I listed in the Description. Also, no errors in Chrome developer console or network. Everything looks good really. Just the opendora devlake-go dora api returns zeroes while the DevLake grafana dora dashboard is reflecting that there are values.

Have you spent some time to check if this bug has been raised before?

[X] I checked and didn't find similar issue

Are you willing to submit PR?

None

javaface commented 9 months ago

I find that there's no calendar_weeks table in my lake database. Only a calendar_months. I think this may have something to do with why the following query returns zeroes..

WITH RECURSIVE calendar_weeks AS ( SELECT STR_TO_DATE( CONCAT(YEARWEEK(FROM_UNIXTIME(1692195382)), ' Sunday'), '%X%V %W' ) AS week_date UNION ALL SELECT DATE_ADD(week_date, INTERVAL 1 WEEK) FROM calendar_weeks WHERE week_date < FROM_UNIXTIME(1708092982) ), _deployments AS( SELECT YEARWEEK(deployment_finished_date) AS week, count(cicd_deployment_id) AS deployment_count FROM ( SELECT cdc.cicd_deployment_id, max(cdc.finished_date) AS deployment_finished_date FROM cicd_deployment_commits cdc JOIN repos ON cdc.repo_id = repos.id WHERE -- ( -- :project = "" -- OR LOWER(repos.name) LIKE CONCAT('%/', LOWER(:project)) -- ) cdc.result = 'SUCCESS' AND cdc.environment = 'PRODUCTION' GROUP BY 1 ) _production_deployments GROUP BY 1 ), count AS ( SELECT YEARWEEK(cw.week_date) AS data_key, CASE WHEN d.deployment_count IS NULL THEN 0 ELSE d.deployment_count END AS data_value FROM calendar_weeks cw LEFT JOIN _deployments d ON YEARWEEK(cw.week_date) = d.week ORDER BY cw.week_date DESC )SELECT t1.data_key, AVG(t2.data_value) AS data_value FROM count t1 JOIN count t2 ON t2.data_key <= t1.data_key GROUP BY t1.data_key;

javaface commented 9 months ago

So with a little help I find the query is returning zeroes due to lake.cicd_deployment_commits table not having values in repo_id column. Not sure why that is.

duke-b commented 9 months ago

Hello @javaface ,

Thank you for your feedback! This is actually a good catch.

1) As for the issue in regards to the repo_id, I see that the field is nullable but the devlake is using it for it's own joins to display the DORA metrics, even listed in their own specification for Grafana. We are going to look into this a bit deeper and get back to you.

2) As for the weeks, we are constructing the weekly table at the start of the query, as we are trying to give the option to display more fine-grained data as well (for example for following the sprint burndown charts etc).

If you run this you can see what it looks like:

`WITH RECURSIVE calendar_weeks AS ( SELECT STR_TO_DATE( CONCAT(YEARWEEK(FROM_UNIXTIME(1708332899)), ' Sunday'), '%X%V %W' ) AS week_date UNION ALL SELECT DATE_ADD(week_date, INTERVAL 1 WEEK) FROM calendar_weeks WHERE week_date < FROM_UNIXTIME(1708332899) )

SELECT * FROM calendar_weeks;`

duke-b commented 8 months ago

@javaface There has been a new release (0.3.1) with the 3rd metric done, and we believe this fixed the devlake issue. Could you please let us know if the issue persists for you?

javaface commented 7 months ago

@javaface There has been a new release (0.3.1) with the 3rd metric done, and we believe this fixed the devlake issue. Could you please let us know if the issue persists for you?

Yes, issue persists. I updated opendora-api from 0.2.2 image ghcr.io/devoteamnl/opendora/opendora-api:sha-4d8a961 to 0.4.0 image ghcr.io/devoteamnl/opendora/opendora-api:sha-f431829. Backstage plugin is still at 0.2.2 when installing with yarn. Shouldn't yarn be installing 0.4.0 now?

Using devlake 0.19.0 as that is still the latest release.

duke-b commented 7 months ago

@javaface it is updated now, we had some changes with the deployment flows,

"@devoteam-nl/open-dora-backstage-plugin": "0.4.0",

It will pull well now with yarn install

javaface commented 7 months ago

@javaface it is updated now, we had some changes with the deployment flows,

"@devoteam-nl/open-dora-backstage-plugin": "0.4.0",

It will pull well now with yarn install

@duke-b Updated Backstage plugin from "@devoteam-nl/open-dora-backstage-plugin": "^0.2.2" to "@devoteam-nl/open-dora-backstage-plugin": "^0.4.0" re-tested and still have the issue updated DevLake from devlake.docker.scarf.sh/apache/devlake:v0.19.0 to devlake.docker.scarf.sh/apache/devlake:v1.0.0-beta4 re-tested and still have the issue deleted all my devlake volumes and re-created my connections, data scopes, scope configs, project, etc.. re-collected data re-tested and still have the issue

Grafana DORA dashboard is showing data while the Backstage OpenDORA dashboard does not and lake.cicd_deployment_commits table does not have values in repo_id column.

DevoteamNL / opendora

devlake-go returns zeroes #139

Description

Expected behavior

Actual Behavior

Reproduction steps

profiles:

profiles:

DB_URL: ${DEVLAKE_MYSQL_CONNECTION_URL:-mysql://merico:merico@mysql:3306/lake?charset=utf8mb4&parseTime=True}

ENCRYPTION_SECRET: "REDACTED"

ADMIN_USER: devlake

ADMIN_PASS: merico

Your Environment

Context of the bug

Have you spent some time to check if this bug has been raised before?

Are you willing to submit PR?