What is the ‘problem’, well its mysql.
In short its about this
Our and other mysql users use the best (valid) sql modes to get the best query’s etc.
One sql mode is ONLY_FULL_GROUP_BY
You need to add all columns you select/use in the GROUP BY
SELECT name, address, MAX(age) FROM t GROUP BY name;
ERROR 1055 (42000): ‘t.address’ isn’t in GROUP BY
Maybe its possible to make this an option or something, the other option is to add all columns to your group by query’s confused smiley
We would like to do this. Maybe the best would be to enable this option on our continuous integration server, so that we always test with this option on? if you can create a ticket at dev.piwik.org that would be appreciated!
When you want to fix it using valid SQL than you have to rewrite almost every sql command that uses group by.
In return you will get the safest mysql query’s and more important no weird output’s results.
But you can also choose to quick fix it by overruling the sql_mode by adding some lines at the db connection creation.
But when you do, you need to remember that mysql don’t give you 100% group by results.
When I search on this mysql with group by result problem, I get some more info
With Piwik 2.13.1. (June 2015) the proper line to insert is as follows, now everything works fine, no problems anymore.
file: core/Db/Adapter/Pdo/Mysql.php, somewhere about line 64 insert:
$this->_connection->exec( “SET SESSION sql_mode=’’” ); //2015-06-05 this line fixes the bug “SQLSTATE[42000]: Syntax error or access violation: 1055 ‘…’ isn’t in GROUP BY”