search

preaction / Minion-Backend-mysql

MySQL backend for the 🐙 Minion job runner
Other
7 stars 14 forks source link

Module fails when Mojo::mysql->strict_mode(1) #36

Closed augensalat closed 3 years ago

augensalat commented 3 years ago

User Story

Minion failed right on startup after upgrade from 0.21 to 0.29. Turns out that https://github.com/preaction/Minion-Backend-mysql/blob/627e0886c2c900830560d4a83654ee5e1e3ec920/lib/Minion/Backend/mysql.pm#L597-L615 fails when you

SET SQL_MODE = ONLY_FULL_GROUP_BY;

This happens when you enable Mojo::mysql strict_mode: https://metacpan.org/pod/Mojo::mysql#strict_mode

Broken since

0.26

How to fix

Add all columns used in SELECT to GROUP BY (not tested).

Workaround

Disable strict_mode in application.

preaction commented 3 years ago

From MySQL's docs, this issue was fixed in 5.7.5 (but I still support 5.6 for the moment):

SQL:1999 and later permits such nonaggregates per optional feature T301 if they are functionally dependent on GROUP BY columns: If such a relationship exists between name and custid, the query is legal. This would be the case, for example, were custid a primary key of customers. MySQL 5.7.5 and later implements detection of functional dependence. https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

Turns out Travis hasn't been testing on 5.6, which explains why it was passing (to my bewilderment). I'll fix the tests and the query. Thanks for reporting this!