0018341: MySQL 5.7 changes the default sql mode

(0058248)
michiel (manager)
15-09-16 22:00

Any idea which ones? Seems related to 0018295

(0058249)
duncanc (developer)
15-09-16 22:16

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.

(0058250)
michiel (manager)
16-09-16 08:22

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

(0058254)
duncanc (developer)
16-09-16 22:37

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.

(0058255)
samtuke (administrator)
17-09-16 11:39

PR ready for merging

(0058257)
michiel (manager)
17-09-16 17:04

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

(0058259)
duncanc (developer)
17-09-16 20:02
edited on: 17-09-16 20:03

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.

(0058290)
duncanc (developer)
23-09-16 11:29

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

(0058291)
michiel (manager)
23-09-16 13:30

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.

(0058296)
duncanc (developer)
25-09-16 11:10

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

(0058297)
michiel (manager)
25-09-16 18:37

nice one!

Notes
(0058248) michiel (manager) 15-09-16 22:00	Any idea which ones? Seems related to 0018295

(0058249) duncanc (developer) 15-09-16 22:16	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.

(0058250) michiel (manager) 16-09-16 08:22	brilliant, that sounds great. Looking forward to the PR.

(0058254) duncanc (developer) 16-09-16 22:37	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.

(0058255) samtuke (administrator) 17-09-16 11:39	PR ready for merging

(0058257) michiel (manager) 17-09-16 17:04	PR Merged. But sounds like it's not all resolved yet? It needs a fix on the user clicks page as well.

(0058259) duncanc (developer) 17-09-16 20:02 edited on: 17-09-16 20:03	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.

(0058290) duncanc (developer) 23-09-16 11:29	What do you want to do about the Message column on the User Clicks page? Leave it as it is?

(0058291) michiel (manager) 23-09-16 13:30	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.

(0058296) duncanc (developer) 25-09-16 11:10	See PR https://github.com/phpList/phplist3/pull/86 [^] It lists all of the message ids, with each a link to the message.

(0058297) michiel (manager) 25-09-16 18:37	nice one!