empty query after mysql upgrade from 5.5 to 5.7

Question

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?

Sorry, that was a typo when I pasted the question. That's the sql Laravel Eloquent reproduces. I just changed the table and cols names for privacy. — cmancre, Dec 16 '16 at 17:26
If I change "count(*)" to "count(`table`.`id`)" it works. Rings a bell? — cmancre, Dec 16 '16 at 18:26

score 1 · Accepted Answer · answered Dec 19 '16 at 14:24

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.

score 0 · Answer 2 · answered Dec 17 '16 at 05:48

In most databases that support it, count(*) doesn't actually retrieve all records and count them -- instead it fetches some metadata field that just tracks the number of rows

Of course, it's reasonable to expect that, regardless of how it's implemented, the result of count(*) would be the same as more a complex but equivalent query.

SELECT * FROM `table1` WHERE `col1` = 'val1' and 
(SELECT count(`table2`.`id`) FROM `table2` 
WHERE `table2`.`table1_id` = `table1`.`id` 
and `driver_id` = '39') >= 1 and `table1`.`id` = '86' LIMIT 1

or for getting boolean try this

SELECT * FROM `table1` WHERE `col1` = 'val1' and 
(SELECT 1 FROM `table2` 
WHERE `table2`.`table1_id` = `table1`.`id` 
and `driver_id` = '39') >= 1 and `table1`.`id` = '86' LIMIT 1

I understand, but that's the code reproduced by Laravel Eloquent ORM. I believe the solution is on the sql_mode configuration. I have the default by now. What do you think? — cmancre, Dec 17 '16 at 10:44

empty query after mysql upgrade from 5.5 to 5.7

2 Answers2