Fatal error - group by problem

hst

Hi, I've installed Vanilla on two separate servers with php 5 (5.2.5) and Mysql 5 (5.0.45) and I keep running into this problem: Error Message An error occurred while retrieving categories. Affected Elements CategoryManager.GetCategories(); The error occurred on or near: 'forum.b.Blocked' isn't in GROUP BY The error occurs when I try to create a new discussion and when I access or change categories. I've try to remedy the problem by adding $s->AddGroupBy('Blocked', 'b'); This helps and I'm able to access the affected pages, but then it asks for more group bys in other instances and it also crashes when depending on whether the user is logged in or not. Any ideas? Cheers

hst

Additional information, this occurs only when I'm logged in. There are no problems browsing categories when not logged in.

Dinoboff

I can reproduce the error by setting sql_mode to ONLY_FULL_GROUP_BY:
SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY'
Some queries use aggregation functions without using a group by All fields were grouped but this one. We will try to fix it ASAP.

In the meantime, You can add after line 77 of library/Framework/Framework.Class.MySQL.php: mysql_query('SET SESSION sql_mode=\'\'', $this->Connection);

It should look like: function GetConnection() { if (!$this->Connection) { if (!function_exists('mysql_connect')) $this->Context->ErrorManager->AddError($this->Context, $this->Name, 'GetConnection', 'You do not appear to have MySQL enabled for PHP.'); $this->Connection = @mysql_connect($this->Context->Configuration['DATABASE_HOST'], $this->Context->Configuration['DATABASE_USER'], $this->Context->Configuration['DATABASE_PASSWORD']); if (!$this->Connection) $this->Context->ErrorManager->AddError($this->Context, $this->Name, 'OpenConnection', 'The connection to the database failed:', $php_errormsg); if (!@mysql_select_db($this->Context->Configuration['DATABASE_NAME'], $this->Connection)) $this->Context->ErrorManager->AddError($this->Context, $this->Name, 'OpenConnection', 'Failed to connect to the "'.$this->Context->Configuration['DATABASE_NAME'].'" database.'); // Set the character encoding if it is defined in the configuration. if ($this->Context->Configuration['DATABASE_CHARACTER_ENCODING'] != '') { mysql_query('SET NAMES "'.$this->Context->Configuration['DATABASE_CHARACTER_ENCODING'].'"', $this->Connection); } mysql_query('SET SESSION sql_mode=\'\'', $this->Connection); } return $this->Connection; }

hst

Greatness, that solved my problems!Thanks!