Postal: messages page 500, query error

[ropic]

I get the following output log:

[31999] [2018-08-27T13:51:48.784] INFO -- : Started GET "/org/servidorcorreo/servers/servidorcorreo/messages/outgoing" for 189.132.22.218 at 2018-08-27 13:51:48 -0500 [31999] [2018-08-27T13:51:48.786] INFO -- : Processing by MessagesController#outgoing as HTML [31999] [2018-08-27T13:51:48.786] INFO -- : Parameters: {"org_permalink"=>"servidorcorreo", "server_id"=>"servidorcorreo"} [31999] [2018-08-27T13:51:48.815] INFO -- : Completed 500 Internal Server Error in 29ms (ActiveRecord: 8.2ms) [31999] [2018-08-27T13:51:48.816] FATAL -- : [31999] [2018-08-27T13:51:48.816] FATAL -- : Mysql2::Error (Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause): [31999] [2018-08-27T13:51:48.816] FATAL -- : [31999] [2018-08-27T13:51:48.816] FATAL -- : lib/postal/message_db/database.rb:319:inquery_on_connection'
lib/postal/message_db/database.rb:311:in block in query' lib/postal/message_db/mysql.rb:20:inclient'
lib/postal/message_db/database.rb:342:in with_mysql' lib/postal/message_db/database.rb:310:inquery'
lib/postal/message_db/database.rb:167:in select' lib/postal/message_db/database.rb:186:inselect_with_pagination'
lib/postal/message_db/message.rb:30:in find_with_pagination' lib/postal/message_db/database.rb:47:inmessages_with_pagination'
app/controllers/messages_controller.rb:204:in get_messages' app/controllers/messages_controller.rb:55:inoutgoing'`

[willpower232]

Are you using MariaDB from the typical Postal install or have you pointed it at a MySQL server instead?

[ropic]

Server version: 10.1.12-MariaDB - MariaDB Server
Thank you

[willpower232]

Okay, does this happen on all the messages page for all your servers?

[ropic]

Only on Messages menu, but now it seems working we made an update on /etc/my.cnf.d removing ONLY_FULL_GROUP_BY on sql_mode

[willpower232]

Ah yes thats why I asked about MySQL, I was aware of this being an issue on more recent versions of MySQL, didn't realise it was a MariaDB thing too.

My MariaDB server version is 10.1.35-MariaDB-1~xenial so I assume someone set that option for you manually when setting up the server?

[ropic]

Sorry I don’t remember setting this value manually, I used rpm packages, centos 7

Only for extra info to other users I’m using plesk and the ruby extension. The main problem for me was setup the service because the enviroment variables.

Thank you for your help, great project !!

[willpower232]

Ah okay thanks for that information. Centos isn't currently supported but this query issue will no doubt raise its head in the future.

[catphish]

I guess we'll have to change this query eventually :(

[willpower232]

An alternative is to modify the problematic parameter on the fly, a bit of a cludge and I'm not sure how it would fit into how the current query is run but it works in a tight spot.

Running this immediately before running the query should do the trick. Its helped me with DISTINCT based queries recently which are also affected by ONLY_FULL_GROUP_BY.

SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));