SQL mode requirements for ProcessWire

[Robin S]

There have been a few issues raised here in the forums that seem to relate to SQL mode settings.

There is nothing stated on the requirements page about SQL mode but it seems that certain mode settings can cause problems, and maybe even settings that are now enabled by default in MySQL 5.7. Might be a question for @ryan, or does anyone else know:

What are the SQL mode requirements for ProcessWire?

 

[Robin S]

In a related topic in the ProFields sub-forum @ryan has said:

Quote

I prefer not to have some SQL mode requirement, but instead to update any queries as needed to always work with whatever new versions of MySQL are defaulting to. We've had to update a few queries here and there over time as MySQL changes defaults in their versions. I think this is a good strategy to go with because changing the SQL mode may not always be an option depending on hosting environment.

So please log any SQL mode issues at GitHub as they arise so they can be tracked and resolved.

[Doc]

hi,

Old thread but I've just tested with MySQL 5.7.14 on PW 3.0.42 and did not encounter any problem so far.

[arnoson]

I have the same Issue with MySQL 5.7.26. I'm trying to install an exported site profile with PW 3.0.123.
Is there any solution that doesn't require changing my sql mode manually?

[dragan]

I just installed mySQL 5.7.26 and switched to that version - everything works just fine (Laragon, Windows).

[adrian]

On 8/9/2016 at 4:30 PM, Robin S said:

I prefer not to have some SQL mode requirement, but instead to update any queries as needed to always work with whatever new versions of MySQL are defaulting to. We've had to update a few queries here and there over time as MySQL changes defaults in their versions.

Interesting that Ryan said this when he actually decided to force removal (https://github.com/processwire/processwire/blob/649d2569abc10bac43e98ca98db474dd3d6603ca/wire/config.php#L975) of the new mysql default for "ONLY_FULL_GROUP_BY", rather than properly rewriting the queries to work with the new default. 

 

[horst]

As I understand it, „I prefer“ is not equal with „I never do“ or „I always do“.

A single decission may be a compromise related to: possibilities, „costs“, and possibly available manpower compared to expected time expenditure. 

?

[teppo]

1 hour ago, horst said:

A single decission may be a compromise related to: possibilities, „costs“, and possibly available manpower compared to expected time expenditure.

Agreed. ONLY_FULL_GROUP_BY can be quite tedious to work around, so that's a good candidate for an exception to the general rule. And, just for the reference, it's also one of the modes deemed incompatible with WordPress – so we're not the only ones who've decided that it's not worth it ?

[adrian]

5 hours ago, teppo said:

Agreed. ONLY_FULL_GROUP_BY can be quite tedious to work around

Yeah I know - I had a LOT of work to do to fix the queries on a large custom application of mine, but from the reading I did at the time, it was a highly recommended change to better ensure you're getting the results you were expecting. Mine was very data / calculation heavy though, so maybe not as important for typical PW queries.