MySQL Issue

[Lmmb74]

Hi, I am getting this error: 03:37:53 [GRAVE] java.lang.RuntimeException: java.sql.SQLException: Expression #1 of ORDER BY clause is not in SELECT list, references column 'bungeeperms.bungeeperms_permissions2.id' which is not in SELECT list; this is incompatible with DISTINCT 03:37:53 [INFORMACIÓN] [BungeePerms] permissions loaded 03:37:53 [GRAVE] at net.alpenblock.bungeeperms.Mysql.returnQuery(Mysql.java:233) 03:37:53 [GRAVE] at net.alpenblock.bungeeperms.Mysql.returnQuery(Mysql.java:102) 03:37:53 [GRAVE] at net.alpenblock.bungeeperms.io.mysql2.MysqlPermsAdapter2.getGroups(MysqlPermsAdapter2.java:45) 03:37:53 [GRAVE] at net.alpenblock.bungeeperms.io.MySQL2BackEnd.loadGroups(MySQL2BackEnd.java:69) 03:37:53 [GRAVE] at net.alpenblock.bungeeperms.PermissionsManager.loadPerms(PermissionsManager.java:120) 03:37:53 [GRAVE] at net.alpenblock.bungeeperms.PermissionsManager.(PermissionsManager.java:65) 03:37:53 [GRAVE] at net.alpenblock.bungeeperms.BungeePerms.(BungeePerms.java:56) 03:37:53 [GRAVE] at net.alpenblock.bungeeperms.platform.bungee.BungeePlugin.onLoad(BungeePlugin.java:63) 03:37:53 [GRAVE] at net.md_5.bungee.api.plugin.PluginManager.enablePlugin(PluginManager.java:309) 03:37:53 [GRAVE] at net.md_5.bungee.api.plugin.PluginManager.loadPlugins(PluginManager.java:212) 03:37:53 [GRAVE] at net.md_5.bungee.BungeeCord.start(BungeeCord.java:262) 03:37:53 [GRAVE] at net.md_5.bungee.BungeeCordLauncher.main(BungeeCordLauncher.java:55) 03:37:53 [GRAVE] at net.md_5.bungee.Bootstrap.main(Bootstrap.java:15) 03:37:53 [GRAVE] Caused by: java.sql.SQLException: Expression #1 of ORDER BY clause is not in SELECT list, references column 'bungeeperms.bungeeperms_permissions2.id' which is not in SELECT list; this is incompatible with DISTINCT 03:37:53 [GRAVE] at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:996) 03:37:53 [GRAVE] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887) 03:37:53 [GRAVE] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823) 03:37:53 [GRAVE] at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435) 03:37:53 [GRAVE] at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582) 03:37:53 [GRAVE] at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2526) 03:37:53 [GRAVE] at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484) 03:37:53 [GRAVE] at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1446) 03:37:53 [GRAVE] at net.alpenblock.bungeeperms.Mysql.returnQuery(Mysql.java:227) 03:37:53 [GRAVE] ... 12 more

I think it is because I am using mysql 5.7.11, and this has been compiled for 5.6. Could you please update it?

[CodeCrafter47]

Users of MySQL 5.7.5 and later are affected by this. See https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by

Users of MariaDB (anyone using a recent version of Ubuntu or CentOS) are not affected as MariaDB has a different default for SQL_MODE.

[Psychlist1972]

I don't use MariaDB. I hadn't heard about it until now, actually.

I upgraded my ubuntu server the other day, and now I'm hitting this as well. Net effect is no one has any permissions on the server. I was able to fix it using this post and the information from CodeCrafter47 above and applying it to the mysql.cnf in /etc/mysql on my server.

[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"

[xaoseric]

This problem can also be fixed by installing mariadb as opposed to mysql https://downloads.mariadb.org/mariadb/repositories/#mirror=ut-austin

[DBBlueDeath]

The problem is the "bad" SQL query, not the strict MySQL setting. (Error is still present in latest version of BungeePerms). Adjust/fix the queries and there won't be any errors.

[2called-chaos]

Alternatively to fixing the poor queries one could copy this solution as a workaround, I'm not familiar with Java so I won't :)

This solution makes it so that only the connection is changed, it works if you don't have control over your mysql.

[aurorasmiles]

Shoud be fixed.