search

mziech / nextcloud-cas

Basic CAS protocol server (IdP) implementation for NextCloud
GNU Affero General Public License v3.0
5 stars 1 forks source link

Ticket Generation seams to be broken on postgresql #5

Closed JGuck closed 2 years ago

JGuck commented 2 years ago

Hi,

i installed your app on: nextcloud 22.3.2 postgresql 11.14

when i try to login i get the following stacktrace on nextcloud:

OC\DB\Exceptions\DbalException: An exception occurred while executing a query: SQLSTATE[55000]: Object not in prerequisite state: 7 ERROR: lastval is not yet defined in this session
/var/www/html/lib/private/DB/ConnectionAdapter.php - line 93:
OC\DB\Exceptions\DbalException::wrap(Doctrine\DBA ... {})
/var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php - line 1297:
OC\DB\ConnectionAdapter->lastInsertId("*PREFIX*cas_ticket")
/var/www/html/lib/public/AppFramework/Db/QBMapper.php - line 143:
OC\DB\QueryBuilder\QueryBuilder->getLastInsertId()
/var/www/html/custom_apps/cas/lib/Service/TicketService.php - line 87:
OCP\AppFramework\Db\QBMapper->insert(OCA\Cas\Doma ... l})
/var/www/html/custom_apps/cas/lib/Controller/CasController.php - line 65:
OCA\Cas\Service\TicketService->createServiceTicket("*** sensiti ... *")
/var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 217:
OCA\Cas\Controller\CasController->login("*** sensiti ... *")
/var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 126:
OC\AppFramework\Http\Dispatcher->executeController(OCA\Cas\Cont ... {}, "login")
/var/www/html/lib/private/AppFramework/App.php - line 156:
OC\AppFramework\Http\Dispatcher->dispatch(OCA\Cas\Cont ... {}, "login")
/var/www/html/lib/private/Route/Router.php - line 302:
OC\AppFramework\App::main("OCA\\Cas\\C ... r", "login", OC\AppFramew ... {}, { _route: "cas.Cas.login"})
/var/www/html/lib/base.php - line 1006:
OC\Route\Router->match("/apps/cas/login")
/var/www/html/index.php - line 36:
OC::handleRequest()

Verursacht durch Doctrine\DBAL\Exception\DriverException: An exception occurred while executing a query: SQLSTATE[55000]: Object not in prerequisite state: 7 ERROR: lastval is not yet defined in this session
/var/www/html/3rdparty/doctrine/dbal/src/Connection.php - line 1780:
Doctrine\DBAL\Driver\API\PostgreSQL\ExceptionConverter->convert(Doctrine\DBA ... {}, Doctrine\DBAL\Query {})
/var/www/html/3rdparty/doctrine/dbal/src/Connection.php - line 1719:
Doctrine\DBAL\Connection->handleDriverException(Doctrine\DBA ... {}, Doctrine\DBAL\Query {})
/var/www/html/3rdparty/doctrine/dbal/src/Connection.php - line 1067:
Doctrine\DBAL\Connection->convertExceptionDuringQuery(Doctrine\DBA ... {}, "SELECT lastval()", [], [])
/var/www/html/lib/private/DB/Connection.php - line 236:
Doctrine\DBAL\Connection->executeQuery("SELECT lastval()", [], [], null)
/var/www/html/lib/private/DB/AdapterPgSql.php - line 33:
OC\DB\Connection->executeQuery("SELECT lastval()")
/var/www/html/lib/private/DB/Connection.php - line 287:
OC\DB\AdapterPgSql->lastInsertId("oc_cas_ticket")
/var/www/html/lib/private/DB/ConnectionAdapter.php - line 91:
OC\DB\Connection->lastInsertId("oc_cas_ticket")
/var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php - line 1297:
OC\DB\ConnectionAdapter->lastInsertId("*PREFIX*cas_ticket")
/var/www/html/lib/public/AppFramework/Db/QBMapper.php - line 143:
OC\DB\QueryBuilder\QueryBuilder->getLastInsertId()
/var/www/html/custom_apps/cas/lib/Service/TicketService.php - line 87:
OCP\AppFramework\Db\QBMapper->insert(OCA\Cas\Doma ... l})
/var/www/html/custom_apps/cas/lib/Controller/CasController.php - line 65:
OCA\Cas\Service\TicketService->createServiceTicket("*** sensiti ... *")
/var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 217:
OCA\Cas\Controller\CasController->login("*** sensiti ... *")
/var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 126:
OC\AppFramework\Http\Dispatcher->executeController(OCA\Cas\Cont ... {}, "login")
/var/www/html/lib/private/AppFramework/App.php - line 156:
OC\AppFramework\Http\Dispatcher->dispatch(OCA\Cas\Cont ... {}, "login")
/var/www/html/lib/private/Route/Router.php - line 302:
OC\AppFramework\App::main("OCA\\Cas\\C ... r", "login", OC\AppFramew ... {}, { _route: "cas.Cas.login"})
/var/www/html/lib/base.php - line 1006:
OC\Route\Router->match("/apps/cas/login")
/var/www/html/index.php - line 36:
OC::handleRequest()

