Database queries fail due to mysql strict / ONLY_FULL_GROUP_BY

[MarkIvanowich]

Immediately after setting up my dev environment, I came across a mysql exception when loading my company page:

Syntax error or access violation: 1055 'officelife.questions.title' isn't in GROUP BY (SQL: select questions.id, questions.title, count(answers.id) as count from questions inner join answers on questions.id = answers.question_id where company_id = 1 group by questions.id order by questions.id desc limit 3)

Which comes from SELECT-ed columns needing to be in the GROUP BY for queries in mysql strict mode.

This can be remedied by changing to 'strict' => false in the config/database.php file, or by explicitly defining modes in the mysql driver:

        'strict' => true,
        'engine' => null,
        'modes'  => [
            // 'ONLY_FULL_GROUP_BY', // comment / omit to disable
            'STRICT_TRANS_TABLES',
            'NO_ZERO_IN_DATE',
            'NO_ZERO_DATE',
            'ERROR_FOR_DIVISION_BY_ZERO',
            'NO_ENGINE_SUBSTITUTION',
        ],
        'options' => ...

note that the other modes above are just an example

[djaiss]

Thanks @MarkIvanowich for the feedback. What does the mySQL strict mode do exactly?

[MarkIvanowich]

MySQL modes dictate how the server should respond to missing or invalid values in queries.

For instance, NO_ZERO_IN_DATE will return a warning if someone enters the date 2021-05-00. It's not a valid date, but is valid in the sense of formatting or comparing months. Another is STRICT_TRANS_TABLES, which will push invalid data to it's nearest valid form. This could be both a blessing and a curse, especially in large applications where you could be interacting with partitioned tables, virtual tables, routines, etc.

Newer versions of MySQL server have strict mode enabled by default. When strict mode is enabled, instead of returning a warning, it will return an error for SELECTs, or flat out rejecting the query for UPDATE and INSERTs. This forces the developer to be more specific with their queries to prevent inadvertent changes.

I've seen other Laravel projects just disable strict and leave it at that. Server SQL modes

[djaiss]

Thanks for taking the time to clarify. Basically, strict mode set to true is a good idea, right?

So we have two choices here:

• be extra permissive and remove this validation
• or keep it that way, and make sure our queries are valid.

[MarkIvanowich]

Correct, keeping set to true is a good idea. Making sure queries are valid is the best approach, no matter what database engine or version the end user picks.

[djaiss]

@MarkIvanowich which version of mysql are you using? I'm also with the strict rules and I don't have this error.

[MarkIvanowich]

vagrant@homestead:~/code/officelife$ mysql -V
mysql  Ver 15.1 Distrib 10.5.6-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

[MarkIvanowich]

How about yourself? What version are you running? You could just reduce this down to an installation note?

Some versions of MySQL server and MariaDB apply different SQL modes. Those users may wish to set strict mode to false under the 'mysql' driver in `/config/database.php`

As long as there is some mention for a future user, I am happy. (I don't want to hold up your development)