GUI: User list not shown - Bad SQL mode

[Jose M. Albarran]

Hi

I'm testing 1.2.0-master build 945 on Ubuntu 16.04, with mysql Ver 14.14 Distrib 5.7.13, for Linux (x86_64) using EditLine wrapper.

In administration mode, I add an user. But in the user list, I see "Existing user: Not found". Internally the user exists because I cannot create twice.

I have added the trace option as commented by you in Issue#561.

After capture the SQL, I see the following SELECT: SQL=SELECT user.uid, isadmin, username, realname, domain, email FROM user,email WHERE user.uid=email.uid group by user.uid ORDER BY username ASC LIMIT 0, 20 Aug 18 08:39:08 irulan piler-webui[5916]: SQL=SELECT COUNT(*) AS num, uid FROM email group by uid Aug 18 08:39:08 irulan piler-webui[5916]: SQL=SELECT * FROM customer_settings WHERE domain=(SELECT mapped FROM domain WHERE domain=?)

If I test this SELECT in my mysql environment, I see the following error: mysql> SELECT user.uid, isadmin, username, realname, domain, email FROM user,email WHERE user.uid=email.uid group by user.uid ORDER BY username ASC LIMIT 0, 20; ERROR 1055 (42000): Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'piler.email.email' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

[Janos SUTO]

It's a feature introduced in mysql 5.7. OK, give me some time until make such an installation, and try it for myself.

[Janos SUTO]

Try the following: edit model/user/user.php, and locate the problematic query:

$query = $this->db->query("SELECT " . TABLE_USER . ".uid, isadmin, username, realname, domain, email FROM " . TABLE_USER . "," . TABLE_EMAIL . " $where_cond group by " . TABLE_USER . ".uid $_order $limit", $q);

and fix it:

$query = $this->db->query("SELECT " . TABLE_USER . ".uid, isadmin, username, realname, domain, email FROM " . TABLE_USER . "," . TABLE_EMAIL . " $where_cond group by " . TABLE_USER . ".uid, " . TABLE_EMAIL . ".email $_order $limit", $q);

[Jose M. Albarran]

Hi,

The changed SQL avoided the crash, but has a collateral effect. I have an user with 3 different e-mail addresses, and now appears three times in the list (one per address). I imagine you will like to use some MIN or FIRST approach to this.

In any case, thanks

[Jose M. Albarran]

And, for a temporary workaround, there is another way:

Check this: http://dba.stackexchange.com/questions/31447/how-can-i-set-a-default-session-sql-mode-for-a-given-user

You can create a piler.cnf in /etc/mysql/conf.d/piler.cnf with [mysqld] SET GLOBAL init_connect = "SET @@sql_mode = CASE CURRENT_USER() WHEN 'piler@%' THEN 'TRADITIONAL' ELSE @@sql_mode END;";

(NOT TESTED YET)

[Janos SUTO]

Did the previous fix work? Anyway the fixed sql query might be the following:

create index email_idx2 on email(uid);
select distinct(email.uid) as u, isadmin, username, realname, domain, (select email from email where uid=u LIMIT 1) from email, user where email.uid=user.uid ORDER BY username ASC LIMIT 0, 20;

THe php stuff may have be to fixed to match it.

[Janos SUTO]

OK, got it: change the original query and fix it like this:

SELECT user.uid, isadmin, username, realname, domain, ANY_VALUE(email) as email FROM user,email WHERE user.uid=email.uid group by user.uid ORDER BY username ASC LIMIT 0, 20;

[Jose M. Albarran]

Ok, I have changed to

$query = $this->db->query("SELECT " . TABLE_USER . ".uid, isadmin, username, realname, domain, ANY_VALUE(email) as email FROM " . TABLE_USER . "," . TABLE_EMAIL . " WHERE " . TABLE_USER . ".uid=" . TABLE_EMAIL . ".uid group by " . TABLE_USER . ".uid ORDER BY username ASC LIMIT 0, 20", $q);

and it seems to work NOTE: I would suggest to use MIN instead of ANY_VALUE, for 2 reasons: - It's SQL standard (future migrations) - It has some logic (minimum alfabetical value)

[Janos SUTO]

OK, it makes sense to use MIN(). I'll update the master branch soon to reflect the fix.

[Jose M. Albarran]

Hi, Janos

I have just updated to last night master. This fails again. You have forgotten the "AS email" in the select.

Current: $query = $this->db->query("SELECT " . TABLE_USER . ".uid, isadmin, username, realname, domain, MIN(email) FROM " . TABLE_USER . "," . TABLE_EMAIL . " $where_cond GROUP BY " . TABLE_USER . ".uid $_order $limit", $q);

Right one: $query = $this->db->query("SELECT " . TABLE_USER . ".uid, isadmin, username, realname, domain, MIN(email) AS email FROM " . TABLE_USER . "," . TABLE_EMAIL . " $where_cond GROUP BY " . TABLE_USER . ".uid $_order $limit", $q);

[Janos SUTO]

Thanks for noticing, I'll fix it soon.

[Janos SUTO]

Fixing #712

→ <<cset 69a2a9e2e2bd>>

[Janos SUTO]

Just did the commit.