SQL Errors With 0.5.5


(ChabilHa) #1

I am using the latest version of Piwik 0.5.5 and get the following errors when I have the ‘week’ time span selected on the reports:

SQLSTATE[42000]: Syntax error or access violation: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),…) with no GROUP columns is illegal if there is no GROUP BY clause

Also, I see that visits are getting recorded in the database, but there the summaries are showing no visitors.

Any ideas?


(vipsoft) #2

There is a bug in some versions of mysql community edition, generating false group-by errors when sql_mode is ‘ONLY_FULL_GROUP_BY’.

Set sql_mode to ‘’ in my.cnf and restart it to see if that changes things.


(ChabilHa) #3

[quote=vipsoft @ Mar 24 2010, 01:37 PM]There is a bug in some versions of mysql community edition, generating false group-by errors when sqlmode is full-group-by.

Set sql_mode to ‘’ in my.cnf and see if that changes things.[/quote]

Unfortunately, I do not have access to the database server to make such a change. Any other ideas?


(vipsoft) #4

Can you look in my.cnf or use mysql client to find out what your default sql_mode is?

SHOW VARIABLES LIKE 'sql_mode';

(ChabilHa) #5

[quote=vipsoft @ Mar 24 2010, 02:29 PM]Can you look in my.cnf or use mysql client to find out what your default sql_mode is?

SHOW VARIABLES LIKE 'sql_mode';

[/quote]

‘sql_mode’, ‘PIPES_AS_CONCAT,ONLY_FULL_GROUP_BY,NO_UNSIGNED_SUBTRACTION,STRICT_TRANS_TABL
ES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRAD
ITIONAL,NO_AUTO_CREATE_USER’


(vipsoft) #6

Yeah, you’re going to have to contact your hosting provider / sysadmin.

Here’s the MySQL bug: http://bugs.mysql.com/bug.php?id=42567

We won’t support this in the core, but you’re welcome to hack your copy of core/Piwik.php’s createDatabaseObject(), and send “SET sql_mode=’’;” each time a connection is established, e.g., (around line 1700)

                        $db = Piwik_Db::factory($adapter, $dbInfos);
                        $db->getConnection();

                        Zend_Db_Table::setDefaultAdapter($db);
                        $db->resetConfig(); // we don't want this information to appear in the logs
                }
                Zend_Registry::set('db', $db);
                Piwik_Exec("SET sql_mode='';"); // <-- add this line; MySQL-specific
        }

(ChabilHa) #7

[quote=vipsoft @ Mar 24 2010, 02:44 PM]Yeah, you’re going to have to contact your hosting provider / sysadmin.

Here’s the MySQL bug: bugs.mysql.com/bug.php?id=42567

We won’t support this in the core, but you’re welcome to hack your copy of core/Piwik.php’s createDatabaseObject(), and send “SET sql_mode=’’;” each time a connection is established, e.g., (around line 1700)

                        $db = Piwik_Db::factory($adapter, $dbInfos);
                        $db->getConnection();

                        Zend_Db_Table::setDefaultAdapter($db);
                        $db->resetConfig(); // we don't want this information to appear in the logs
                }
                Zend_Registry::set('db', $db);
                Piwik_Exec("SET sql_mode='';"); // <-- add this line; MySQL-specific
        }

[/quote]

That seems to make the SQL errors go away, but I cannot see any data showing up in the reports, even though I can see in the piwik_log_visit table that there is data.


(vipsoft) #8

Did you get a timezone warning during installation?


(ChabilHa) #9

Yes. Almost, if not all my users will be in the same timezone, so I didn’t think this would be an issue. Any workarounds?


(vipsoft) #10

If the PHP timezone isn’t set, it’ll default to America/New_York. This will cause lag in reports since 0.5.5 still uses the MySQL server time for some queries.

We plan to address this in 0.6. In the meantime, you have to set your PHP timezone.

http://piwik.org/faq/troubleshooting/#faq_58