Query working on localhost but not on live server

Question

My database query works fine on localhost but showing error on a live server. Same code i'm using in another server and it works just fine. I'm using Mysql (localhost version 5.5.54-0ubuntu0.14.04.1) and live server (5.7.10-0ubuntu0.16.04.1).

This is the error i'm getting:

A Database Error Occurred

Error Number: 1055

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

The code:

SELECT u.id, u.user, u.email, u.full_name, u.stat, 
GROUP_CONCAT(distinct(ar.role)) as roles, GROUP_CONCAT(distinct(g.title)) as group_names,
count(lug.user_id) as login_times, MAX(lug.created_at) as last_login, lut.log_text, 
lut.created_at as log_text_date, mobile FROM users as u LEFT JOIN 
group_users as gu ON gu.user_id = u.id LEFT JOIN groups as g ON g.id = gu.group_id 
LEFT JOIN user_roles as ur ON ur.user_id = u.id LEFT JOIN access_roles as ar ON 
ar.id = ur.role_id LEFT JOIN log_user_login as lug ON lug.user_id = u.id LEFT JOIN 
log_user_texts as lut ON lut.user_id = u.id AND lut.id = (SELECT l.id FROM log_user_texts l 
WHERE u.id = l.user_id ORDER BY l.id DESC LIMIT 1) WHERE u.id > 1 GROUP BY u.id

Filename: modules/admin/models/Users_model.php

Line Number: 84

This is my Code:

<?php
$query = $this->db->select('u.id, u.user, u.email, u.full_name, u.stat, GROUP_CONCAT(distinct(ar.role)) as roles, '
                        . 'GROUP_CONCAT(distinct(g.title)) as group_names, count(lug.user_id) as login_times, MAX(lug.created_at) as last_login, '
                        . 'lut.log_text, lut.created_at as log_text_date, mobile')
                ->join(static::T_GROUP_USERS . ' as gu', 'gu.user_id = u.id', 'left')
                ->join(static::T_GROUPS . ' as g', 'g.id = gu.group_id', 'left')
                ->join(static::T_USER_ROLES . ' as ur', 'ur.user_id = u.id', 'left')
                ->join(static::T_ROLES . ' as ar', 'ar.id = ur.role_id', 'left')
                ->join(static::T_LOG_USER_LOGIN . ' as lug', 'lug.user_id = u.id', 'left')
                ->join(static::T_LOG_USER_TEXTS . ' as lut', 'lut.user_id = u.id AND '
                        . 'lut.id = (SELECT l.id FROM log_user_texts l WHERE u.id = l.user_id ORDER BY l.id DESC LIMIT 1)', 'LEFT')
                ->where('u.id > 1')->group_by('u.id')->get(static::T_USERS . ' as u');

I will appreciate any kind of help. Thanks

Try to join `log_user_texts` as first table. That might help the engine to determine functional dependency. — Paul Spiegel, Sep 24 '17 at 20:04

score 1 · Answer 1 · answered Sep 24 '17 at 19:28

1

I think that this is the key to your problem this is incompatible with sql_mode=only_full_group_by.

Take a look at this Disable ONLY_FULL_GROUP_BY.

It could be that your database on live server has this option enabled, but on your localhost is probably disabled.

answered Sep 24 '17 at 19:28

Branimir Đurek

632
5
13

1

It's better to fix the query rather than switching off the ONLY_FULL_GROUP_BY mode. – Paul Spiegel Sep 24 '17 at 19:32
I totally agree with you but this points out why the query doesn't run on same databases. – Branimir Đurek Sep 24 '17 at 19:44

score 1 · Answer 2 · answered Sep 26 '17 at 05:27

A quick solution could be using the following statement before your select query to set sql_mode to none.

$this->db->query("SET sql_mode = ''");

$query = $this->db->select(...
...

Obviously, you should update the query by adjusting the joins in a convinient time.

Query working on localhost but not on live server

2 Answers2