Verursacht durch Doctrine\DBAL\Driver\PDO\Exception: SQLSTATE[55000]: Object not in prerequisite state: 7 ERROR: lastval is not yet defined in this session
/var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Connection.php - line 87:
Doctrine\DBAL\Driver\PDO\Exception::new(PDOException ... ]})
/var/www/html/3rdparty/doctrine/dbal/src/Connection.php - line 1062:
Doctrine\DBAL\Driver\PDO\Connection->query("SELECT lastval()")
/var/www/html/lib/private/DB/Connection.php - line 236:
Doctrine\DBAL\Connection->executeQuery("SELECT lastval()", [], [], null)
/var/www/html/lib/private/DB/AdapterPgSql.php - line 33:
OC\DB\Connection->executeQuery("SELECT lastval()")
/var/www/html/lib/private/DB/Connection.php - line 287:
OC\DB\AdapterPgSql->lastInsertId("oc_cas_ticket")
/var/www/html/lib/private/DB/ConnectionAdapter.php - line 91:
OC\DB\Connection->lastInsertId("oc_cas_ticket")
/var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php - line 1297:
OC\DB\ConnectionAdapter->lastInsertId("*PREFIX*cas_ticket")
/var/www/html/lib/public/AppFramework/Db/QBMapper.php - line 143:
OC\DB\QueryBuilder\QueryBuilder->getLastInsertId()
/var/www/html/custom_apps/cas/lib/Service/TicketService.php - line 87:
OCP\AppFramework\Db\QBMapper->insert(OCA\Cas\Doma ... l})
/var/www/html/custom_apps/cas/lib/Controller/CasController.php - line 65:
OCA\Cas\Service\TicketService->createServiceTicket("*** sensiti ... *")
/var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 217:
OCA\Cas\Controller\CasController->login("*** sensiti ... *")
/var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 126:
OC\AppFramework\Http\Dispatcher->executeController(OCA\Cas\Cont ... {}, "login")
/var/www/html/lib/private/AppFramework/App.php - line 156:
OC\AppFramework\Http\Dispatcher->dispatch(OCA\Cas\Cont ... {}, "login")
/var/www/html/lib/private/Route/Router.php - line 302:
OC\AppFramework\App::main("OCA\\Cas\\C ... r", "login", OC\AppFramew ... {}, { _route: "cas.Cas.login"})
/var/www/html/lib/base.php - line 1006:
OC\Route\Router->match("/apps/cas/login")
/var/www/html/index.php - line 36:
OC::handleRequest()

