Different query result, depending from data load pattern

[Daniel Rupp]

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)]

FYI: Same result on 5.6.37-82.2

[Nickolay Ihalainen (ihanick)]

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

[Daniel Rupp (rupp.riege.com)]

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)]

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

[Sveta Smirnova (svetasmirnova)]

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)]

Not repeatable with version 5.5

[Sveta Smirnova (svetasmirnova)]

Workaround: use ignore index(organizationOID)

[Shahriyar Rzayev (rzayev-sehriyar)]

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