sql_mode=only_full_group_by incompatibility

[RoyCurtis]

On newer versions of MySQL (which?), PRISM throws this exception: https://gist.github.com/RoyCurtis/c28703d8329d587d1774d9b4e681cfe1

Reported via email by Crypt (Tom)

[RoyCurtis]

I seem to recall hitting this issue on our server, which I fixed by changing MySQL config IIRC. But this is not ideal; GPrism should fix this issue.

[Cryptite]

I attempted an (initially working) fix by doing this: http://stackoverflow.com/a/35729681/369878

But that has since failed.

This specific error is a server running 5.7.17-0ubuntu0.16.04.2 (Ubuntu)

[Cryptite]

From somebody who knows more about SQL than I do, if this helps at all:

When you have aggregate functions in an SQL query, those consume the rows to produce their results. AVG, and COUNT are doing that in that query
In SQL, that means you need to supply a GROUP BY clause that says you want to divide all the rows into groups that are equal in all the columns of the GROUP BY to provide absolutely equal values to the aggregate functions so they can do their job. So for example, if you have columns A,B,C, and want to SUM(C), but produce multiple rows,
if you GROUP BY A,B, then for every unique value A,B, ... SUM can add up all the int values C where A,B are the same.
In your query, I'm guessing prism_data.id is totally unique, and you're not grouping on it (which wouldn't even help if you did), prevents the aggregate functions AVG and COUNT from doing anything.

[RoyCurtis]

@Cryptite - When you said you attempted a fix, was it by doing this?

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

If so, you need to actually add this to MySQL's config file instead, to make it permanent across server restarts or MySQL updates. Try the following:

1. On the box, do sudo nano /etc/mysql/conf.d/sqlfix.cnf - This will create a new file and open it in a text editor
2. Copy and paste the following contents:

# Fix for PRISM select queries in MySQL 5.7.5+
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

3. Press CTRL+O to save, ENTER to confirm, CTRL+X to quit
4. Safely take down your Minecraft server(s)
5. Execute sudo service mysql restart to restart MySQL

Please report back if this does or doesn't properly fix it.

[Cryptite]

Alas! That did work. Many thanks!

[RoyCurtis]

No problem! Sorry for being slow on this, but hopefully this'll help you guys out for now :)

[RestlessZombi]

Sorry to bump an old thread but getting this error on a multicraft server, is there a way to fix this using the phpadmin page as i don't believe i have access to the mysql server to create this file using the method above? Seems they must have updated the software they run so this is now incompatible. I have tried to connect to the sql IP given but all types of connection are refused as expected. Server is hosted at BisectHosting if that helps.

[GriffinCodes]

@RestlessZombi Try contacting the company and explaining your problem. Hopefully, they will assist you and get it resolved. AFAIK, there is no way to make a permanent/persistent fix via PMA, but if you just click the "SQL" tab (without selecting a database) at the top and run the commands shown above, it should fix the Prism errors until reboot/update.
I recommend always safely shutting down your MC server before making any changes.