On the 15 of May there was a few updated that forced my Windows server 2008 to auto reboot, after that my piwik stats have not been working.
In the Visits over time window I get the following Error:
SQLSTATE[42000]: Syntax error or access violation: 1055 ‘piwik.log_action.name’ isn’t in GROUP BY
In the Visitors in real time window I get:
SQLSTATE[42000]: Syntax error or access violation: 1055 ‘sub.idsite’ isn’t in GROUP BY
I’m using 1.11.1
The Visits over time works for dates prior to May 15 but the real time tracking is still broken.
Things I have tested is:
reinstalled 1.11.1 but left the data intact, same errors
created an empty, ran the installation again with a new db name, and now the visits over time works but still same error on the real time window.
Installed 1.12-b20 but it made no difference.
So the Visits over time error is probably due to corrupt data even tho I have used Repair on all the tables.
I have no clue on why the Real Time tracking doesn’t work.
I’m using 5.6.11-log - MySQL Community Server (GPL) so I’m on the latest.
Just did a google search on the error it’s self and left out piwik and I came across this bug report: http://bugs.mysql.com/bug.php?id=69310
[23 May 11:47] Shlomi Noach
Description:
A valid GROUP BY query is rejected with:
ERROR 1055 (42000): ‘###########’ isn’t in GROUP BY
while in ‘ONLY_FULL_GROUP_BY’ sql_mode.
It looks pretty similar to what I have… Could it be because they are using AS in the GROUP BY statements?
I’m going to check this a bit more and experiment a little
btw, where in the database can I find the current tracking data, all I can find is the archive and it ends at May 15… right where the trouble begins…
I turns out that I had ONLY_FULL_GROUP_BY in the sql_mode variable in my.ini file.
It worked after I removed it and restarted the server.
Odd thing tho that I changed this before the server reboot…
I didn’t check if all the changes I did to MySQL worked and when I found the errors, after the server reboot, I blame it for the errors…