Verursacht durch PDOException: SQLSTATE[55000]: Object not in prerequisite state: 7 ERROR: lastval is not yet defined in this session
/var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Connection.php - line 82:
PDO->query("SELECT lastval()")
/var/www/html/3rdparty/doctrine/dbal/src/Connection.php - line 1062:
Doctrine\DBAL\Driver\PDO\Connection->query("SELECT lastval()")
/var/www/html/lib/private/DB/Connection.php - line 236:
Doctrine\DBAL\Connection->executeQuery("SELECT lastval()", [], [], null)
/var/www/html/lib/private/DB/AdapterPgSql.php - line 33:
OC\DB\Connection->executeQuery("SELECT lastval()")
/var/www/html/lib/private/DB/Connection.php - line 287:
OC\DB\AdapterPgSql->lastInsertId("oc_cas_ticket")
/var/www/html/lib/private/DB/ConnectionAdapter.php - line 91:
OC\DB\Connection->lastInsertId("oc_cas_ticket")
/var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php - line 1297:
OC\DB\ConnectionAdapter->lastInsertId("*PREFIX*cas_ticket")
/var/www/html/lib/public/AppFramework/Db/QBMapper.php - line 143:
OC\DB\QueryBuilder\QueryBuilder->getLastInsertId()
/var/www/html/custom_apps/cas/lib/Service/TicketService.php - line 87:
OCP\AppFramework\Db\QBMapper->insert(OCA\Cas\Doma ... l})
/var/www/html/custom_apps/cas/lib/Controller/CasController.php - line 65:
OCA\Cas\Service\TicketService->createServiceTicket("*** sensiti ... *")
/var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 217:
OCA\Cas\Controller\CasController->login("*** sensiti ... *")
/var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 126:
OC\AppFramework\Http\Dispatcher->executeController(OCA\Cas\Cont ... {}, "login")
/var/www/html/lib/private/AppFramework/App.php - line 156:
OC\AppFramework\Http\Dispatcher->dispatch(OCA\Cas\Cont ... {}, "login")
/var/www/html/lib/private/Route/Router.php - line 302:
OC\AppFramework\App::main("OCA\\Cas\\C ... r", "login", OC\AppFramew ... {}, { _route: "cas.Cas.login"})
/var/www/html/lib/base.php - line 1006:
OC\Route\Router->match("/apps/cas/login")
/var/www/html/index.php - line 36:
OC::handleRequest()

I found a similar bug on nextcloud https://github.com/nextcloud/server/issues/12465 It seams to be that the ticket id can not be auto generated.

mziech commented 2 years ago

Hi, thanks for the report and pointing out the discussion on the root cause! :+1:

Indeed, it's because the id column could not be auto-generated ... because there is no id column ;)

QBMapper seems to implicitly assume that such a column is being used and as usual MySQL seems to be more forgiving regarding useless last-inserted-id requests. :thinking:

mziech commented 2 years ago

@JGuck I figured out that the easiest way to fix this problem is to add the id column. I guess it's also cleaner from a DB architecture point of view. Please check whether 0.2.7 fixes your problem.

JGuck commented 2 years ago

Thank you for the fast response. I testet the new version but the update and reinstalation on my system fails now. The stacktrace is: 

