search

apache / incubator-devlake

Apache DevLake is an open-source dev data platform to ingest, analyze, and visualize the fragmented data from DevOps tools, extracting insights for engineering excellence, developer experience, and community growth.
https://devlake.apache.org/
Apache License 2.0
2.51k stars 493 forks source link

[Bug][Module Name] AxiosError: Request failed with status code 500 #7562

Closed kzhou57 closed 3 weeks ago

kzhou57 commented 1 month ago

Search before asking

What happened

It works very well, until today, when I access the projects page, I got this error :

AxiosError: Request failed with status code 500

And on grafana , my dora dashboard shows no data and some errors like :

Templating [project] Error updating options: error when executing the sql query

What do you expect to happen

The project pages loads, and grafana shows my dora dashboard.

How to reproduce

I don't how.

Anything else

No response

Version

Latest, I assume, we install it last month

Are you willing to submit PR?

Code of Conduct

dosubot[bot] commented 1 month ago

Hey @kzhou57! 👋 I'm here to help you with any bugs, questions, or contributions you have for the project. Just give me a shout if you need anything!

To resolve the AxiosError: Request failed with status code 500 on the projects page and the SQL query execution errors in the Grafana DORA dashboard, follow these steps:

  1. Check Data Sources: Ensure that the required data sources (Deployments, Pull Requests, Incidents) are correctly set up and connected in Grafana. These data sources should be from Jenkins, GitLab CI, GitHub Action, webhook, Jira issues, GitHub issues, TAPD issues, PagerDuty Incidents, etc.

  2. Define Transformations: Make sure that the deployments and incidents are defined in the data transformations while configuring the blueprint of a project. This is crucial for the SQL queries to fetch the correct data.

  3. Validate SQL Queries: Ensure that the SQL queries used in the Grafana panels are correct and can execute without errors. Here is an example of a SQL query used in the DORA dashboard:

    -- Metric 1: Deployment Frequency
with last_few_calendar_months as(
    -- construct the last few calendar months within the selected time period in the top-right corner
    SELECT CAST(($__timeTo()-INTERVAL (H+T+U) DAY) AS date) day
    FROM ( SELECT 0 H
            UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300
        ) H CROSS JOIN ( SELECT 0 T
            UNION ALL SELECT  10 UNION ALL SELECT  20 UNION ALL SELECT  30
            UNION ALL SELECT  40 UNION ALL SELECT  50 UNION ALL SELECT  60
            UNION ALL SELECT  70 UNION ALL SELECT  80 UNION ALL SELECT  90
        ) T CROSS JOIN ( SELECT 0 U
            UNION ALL SELECT   1 UNION ALL SELECT   2 UNION ALL SELECT   3
            UNION ALL SELECT   4 UNION ALL SELECT   5 UNION ALL SELECT   6
            UNION ALL SELECT   7 UNION ALL SELECT   8 UNION ALL SELECT   9
        ) U
    WHERE
        ($__timeTo()-INTERVAL (H+T+U) DAY) > $__timeFrom()
),

_production_deployment_days as(
    -- When deploying multiple commits in one pipeline, GitLab and BitBucket may generate more than one deployment. However, DevLake consider these deployments as ONE production deployment and use the last one's finished_date as the finished date.
    SELECT
        cdc.cicd_deployment_id as deployment_id,
        max(DATE(cdc.finished_date)) as day
    FROM cicd_deployment_commits cdc
    JOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and pm.`table` = 'cicd_scopes'
    WHERE
        pm.project_name in (${project})
        and cdc.result = 'SUCCESS'
        and cdc.environment = 'PRODUCTION'
    GROUP BY 1
),

_days_weekly_deploy as(
    -- calculate the number of deployment days every week
    SELECT
            date(DATE_ADD(last_few_calendar_months.day, INTERVAL -WEEKDAY(last_few_calendar_months.day) DAY)) as week,
            MAX(if(_production_deployment_days.day is not null, 1, 0)) as weeks_deployed,
            COUNT(distinct _production_deployment_days.day) as days_deployed
    FROM 
        last_few_calendar_months
        LEFT JOIN _production_deployment_days ON _production_deployment_days.day = last_few_calendar_months.day
    GROUP BY week
),

_days_monthly_deploy as(
    -- calculate the number of deployment days every month
    SELECT
            date(DATE_ADD(last_few_calendar_months.day, INTERVAL -DAY(last_few_calendar_months.day)+1 DAY)) as month,
            MAX(if(_production_deployment_days.day is not null, 1, null)) as months_deployed,
          COUNT(distinct _production_deployment_days.day) as days_deployed
    FROM 
        last_few_calendar_months
        LEFT JOIN _production_deployment_days ON _production_deployment_days.day = last_few_calendar_months.day
    GROUP BY month
),

