Admindaten können nicht gespeichert werden - Could not save Settings

[chrismey-71]

Hallo,
ich habe die App in Nextcloud 27.0.2 installiert. Zuerst konnten die Daten in der App nicht gesichert werden. Nachdem ich die Gruppen und den Schichtkalender angelegt hatte, konnte die App zumindest fehlerfrei die Woche anzeigen.
Leider funktioniert der Admin-Bereich nicht. Hier die Fehlermeldung aus der nextcloud.log:

`[index] Fehler: OC\DB\Exceptions\DbalException: An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db.oc_shifts_type.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by at <>

0. /pfad/public_html/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php line 295f
   OC\DB\Exceptions\DbalException::wrap()
1. /pfad/public_html/nextcloud/lib/public/AppFramework/Db/QBMapper.php line 335
   OC\DB\QueryBuilder\QueryBuilder->executeQuery()
2. /pfad/public_html/nextcloud/apps/shifts/lib/Db/ShiftsTypeMapper.php line 74
   OCP\AppFramework\Db\QBMapper->findEntities()
3. /pfad/public_html/nextcloud/apps/shifts/lib/Controller/SettingsController.php line 79
   OCA\Shifts\Db\ShiftsTypeMapper->findAllSkillGroupIds()
4. /pfad/public_html/nextcloud/lib/private/AppFramework/Http/Dispatcher.php line 230
   OCA\Shifts\Controller\SettingsController->saveSettings()
5. /pfad/public_html/nextcloud/lib/private/AppFramework/Http/Dispatcher.php line 137
   OC\AppFramework\Http\Dispatcher->executeController()
6. /pfad/public_html/nextcloud/lib/private/AppFramework/App.php line 183
   OC\AppFramework\Http\Dispatcher->dispatch()
7. /pfad/public_html/nextcloud/lib/private/Route/Router.php line 315
   OC\AppFramework\App::main()
8. /pfad/public_html/nextcloud/lib/base.php line 1071
   OC\Route\Router->match()
9. /pfad/public_html/nextcloud/index.php line 36
   OC::handleRequest()

Caused by:

Doctrine\DBAL\Exception\DriverException: An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db.oc_shifts_type.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by at <>

0. /pfad/public_html/nextcloud/3rdparty/doctrine/dbal/src/Connection.php line 1814
   Doctrine\DBAL\Driver\API\MySQL\ExceptionConverter->convert()
1. /pfad/public_html/nextcloud/3rdparty/doctrine/dbal/src/Connection.php line 1749
   Doctrine\DBAL\Connection->handleDriverException()
2. /pfad/public_html/nextcloud/3rdparty/doctrine/dbal/src/Connection.php line 1055
   Doctrine\DBAL\Connection->convertExceptionDuringQuery()
3. /pfad/public_html/nextcloud/lib/private/DB/Connection.php line 262
   Doctrine\DBAL\Connection->executeQuery()
4. /pfad/public_html/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php line 345
   OC\DB\Connection->executeQuery()
5. /pfad/public_html/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php line 280
   Doctrine\DBAL\Query\QueryBuilder->execute()
6. /pfad/public_html/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php line 293
   OC\DB\QueryBuilder\QueryBuilder->execute()
7. /pfad/public_html/nextcloud/lib/public/AppFramework/Db/QBMapper.php line 335
   OC\DB\QueryBuilder\QueryBuilder->executeQuery()
8. /pfad/public_html/nextcloud/apps/shifts/lib/Db/ShiftsTypeMapper.php line 74
   OCP\AppFramework\Db\QBMapper->findEntities()
9. /pfad/public_html/nextcloud/apps/shifts/lib/Controller/SettingsController.php line 79
   OCA\Shifts\Db\ShiftsTypeMapper->findAllSkillGroupIds()
10. /pfad/public_html/nextcloud/lib/private/AppFramework/Http/Dispatcher.php line 230
    OCA\Shifts\Controller\SettingsController->saveSettings()
11. /pfad/public_html/nextcloud/lib/private/AppFramework/Http/Dispatcher.php line 137
    OC\AppFramework\Http\Dispatcher->executeController()
12. /pfad/public_html/nextcloud/lib/private/AppFramework/App.php line 183
    OC\AppFramework\Http\Dispatcher->dispatch()
13. /pfad/public_html/nextcloud/lib/private/Route/Router.php line 315
    OC\AppFramework\App::main()
14. /pfad/public_html/nextcloud/lib/base.php line 1071
    OC\Route\Router->match()
15. /pfad/public_html/nextcloud/index.php line 36
    OC::handleRequest()

Caused by:

Doctrine\DBAL\Driver\PDO\Exception: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db.oc_shifts_type.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by at <>

0. /pfad/public_html/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Connection.php line 77
   Doctrine\DBAL\Driver\PDO\Exception::new()
1. /pfad/public_html/nextcloud/3rdparty/doctrine/dbal/src/Connection.php line 1050
   Doctrine\DBAL\Driver\PDO\Connection->query()
2. /pfad/public_html/nextcloud/lib/private/DB/Connection.php line 262
   Doctrine\DBAL\Connection->executeQuery()
3. /pfad/public_html/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php line 345
   OC\DB\Connection->executeQuery()
4. /pfad/public_html/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php line 280
   Doctrine\DBAL\Query\QueryBuilder->execute()
5. /pfad/public_html/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php line 293
   OC\DB\QueryBuilder\QueryBuilder->execute()
6. /pfad/public_html/nextcloud/lib/public/AppFramework/Db/QBMapper.php line 335
   OC\DB\QueryBuilder\QueryBuilder->executeQuery()
7. /pfad/public_html/nextcloud/apps/shifts/lib/Db/ShiftsTypeMapper.php line 74
   OCP\AppFramework\Db\QBMapper->findEntities()
8. /pfad/public_html/nextcloud/apps/shifts/lib/Controller/SettingsController.php line 79
   OCA\Shifts\Db\ShiftsTypeMapper->findAllSkillGroupIds()
9. /pfad/public_html/nextcloud/lib/private/AppFramework/Http/Dispatcher.php line 230
   OCA\Shifts\Controller\SettingsController->saveSettings()
10. /pfad/public_html/nextcloud/lib/private/AppFramework/Http/Dispatcher.php line 137
    OC\AppFramework\Http\Dispatcher->executeController()
11. /pfad/public_html/nextcloud/lib/private/AppFramework/App.php line 183
    OC\AppFramework\Http\Dispatcher->dispatch()
12. /pfad/public_html/nextcloud/lib/private/Route/Router.php line 315
    OC\AppFramework\App::main()
13. /pfad/public_html/nextcloud/lib/base.php line 1071
    OC\Route\Router->match()
14. /pfad/public_html/nextcloud/index.php line 36
    OC::handleRequest()

Caused by:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db.oc_shifts_type.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by at <>

0. /pfad/public_html/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Connection.php line 72
   PDO->query()
1. /pfad/public_html/nextcloud/3rdparty/doctrine/dbal/src/Connection.php line 1050
   Doctrine\DBAL\Driver\PDO\Connection->query()
2. /pfad/public_html/nextcloud/lib/private/DB/Connection.php line 262
   Doctrine\DBAL\Connection->executeQuery()
3. /pfad/public_html/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php line 345
   OC\DB\Connection->executeQuery()
4. /pfad/public_html/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php line 280
   Doctrine\DBAL\Query\QueryBuilder->execute()
5. /pfad/public_html/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php line 293
   OC\DB\QueryBuilder\QueryBuilder->execute()
6. /pfad/public_html/nextcloud/lib/public/AppFramework/Db/QBMapper.php line 335
   OC\DB\QueryBuilder\QueryBuilder->executeQuery()
7. /pfad/public_html/nextcloud/apps/shifts/lib/Db/ShiftsTypeMapper.php line 74
   OCP\AppFramework\Db\QBMapper->findEntities()
8. /pfad/public_html/nextcloud/apps/shifts/lib/Controller/SettingsController.php line 79
   OCA\Shifts\Db\ShiftsTypeMapper->findAllSkillGroupIds()
9. /pfad/public_html/nextcloud/lib/private/AppFramework/Http/Dispatcher.php line 230
   OCA\Shifts\Controller\SettingsController->saveSettings()
10. /pfad/public_html/nextcloud/lib/private/AppFramework/Http/Dispatcher.php line 137
    OC\AppFramework\Http\Dispatcher->executeController()
11. /pfad/public_html/nextcloud/lib/private/AppFramework/App.php line 183
    OC\AppFramework\Http\Dispatcher->dispatch()
12. /pfad/public_html/nextcloud/lib/private/Route/Router.php line 315
    OC\AppFramework\App::main()
13. /pfad/public_html/nextcloud/lib/base.php line 1071
    OC\Route\Router->match()
14. /pfad/public_html/nextcloud/index.php line 36
    OC::handleRequest()

PUT /index.php/apps/shifts/settings
from ip-adresse by christian at 2023-08-17T19:21:51+02:00`

[csoc-kevink]

Hallo,
anscheinend ist in deiner Datenbank der SQL_MODE 'only_full_group_by' (SHOW VARIABLES LIKE 'sql_mode') aktiviert.
Als quick fix könnte man diesen deaktivieren, wenn er nicht benötigt wird. Ich werde versuchen die Query anzupassen und ein Update bereitzustellen.

[chrismey-71]

Danke (auch für die tolle App), hat erstmal funktioniert.
Für alle denen es genauso geht, vorrübergehende Lösung und auf eigene Gefahr:
SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';

[hauke68]

Ich nutze PostgreSQL für meine Nextcloud und habe daher die id mit in die GROUP BY Klausel im Code aufgenommen. Damit funktioniert das Speichern auch erst einmal. Die Verwendung von DISTINCT sollte aber besser klappen. Siehe https://stackoverflow.com/q/69303573/10433844

[csoc-kevink]

Der Fehler in der SQL-Query ist in der neusten Version behoben und sollte nicht mehr auftreten.