Different query result, depending from data load pattern

Bug #1721753 reported by Daniel Rupp on 2017-10-06
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
Percona Server moved to https://jira.percona.com/projects/PS
Status tracked in 5.7
5.5
Invalid
Undecided
Unassigned
5.6
Triaged
High
Unassigned
5.7
Triaged
High
Unassigned
8.0
Triaged
High
Unassigned

Bug Description

The query result changes depending on the server version, this can´t be right?

Table for the query is attached.

Results from the Master
-- EXAMPLE #1 --
@ Percona Server 5.6.36-82.0
mysql> SELECT DISTINCT partner0_.organizationOID AS col_0_0_ FROM MAD_PARTNER partner0_ WHERE partner0_.OID IN (
    -> 'be067ca5-6cd3-42dc-8a68-a08e9800b195',
    -> 'dff1ffa7-58c2-46f3-9880-9a02e8e9c8ab',
    -> '8c1fb1ae-25e2-49f8-bb6a-65b5716d0d2b',
    -> '7a090a98-08ec-4924-ac1a-d4b45c749cac',
    -> '83031acb-43cd-4c97-963e-8692a007aba5');
+--------------------------------------+
| col_0_0_ |
+--------------------------------------+
| e0f52141-941b-437c-87a5-dff3f4f74999 |
+--------------------------------------+
1 row in set (0.00 sec)
-- EXAMPLE #1 --

Results from the Slave
-- EXAMPLE #2 --
@ Percona Server 5.6.36-82.1 (and 82.2)
mysql> SELECT DISTINCT partner0_.organizationOID AS col_0_0_ FROM MAD_PARTNER partner0_ WHERE partner0_.OID IN (
    -> 'be067ca5-6cd3-42dc-8a68-a08e9800b195',
    -> 'dff1ffa7-58c2-46f3-9880-9a02e8e9c8ab',
    -> '8c1fb1ae-25e2-49f8-bb6a-65b5716d0d2b',
    -> '7a090a98-08ec-4924-ac1a-d4b45c749cac',
    -> '83031acb-43cd-4c97-963e-8692a007aba5');
Empty set (0.00 sec)
-- EXAMPLE #2 --

The second server is an identical slave of the first one.

If you reduce the list the result changes:
-- EXAMPLE #3 --
@ Percona Server 5.6.36-82.1 (and 82.2)
mysql> SELECT DISTINCT partner0_.organizationOID AS col_0_0_ FROM MAD_PARTNER partner0_ WHERE partner0_.OID IN (
    -> 'dff1ffa7-58c2-46f3-9880-9a02e8e9c8ab',
    -> '7a090a98-08ec-4924-ac1a-d4b45c749cac',
    -> 'be067ca5-6cd3-42dc-8a68-a08e9800b195');
+--------------------------------------+
| col_0_0_ |
+--------------------------------------+
| e0f52141-941b-437c-87a5-dff3f4f74999 |
+--------------------------------------+
1 row in set (0.01 sec)
-- EXAMPLE #3 --

A 5.7.18 client works correctly.

If you need any further info or testing i am glad to help.

Daniel Rupp (rupp.riege.com) wrote :
Daniel Rupp (rupp.riege.com) wrote :

FYI: Same result on 5.6.37-82.2

Nickolay Ihalainen (ihanick) wrote :

Hi,

I'm getting exactly the same results for 5.6.36-82.1 as you listed for 5.6.36-82.0.

mysql> SELECT DISTINCT partner0_.organizationOID AS col_0_0_ FROM mad_partner partner0_ WHERE partner0_.OID IN (
    -> 'be067ca5-6cd3-42dc-8a68-a08e9800b195',
    -> 'dff1ffa7-58c2-46f3-9880-9a02e8e9c8ab',
    -> '8c1fb1ae-25e2-49f8-bb6a-65b5716d0d2b',
    -> '7a090a98-08ec-4924-ac1a-d4b45c749cac',
    -> '83031acb-43cd-4c97-963e-8692a007aba5');
+--------------------------------------+
| col_0_0_ |
+--------------------------------------+
| e0f52141-941b-437c-87a5-dff3f4f74999 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DISTINCT partner0_.organizationOID AS col_0_0_ FROM mad_partner partner0_ WHERE partner0_.OID IN (
    -> 'dff1ffa7-58c2-46f3-9880-9a02e8e9c8ab',
    -> '7a090a98-08ec-4924-ac1a-d4b45c749cac',
    -> 'be067ca5-6cd3-42dc-8a68-a08e9800b195');
