Assets errors with MySQL 5.7.X

Question

I've started having issues with old EE sites on my development machine now that I'm running newer versions of PHP and MySQL. I've been able to sort most of these out except for the following MySQL errors from the Assets module.

This error displays when trying to view an Assets field in the admin:

 A Database Error Occurred
 Error Number: 3065

 Expression #1 of ORDER BY clause is not in SELECT list, references column 'mysite.ae.sort_order' which is not in SELECT list; this is incompatible with DISTINCT

 SELECT DISTINCT a.source_type, a.folder_id, a.file_name, a.file_id, af.source_id, af.filedir_id FROM exp_assets_files AS a INNER JOIN exp_assets_selections AS ae ON ae.file_id = a.file_id INNER JOIN exp_assets_folders AS af ON af.folder_id = a.folder_id WHERE ae.col_id = '1' AND ae.row_id = '115' AND ae.entry_id = '36' AND ae.field_id = '2' ORDER BY ae.sort_order

 Filename: third_party/assets/ft.assets.php

 Line Number: 682

This error displays on the frontend when using the asset fieldtype pair tags.

Error Number: 3065

Expression #1 of ORDER BY clause is not in SELECT list, references column 'mysite.ae.sort_order' which is not in SELECT list; this is incompatible with DISTINCT

SELECT DISTINCT a.* FROM exp_assets_files AS a INNER JOIN exp_assets_folders AS af ON af.folder_id = a.folder_id INNER JOIN exp_assets_selections AS ae ON ae.file_id = a.file_id WHERE ae.entry_id = "28" AND ae.field_id = "2" AND ae.col_id = "1" AND ae.row_id = "107" AND ae.is_draft = 0 ORDER BY ae.sort_order

Filename: third_party/assets/ft.assets.php

Line Number: 1538

I'm using:

• EE 2.11.2
• Assets 2.6
• MySQL 5.7.9
• PHP 5.5.30

It looks like EE had some compatibility issues with MySQL 5.7 and I wonder if this is related. https://support.ellislab.com/bugs/detail/21444/a-database-error-occurred-this-is-incompatible-with-distinct-mysql-5.7.10

Any help would be really appreciated.

Answer 1

The problem is that many developers don't know how to write compliant SQL statements - they're software developers who find themselves also filling the role of database developers. This problem crops up in many addons and was recently fixed in EE core code.

For Assets v2.6 Change line 615 of ft.assets.php

FROM:

$sql = "SELECT DISTINCT a.source_type, a.folder_id, a.file_name, a.file_id, af.source_id, af.filedir_id

TO:

$sql = "SELECT DISTINCT  a.source_type, a.folder_id, a.file_name, a.file_id, af.source_id, af.filedir_id, ae.sort_order

And line 1505

FROM:

$sql = 'SELECT DISTINCT a.* FROM exp_assets_files AS a

TO:

$sql = 'SELECT DISTINCT a.*, ae.sort_order FROM exp_assets_files AS a