[MDEV-16868] Same query gives different results - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.2.9, 10.3.2, 10.2.16, 10.3.8
Fix Version/s: 10.2.18, 10.3.10
Component/s: Data Definition - Temporary, Storage Engine - InnoDB
Labels:
Environment:
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

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

mariadb.cnf
0.5 kB
2018-08-01 13:02
my.cnf
5 kB
2018-08-01 13:02
mysqld_safe_syslog.cnf
0.0 kB
2018-08-01 13:02
query_results_mariadb.txt
2 kB
2018-07-31 13:58

Issue Links

is duplicated by

MDEV-16182 innodb.temp_table_savepoint failed in buildbot with wrong result

Closed

Activity

Ascending order - Click to sort in descending order

Leif Matthiessen created issue - 2018-07-31 14:03

Alice Sherepa added a comment - 2018-07-31 15:25 - edited

Please attach your cnf files and yes, please upload your data to ftp.askmonty.org/private/

Alice Sherepa added a comment - 2018-07-31 15:25 - edited Please attach your cnf files and yes, please upload your data to ftp.askmonty.org/private/

Alice Sherepa made changes - 2018-07-31 15:26

Field	Original Value	New Value
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;	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: {code:sql} 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; {code}

Alice Sherepa made changes - 2018-08-01 11:09

Labels

need_feedback

Alice Sherepa made changes - 2018-08-01 11:10

Assignee

Alice Sherepa [ alice ]

Leif Matthiessen made changes - 2018-08-01 13:02

Attachment		mariadb.cnf [ 45971 ]
Attachment		my.cnf [ 45972 ]
Attachment		mysqld_safe_syslog.cnf [ 45973 ]

Leif Matthiessen added a comment - 2018-08-01 13:07 - edited

Thanks. I attached the cnf files. I changed bind-address to ::ffff:127.0.0.1, because of reasons.

It could take a few days until I can provide the pseudonymized database. I will also try to delete tables that are not necessary to reproduce the bug.

Doesn't the ftp server need any credentials?

Leif Matthiessen added a comment - 2018-08-01 13:07 - edited Thanks. I attached the cnf files. I changed bind-address to ::ffff:127.0.0.1, because of reasons. It could take a few days until I can provide the pseudonymized database. I will also try to delete tables that are not necessary to reproduce the bug. Doesn't the ftp server need any credentials?

Leif Matthiessen added a comment - 2018-08-02 07:57 - edited

I used to work with mysql locally, which always gives the correct result (5 rows). Yesterday, I installed MariaDB via Homebrew (I am on a Mac), did not adjust the config, and had the same bug there. Actually the first result was only 1 row, but the following query executions returned 5 rows.

Some notes for narrowing down the bug:

The SELECT that fills the temporary table always works and returns 5 rows.
If you run the SQL up to the last JOIN (not including), it always returns 5 rows (you have to comment out mpgs_clicks_lists.mpgs_clicks_list of course). This strongly indicates that the cause of this bug has to do with the subselect, that is joined.

curl fails to connect to ftps://ftp.askmonty.org/private. Is it ok, if I upload the file to ftp.mariadb.com like described here: https://mariadb.com/kb/en/meta/mariadb-ftp-server/ ?

Leif Matthiessen added a comment - 2018-08-02 07:57 - edited I used to work with mysql locally, which always gives the correct result (5 rows). Yesterday, I installed MariaDB via Homebrew (I am on a Mac), did not adjust the config, and had the same bug there. Actually the first result was only 1 row, but the following query executions returned 5 rows. Some notes for narrowing down the bug: The SELECT that fills the temporary table always works and returns 5 rows. If you run the SQL up to the last JOIN (not including), it always returns 5 rows (you have to comment out mpgs_clicks_lists.mpgs_clicks_list of course). This strongly indicates that the cause of this bug has to do with the subselect, that is joined. curl fails to connect to ftps://ftp.askmonty.org/private . Is it ok, if I upload the file to ftp.mariadb.com like described here: https://mariadb.com/kb/en/meta/mariadb-ftp-server/ ?

Alice Sherepa made changes - 2018-08-02 09:11

Labels

need_feedback

Alice Sherepa added a comment - 2018-08-02 09:16

please try again with ftp://ftp.askmonty.org/private/, checked just now with curl, seems to work

Alice Sherepa added a comment - 2018-08-02 09:16 please try again with ftp://ftp.askmonty.org/private/ , checked just now with curl, seems to work

Leif Matthiessen added a comment - 2018-08-02 09:22

Please note the protocol. I am uploading sensitive data, and ftp is not secure, so I tried ftps. If there is no other way, I will have to encrypt the file and tell you the key via private message. Surely there must be a routinely used way to upload sensitive data, I can't be the first.

Leif Matthiessen added a comment - 2018-08-02 09:22 Please note the protocol. I am uploading sensitive data, and ftp is not secure, so I tried ftps. If there is no other way, I will have to encrypt the file and tell you the key via private message. Surely there must be a routinely used way to upload sensitive data, I can't be the first.

Alice Sherepa added a comment - 2018-08-02 10:34

