Hello,
We are facing a lot of issues with new Funnel creation triggering past report generation, as mentioned in 2 previous topics:
In top of those 2 issues, we are facing one more:
A new Funnel was created on March 16th at 4:30pm, and past reports generation were triggered during the next cron job.
But some of past report generation for the new Funnel were still running after more than 23h:
You can see that the cron job started at 5pm on March 16th, and was still running at 4pm on March 17th, when we killed it manually.
(previous spikes concern the regular cron job, taking less than 20 minutes)
Here is the logs of the Funnel report that took more that 10 seconds:
Also, keep in ming that the most costly queries are not logged, because they never finished.
We monitored on MaridDB instance to capture the faulty queries, and we could observe that there seem to be an infinite loop:
SELECT /*+ MAX_EXECUTION_TIME(7200000) */
log_action1.name AS `label`,
count(distinct log_link_visit_action.idvisit) AS `2`,
count(distinct log_link_visit_action.idvisitor) AS `1`,
count(*) AS `3`,
AVG(log_link_visit_action.product_price) AS `avg_price_viewed`
FROM
matomo_logtmpsegmentf4938b7b1ce8d76fac6ed880e4f827af AS logtmpsegmentf4938b7b1ce8d76fac6ed880e4f827af INNER JOIN matomo_log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = logtmpsegmentf4938b7b1ce8d76fac6ed880e4f827af.idvisit LEFT JOIN matomo_log_action AS log_action1 ON log_action1.idaction = log_link_visit_action.idaction_product_sku
WHERE
log_link_visit_action.idaction_product_sku is not null
GROUP BY
log_action1.name
SELECT /*+ MAX_EXECUTION_TIME(7200000) */
log_action1.name AS `label`,
count(distinct log_link_visit_action.idvisit) AS `2`,
count(distinct log_link_visit_action.idvisitor) AS `1`,
count(*) AS `3`,
AVG(log_link_visit_action.product_price) AS `avg_price_viewed`
FROM
matomo_logtmpsegmenta04727f7f3b247334d7731c5b6a8cc96 AS logtmpsegmenta04727f7f3b247334d7731c5b6a8cc96 INNER JOIN matomo_log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = logtmpsegmenta04727f7f3b247334d7731c5b6a8cc96.idvisit LEFT JOIN matomo_log_action AS log_action1 ON log_action1.idaction = log_link_visit_action.idaction_product_cat4
WHERE
log_link_visit_action.idaction_product_cat4 is not null
GROUP BY
log_action1.name
SELECT /*+ MAX_EXECUTION_TIME(7200000) */
log_action1.name AS `label`,
count(distinct log_link_visit_action.idvisit) AS `2`,
count(distinct log_link_visit_action.idvisitor) AS `1`,
count(*) AS `3`,
AVG(log_link_visit_action.product_price) AS `avg_price_viewed`
FROM
matomo_logtmpsegmentd038465828697fee7478bc6aeac6c716 AS logtmpsegmentd038465828697fee7478bc6aeac6c716 INNER JOIN matomo_log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = logtmpsegmentd038465828697fee7478bc6aeac6c716.idvisit LEFT JOIN matomo_log_action AS log_action1 ON log_action1.idaction = log_link_visit_action.idaction_product_sku
WHERE
log_link_visit_action.idaction_product_sku is not null
GROUP BY
log_action1.name
You can notice that we check a second time log_link_visit_action.idaction_product_sku is not null on a different temporary table. This keep going indefinitely simply changing the temporary table name, and switching WHERE clause between
log_link_visit_action.idaction_product_cat is not null
log_link_visit_action.idaction_product_cat2 is not null
log_link_visit_action.idaction_product_cat3 is not null
log_link_visit_action.idaction_product_cat4 is not null
log_link_visit_action.idaction_product_cat5 is not null
log_link_visit_action.idaction_product_name is not null
log_link_visit_action.idaction_product_sku is not null
...
Could you please assess what could be the root cause of this issue?
FYI, we are using Matomo 4.2.1 and Funnel 4.0.5
Thanks and Regards