Error   settings    Doctrine\DBAL\Exception\DriverException: An exception occurred while executing a query: SQLSTATE[42704]: Undefined object: 7 ERROR: index "primary" does not exist
/var/www/html/3rdparty/doctrine/dbal/src/Connection.php - line 1780:
Doctrine\DBAL\Driver\API\PostgreSQL\ExceptionConverter->convert(Doctrine\DBA ... {}, Doctrine\DBAL\Query {})
/var/www/html/3rdparty/doctrine/dbal/src/Connection.php - line 1719:
Doctrine\DBAL\Connection->handleDriverException(Doctrine\DBA ... {}, Doctrine\DBAL\Query {})
/var/www/html/3rdparty/doctrine/dbal/src/Connection.php - line 1067:
Doctrine\DBAL\Connection->convertExceptionDuringQuery(Doctrine\DBA ... {}, "DROP INDEX \"primary\"", [], [])
/var/www/html/lib/private/DB/Connection.php - line 236:
Doctrine\DBAL\Connection->executeQuery("DROP INDEX \"primary\"", [], [], null)
/var/www/html/3rdparty/doctrine/dbal/src/Connection.php - line 1809:
OC\DB\Connection->executeQuery("DROP INDEX \"primary\"")
/var/www/html/lib/private/DB/Migrator.php - line 175:
Doctrine\DBAL\Connection->query("DROP INDEX \"primary\"")
/var/www/html/lib/private/DB/Migrator.php - line 76:
OC\DB\Migrator->applySchema(Doctrine\DBAL\Schema\Schema {})
/var/www/html/lib/private/DB/Connection.php - line 532:
OC\DB\Migrator->migrate(Doctrine\DBAL\Schema\Schema {})
/var/www/html/lib/private/DB/MigrationService.php - line 463:
OC\DB\Connection->migrateToSchema(Doctrine\DBAL\Schema\Schema {})
/var/www/html/lib/private/DB/MigrationService.php - line 417:
OC\DB\MigrationService->migrateSchemaOnly("latest")
/var/www/html/lib/private/Installer.php - line 153:
OC\DB\MigrationService->migrate("latest", true)
/var/www/html/apps/settings/lib/Controller/AppSettingsController.php - line 452:
OC\Installer->installApp("cas")
/var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 217:
OCA\Settings\Controller\AppSettingsController->enableApps([ "cas"], [])
/var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 126:
OC\AppFramework\Http\Dispatcher->executeController(OCA\Settings ... {}, "enableApps")
/var/www/html/lib/private/AppFramework/App.php - line 156:
OC\AppFramework\Http\Dispatcher->dispatch(OCA\Settings ... {}, "enableApps")
/var/www/html/lib/private/Route/Router.php - line 302:
OC\AppFramework\App::main("OCA\\Settin ... r", "enableApps", OC\AppFramew ... {}, { _route: "s ... "})
/var/www/html/lib/base.php - line 1006:
OC\Route\Router->match("/settings/apps/enable")
/var/www/html/index.php - line 36:
OC::handleRequest()

Verursacht durch Doctrine\DBAL\Driver\PDO\Exception: SQLSTATE[42704]: Undefined object: 7 ERROR: index "primary" does not exist
/var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Connection.php - line 87:
Doctrine\DBAL\Driver\PDO\Exception::new(PDOException ... ]})
/var/www/html/3rdparty/doctrine/dbal/src/Connection.php - line 1062:
Doctrine\DBAL\Driver\PDO\Connection->query("DROP INDEX \"primary\"")
/var/www/html/lib/private/DB/Connection.php - line 236:
Doctrine\DBAL\Connection->executeQuery("DROP INDEX \"primary\"", [], [], null)
/var/www/html/3rdparty/doctrine/dbal/src/Connection.php - line 1809:
OC\DB\Connection->executeQuery("DROP INDEX \"primary\"")
/var/www/html/lib/private/DB/Migrator.php - line 175:
Doctrine\DBAL\Connection->query("DROP INDEX \"primary\"")
/var/www/html/lib/private/DB/Migrator.php - line 76:
OC\DB\Migrator->applySchema(Doctrine\DBAL\Schema\Schema {})
/var/www/html/lib/private/DB/Connection.php - line 532:
OC\DB\Migrator->migrate(Doctrine\DBAL\Schema\Schema {})
/var/www/html/lib/private/DB/MigrationService.php - line 463:
OC\DB\Connection->migrateToSchema(Doctrine\DBAL\Schema\Schema {})
/var/www/html/lib/private/DB/MigrationService.php - line 417:
OC\DB\MigrationService->migrateSchemaOnly("latest")
/var/www/html/lib/private/Installer.php - line 153:
OC\DB\MigrationService->migrate("latest", true)
/var/www/html/apps/settings/lib/Controller/AppSettingsController.php - line 452:
OC\Installer->installApp("cas")
/var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 217:
OCA\Settings\Controller\AppSettingsController->enableApps([ "cas"], [])
/var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 126:
OC\AppFramework\Http\Dispatcher->executeController(OCA\Settings ... {}, "enableApps")
/var/www/html/lib/private/AppFramework/App.php - line 156:
OC\AppFramework\Http\Dispatcher->dispatch(OCA\Settings ... {}, "enableApps")
/var/www/html/lib/private/Route/Router.php - line 302:
OC\AppFramework\App::main("OCA\\Settin ... r", "enableApps", OC\AppFramew ... {}, { _route: "s ... "})
/var/www/html/lib/base.php - line 1006:
OC\Route\Router->match("/settings/apps/enable")
/var/www/html/index.php - line 36:
OC::handleRequest()

