devlake-go returns zeroes

[javaface]

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:

1. 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" "-"
2. 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?

• I checked and didn't find similar issue

Are you willing to submit PR?

None

[javaface]

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]

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]

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]

@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]

@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]

@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]

@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.