Koken is not compatible with ONLY_FULL_GROUP_BY sql_mode

[dpash]

When MySQL is running under ONLY_FULL_GROUP_BY in sql_mode, as is the default in MySQL 5.7, Koken throws errors.

without ONLY_FULL_GROUP_BY:

mysql> SELECT COUNT(*) as count, YEAR(FROM_UNIXTIME(folio_content.published_on)) as event_year, MONTH(FROM_UNIXTIME(folio_content.published_on)) as event_month, `folio_albums`.`id` AS album_id FROM (`folio_content`) LEFT OUTER JOIN `folio_join_albums_content` folio_join_albums_content ON `folio_content`.`id` = `folio_join_albums_content`.`content_id` LEFT OUTER JOIN `folio_albums` folio_albums ON `folio_albums`.`id` = `folio_join_albums_content`.`album_id` WHERE `folio_content`.`visibility` =  0 AND `folio_content`.`deleted` =  0 GROUP BY `event_year`, `event_month` ORDER BY `event_year` DESC, `event_month` DESC;
+-------+------------+-------------+----------+
| count | event_year | event_month | album_id |
+-------+------------+-------------+----------+
|    73 |       2016 |          12 |        3 |
+-------+------------+-------------+----------+
1 row in set (0.00 sec)

With:

mysql> SELECT     COUNT(*) as count,     YEAR(FROM_UNIXTIME(folio_content.published_on)) as event_year,     MONTH(FROM_UNIXTIME(folio_content.published_on)) as event_month,     `folio_albums`.`id` AS album_id  FROM (`folio_content`)  LEFT OUTER JOIN `folio_join_albums_content` folio_join_albums_content ON `folio_content`.`id` = `folio_join_albums_content`.`content_id`  LEFT OUTER JOIN `folio_albums` folio_albums ON `folio_albums`.`id` = `folio_join_albums_content`.`album_id`  WHERE `folio_content`.`visibility` =  0 AND `folio_content`.`deleted` =  0  GROUP BY `event_year`, `event_month`  ORDER BY `event_year` DESC, `event_month` DESC;
ERROR 1055 (42000): Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'vickehco_koken.folio_albums.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

In this example, the issue is that there is the album_id column, which is not in the GROUP BY clause. Putting it in demonstrates why this error is reported by MySQL:

mysql> SELECT     COUNT(*) as count,     YEAR(FROM_UNIXTIME(folio_content.published_on)) as event_year,     MONTH(FROM_UNIXTIME(folio_content.published_on)) as event_month,     `folio_albums`.`id` AS album_id  FROM (`folio_content`)  LEFT OUTER JOIN `folio_join_albums_content` folio_join_albums_content ON `folio_content`.`id` = `folio_join_albums_content`.`content_id`  LEFT OUTER JOIN `folio_albums` folio_albums ON `folio_albums`.`id` = `folio_join_albums_content`.`album_id`  WHERE `folio_content`.`visibility` =  0 AND `folio_content`.`deleted` =  0  GROUP BY `event_year`, `event_month`, album_id  ORDER BY `event_year` DESC, `event_month` DESC;
+-------+------------+-------------+----------+
| count | event_year | event_month | album_id |
+-------+------------+-------------+----------+
|     8 |       2016 |          12 |     NULL |
|     3 |       2016 |          12 |        2 |
|     6 |       2016 |          12 |        3 |
|     4 |       2016 |          12 |        4 |
|    10 |       2016 |          12 |        8 | 
|    10 |       2016 |          12 |       14 |
|     3 |       2016 |          12 |       15 | 
|    10 |       2016 |          12 |       18 |
|     3 |       2016 |          12 |       19 |
|    21 |       2016 |          12 |       20 |
|     3 |       2016 |          12 |       21 |
+-------+------------+-------------+----------+
11 rows in set, 1 warning (0.00 sec)

As you can see the album_id in the first response is picked roughly by random, but is not correct. We either want to know the total count in all albums, in which case we should remove the album_id from the query, or we want to know counts for each album, in which case it should be in the GROUP BY clause.

There are probably other queries with similar issues.

I would recommend a short term solution of warning in the admin side if this setting is enabled, as it's very hard to debug without quite a bit of know-how.

[baurmatt]

This problem still exists? Can we please get a fixed version?

[baurmatt]

This is unbelievable... It's still broken!

As I'm not willing to change my Global MySQL configuration for this broken software, I've implemented this dirty hack:

root@example:/var/www/html/app/database/drivers/mysqli# diff -u mysqli_driver.php.bak mysqli_driver.php
--- mysqli_driver.php.bak   2018-10-13 20:54:11.691704025 +0200
+++ mysqli_driver.php   2018-10-13 20:59:03.819702323 +0200
@@ -74,7 +74,9 @@
        }
        else
        {
-           return @mysqli_connect($this->hostname, $this->username, $this->password, $this->database);
+                       $connection = @mysqli_connect($this->hostname, $this->username, $this->password, $this->database);
+                       $connection->query("SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'");
+                       return $connection;
        }

    }

This works for me using the mysqli driver:

root@example:/var/www/html# grep driver storage/configuration/database.php 
        'driver' => 'mysqli',