I have some querys that are acting very differently between the two systems… I did find the following that spits out the differences between the versions.
Once Query (attached) is largely different on 8.0 vs 5.7. I can make it match on 8.0 if I set
SET GLOBAL optimizer_switch = ‘derived_merge=off’;
But this, in turn, causes other queries to act differently I think it has to do with the optimizer where the join is now actually joining on a null value where before it was not.
I do run in the following SQL mode and have for years.
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Net/Net What am I missing that is causing it now to join on null values…
Differences in query execution do change with different versions of MySQL. You can remove the “GLOBAL” from that command and make the change apply only to that session. Or you can use optimizer hints to make the change apply to only 1 single query. Here’s an example:
SELECT /*+ NO_MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt;
Thank you for the quick reply… I do understand that it can be session or query-based but I was attempting to not have to validate every single query that the application uses which is in the tens of thousands… I was looking to try to set the server defaults to similar to the execution plans in 5.7.
Well, that’s the job and best-practice of every DBA when upgrading to new databases right? The best practice is to create an 8.0 replica of your 5.7 source and validate queries on this server before migrating production, which that sounds like what you are doing. Yes, check sql_mode defaults between 5.7 and 8.0 and check for any new optimizer features that were introduced in 8.0 and disable them.
I’ve had similar issues (8.0.23)
A sub-select was no longer joining what it should have.
I was told they filed an internal bug and never heard from it again.
Still blocked from updating to 8.
The best practice is to create an 8.0 replica of your 5.7 source and validate queries on this server before migrating production, which that sounds like what you are doing. Yes, check sql_mode defaults between 5.7 and 8.0 and check for any new optimizer features that were introduced in 8.0 and disable them.