Can't list user accounts after MySQL update

[joaolm]

Hi,

The problem started after upgrading from MySQL to version 5.7.18.

The error below occurs when accessing User Accounts > List Account.

DataTables warning: table id=userTable - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1

[joaolm]

Found the problem following this https://datatables.net/manual/tech-notes/1

The response was:

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'root_cwp.user.domain' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

After setting the sql_mode parameter in /etc/my.cnf the problem has stopped.

[blakilox]

Had this same issue in Centos 6 and Centos 7
just to clarify how to do that

Login as root and vi /etc/my.cnf
Unhash the line that looks like
# Optionally change the SQL mode.
#sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

and change it to

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

so you are Enabling that line and removing the "ONLY_FULL_GROUP_BY," part

Regards Garry, Outback Central Australia

[blakilox]

Same thing again on a third install, this saved me again, Re-boot afterwards

[blakilox]

New Install of CWP and this one is BACK, on a totally new instance and new server, same JSON error while listing users, though this time the above fix I have had success with before is not working, Pulling my hair out and thinking about doing another install, Perhaps Configure MySQL like the above BEFORE installing CWP???
Tried different browser, re-booting, I am using Centos 6 perhaps this lags behind the Centos 7 development
The User account does create, and displays in the Count, though cannot list it

Any Ideas Appreciated. I don't know where to go from here, Except for starting from Scratch

[blakilox]

CWP MySQL Database has no such table as "userTable" (it uses "user")
However I find this userTable in a search of CWP code

# find . -type f -exec grep -l 'userTable' {} \;
./cwpsrv/var/services/pma/libraries/Menu.php
./cwpsrv/var/services/pma/libraries/server_user_groups.lib.php
./cwpsrv/var/services/pma/libraries/server_privileges.lib.php

[AlexG]

Hi there,
that can be fixed by executing (on my end I've executed twice):

Code Select Expand

sh /scripts/update_cwp

[Wjatscheslaw]

The same issue, this command doesn't work  - sh /scripts/update_cwp...

[jnet]

Please some body help my site has issue this bug is making me insane
I can not delete account