Database Error with MySQL after logging in

[nickd32]

We're on the latest version of OpenVBX, and all admin users are getting an error message after logging in. We cannot proceed from here.

A Database Error Occurred

Error Number: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

SELECT COUNT(*) AS numrows FROM (messages) LEFT JOIN group_messages gm ON gm.message_id = messages.id LEFT JOIN groups g ON g.id = gm.group_id LEFT JOIN user_messages um ON um.message_id = messages.id LEFT JOIN users u ON u.id = um.user_id WHERE (um.user_id IN (1) OR gm.group_id IN (0)) AND messages.tenant_id = '1' AND archived = 0 ORDER BY messages.created DESC

[nickd32]

UPDATE and a FIX on this problem.

First, the issue appears to have stemmed from a recent upgrade we made to MySQL 5.6. In doing so, we implemented stricter controls on queries (in the mysql config). This was the "recommended configuration" from Percona.

In order to fix, we had to adjust two separate queries in OpenVBX that were a little... shall we say... sloppy.

The first (referenced in my original ticket, above) does not need the ORDER BY clause at the end. If you look at what it's doing, it's simply doing a count of the number of messages the current user has.

By removing that ORDER BY clause, the errors went away. :)

To remove it, we modified this file: ./system/database/DB_active_rec.php, Line 1074 ... and we added the following line... $this->ar_orderby = array();

... right above this query: $sql = $this->_compile_select($this->_count_string . $this->_protect_identifiers('numrows'));

...so the final function looks like this: http://pastebin.com/qmEGMW5X

[nickd32]

The second Database error was appearing when users logged in to OpenVBX that were also attached to groups. The error looked like this:

Error Number: 1055 'open_vbx.g.name' isn't in GROUP BY

SELECT g.name, status, g.id, count(m.status) as count FROM (groups g) JOIN group_messages gm ON gm.group_id = g.id JOIN messages m ON m.id = gm.message_id WHERE gm.group_id IN ('3') AND archived = 0 AND m.tenant_id = '1' GROUP BY m.status, g.id

[nickd32]

To FIX this second issue, we modified this file: /OpenVBX/models/vbx_message.php, Line 609

Originally, it looked like this: ->group_by('m.status, g.id')

We modified it to look like this: ->group_by('m.status, g.id, g.name')

[Gipetto]

Heh, yeah, "sloppy" is a nice way of putting it for some of the code in here. Can I tempt you in to opening a pull request with these fixes?