MYSQL ONLY_FULL_GROUP_BY valid or catch


#1

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

You do this to tackle some mysql problems.
http://dev.mysql.com/doc/refman/5.0/en/sql-mode.html#sqlmode_only_full_group_by

But piwik don’t use this way so upgrading/installing failes.
To fix this we need manual add a sql command in

public function getConnection()
{
if ($this->_connection) {
return $this->_connection;
}

    $this->_connect();
    $this->_connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

SQL COMMAND -> “SET sql_mode=’’;”

    return $this->_connection;
}

Maybe its possible to make this an option or something, the other option is to add all columns to your group by query’s :S


(Matthieu Aubry) #2

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!


#3

H!

A while ago I created the ticket, and its now here Piwik Issue #5124 - Piwik compatible with MySQL mode: ONLY_FULL_GROUP_BY
And I understand that you can’t fix all the bugs in a short time :slight_smile: but i’m affraid that you don’t see the huge impact it can have.

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

http://www.tocker.ca/2014/01/24/proposal-to-enable-sql-mode-only-full-group-by-by-default.html

I think many more people will going to see this problem in the future.


(Matthieu Aubry) #4

Thanks for your note. I added a comment in: Piwik compatible with MySQL mode: ONLY_FULL_GROUP_BY · Issue #5124 · matomo-org/matomo · GitHub

Cheers!


#5

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=’’” );

So the code should look like this:


public function getConnection()
{
if ($this->_connection) {
return $this->_connection;
}

$this->_connect();
$this->_connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

$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”

return $this->_connection;
}

best