_days_six_months_deploy AS (
  SELECT
    month,
    SUM(days_deployed) OVER (
      ORDER BY month
      ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
    ) AS days_deployed_per_six_months,
    COUNT(months_deployed) OVER (
      ORDER BY month
      ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
    ) AS months_deployed_count,
    ROW_NUMBER() OVER (
      PARTITION BY DATE_FORMAT(month, '%Y-%m') DIV 6
      ORDER BY month DESC
    ) AS rn
  FROM _days_monthly_deploy
),

_median_number_of_deployment_days_per_week_ranks as(
    SELECT *, percent_rank() over(order by days_deployed) as ranks
    FROM _days_weekly_deploy
),

_median_number_of_deployment_days_per_week as(
    SELECT max(days_deployed) as median_number_of_deployment_days_per_week
    FROM _median_number_of_deployment_days_per_week_ranks
    WHERE ranks <= 0.5
),

_median_number_of_deployment_days_per_month_ranks as(
    SELECT *, percent_rank() over(order by days_deployed) as ranks
    FROM _days_monthly_deploy
),

_median_number_of_deployment_days_per_month as(
    SELECT max(days_deployed) as median_number_of_deployment_days_per_month
    FROM _median_number_of_deployment_days_per_month_ranks
    WHERE ranks <= 0.5
),

_days_per_six_months_deploy_by_filter AS (
SELECT
  month,
  days_deployed_per_six_months,
  months_deployed_count
FROM _days_six_months_deploy
WHERE rn%6 = 1
),

_median_number_of_deployment_days_per_six_months_ranks as(
    SELECT *, percent_rank() over(order by days_deployed_per_six_months) as ranks
    FROM _days_per_six_months_deploy_by_filter
),

_median_number_of_deployment_days_per_six_months as(
    SELECT min(days_deployed_per_six_months) as median_number_of_deployment_days_per_six_months, min(months_deployed_count) as is_collected
    FROM _median_number_of_deployment_days_per_six_months_ranks
    WHERE ranks >= 0.5
)

SELECT 
  CASE
    WHEN ('$dora_report') = '2023' THEN
            CASE  
                WHEN median_number_of_deployment_days_per_week >= 7 THEN CONCAT(median_number_of_deployment_days_per_week, ' deployment days per week(elite)')
                WHEN median_number_of_deployment_days_per_week >= 1 THEN CONCAT(median_number_of_deployment_days_per_week, ' deployment days per week(high)')
                WHEN median_number_of_deployment_days_per_month >= 1 THEN CONCAT(median_number_of_deployment_days_per_month, ' deployment days per month(medium)')
                WHEN median_number_of_deployment_days_per_month < 1 and is_collected is not null THEN CONCAT(median_number_of_deployment_days_per_month, ' deployment days per month(low)')
                ELSE "N/A. Please check if you have collected deployments." END
     WHEN ('$dora_report') = '2021' THEN
            CASE  
                WHEN median_number_of_deployment_days_per_week >= 7 THEN CONCAT(median_number_of_deployment_days_per_week, ' deployment days per week(elite)')
                WHEN median_number_of_deployment_days_per_month >= 1 THEN CONCAT(median_number_of_deployment_days_per_month, ' deployment days per month(high)')
                WHEN median_number_of_deployment_days_per_six_months >= 1 THEN CONCAT(median_number_of_deployment_days_per_six_months, ' deployment days per six months(medium)')
                WHEN median_number_of_deployment_days_per_six_months < 1 and is_collected is not null THEN CONCAT(median_number_of_deployment_days_per_six_months, ' deployment days per six months(low)')
                ELSE "N/A. Please check if you have collected deployments." END
    ELSE 'Invalid dora report'
  END AS 'Deployment Frequency'
FROM _median_number_of_deployment_days_per_week, _median_number_of_deployment_days_per_month, _median_number_of_deployment_days_per_six_months

  4. Validate and Debug: Use the DORA validation dashboard to validate and debug the dashboard. This can help identify any issues with the data or queries.

By following these steps, you should be able to resolve the AxiosError: Request failed with status code 500 and the SQL query execution errors in the Grafana DORA dashboard [1].

To continue talking to Dosu, mention @dosu.

klesh commented 1 month ago

Please check if your database system is up and running.