+--------------------------------------+
| col_0_0_ |
+--------------------------------------+
| e0f52141-941b-437c-87a5-dff3f4f74999 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-------------+
| version() |
+-------------+
| 5.6.36-82.1 |
+-------------+
1 row in set (0.00 sec)

Could you try to reproduce the issue on single server or verify that data is the same on master and slaves with pt-table-checksum: https://www.percona.com/blog/2015/08/12/mysql-replication-primer-with-pt-table-checksum-and-pt-table-sync/

Thanks,
Nickolay

Changed in percona-server:
status: New → Incomplete
Daniel Rupp (rupp.riege.com) wrote :

FYI: I will try to reproduce this problem some more times, it´s just taking a while because of other work. Will get back to you.

Daniel Rupp (rupp.riege.com) wrote :

Ok i was able to test a bit and i don´t think that 5.6.36-82.0 or 2.6.37-82.2 are different, so lets just go with the most recent one.

I built a test-case that can be re-run a couple of times. I´m seeing different behavior over a couple of runs, so be sure to try this at least 10-15 times. Also let the script run till it did the queries at least 15 times.

My observation is that one of two things happen:
- it will show no result on the first query and results for #2 & #3 from the start and stay that way
- it will show NO results on all queries and suddenly at the 10th try it will start showing results on all three queries

Hopefully you can reproduce this and maybe explain.

Test setup:
RHEL6.7.z
Percona-Server-shared-56-5.6.37-rel82.2.el6.x86_64
Percona-Server-shared-51-5.1.73-rel14.12.625.rhel6.x86_64
Percona-Server-client-56-5.6.37-rel82.2.el6.x86_64
Percona-Server-server-56-5.6.37-rel82.2.el6.x86_64

Default install - default my.cnf, no changes, single server only

[<email address hidden> ~]$ cat testscript.sh
service mysql stop
rm -rf /var/lib/mysql/*
mysql_install_db --user=mysql
service mysql start
mysql test < /root/mad_partner-anonymized.sql
COUNT=1
while true; do
    echo $COUNT
    bash testquery.sh
    sleep 1
    COUNT=$(($COUNT + 1))
done
[<email address hidden> ~]$ cat testquery.sh
#!/bin/bash

mysql -vvv test -e"SELECT DISTINCT partner0_.organizationOID AS col_0_0_ FROM mad_partner partner0_ WHERE partner0_.OID IN (
 'be067ca5-6cd3-42dc-8a68-a08e9800b195',
 'dff1ffa7-58c2-46f3-9880-9a02e8e9c8ab',
 '8c1fb1ae-25e2-49f8-bb6a-65b5716d0d2b',
 '7a090a98-08ec-4924-ac1a-d4b45c749cac',
 '83031acb-43cd-4c97-963e-8692a007aba5');"

mysql -vvv test -e"SELECT DISTINCT partner0_.organizationOID AS col_0_0_ FROM mad_partner partner0_ WHERE partner0_.OID IN (
 'dff1ffa7-58c2-46f3-9880-9a02e8e9c8ab',
 '7a090a98-08ec-4924-ac1a-d4b45c749cac',
 'be067ca5-6cd3-42dc-8a68-a08e9800b195');"

mysql -vvv test -e"SELECT DISTINCT partner0_.organizationOID AS col_0_0_ FROM mad_partner partner0_ WHERE partner0_.OID IN (
 '8c1fb1ae-25e2-49f8-bb6a-65b5716d0d2b',
        '83031acb-43cd-4c97-963e-8692a007aba5');"
[<email address hidden> ~]$ bash testscript.sh

Changed in percona-server:
status: Incomplete → New
Changed in percona-server:
assignee: nobody → Sveta Smirnova (svetasmirnova)
Sveta Smirnova (svetasmirnova) wrote :

Thank you for the report.

Verified as described. One of reasons for output change is disable keys/enable keys statements in the dump file

Sveta Smirnova (svetasmirnova) wrote :
Sveta Smirnova (svetasmirnova) wrote :

Not repeatable with version 5.5

tags: added: regression
Sveta Smirnova (svetasmirnova) wrote :

Workaround: use ignore index(organizationOID)

tags: added: upstream
summary: - Query result changed between 5.6.36-82.0 and 5.6.36-82.1
+ Different query result, depending from data load pattern

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1118

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.