Blog module group by issue 1055 error

edited August 2018 in Modules

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']);

Comments

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

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

  • 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.

  • edited August 2018

    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!?

  • 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.

  • 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.

Sign In or Register to comment.