Plan icon indicating copy to clipboard operation
Plan copied to clipboard
verified publisher icon plan-player-analytics

MySQL error with only_full_group_by setting turned on

Open AuroraLS3 opened this issue 3 years ago • 0 comments

Describe the issue

Playerbase overview query for Regular player count fails with this mode turned on.

Change causing the issue

https://github.com/plan-player-analytics/Plan/commit/ead1b401bca13cc963d7daf9ac2d8fd12efd1a66#diff-b2f69297a19f63f37649cc101fda914e23a685d65204cb5f11bb8c1274723ce0R125-R131

Exceptions & Other Logs

5feceb66ff - Last occurred: 2022-09-21 Occurrences: 4
---- Context 4 ----
Plan v5.5 build 1953
Paper git-Paper-161 (MC: 1.19.2)
Server v1.19.2-R0.1-SNAPSHOT

Error code: 1140
SELECT COUNT(1) as count, COALESCE(activity_index, 0) as activity_index FROM plan_user_info u LEFT JOIN (SELECT 5.0 - 5.0 * AVG(1.0 / (?/2.0 * (q1.active_playtime*1.0/?) +1.0)) as activity_index,u.id as user_id,u.uuid FROM (SELECT ux.user_id,COALESCE(active_playtime,0) AS active_playtime FROM plan_user_info ux LEFT JOIN (SELECT user_id,SUM(session_end-session_start-afk_time) as active_playtime FROM plan_sessions WHERE server_id=(SELECT plan_servers.id FROM plan_servers WHERE plan_servers.uuid=? LIMIT 1) AND session_end>=? AND session_start<=? GROUP BY user_id) sx on sx.user_id=ux.user_id UNION ALL SELECT ux.user_id,COALESCE(active_playtime,0) AS active_playtime FROM plan_user_info ux LEFT JOIN (SELECT user_id,SUM(session_end-session_start-afk_time) as active_playtime FROM plan_sessions WHERE server_id=(SELECT plan_servers.id FROM plan_servers WHERE plan_servers.uuid=? LIMIT 1) AND session_end>=? AND session_start<=? GROUP BY user_id) sx on sx.user_id=ux.user_id UNION ALL SELECT ux.user_id,COALESCE(active_playtime,0) AS active_playtime FROM plan_user_info ux LEFT JOIN (SELECT user_id,SUM(session_end-session_start-afk_time) as active_playtime FROM plan_sessions WHERE server_id=(SELECT plan_servers.id FROM plan_servers WHERE plan_servers.uuid=? LIMIT 1) AND session_end>=? AND session_start<=? GROUP BY user_id) sx on sx.user_id=ux.user_id) q1 JOIN plan_users u on u.id=q1.user_id GROUP BY q1.user_id) q2 on q2.user_id=u.user_id WHERE u.server_id=(SELECT plan_servers.id FROM plan_servers WHERE plan_servers.uuid=? LIMIT 1) AND u.registered<=? AND activity_index>=? AND activity_index<?
Unknown SQL Error code

---- Context 3 ----
Plan v5.5 build 1953
Paper git-Paper-161 (MC: 1.19.2)
Server v1.19.2-R0.1-SNAPSHOT

