MySQL query returns different set of results on two identical databases

Question

I exported a live MySQL database (running mysql 5.0.45) to a local copy (running mysql 5.1.33) with no errors upon import. There is a view in the database, that when executed locally, returns a different set of data than when executed remotely. It's returning 32 results instead of 63. When I execute the raw sql, the same problem occurs. I've inspected the data in all tables being joined, and the counts are the same.

The query is simple and has no where conditions - but about 10 joins. Aside from the differences in mysql versions... I can't find any reason that this query would return different results between databases... since they are effectively exact copies.

Has anyone experienced a problem like this before?

Answer 1

I had problems like this on upgrading from 4.1 to 5.0 caused by changes in how SQL was implemented on the different versions. Your views might be affected by something like this.

Our upgrade process now involves building synchronized replicas of the two versions, taking extracts from the live query logs, and playing these against both versions and comparing the results. For 4.1 to 5.0 upgrade, it took us 6 months to work through the changes required to get our system compatible with both given that we have over 1000 distinct queries.

Maatkit provide some tools to help with upgrades:

mk-table-checksum - create checksums of your tables on both versions. This will identify minute differences that can creep in across the load.

Possibly this could be used too:

mk-query-digest - extract queries from logs for replaying. (I am not sure if this works with query log or just slow-query log: we wrote a similar tool and so did not use this.)

Answer 2

The problem was that certain rows in the exported DB had IDs of 0, but when imported were given a positive integer ID. As a result, the broken FK references caused the different resulting queries.