I’m running CakePHP 2.10.7.
Having already read about issues relating to sql mode only_full_group_by, my app is now working error free however when running on my production servers with MySQL 5.7.21 none of the generated SQL queries are including JOIN statements. In contrast, when I run the app locally on MySQL 5.6.27 they are working as expected.
I’ve checked all the cake, MySQL and more general logs on my local environment and remote server and found no signs of errors or explanation relating to to the incomplete queries.
I’ve extracted the generated queries using the debug bar. Here’s what I see on localhost (MySQL 5.6.27):
SELECT `Post`.
`id`, `Post`.
`title`, `Post`.
`body`, `Post`.
`allow_comments`, `Post`.
`comments`, `Post`.
`created`, `Post`.
`explicit`
FROM `mytable`.
`posts`
AS `Post`
INNER JOIN `mytable`.
`categories_posts`
AS `CategoriesPost`
ON(`Post`.
`id` = `CategoriesPost`.
`post_id`) inner JOIN `mytable`.
`categories`
AS `Category`
ON(`CategoriesPost`.
`category_id` = `Category`.
`id`) WHERE `Post`.
`id` < 94127 AND `Post`.
`explicit` = 0 AND((((`Post`.
`report_count` < 5) AND(`Post`.
`banned` = 0))) OR(`Post`.
`whitelisted` = 1)) AND `Category`.
`id` = 11752 AND `Post`.
`is_private` = 0 ORDER BY `Post`.
`created`
DESC LIMIT 2
And here’s what is generated on my server (MySQL 5.7.21):
SELECT `Post`.
`id`, `Post`.
`title`, `Post`.
`body`, `Post`.
`allow_comments`, `Post`.
`comments`, `Post`.
`created`, `Post`.
`explicit`
FROM `mytable`.
`posts`
AS `Post`
WHERE `Post`.
`id` < 94127 AND `Post`.
`explicit` = 0 AND((((`Post`.
`report_count` < 5) AND(`Post`.
`banned` = 0))) OR(`Post`.
`whitelisted` = 1)) AND `Post`.
`is_private` = 0 ORDER BY `Post`.
`created`
DESC LIMIT 2
You can see where the inner JOIN is not being generated as part of the statement on the production server. Is there something else I can check to find out what’s causing the difference between the SQL statements on the two environments?
Thank you for any help.