Mysql – Troubles with: sql_mode=only_full_group_by

MySQL

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.

Best Answer

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?

Related Solutions

Mysql – Incorrect definition of table thesql.event after migrating database to mariadb

On centos 7:

mysql Ver 15.1 Distrib 5.5.50-MariaDB, for Linux (x86_64) using readline 5.1

mysql_upgrade -u root -p

This issue is fixed.

MySQL – Alternative to Removing ONLY_FULL_GROUP_BY

I would suggest rewriting the query in a way that minimises the number of columns necessary to put into GROUP BY. In your case you can do that by applying the grouping to the Product_Category table only.

According to your example, that table has the following entries:

+------------+-------------+
| product_id | category_id |
+------------+-------------+
|          1 |           1 |
|          2 |           1 |
|          3 |           1 |
|          1 |           2 |
|          4 |           2 |
|          5 |           2 |
+------------+-------------+

Since you want product names to be unique in the output, group this table by product_id, and for category_id select e.g. the minimum value per product:

SELECT
  product_id,
  MIN(category_id) AS category_id
FROM
  Product_Category
GROUP BY
  product_id

That will give you an output like this:

+------------+-------------+
| product_id | category_id |
+------------+-------------+
|          1 |           1 |
|          2 |           1 |
|          3 |           1 |
|          4 |           2 |
|          5 |           2 |
+------------+-------------+

You can see that each product is listed only once. Joining that table to the other two will not produce duplicates. Therefore, just substitute the above query, as a derived table, for the Product_Category in your query (also removing your GROUP BY from it, of course):

SELECT
  *
FROM
  Product
  JOIN (
    SELECT
      product_id,
      MIN(category_id) AS category_id
    FROM
      Product_Category
    GROUP BY
      product_id
  ) AS pc USING(product_id)
  JOIN Category USING(category_id)
;

Best Answer

Related Solutions

Mysql – Incorrect definition of table thesql.event after migrating database to mariadb

MySQL – Alternative to Removing ONLY_FULL_GROUP_BY

Related Question