search

spatie / mailcoach-support

Questions and support for Mailcoach
https://mailcoach.app
31 stars 2 forks source link

CalculateStatisticsJob QueryException #176

Closed shatterproof closed 4 years ago

shatterproof commented 4 years ago

PHP: 7.4.5 Laravel: 7.10.3 Mailcoach: 2.11.4

After updating from Mailcoach 2.11.3 to 2.11.4 the CalculateStatisticsJob has been giving this exception every minute it is called:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'app.mailcoach_campaign_opens.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select count(`subscriber_id`) as aggregate from (select * from `mailcoach_campaign_opens` where `mailcoach_campaign_opens`.`campaign_id` = XX and `mailcoach_campaign_opens`.`campaign_id` is not null group by `subscriber_id`) as `aggregate_table`)

With XX representing the campaign_id of the campaign being tracked.

The jobs appear completed in Horizon but Telescope shows the exceptions after. My config is up to date so I'm not sure what is causing this.

YannikFirre commented 4 years ago

Hello, same issue.

goaround commented 4 years ago

+1

YannikFirre commented 4 years ago

FYI : Works with Laravel v7.9.2

electronick86 commented 4 years ago

https://stackoverflow.com/a/53876660

This (disable strict mode in config/database.php) solve the problem. But the query should maybe/probably be adapted.

freekmurze commented 4 years ago

Yeah, we'll adapt the query soon.

nkeena commented 4 years ago

Disabling strict mode seems to solve that problem, but now getting this error when using mailgun feedback:

Illuminate\Database\QueryExceptionSpatie\Mailcoach\Jobs\CalculateStatisticsJob
SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'id' (SQL: select count(*) as aggregate from (select * from `mailcoach_campaign_clicks` inner join `mailcoach_campaign_links` on `mailcoach_campaign_links`.`id` = `mailcoach_campaign_clicks`.`campaign_link_id` where `mailcoach_campaign_links`.`campaign_id` = 5 group by `subscriber_id`) as `aggregate_table`)
ziming commented 4 years ago

If i downgrade to 2.11.3 what will I lose?

Because this CalculateStatisticsJob is important to me.

ziming commented 4 years ago

Ouch. Fix for PrepareEmailHtmlAction breaking html (latest version) 2.11.4 changes is more important to me.

riasvdv commented 4 years ago

Should be fixed in 2.11.6!