0

I developed a simple PHP/MySQL app which works well on my localhost using MAMP PRO 4.1.

The issue is when I moved my app to VPS managed by ServerPilot (basically classic LAMP stack).

I'm getting this error from sql:

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'voluum_optimizer.c.campaign_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

After Googling I managed to suppress it by running the following code before my main query:

SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

But this is probably a "hack" and not the right way to do things.

I wonder how I should write my queries as I didn't have this problem for years, but probably from some new versions of MySQL some stuff changed and now I need to change all my apps until I suppress these errors.

Any guide or tips would be highly appreciated.

Thank you.

0

with any new version of MySQL, MySQL more and more "SQL"

Yes, MySQL allow many hints, which most of other databases reject with errors.

it is and:

SELECT * FROM table GROUP BY column

and hidden datatype conversion, like:

SELECT xxx FROM table WHERE VARCHAR = INT

but You have only one true choice - start change code for proper form

because if in case of hidden type conversion, You are just slowdown server (sometime dramatically), in case of suppress

sql_mode=only_full_group_by

You never can be sure - what information fetched? And what if it official (government required) report? and You fetch wrong, not current address? Or You send package from Your internet store to wrong (outdate) address?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.