search

prism / PrismRefracted

A continuation of Prism, a change-tracking plugin for Bukkit-based servers.
Other
44 stars 23 forks source link

[BUG] SQL syntax error when using `/pr near` #55

Closed HKayani closed 1 year ago

HKayani commented 1 year ago

Describe the bug When using /pr near no results are returned and upon inspection of the console, I see a SQL syntax error.

To Reproduce Steps to reproduce the behavior: Using Prism 3.6.0 (all) in a 1.19.3 Purpur server instance, I broke some stone blocks I placed under my feet using World Edit and attempted to check that it's being registered with Prism using /pr near. It wasn't returning any results, not even showing an error had occurred.

Expected behavior I expected the command to return some log of the recently broken stone blocks. I was in creative and also OP so I didn't pay much attention at first, then realized after checking the server log.

Screenshots No screenshots but here's the log of the error from the console.

[05:01:42 INFO]: CribRobber issued server command: /pr near
[05:01:42 WARN]: [Prism] Database connection error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mcdatabase.prism_data.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
[05:01:42 WARN]: java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mcdatabase.prism_data.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
[05:01:42 WARN]:        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
[05:01:42 WARN]:        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
[05:01:42 WARN]:        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
[05:01:42 WARN]:        at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:972)
[05:01:42 WARN]:        at Prism-3.6.0-all.jar//network.darkhelmet.prism.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
[05:01:42 WARN]:        at Prism-3.6.0-all.jar//network.darkhelmet.prism.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
[05:01:42 WARN]:        at Prism-3.6.0-all.jar//network.darkhelmet.prism.database.sql.SqlSelectQueryBuilder.executeSelect(SqlSelectQueryBuilder.java:478)
[05:01:42 WARN]:        at Prism-3.6.0-all.jar//network.darkhelmet.prism.actionlibs.ActionsQuery.lookup(ActionsQuery.java:66)
[05:01:42 WARN]:        at Prism-3.6.0-all.jar//network.darkhelmet.prism.commands.NearCommand.lambda$handle$0(NearCommand.java:66)
[05:01:42 WARN]:        at org.bukkit.craftbukkit.v1_19_R2.scheduler.CraftTask.run(CraftTask.java:101)
[05:01:42 WARN]:        at org.bukkit.craftbukkit.v1_19_R2.scheduler.CraftAsyncTask.run(CraftAsyncTask.java:57)
[05:01:42 WARN]:        at com.destroystokyo.paper.ServerSchedulerReportingWrapper.run(ServerSchedulerReportingWrapper.java:22)
[05:01:42 WARN]:        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
[05:01:42 WARN]:        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
[05:01:42 WARN]:        at java.base/java.lang.Thread.run(Thread.java:833)
[05:01:48 INFO]: CribRobber issued server command: /pr near
[05:01:48 WARN]: [Prism] Database connection error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mcdatabase.prism_data.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
[05:01:48 WARN]: java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mcdatabase.prism_data.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
[05:01:48 WARN]:        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
[05:01:48 WARN]:        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
[05:01:48 WARN]:        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
[05:01:48 WARN]:        at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:972)
[05:01:48 WARN]:        at Prism-3.6.0-all.jar//network.darkhelmet.prism.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
[05:01:48 WARN]:        at Prism-3.6.0-all.jar//network.darkhelmet.prism.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
[05:01:48 WARN]:        at Prism-3.6.0-all.jar//network.darkhelmet.prism.database.sql.SqlSelectQueryBuilder.executeSelect(SqlSelectQueryBuilder.java:478)
[05:01:48 WARN]:        at Prism-3.6.0-all.jar//network.darkhelmet.prism.actionlibs.ActionsQuery.lookup(ActionsQuery.java:66)
[05:01:48 WARN]:        at Prism-3.6.0-all.jar//network.darkhelmet.prism.commands.NearCommand.lambda$handle$0(NearCommand.java:66)
[05:01:48 WARN]:        at org.bukkit.craftbukkit.v1_19_R2.scheduler.CraftTask.run(CraftTask.java:101)
[05:01:48 WARN]:        at org.bukkit.craftbukkit.v1_19_R2.scheduler.CraftAsyncTask.run(CraftAsyncTask.java:57)
[05:01:48 WARN]:        at com.destroystokyo.paper.ServerSchedulerReportingWrapper.run(ServerSchedulerReportingWrapper.java:22)
[05:01:48 WARN]:        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
[05:01:48 WARN]:        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
[05:01:48 WARN]:        at java.base/java.lang.Thread.run(Thread.java:833)
[05:01:57 INFO]: CribRobber issued server command: /pr near
[05:01:57 WARN]: [Prism] Database connection error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mcdatabase.prism_data.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
[05:01:57 WARN]: java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mcdatabase.prism_data.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
[05:01:57 WARN]:        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
[05:01:57 WARN]:        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
[05:01:57 WARN]:        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
[05:01:57 WARN]:        at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:972)
[05:01:57 WARN]:        at Prism-3.6.0-all.jar//network.darkhelmet.prism.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
[05:01:57 WARN]:        at Prism-3.6.0-all.jar//network.darkhelmet.prism.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
[05:01:57 WARN]:        at Prism-3.6.0-all.jar//network.darkhelmet.prism.database.sql.SqlSelectQueryBuilder.executeSelect(SqlSelectQueryBuilder.java:478)
[05:01:57 WARN]:        at Prism-3.6.0-all.jar//network.darkhelmet.prism.actionlibs.ActionsQuery.lookup(ActionsQuery.java:66)
[05:01:57 WARN]:        at Prism-3.6.0-all.jar//network.darkhelmet.prism.commands.NearCommand.lambda$handle$0(NearCommand.java:66)
[05:01:57 WARN]:        at org.bukkit.craftbukkit.v1_19_R2.scheduler.CraftTask.run(CraftTask.java:101)
[05:01:57 WARN]:        at org.bukkit.craftbukkit.v1_19_R2.scheduler.CraftAsyncTask.run(CraftAsyncTask.java:57)
[05:01:57 WARN]:        at com.destroystokyo.paper.ServerSchedulerReportingWrapper.run(ServerSchedulerReportingWrapper.java:22)
[05:01:57 WARN]:        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
[05:01:57 WARN]:        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
[05:01:57 WARN]:        at java.base/java.lang.Thread.run(Thread.java:833)

Server (please complete the following information):

Additional context I have 9 other plugins installed:

Bobcat00 commented 1 year ago

I had this same problem. It's because ONLY_FULL_GROUP_BY is enabled by default in MySQL 5.7.5 and later. See https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by

So you should disable this if you can.

Another possibility that I haven't tried is setting useNonStandardSql to false.

viveleroi commented 1 year ago

As bobcat said, while this has long been a bug the useNonStandardSql option was added as a work around. Prism v4 will not have this flaw and is in dev now.