search

csoc-de / Shifts

MIT License
30 stars 7 forks source link

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

Closed chrismey-71 closed 9 months ago

chrismey-71 commented 1 year ago

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

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

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

  1. /pfad/public_html/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Connection.php line 77 Doctrine\DBAL\Driver\PDO\Exception::new()
  2. /pfad/public_html/nextcloud/3rdparty/doctrine/dbal/src/Connection.php line 1050 Doctrine\DBAL\Driver\PDO\Connection->query()
  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()
    1. /pfad/public_html/nextcloud/lib/private/AppFramework/Http/Dispatcher.php line 137 OC\AppFramework\Http\Dispatcher->executeController()
    2. /pfad/public_html/nextcloud/lib/private/AppFramework/App.php line 183 OC\AppFramework\Http\Dispatcher->dispatch()
    3. /pfad/public_html/nextcloud/lib/private/Route/Router.php line 315 OC\AppFramework\App::main()
    4. /pfad/public_html/nextcloud/lib/base.php line 1071 OC\Route\Router->match()
    5. /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 <>

  1. /pfad/public_html/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Connection.php line 72 PDO->query()
  2. /pfad/public_html/nextcloud/3rdparty/doctrine/dbal/src/Connection.php line 1050 Doctrine\DBAL\Driver\PDO\Connection->query()
  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()
    1. /pfad/public_html/nextcloud/lib/private/AppFramework/Http/Dispatcher.php line 137 OC\AppFramework\Http\Dispatcher->executeController()
    2. /pfad/public_html/nextcloud/lib/private/AppFramework/App.php line 183 OC\AppFramework\Http\Dispatcher->dispatch()
    3. /pfad/public_html/nextcloud/lib/private/Route/Router.php line 315 OC\AppFramework\App::main()
    4. /pfad/public_html/nextcloud/lib/base.php line 1071 OC\Route\Router->match()
    5. /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 commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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 commented 9 months ago

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