Error code: 1140
SELECT COUNT(1) as count, COALESCE(activity_index, 0) as activity_index FROM plan_user_info u LEFT JOIN (SELECT 5.0 - 5.0 * AVG(1.0 / (?/2.0 * (q1.active_playtime*1.0/?) +1.0)) as activity_index,u.id as user_id,u.uuid FROM (SELECT ux.user_id,COALESCE(active_playtime,0) AS active_playtime FROM plan_user_info ux LEFT JOIN (SELECT user_id,SUM(session_end-session_start-afk_time) as active_playtime FROM plan_sessions WHERE server_id=(SELECT plan_servers.id FROM plan_servers WHERE plan_servers.uuid=? LIMIT 1) AND session_end>=? AND session_start<=? GROUP BY user_id) sx on sx.user_id=ux.user_id UNION ALL SELECT ux.user_id,COALESCE(active_playtime,0) AS active_playtime FROM plan_user_info ux LEFT JOIN (SELECT user_id,SUM(session_end-session_start-afk_time) as active_playtime FROM plan_sessions WHERE server_id=(SELECT plan_servers.id FROM plan_servers WHERE plan_servers.uuid=? LIMIT 1) AND session_end>=? AND session_start<=? GROUP BY user_id) sx on sx.user_id=ux.user_id UNION ALL SELECT ux.user_id,COALESCE(active_playtime,0) AS active_playtime FROM plan_user_info ux LEFT JOIN (SELECT user_id,SUM(session_end-session_start-afk_time) as active_playtime FROM plan_sessions WHERE server_id=(SELECT plan_servers.id FROM plan_servers WHERE plan_servers.uuid=? LIMIT 1) AND session_end>=? AND session_start<=? GROUP BY user_id) sx on sx.user_id=ux.user_id) q1 JOIN plan_users u on u.id=q1.user_id GROUP BY q1.user_id) q2 on q2.user_id=u.user_id WHERE u.server_id=(SELECT plan_servers.id FROM plan_servers WHERE plan_servers.uuid=? LIMIT 1) AND u.registered<=? AND activity_index>=? AND activity_index<?
Unknown SQL Error code

---- Context 2 ----
Plan v5.5 build 1953
Paper git-Paper-161 (MC: 1.19.2)
Server v1.19.2-R0.1-SNAPSHOT

Error code: 1140
SELECT COUNT(1) as count, COALESCE(activity_index, 0) as activity_index FROM plan_user_info u LEFT JOIN (SELECT 5.0 - 5.0 * AVG(1.0 / (?/2.0 * (q1.active_playtime*1.0/?) +1.0)) as activity_index,u.id as user_id,u.uuid FROM (SELECT ux.user_id,COALESCE(active_playtime,0) AS active_playtime FROM plan_user_info ux LEFT JOIN (SELECT user_id,SUM(session_end-session_start-afk_time) as active_playtime FROM plan_sessions WHERE server_id=(SELECT plan_servers.id FROM plan_servers WHERE plan_servers.uuid=? LIMIT 1) AND session_end>=? AND session_start<=? GROUP BY user_id) sx on sx.user_id=ux.user_id UNION ALL SELECT ux.user_id,COALESCE(active_playtime,0) AS active_playtime FROM plan_user_info ux LEFT JOIN (SELECT user_id,SUM(session_end-session_start-afk_time) as active_playtime FROM plan_sessions WHERE server_id=(SELECT plan_servers.id FROM plan_servers WHERE plan_servers.uuid=? LIMIT 1) AND session_end>=? AND session_start<=? GROUP BY user_id) sx on sx.user_id=ux.user_id UNION ALL SELECT ux.user_id,COALESCE(active_playtime,0) AS active_playtime FROM plan_user_info ux LEFT JOIN (SELECT user_id,SUM(session_end-session_start-afk_time) as active_playtime FROM plan_sessions WHERE server_id=(SELECT plan_servers.id FROM plan_servers WHERE plan_servers.uuid=? LIMIT 1) AND session_end>=? AND session_start<=? GROUP BY user_id) sx on sx.user_id=ux.user_id) q1 JOIN plan_users u on u.id=q1.user_id GROUP BY q1.user_id) q2 on q2.user_id=u.user_id WHERE u.server_id=(SELECT plan_servers.id FROM plan_servers WHERE plan_servers.uuid=? LIMIT 1) AND u.registered<=? AND activity_index>=? AND activity_index<?
Unknown SQL Error code

---- Context 1 ----
Plan v5.5 build 1953
Paper git-Paper-161 (MC: 1.19.2)
Server v1.19.2-R0.1-SNAPSHOT

Error code: 1140
SELECT COUNT(1) as count, COALESCE(activity_index, 0) as activity_index FROM plan_user_info u LEFT JOIN (SELECT 5.0 - 5.0 * AVG(1.0 / (?/2.0 * (q1.active_playtime*1.0/?) +1.0)) as activity_index,u.id as user_id,u.uuid FROM (SELECT ux.user_id,COALESCE(active_playtime,0) AS active_playtime FROM plan_user_info ux LEFT JOIN (SELECT user_id,SUM(session_end-session_start-afk_time) as active_playtime FROM plan_sessions WHERE server_id=(SELECT plan_servers.id FROM plan_servers WHERE plan_servers.uuid=? LIMIT 1) AND session_end>=? AND session_start<=? GROUP BY user_id) sx on sx.user_id=ux.user_id UNION ALL SELECT ux.user_id,COALESCE(active_playtime,0) AS active_playtime FROM plan_user_info ux LEFT JOIN (SELECT user_id,SUM(session_end-session_start-afk_time) as active_playtime FROM plan_sessions WHERE server_id=(SELECT plan_servers.id FROM plan_servers WHERE plan_servers.uuid=? LIMIT 1) AND session_end>=? AND session_start<=? GROUP BY user_id) sx on sx.user_id=ux.user_id UNION ALL SELECT ux.user_id,COALESCE(active_playtime,0) AS active_playtime FROM plan_user_info ux LEFT JOIN (SELECT user_id,SUM(session_end-session_start-afk_time) as active_playtime FROM plan_sessions WHERE server_id=(SELECT plan_servers.id FROM plan_servers WHERE plan_servers.uuid=? LIMIT 1) AND session_end>=? AND session_start<=? GROUP BY user_id) sx on sx.user_id=ux.user_id) q1 JOIN plan_users u on u.id=q1.user_id GROUP BY q1.user_id) q2 on q2.user_id=u.user_id WHERE u.server_id=(SELECT plan_servers.id FROM plan_servers WHERE plan_servers.uuid=? LIMIT 1) AND u.registered<=? AND activity_index>=? AND activity_index<?
Unknown SQL Error code

---- Stacktrace ----
java.lang.IllegalStateException: com.djrapitops.plan.exceptions.database.DBOpException: SQL Failure: In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'q2.activity_index'; this is incompatible with sql_mode=only_full_group_by
   Plan-5.5-build-1953.jar//com.djrapitops.plan.processing.Processing.lambda$submitNonCritical$3(Processing.java:106)
   java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1768)
   java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
   java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
   java.base/java.lang.Thread.run(Thread.java:833)
Caused by:
com.djrapitops.plan.exceptions.database.DBOpException: SQL Failure: In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'q2.activity_index'; this is incompatible with sql_mode=only_full_group_by
   Plan-5.5-build-1953.jar//com.djrapitops.plan.exceptions.database.DBOpException.forCause(DBOpException.java:152)
   Plan-5.5-build-1953.jar//com.djrapitops.plan.storage.database.queries.QueryStatement.executeWithConnection(QueryStatement.java:64)
   Plan-5.5-build-1953.jar//com.djrapitops.plan.storage.database.queries.QueryStatement.executeQuery(QueryStatement.java:52)
   Plan-5.5-build-1953.jar//com.djrapitops.plan.storage.database.SQLDB.query(SQLDB.java:325)
   Plan-5.5-build-1953.jar//com.djrapitops.plan.delivery.rendering.json.PlayerBaseOverviewJSONCreator.createTrendsMap(PlayerBaseOverviewJSONCreator.java:86)
   Plan-5.5-build-1953.jar//com.djrapitops.plan.delivery.rendering.json.PlayerBaseOverviewJSONCreator.createJSONAsMap(PlayerBaseOverviewJSONCreator.java:66)
   Plan-5.5-build-1953.jar//com.djrapitops.plan.delivery.rendering.json.PlayerBaseOverviewJSONCreator.createJSONAsMap(PlayerBaseOverviewJSONCreator.java:42)
   Plan-5.5-build-1953.jar//com.djrapitops.plan.delivery.rendering.json.ServerTabJSONCreator.apply(ServerTabJSONCreator.java:34)
   Plan-5.5-build-1953.jar//com.djrapitops.plan.delivery.rendering.json.ServerTabJSONCreator.apply(ServerTabJSONCreator.java:28)
   Plan-5.5-build-1953.jar//com.djrapitops.plan.delivery.webserver.cache.AsyncJSONResolverService.lambda$resolve$0(AsyncJSONResolverService.java:69)
   Plan-5.5-build-1953.jar//com.djrapitops.plan.delivery.webserver.cache.AsyncJSONResolverService.lambda$submitToProcessing$2(AsyncJSONResolverService.java:151)
   Plan-5.5-build-1953.jar//com.djrapitops.plan.processing.Processing.lambda$submitNonCritical$3(Processing.java:104)
   java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1768)
   java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
   java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
   java.base/java.lang.Thread.run(Thread.java:833)
Caused by:
java.sql.SQLSyntaxErrorException: In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'q2.activity_index'; this is incompatible with sql_mode=only_full_group_by
   com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
   com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
   com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
   com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:972)
   Plan-5.5-build-1953.jar//plan.com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
   Plan-5.5-build-1953.jar//plan.com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
   Plan-5.5-build-1953.jar//com.djrapitops.plan.storage.database.queries.QueryStatement.executeQuery(QueryStatement.java:72)
   Plan-5.5-build-1953.jar//com.djrapitops.plan.storage.database.queries.QueryStatement.executeWithConnection(QueryStatement.java:62)
   Plan-5.5-build-1953.jar//com.djrapitops.plan.storage.database.queries.QueryStatement.executeQuery(QueryStatement.java:52)
   Plan-5.5-build-1953.jar//com.djrapitops.plan.storage.database.SQLDB.query(SQLDB.java:325)
   Plan-5.5-build-1953.jar//com.djrapitops.plan.delivery.rendering.json.PlayerBaseOverviewJSONCreator.createTrendsMap(PlayerBaseOverviewJSONCreator.java:86)
   Plan-5.5-build-1953.jar//com.djrapitops.plan.delivery.rendering.json.PlayerBaseOverviewJSONCreator.createJSONAsMap(PlayerBaseOverviewJSONCreator.java:66)
   Plan-5.5-build-1953.jar//com.djrapitops.plan.delivery.rendering.json.PlayerBaseOverviewJSONCreator.createJSONAsMap(PlayerBaseOverviewJSONCreator.java:42)
   Plan-5.5-build-1953.jar//com.djrapitops.plan.delivery.rendering.json.ServerTabJSONCreator.apply(ServerTabJSONCreator.java:34)
   Plan-5.5-build-1953.jar//com.djrapitops.plan.delivery.rendering.json.ServerTabJSONCreator.apply(ServerTabJSONCreator.java:28)
   Plan-5.5-build-1953.jar//com.djrapitops.plan.delivery.webserver.cache.AsyncJSONResolverService.lambda$resolve$0(AsyncJSONResolverService.java:69)
   Plan-5.5-build-1953.jar//com.djrapitops.plan.delivery.webserver.cache.AsyncJSONResolverService.lambda$submitToProcessing$2(AsyncJSONResolverService.java:151)
   Plan-5.5-build-1953.jar//com.djrapitops.plan.processing.Processing.lambda$submitNonCritical$3(Processing.java:104)
   java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1768)
   java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
   java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
   java.base/java.lang.Thread.run(Thread.java:833)

Plugin versions

5.5 build 1953

Additional information

Temporary fix is to turn off only_full_group_by - and reverse is easy way to reproduce the issue (I hope)

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')

AuroraLS3 avatar Sep 21 '22 16:09 AuroraLS3