MySQL error when ordering users by role in users admin page table

[ocorreiododiogo]

Short description of the issue

MySQL error when ordering users by role in users admin page table

Expected behavior

Rows order by role (thinking of it, there are many roles, so it's not that easy. Still, we shouldn't get an error)

Actual behavior

I get this error:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column '{removed}._sort_page_roles_name.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Steps to reproduce the issue

1. Go to Access > Users
2. Click the title of the "Roles" column
3. The page will be messed up, even if you leave and return again. Logout and login to get it back.

Setup/Environment

ProcessWire 3.0.35
PHP: 7.0.11
Apache: 2.4.18 (Unix) OpenSSL/1.0.2g
MySQL: 5.7.15-0ubuntu0.16.04.1-log

[adrianbj]

Weird I am not seeing any issues here with a very similar server setup.

Any chance you can disable that only_full_group_by mode to see if it helps.
http://stackoverflow.com/questions/23921117/disable-only-full-group-by

Server Details

ProcessWire: 3.0.35
PHP: 7.1.0RC2
Apache: 2.4.23 (Unix)
MySQL: 5.7.15

Server Settings

allow_url_fopen: 1
max_execution_time: 3000 (changeable)
max_input_nesting_level: 64
max_input_time: 60
max_input_vars: 10000
memory_limit: 512M
post_max_size: 100M
upload_max_filesize: 100M
xdebug:
xdebug.max_nesting_level:

mod_rewrite: 1
mod_security: *confirmed off

GD: bundled (2.1.0 compatible)
GIF: 1
JPG: 1
PNG: 1

EXIF Support: 1
FreeType: 1
Imagick Extension: 1

Module Details

AdminRestrictBranch: 0.3.9
AllInOneMinify: 3.2.3
BatchChildEditor: 1.3.8
ClearCacheAdmin: 0.0.5
DescriptionNoteVariables: 0.0.3
EmailNewUser: 0.2.9
FieldtypeImageMarker: 0.1.0
FieldtypeMapMarker: 2.0.9
FieldtypeMatrix: 1.0.4
FieldtypeMultiplier: 0.0.9
FieldtypeRuntimeMarkup: 0.0.2
FieldtypeTable: 0.1.4
FileValidatorSvgSanitizer: 0.0.1
FormSaveReminder: 1.0.6
HelperFieldLinks: 1.0.8
ImageExtra: 1.0.1
ImageRasterizer: 0.2.2
InputfieldImageMarker: 0.1.0
InputfieldMapMarker: 2.0.9
InputfieldMatrix: 1.0.4
InputfieldMultiplier: 0.0.9
InputfieldRuntimeMarkup: 0.0.2
InputfieldTable: 0.1.4
MarkupSitemapXML: 1.1.0
PageEditSoftLock: 1.0.1
PageProtector: 0.1.5
PageRenameOptions: 0.3.2
PasswordForceChange: 0.1.7
Process404Search: 0.1.9
ProcessChangelog: 1.2.15
ProcessChangelogHooks: 1.1.2
ProcessChildrenCsvExport: 1.3.8
ProcessCustomUploadNames: 0.6.0
ProcessHannaCode: 0.2.0
ProcessLoginHistory: 1.2.8
ProcessLoginHistoryHooks: 1.1.2
ProcessMigrator: 0.6.8
ProcessPDFImageCreator: 1.0.3
ProcessPageEditSoftLock: 1.0.1
ProcessPageFieldSelectCreator: 0.2.6
ProcessPageListerPro: 1.1.0
ProcessTableCsvExport: 0.4.3
ProcessWireUpgrade: 0.0.7
ProcessWireUpgradeCheck: 0.0.7
RestrictTabView: 0.0.8
TableCsvImportExport: 0.4.3
TextformatterHannaCode: 0.2.0
TracyDebugger: 3.3.5

[ryancramerdesign]

I also can't duplicate this issue. But it looks like MySQL 5.7 added that new option to the SQL mode, and then decided to make it one of the defaults, basically breaking backwards compatibility. I don't know why they take that approach. @ocorreiododiogo Can you try adding this to your /site/ready.php file?

$database->exec("SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))");

[ryancramerdesign]

@ocorreiododiogo I've just pushed an update to the dev branch that I think may fix this. When you get a chance could you try it out? Thanks.

[ocorreiododiogo]

Hey guys, sorry, couldn't do this before. I will test the dev.

[ocorreiododiogo]

Yes, it's solved on the dev :)
Nicely done Ryan!

closed the issue