Blog module group by issue 1055 error

Gumster

Some Fuel sites I have migrated to a new server, and on at least 2 occasions (so far) the Blog module has hit errors regarding MySQL 1055 error (where MySQL is v5.7.23).

In the Blog users and posts models' common queries, there is only 1 column in the GROUP BY, but this is denied by the MySQL settings.

I fixed the errors using:

posts model _common_query()
$this->db->group_by([$this->_tables['blog_posts'].'.id', $this->_tables['blog_users'].'.display_name' ]);

and in users model _common_query():
$this->db->group_by([$this->_tables['fuel_users'].'.id', $this->_tables['blog_users'].'.id']);

admin

Can you run $this->debug_query() after it executes the query to output the raw SQL statement?

admin

Actually... that won't work if it's erring before that.

admin

It looks like it has something to do with the MySQL setting of "STRICT_TRANS_TABLES" (new one for me). I've pushed that fix to the blog module.

Gumster

I was getting this specifically:
Error Code: 1055. Expression #26 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'display_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

The expression number varied in each model instance .

It was new to me too - a new Cpanel / MySQL configuration default!?

marcus_at_localhost

I came across the same error (MySQL 5.7)

Error in query (1055): Expression #16 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'temp.posts_count' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

with this query:

SELECT 
authors.*, 
CONCAT(first_name, " ", last_name) as name, 
fuel_users.first_name, 
fuel_users.last_name, 
fuel_users.email, 
fuel_users.user_name, 
fuel_users.active as users_active, 
`posts_count` FROM `authors` LEFT JOIN `fuel_users` ON `fuel_users`.`id` = `authors`.`fuel_user_id` 
LEFT JOIN (
    SELECT COUNT(*) AS posts_count, 
    news.author_id 
    FROM news GROUP BY news.author_id
) AS temp ON `temp`.`author_id`= `fuel_users`.`id` WHERE `authors`.`fuel_user_id` = '1' 
GROUP BY `fuel_users`.`id`, `authors`.`id` LIMIT 1

Which originated from this piece of code in my Authors_model (copied from blog module I believe)

    function _common_query($display_unpublished_if_logged_in = NULL)
    {
        parent::_common_query($display_unpublished_if_logged_in);
        $this->db->select('authors.*, CONCAT(first_name, " ", last_name) as name, '.$this->_tables['fuel_users'].'.first_name, '.$this->_tables['fuel_users'].'.last_name, '.$this->_tables['fuel_users'].'.email, '.$this->_tables['fuel_users'].'.user_name, '.$this->_tables['fuel_users'].'.active as users_active', FALSE);
        $this->db->select('posts_count'); // for temp table to get posts count
        $this->db->group_by($this->_tables['fuel_users'].'.id, authors.id');
    }

    function _common_joins()
    {

        $this->db->join($this->_tables['fuel_users'], $this->_tables['fuel_users'].'.id = authors.fuel_user_id', 'left');
        $this->db->join('(SELECT COUNT(*) AS posts_count, news.author_id FROM news GROUP BY news.author_id) AS temp', 'temp.author_id= fuel_users.id', 'left'); 
    }

The solution, and this StackOverflow answer helped me to understand the problem, was adding temp.posts_count to the group by clause

$this->db->group_by($this->_tables['fuel_users'].'.id, authors.id, temp.posts_count');

I'll send a pull request on github.

admin

There was a fix for that posted in August I believe:
https://github.com/daylightstudio/FUEL-CMS-Blog-Module/commit/d4d0e673285939321694a8e29bcd55253b913411

marcus_at_localhost

yes, but I got this error:

Error Number: 1055

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

SELECT authors.*, CONCAT(first_name, " ", last_name) as name, fuel_users.first_name, fuel_users.last_name, fuel_users.email, fuel_users.user_name, fuel_users.active as users_active, `posts_count` FROM `authors` LEFT JOIN `fuel_users` ON `fuel_users`.`id` = `authors`.`fuel_user_id` LEFT JOIN (SELECT COUNT(*) AS posts_count, news.author_id FROM news GROUP BY news.author_id) AS temp ON `temp`.`author_id`= `fuel_users`.`id` WHERE `authors`.`fuel_user_id` = '1' GROUP BY `fuel_users`.`id`, `authors`.`id` LIMIT 1

Filename: /fuel/modules/fuel/core/MY_Model.php

Line Number: 481

and adding , temp.posts_count solved it. And the error message is pointing out that "nonaggregated column 'temp.posts_count'" is the issue. I only had this problem on the live server where MySQL was configured more strict. Maybe it helps if someone else is coming across this.

Cheers.