I'm working on a laravel 4.2 app that was running on a mysql 5.5. This app migrated to a new server, now running a mysql 5.7.
After the upgrade some queries are now returning empty.
I believe this as something to do with the sql_mode mysql 5.7 has introduce.
Example query
SELECT * FROM `table1` WHERE `col1` = 'val1' and
(SELECT count(*) FROM `table2`
WHERE `table2`.`table1_id` = `table1`.`id`
and `driver_id` = '39') >= 1 and `table1`.`id` = '86' LIMIT 1
This is a query that laravel eloquent produces. It returns empty. On the old mysql 5.5 it returns one line as expected.
If fixed the id from the main query it works.
... WHERE `table2`.`table1_id` = 86 ...
As I said before I believe this as something to do with the sql_mode.
Any thoughts?
There's nothing wrong with the query, it's true that you can change the query to avoid the problem but that doesn't solve the real issue.
The solution is to disable index_merge_intersection in the optimizer_switch configuration.
It's a bug found on mysql 5.7 https://bugs.mysql.com/bug.php?id=79675
It worked for me.