sql_mode=only_full_group_by is incompatible in PHP mysql query with GROUP BY codeigniter

#1055 - Expression #24 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'customers.first_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Add $this->db->query("SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));") line above your query.

See Example :

$this->db->query("SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));");
$this->db->select('*, customers.first_name, customers.last_name, customers.id');
$this->db->select("(SELECT count(video_id) FROM customer_videos WHERE c.customer_id = customer_videos.customer_id AND (created_at >= '$from' OR created_at <= '$todate')) as seen");
$this->db->join('customers', 'customers.id = c.customer_id', 'left');
$this->db->group_by('c.customer_id');
$this->db->from('customer_videos as c');
$query = $this->db->get();
echo "<pre/>"; print_r($query->result());exit;
return $query->result_array();

SELECT list is not in GROUP BY clause and contains nonaggregated column … incompatible with sql_mode=only_full_group_by

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

will be simply solved by changing the sql mode in MySQL by this command,

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

OR

set @@global.show_compatibility_56=ON;

OR from CMD

mysql -u root -p mg_cstore < db.sql

This is too works me.. I used this becz in my project there are many Queries like this so just change this sql mode only_full_group_by


For permenant solution in ubuntu 16 and 18

Step1) find and modify the config file my.cnf. Usually it’s in /etc/my.cnf or /etc/mysql/my.cnf.

Step 2) Add following code end of the file
[mysqld]sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION