Question

how to fix the 'MySQL Syntax error is showing in 'database performance dashboard' of system activity reports'

  • 30 September 2022
  • 0 replies
  • 14 views

looker is throwing the sql syntax error in pre-build ‘Database Performance’ ,’Instance Performance’ Dashboards in the System Activity page. 

For Ex: Looker is using Pre-build views ‘PDT Builds’ and ‘PDT Event Log’  in building the ‘Database performance dashboard’. ‘PDT Event Log’ is the Direct Log table where looker used to create in underlying MYSQL database and hence no issue with this explore, but ‘PDT Builds’ is the derived view(Explore) using the CTE ,where it is throwing the Syntax Error.

Query generated by Looker is:

WITH pdt_builds AS (SELECT

    pdt_event_log.tid AS `tid`,

    pdt_event_log.connection AS `connection`,

    pdt_event_log.model_name AS `model_name`,

    pdt_event_log.view_name AS `view_name`,

    MIN(

      pdt_event_log.AT

    ) AS `transaction_start`,

    MAX(

      pdt_event_log.AT

    ) AS `transaction_end`,

    SUBSTR(MAX(CASE

        WHEN  pdt_event_log.action  LIKE 'create begin' THEN '00-waiting dependencies'

        WHEN  pdt_event_log.action  LIKE 'create ready' THEN '01-in process'

        WHEN  pdt_event_log.action  LIKE 'create%complete' THEN '03-done'

        WHEN  pdt_event_log.action  LIKE 'create%error' THEN CONCAT('03-', pdt_event_log.action )

        ELSE CONCAT('02-', pdt_event_log.action ) -- unhandled

      END ), 4) AS `status`,

    MAX(

        CASE

          WHEN  pdt_event_log.action  = 'create begin' THEN COALESCE( pdt_event_log.action_data , "Unknown")

          ELSE NULL

        END

       ) AS `context`,

      AS `action_list`

FROM pdt_event_log

WHERE (pdt_event_log.action) LIKE 'create%'

GROUP BY

    1,

    2,

    3,

    4)

 

Appreciated the Help….

 

 


0 replies

Be the first to reply!

Reply