View Issue Details

Jump to Notes
IDProjectCategoryView StatusDate SubmittedLast Update
0018341phpList 3 applicationGeneralpublic15-09-16 21:2913-02-19 12:28
Reporterduncanc Assigned To 
PrioritynormalSeverityminorReproducibilitysometimes
Status resolvedResolutionfixed 
Product Version3.2.5 
Target Version3.2.6Fixed in Version3.2.6 
Summary0018341: MySQL 5.7 changes the default sql mode
DescriptionMySQL release 5.7 has a change to the default sql mode that causes some queries to fail. See http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html the section SQL mode changes.

Some queries that use GROUP By now fail because of the mode ONLY_FULL_GROUP_BY. The problem is that some queries use non-aggregated columns (which is a bug), which are now rejected.
TagsNo tags attached.

Relationships

Relationship GraphDependency Graph
related to 0018295 closedsamtuke SQL GROUP BY Errors 

Activities

michiel

15-09-16 22:00

administrator   ~0058248

Any idea which ones? Seems related to 0018295

duncanc

15-09-16 22:16

updater   ~0058249

Two pages, uclicks.php and userclicks.php have queries using GROUP BY that need changing (including the related issue). I've been looking at those today and should be able to make a pull request tomorrow.

michiel

16-09-16 08:22

administrator   ~0058250

brilliant, that sounds great. Looking forward to the PR.

duncanc

16-09-16 18:25

updater  

Screenshot from 2016-09-16 18-24-32.png (14,384 bytes)   
Screenshot from 2016-09-16 18-24-32.png (14,384 bytes)   

duncanc

16-09-16 18:39

updater  

Screenshot from 2016-09-16 18-35-44.png (18,950 bytes)   
Screenshot from 2016-09-16 18-35-44.png (18,950 bytes)   

duncanc

16-09-16 22:37

updater   ~0058254

See https://github.com/phpList/phplist3/pull/85

There is an outstanding problem with the user clicks page 'Clicks of a subscriber'. The query for that now fails because it uses a non-aggregated field, message id. This highlights a problem with the current code using the non-strict group by.

A url can be used in more than one message. The results listing has a message column but, as a url can be used in more than one message, mysql will have returned an arbitrary message id value when there the url has been used in several messages.

samtuke

17-09-16 11:39

manager   ~0058255

PR ready for merging

michiel

17-09-16 17:04

administrator   ~0058257

PR Merged. But sounds like it's not all resolved yet? It needs a fix on the user clicks page as well.

duncanc

17-09-16 20:02

updater   ~0058259

Last edited: 17-09-16 20:03

View 2 revisions

 If you go to this page in your phplist with a user id - admin/?page=userclicks&userid=9999 to see what I mean.

The totals are correct but the Message column in the results is misleading. When a url has been used in only one message then it is correct, but otherwise it is just an arbitrary value from the several possible values selected by mysql.

duncanc

23-09-16 11:29

updater   ~0058290

What do you want to do about the Message column on the User Clicks page? Leave it as it is?

michiel

23-09-16 13:30

administrator   ~0058291

I guess best would be if it's a list of messageIDs, but that may be tricky to squeeze in a single SQL query. Maybe drop the column for now.

duncanc

25-09-16 11:10

updater   ~0058296

See PR https://github.com/phpList/phplist3/pull/86
It lists all of the message ids, with each a link to the message.

michiel

25-09-16 18:37

administrator   ~0058297

nice one!