Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.2.9, 10.3.2, 10.2.16, 10.3.8
-
Ubuntu Xenial Xerus, Mac OS 10.13.6
Description
I don't use the critical priority frivolously, but I have a query that always works on a mysql server, but only once at the first execution every 20 to 60 minutes on a MariaDB server. I am able to reproduce, that the query gives a different (wrong) result, if executed a few seconds after it gave the correct result. It also runs about 10 times slower on MariaDB than on mysql 5.7.
I am ready to give you more information than just the query, but the data is sensitive. I have a pseudonymized database dump (101 MB compressed) and a screen recording (87 MB) that I could send to the developers who want to look into the problem. The screen recording shows me executing the query twice with different results and a proof via query log and diff tool, that the queries are the same.
I filed this issue under "Data Definition - Temporary" because I did not find a component for issues with query results and had a vague feeling, that the temporary table has to do with it. However, I tried renaming it every time, but to no avail! Could it be the query cache?
This bug affects our production environment.
I appended the query results with columns separated by tabs, since I could not display them cleanly with this editor.
Here is the query:
DROP TEMPORARY TABLE IF EXISTS top5ads; |
|
|
CREATE TEMPORARY TABLE top5ads (click_count INT(10) UNSIGNED, ad_id INT(10) UNSIGNED); |
|
|
INSERT INTO top5ads (click_count, ad_id) |
SELECT
|
COUNT(*) AS click_count, |
ads.id AS ad_id |
FROM
|
employ__ad_clicks ad_clicks
|
JOIN employ__ads ads ON ads.id = ad_clicks.ad_id |
JOIN employ__companies cmps ON cmps.id = ads.company_id |
WHERE
|
cmps.vdma_company_id > 0 AND |
ads.job_type_id IN (1,2,3,4,5,13,19) AND |
ad_clicks.host <> '' AND |
-- {job_type_id} AND |
click_date BETWEEN '2018-01-01' AND '2018-07-01' |
GROUP BY |
ads.id
|
ORDER BY |
click_count DESC |
LIMIT 5;
|
|
|
SELECT
|
click_count,
|
CONCAT
|
(
|
TRIM(ads.title), ' | ', |
(
|
SELECT |
CONCAT(pcs.postal_code, ' ', ad_pcs.city) |
FROM |
employ__ads_postal_codes ad_pcs
|
JOIN employ__postal_codes pcs ON pcs.id = ad_pcs.postal_code_id |
WHERE |
ad_pcs.ad_id = ads.id
|
LIMIT 1
|
), ' | ', |
click_count,
|
'\n', |
cmps.name, ' ', cmps.second_name, ' | ', DATE_FORMAT(ads.created_at, '%d.%m.%Y'), ' | ', |
CASE WHEN |
ads.published AND |
CURRENT_DATE BETWEEN ads.publishes_at AND ads.ends_at |
THEN |
'online' |
ELSE |
'offline' |
END |
) AS ad_title, |
ads.job_type_id,
|
ads.permalink,
|
mpgs_clicks_lists.mpgs_clicks_list,
|
'Top' AS x_label |
FROM
|
top5ads
|
JOIN employ__ads ads ON ads.id = top5ads.ad_id |
JOIN employ__companies cmps ON cmps.id = ads.company_id |
JOIN
|
(
|
SELECT |
adcs_ad_id,
|
GROUP_CONCAT(mpg_name_click_count ORDER BY mpg_name_click_count SEPARATOR '\n') AS mpgs_clicks_list |
FROM |
(
|
SELECT |
adcs_ad_id,
|
CONCAT(click_counts_mpgs.name, ': ', click_counts_mpgs.click_count_mpg) AS mpg_name_click_count |
FROM |
(
|
SELECT |
adcs.ad_id AS adcs_ad_id, |
COUNT(*) as click_count_mpg, |
mpgs.name |
FROM |
employ__ad_clicks adcs
|
JOIN employ__marketplace_group_hosts mpg_hosts ON adcs.host = mpg_hosts.name |
JOIN employ__marketplace_groups mpgs ON mpgs.id = mpg_hosts.marketplace_group_id |
WHERE |
adcs.host <> '' AND |
adcs.click_date BETWEEN '2018-01-01' AND '2018-07-01' |
GROUP BY |
mpgs.name, |
adcs.ad_id
|
) click_counts_mpgs
|
) mpg_name_click_counts
|
GROUP BY |
adcs_ad_id
|
ORDER BY mpg_name_click_counts.adcs_ad_id |
)
|
mpgs_clicks_lists ON mpgs_clicks_lists.adcs_ad_id = ads.id; |
|
Attachments
Issue Links
- is duplicated by
-
-
- Closed
-