search

mailwatch / MailWatch

MailWatch for MailScanner is a web-based front-end to MailScanner
http://mailwatch.org/
GNU General Public License v2.0
115 stars 66 forks source link

When using sql_mode ONLY_FULL_GROUP_BY the Total Mail By Date report doesn't work #1253

Closed th3penguinwhisperer closed 2 years ago

th3penguinwhisperer commented 2 years ago

When having ONLY_FULL_GROUP_BY in your sql_mode in MySQL the report is not generated. (not the graph and not the table). I see this error in my php error logging:

Uncaught mysqli_sql_exception: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'mailscanner.mtalog.timestamp' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I'm aware you can just change your sql_mode but a sql_mode can be set for a reason.

With these 2 simple changes the report works:

  1. Add mailscanner.maillog.date to the GROUP BY of the first query

https://github.com/mailwatch/MailWatch/blob/c3672e61b18c86ff41535274dd775bc44dd14a02/mailscanner/rep_total_mail_by_date.php#L113

  1. Add mailscanner.mtalog.timestamp to the GROUP BY clause of the query below it.

Output looks exactly the same as if the mode was changed.

th3penguinwhisperer commented 2 years ago

My bad :( I realized I have it installed through GIT and did an update to the latest version... guess what: it's already resolved.