right, please use ftps://ftp.mariadb.com/ in this case

Alice Sherepa added a comment - 2018-08-02 10:34 right, please use ftps://ftp.mariadb.com/ in this case

Leif Matthiessen added a comment - 2018-08-02 12:49

I uploaded it to ftp.mariadb.com. However, I had to take a risk, because the ssl certificate is expired.
Check with

openssl s_client -showcerts -connect ftp.mariadb.com:990

Also, on https://mariadb.com/kb/en/meta/mariadb-ftp-server/ it reads
Files may be downloaded from ftp://ftp.mariadb.com/downloads if you know the exact filename.

So we must never disclose the file name. I hope, you can browse the files on the ftp. Filename starts with "MDEV16868".

Leif Matthiessen added a comment - 2018-08-02 12:49 I uploaded it to ftp.mariadb.com. However, I had to take a risk, because the ssl certificate is expired. Check with openssl s_client -showcerts -connect ftp.mariadb.com:990 Also, on https://mariadb.com/kb/en/meta/mariadb-ftp-server/ it reads Files may be downloaded from ftp://ftp.mariadb.com/downloads if you know the exact filename. So we must never disclose the file name. I hope, you can browse the files on the ftp. Filename starts with "MDEV16868".

Leif Matthiessen made changes - 2018-08-02 12:51

Environment

Ubuntu Xenial Xerus

Ubuntu Xenial Xerus, Mac OS

Leif Matthiessen made changes - 2018-08-02 12:52

Environment

Ubuntu Xenial Xerus, Mac OS

Ubuntu Xenial Xerus, Mac OS 10.13.6

Alice Sherepa added a comment - 2018-08-03 08:22

lm, Thanks a lot, I got your file and now deleted it from ftp server.
I can reproduce it on 10.2.16, working on the test case.

Alice Sherepa added a comment - 2018-08-03 08:22 lm , Thanks a lot, I got your file and now deleted it from ftp server. I can reproduce it on 10.2.16, working on the test case.

Leif Matthiessen added a comment - 2018-08-03 08:43

I have to thank you for your patience. I am also amazed, that this bug is now confirmed, because it is so weird. Good luck finding it.

Leif Matthiessen added a comment - 2018-08-03 08:43 I have to thank you for your patience. I am also amazed, that this bug is now confirmed, because it is so weird. Good luck finding it.

Alice Sherepa made changes - 2018-08-06 21:05

Assignee

Alice Sherepa [ alice ]

Thirunarayanan B [ thiru ]

Alice Sherepa made changes - 2018-08-06 21:05

Status

Open [ 1 ]

Confirmed [ 10101 ]

Alice Sherepa made changes - 2018-08-07 11:24

Affects Version/s

10.3.8 [ 23113 ]

Alice Sherepa made changes - 2018-08-07 11:25

Fix Version/s		10.2 [ 14601 ]
Fix Version/s		10.3 [ 22126 ]

Marko Mäkelä made changes - 2018-08-24 06:53

Affects Version/s		10.3.2 [ 22533 ]
Affects Version/s		10.2.9 [ 22611 ]

Marko Mäkelä made changes - 2018-08-24 06:53

Component/s		Storage Engine - InnoDB [ 10129 ]
Component/s	Query Cache [ 10120 ]

Marko Mäkelä made changes - 2018-08-24 06:54

Labels

corruption flush temporary

Marko Mäkelä added a comment - 2018-08-24 06:58

I am sorry, I accidentally caused this regression.
The bug only affected INSERT into a TEMPORARY TABLE…ENGINE=InnoDB.
We would fail to flag the page as modified, and it could happen that the modified page was evicted from the buffer pool and then an older version of the page would be read back from the ibtmp1 file. See the commit message for more details.

Marko Mäkelä added a comment - 2018-08-24 06:58 I am sorry, I accidentally caused this regression. The bug only affected INSERT into a TEMPORARY TABLE…ENGINE=InnoDB. We would fail to flag the page as modified, and it could happen that the modified page was evicted from the buffer pool and then an older version of the page would be read back from the ibtmp1 file. See the commit message for more details.

Marko Mäkelä made changes - 2018-08-24 06:58

issue.field.resolutiondate

2018-08-24 06:58:17.0

2018-08-24 06:58:17.485

Marko Mäkelä made changes - 2018-08-24 06:58

Fix Version/s		10.2.18 [ 23112 ]
Fix Version/s		10.3.10 [ 23140 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	10.3 [ 22126 ]
Resolution		Fixed [ 1 ]
Status	Confirmed [ 10101 ]	Closed [ 6 ]

Marko Mäkelä made changes - 2019-09-30 19:42

Link

This issue is duplicated by ~~MDEV-16182~~ [ ~~MDEV-16182~~ ]

Sergei Golubchik made changes - 2021-12-06 21:47

Workflow

MariaDB v3 [ 88669 ]

MariaDB v4 [ 154738 ]

People

Assignee:: Thirunarayanan Balathandayuthapani

Reporter:: Leif Matthiessen

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2018-07-31 14:03

Updated:: 2019-09-30 19:42

Resolved:: 2018-08-24 06:58

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration