SQL error when loading the library with the MySQL database server

[michael-j-green]

See: https://stackoverflow.com/questions/23921117/disable-only-full-group-by

gaseous-server  | 20240217 024536: Critical: Database: Error while executing 'SELECT Game.AgeGroupId, COUNT(Game.Id) AS GameCount FROM (SELECT DISTINCT Game.Id, AgeGroup.AgeGroupId, COUNT(Games_Roms.Id) AS RomCount FROM Game LEFT JOIN AgeGroup ON Game.Id = AgeGroup.GameId LEFT JOIN Games_Roms ON Game.Id = Games_Roms.GameId WHERE (AgeGroup.AgeGroupId <= 4 OR AgeGroup.AgeGroupId IS NULL) GROUP BY Game.Id HAVING RomCount > 0) Game GROUP BY Game.AgeGroupId ORDER BY Game.AgeGroupId DESC'
gaseous-server  | MySqlConnector.MySqlException (0x80004005): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'gaseous.AgeGroup.AgeGroupId' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
gaseous-server  |    at MySqlConnector.Core.ServerSession.ReceiveReplyAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ServerSession.cs:line 894
gaseous-server  |    at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 37
gaseous-server  |    at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 130
gaseous-server  |    at MySqlConnector.MySqlDataReader.InitAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 483
gaseous-server  |    at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 56
gaseous-server  |    at MySqlConnector.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 357
gaseous-server  |    at gaseous_server.Classes.Database.MySQLServerConnector.ExecCMD(String SQL, Dictionary`2 Parameters, Int32 Timeout) in /App/gaseous-server/Classes/Database.cs:line 362
gaseous-server  | 20240217 024536: Critical: Database: Error while executing '
gaseous-server  | SELECT DISTINCT
gaseous-server  |     Game.Id,
gaseous-server  |     Game.`Name`,
gaseous-server  |     Game.NameThe,
gaseous-server  |     Game.Slug,
gaseous-server  |     Game.PlatformId,
gaseous-server  |     Game.TotalRating,
gaseous-server  |     Game.TotalRatingCount,
gaseous-server  |     Game.Cover,
gaseous-server  |     Game.Artworks,
gaseous-server  |     Game.FirstReleaseDate,
gaseous-server  |     Game.Category,
gaseous-server  |     Game.ParentGame,
gaseous-server  |     Game.AgeRatings,
gaseous-server  |     Game.AgeGroupId,
gaseous-server  |     Game.RomCount,
gaseous-server  |     RomSavedStates.RomSaveCount,
gaseous-server  |     RomGroupSavedStates.MediaGroupSaveCount,
gaseous-server  |     CASE
gaseous-server  |         WHEN Favourites.UserId IS NULL THEN 0
gaseous-server  |         ELSE 1
gaseous-server  |     END AS Favourite
gaseous-server  | FROM
gaseous-server  |     (SELECT DISTINCT
gaseous-server  |         Game.*,
gaseous-server  |             CASE
gaseous-server  |                 WHEN Game.`Name` LIKE 'The %' THEN CONCAT(TRIM(SUBSTR(Game.`Name` FROM 4)), ', The')
gaseous-server  |                 ELSE Game.`Name`
gaseous-server  |             END AS NameThe,
gaseous-server  |             Games_Roms.PlatformId,
gaseous-server  |             AgeGroup.AgeGroupId,
gaseous-server  |             COUNT(Games_Roms.Id) AS RomCount
gaseous-server  |     FROM
gaseous-server  |         Game
gaseous-server  |     LEFT JOIN AgeGroup ON Game.Id = AgeGroup.GameId
gaseous-server  |     LEFT JOIN Games_Roms ON Game.Id = Games_Roms.GameId
gaseous-server  |     LEFT JOIN AlternativeName ON Game.Id = AlternativeName.Game 
gaseous-server  |     GROUP BY Game.Id
gaseous-server  |     HAVING RomCount > 0) Game
gaseous-server  |         LEFT JOIN
gaseous-server  |     (SELECT 
gaseous-server  |         Games_Roms.GameId, COUNT(GameState.Id) AS RomSaveCount
gaseous-server  |     FROM
gaseous-server  |         GameState
gaseous-server  |     JOIN Games_Roms ON GameState.RomId = Games_Roms.Id
gaseous-server  |     WHERE
gaseous-server  |         GameState.IsMediaGroup = 0
gaseous-server  |             AND GameState.UserId = @userid
gaseous-server  |     GROUP BY Games_Roms.GameId) RomSavedStates ON Game.Id = RomSavedStates.GameId
gaseous-server  |         LEFT JOIN
gaseous-server  |     (SELECT 
gaseous-server  |         RomMediaGroup.GameId,
gaseous-server  |             COUNT(RomMediaGroup.GameId) AS MediaGroupSaveCount
gaseous-server  |     FROM
gaseous-server  |         RomMediaGroup
gaseous-server  |     JOIN GameState ON RomMediaGroup.Id = GameState.RomId
gaseous-server  |         AND GameState.IsMediaGroup = 1
gaseous-server  |         AND GameState.UserId = @userid
gaseous-server  |     GROUP BY RomMediaGroup.GameId) RomGroupSavedStates ON Game.Id = RomGroupSavedStates.GameId
gaseous-server  |         LEFT JOIN
gaseous-server  |     Relation_Game_Genres ON Game.Id = Relation_Game_Genres.GameId
gaseous-server  |         LEFT JOIN
gaseous-server  |     Relation_Game_GameModes ON Game.Id = Relation_Game_GameModes.GameId
gaseous-server  |         LEFT JOIN
gaseous-server  |     Relation_Game_PlayerPerspectives ON Game.Id = Relation_Game_PlayerPerspectives.GameId
gaseous-server  |         LEFT JOIN
gaseous-server  |     Relation_Game_Themes ON Game.Id = Relation_Game_Themes.GameId
gaseous-server  |         LEFT JOIN
gaseous-server  |     Favourites ON Game.Id = Favourites.GameId AND Favourites.UserId = @userid WHERE (Game.AgeGroupId IN (@Rating0, @Rating1, @Rating2, @Rating3) OR Game.AgeGroupId IS NULL)  ORDER BY `NameThe` ASC'
gaseous-server  | MySqlConnector.MySqlException (0x80004005): Expression #62 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'gaseous.Games_Roms.PlatformId' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
gaseous-server  |    at MySqlConnector.Core.ServerSession.ReceiveReplyAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ServerSession.cs:line 894
gaseous-server  |    at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 37
gaseous-server  |    at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 130
gaseous-server  |    at MySqlConnector.MySqlDataReader.InitAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 483
gaseous-server  |    at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 56
gaseous-server  |    at MySqlConnector.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 357
gaseous-server  |    at gaseous_server.Classes.Database.MySQLServerConnector.ExecCMD(String SQL, Dictionary`2 Parameters, Int32 Timeout) in /App/gaseous-server/Classes/Database.cs:line 362

[michael-j-green]

Closed in #327