Author Topic: Can't list user accounts after MySQL update  (Read 1441 times)

0 Members and 1 Guest are viewing this topic.

Offline
*
Can't list user accounts after MySQL update
« on: July 20, 2017, 03:06:26 PM »
Hi,

The problem started after upgrading from MySQL to version 5.7.18.

The error below occurs when accessing User Accounts > List Account.
Quote
DataTables warning: table id=userTable - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1


Offline
*
Re: Can't list user accounts after MySQL update
« Reply #1 on: July 20, 2017, 06:20:25 PM »
Found the problem following this https://datatables.net/manual/tech-notes/1

The response was:
Quote
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.

Offline
*
Re: Can't list user accounts after MySQL update
« Reply #2 on: June 24, 2018, 07:12:45 AM »
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


Offline
*
Re: Can't list user accounts after MySQL update
« Reply #3 on: August 17, 2018, 04:05:45 AM »
Same thing again on a third install, this saved me again, Re-boot afterwards

Offline
*
Re: Can't list user accounts after MySQL update
« Reply #4 on: Today at 12:43:58 PM »
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

Offline
*
Re: Can't list user accounts after MySQL update
« Reply #5 on: Today at 01:17:40 PM »
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