No stats generated in table counter_stats

[Martin Henne]

I set up a fresh install on Ubuntu 16.04. LTS with LAMP. No stats are generated in the table counter_stats. Switched to DEBUG mode. Started

#!
/usr/bin/php /usr/local/libexec/piler/generate_stats.php --webui /var/www/piler

and pasted the sql line

#!
SELECT `sent`-(`sent`%86400) as `day`,`to`,count(*) as `count`,sum(`size`) as `size` FROM v_messages WHERE `sent` >= 1481414400 AND `sent` < 1481500800 GROUP BY FROM_UNIXTIME(`day`, "%Y.%m.%d."), `to`;

directly into sql. Result:

#!
SQL Fehler (1055): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'v_messages.sent' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Adding SET sql_mode = ''; in the sqleditor fixed it.

Is it a bug or do i have to change something in the standard mysql settings? Perhapes sql_mode "only_full_group_by" is set as standard in mysql 5.7.

[Martin Henne]

adding the following to mysql.cnf fixed it.

#!
[mysqld]
sql-mode    = 

[Janos SUTO]

It seems that mysql 5.7 has brought some changes making previously working sql commands fail. I'll recreate my test environment with stock mysql 5.7, and figure out the solution. It's very likely that the sql statement must be fixed. I'll keep you posted.

[Janos SUTO]

Try the following. Edit model/accounting/accounting.php, and fix the $tousers and $from users queries around 43-44th lines. Change sent-(sent%86400) to ANY_VALUE(sent-(sent%86400)), and from to ANY_VALUE(from). It should solve this problem. Let me know how it goes.

[Martin Henne]

the statistics section still displays an empty graphic.

[Janos SUTO]

OK, we are one step closer. I'll check the issue.

[Janos SUTO]

Do you have your domains listed in the domain table? Check the administration / domain menu.

[Martin Henne]

This link index.php?route=accounting/accounting&view=email shows me all domains. Graphics in this link index.php?route=stat/stat&timespan=daily not working.

Perhapes it would be easier to implement googles charts: https://developers.google.com/chart/interactive/docs/gallery

[Janos SUTO]

I'm confused whether the accounting feature works or not? Also, is there any clue in the logs (webserver error log, php log, mail log, etc) regarding the stat image?

[Martin Henne]

Accounting display script is working. I am not sure if generate_stats.php is working! I cleared the counter_stats table and ran the script: The table wasn't filled with data at all!

[Janos SUTO]

generate_stats.php fills counter_stats table with data. So run it again, and also clarify whether the stat image is displayed or not? It would be good to see a screenshot.

[Janos SUTO]

No news is good news.