A Database Error Occurred Error Number: 1055

Question

Changed the database from MySQL to MySQLI and getting the error - A Database Error Occurred

Error Number: 1055

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

SELECT *,o.id as id, GROUP_CONCAT(pia.applicantid) as applicants 
FROM `pr_opportunity` as o 
    LEFT OUTER JOIN pr_internal_applicant as pia 
            ON o.id = pia.positionid 
WHERE o.approval_status='Approved' 
and DATE(o.deadline) > DATE(NOW()) 
GROUP BY o.id

Filename: /var/www/html/singlecodebase/Feb152017/models/mod_common.php

Line Number: 6999

My model file mod_common is as below:

function get_opportunity_list()
{
    $sql = "SELECT *,o.id as id, GROUP_CONCAT(pia.applicantid) as applicants 
            FROM `".$this->myTables['opportunity']."` as o 
            LEFT OUTER JOIN pr_internal_applicant as pia 
            ON o.id = pia.positionid 
            WHERE o.approval_status='Approved' 
            and DATE(o.deadline) > DATE(NOW()) 
            GROUP BY o.id";
    $query=$this->db->query($sql);

    if($query->num_rows() > 0){
        $rows = $query->result();

    }       
    return $rows;
}

No clue how to solve this error

`Changed the database from mysql to mysqli` do you mean you upgraded mysql from 5.6 to 5.7? `mysqli` and `mysql_` are different PHP drivers to interact with `mysql`. — chris85, Sep 12 '17 at 11:27
You might try matching your `(` and `)` brackets in the query — RiggsFolly, Sep 12 '17 at 11:28
Remove the `*,` in both queries and it will work. If you do a group by, you either have to use aggregation functions (like GROUP_CONCAT) or put columns you select into the group by clause. — Adder, Sep 12 '17 at 11:30
installed phpmyadmin using one click install which runs Database client version: libmysql - mysqlnd 5.0.12-dev - 20150407 , phpmyadmin vVersion information: 4.5.4.1deb2ubuntu2ersion . Then imported existing db — sunshine, Sep 12 '17 at 11:30
You'll need to look at what mysql version you are running, 5.6 and 5.7 handle group bys differently. — chris85, Sep 12 '17 at 11:33
@sunshine See https://stackoverflow.com/questions/8987679/how-to-retrieve-the-current-version-of-a-mysql-database. — chris85, Sep 12 '17 at 11:43

Vijay Sharma · Accepted Answer · 2017-09-12T12:33:21.853

2

it occurs in mysql 5.7 so check this link [https://www.sitepoint.com/quick-tip-how-to-permanently-change-sql-mode-in-mysql/] and follow steps it work fine for me.

Or open file

vi /etc/mysql/my.cnf

Add these line at the bottom of the file

[mysqld]
# ... other stuff will probably be here
sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

and save then restart mysql

sudo service mysql restart

Note not for window os

edited Sep 12 '17 at 12:33

answered Sep 12 '17 at 11:39

Vijay Sharma

833
8
15

did you follow steps – Vijay Sharma Sep 12 '17 at 11:43
using which OS? – Vijay Sharma Sep 12 '17 at 11:44
@VijaySharma That page is a 404. Best to use the primary source, https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html. I also wouldn't change the mode, better to correct the query. – chris85 Sep 12 '17 at 11:45
@chris85 i changed query but its not working for me so i used that it work fine for me , Ya i know that better to correct query rather then changing mode , thanks for your suggestion . – Vijay Sharma Sep 12 '17 at 11:50
@vijaySharma, your solutions seems to be a perfect answer, i dont want to rewrite all the queries in the app, thats why – sunshine Sep 12 '17 at 11:53
its ubuntu server – sunshine Sep 12 '17 at 11:53
can i achieve this with phpmyadmin? – sunshine Sep 12 '17 at 11:53
that i don't know – Vijay Sharma Sep 12 '17 at 11:54
did you follow step that link i sent you? – Vijay Sharma Sep 12 '17 at 11:54
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/154236/discussion-between-vijay-sharma-and-sunshine). – Vijay Sharma Sep 12 '17 at 11:55
Please come on chat , have been waiting – sunshine Sep 12 '17 at 12:16

score 1 · Answer 2 · answered Dec 19 '18 at 07:07

1

This worked for me:

Open http://localhost/phpmyadmin/ then click SQL and run this query :

SET sql_mode = '';
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

answered Dec 19 '18 at 07:07

Shoukat Mirza

800
9
19

That does the trick for me with laragon mysql 5.7 – ABTanjir May 19 '21 at 13:01

Dom DaFonte · Answer 3 · 2017-09-12T11:57:41.560

In reading your SQL, you are asking to return every column (i.e. *), as well as the id a second time (o.id is already retrieved by *), then you are querying for the aggregate function of GROUP_CONCAT(pia.applicantid). However, your group by Clause only requests to aggregate by (group by) o.id.

SQL doesn't like this because it doesn't know what to do with the other fields in your dataset that you are querying for using *.

I suspect you want to see every applicantid related to o.id in an aggregate fashion. If so, this is your query and it will work.

SELECT o.id as id, GROUP_CONCAT(pia.applicantid) as applicants 
        FROM `".$this->myTables['opportunity']."` as o 
        LEFT OUTER JOIN pr_internal_applicant as pia 
        ON o.id = pia.positionid 
        WHERE o.approval_status='Approved' 
        and DATE(o.deadline) > DATE(NOW()) 
        GROUP BY o.id"

If you want to group by other fields, then just add them specifically in your select as well as your group by. All non-aggregate fields need to be in both the select and the group by. This is part of the ANSI Standard and should be true for all SQL flavors. I believe mysql 5.7 and up is more on this than the prior versions, but I'd follow this as a rule.

Here's some supplemental reading regarding the topic. Hope this helps. MySQL 5.7 Documentation regarding Group by

A Database Error Occurred Error Number: 1055

3 Answers3