Different query result, depending from data load pattern

FYI: Same result on 5.6.37-82.2

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

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.

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

Thank you for the report.

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

Not repeatable with version 5.5

Workaround: use ignore index(organizationOID)

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