Verursacht durch PDOException: SQLSTATE[42704]: Undefined object: 7 ERROR: index "primary" does not exist
/var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Connection.php - line 82:
PDO->query("DROP INDEX \"primary\"")
/var/www/html/3rdparty/doctrine/dbal/src/Connection.php - line 1062:
Doctrine\DBAL\Driver\PDO\Connection->query("DROP INDEX \"primary\"")
/var/www/html/lib/private/DB/Connection.php - line 236:
Doctrine\DBAL\Connection->executeQuery("DROP INDEX \"primary\"", [], [], null)
/var/www/html/3rdparty/doctrine/dbal/src/Connection.php - line 1809:
OC\DB\Connection->executeQuery("DROP INDEX \"primary\"")
/var/www/html/lib/private/DB/Migrator.php - line 175:
Doctrine\DBAL\Connection->query("DROP INDEX \"primary\"")
/var/www/html/lib/private/DB/Migrator.php - line 76:
OC\DB\Migrator->applySchema(Doctrine\DBAL\Schema\Schema {})
/var/www/html/lib/private/DB/Connection.php - line 532:
OC\DB\Migrator->migrate(Doctrine\DBAL\Schema\Schema {})
/var/www/html/lib/private/DB/MigrationService.php - line 463:
OC\DB\Connection->migrateToSchema(Doctrine\DBAL\Schema\Schema {})
/var/www/html/lib/private/DB/MigrationService.php - line 417:
OC\DB\MigrationService->migrateSchemaOnly("latest")
/var/www/html/lib/private/Installer.php - line 153:
OC\DB\MigrationService->migrate("latest", true)
/var/www/html/apps/settings/lib/Controller/AppSettingsController.php - line 452:
OC\Installer->installApp("cas")
/var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 217:
OCA\Settings\Controller\AppSettingsController->enableApps([ "cas"], [])
/var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 126:
OC\AppFramework\Http\Dispatcher->executeController(OCA\Settings ... {}, "enableApps")
/var/www/html/lib/private/AppFramework/App.php - line 156:
OC\AppFramework\Http\Dispatcher->dispatch(OCA\Settings ... {}, "enableApps")
/var/www/html/lib/private/Route/Router.php - line 302:
OC\AppFramework\App::main("OCA\\Settin ... r", "enableApps", OC\AppFramew ... {}, { _route: "s ... "})
/var/www/html/lib/base.php - line 1006:
OC\Route\Router->match("/settings/apps/enable")
/var/www/html/index.php - line 36:
OC::handleRequest()
JGuck commented 2 years ago

I did the database clean up form https://github.com/nextcloud/mail/issues/2383 this enables me to install version 0.2.7. The app is also working corect. so it is a migration thing

mziech commented 2 years ago

I can reproduce the problem locally if I try to apply the incremental update from 0.2.6 to 0.2.7. Seems like the SchemaWrapper does not handle dropping and creating the primary key on the same table within 1 migration well, if the database is PostgreSQL.

mziech commented 2 years ago

This failing incremental migration on PostgreSQL might be related to https://github.com/doctrine/dbal/issues/3736 (different DB, I know). Unfortunately, the provided workaround of splitting the migration steps does not work with Nextcloud as it seems to combine all migration steps implicitly. This behavior also makes the app work if you install it from a clean state.

As you noted & for the records, the workaround to restore this state is:

DELETE FROM oc_migrations WHERE app = 'cas';
DROP TABLE oc_cas_ticket;