TyperError 500 in /apps/notes/notes?pruneBefore & unique constraint error in "notes_meta_file_user_index"

[bentolor]

Not sure, but it might be related to creating a Note with special characters in the title like MyNote() #36 äöü (which fails) and then trying to rename it to a sane name. As my installation seems broken i can't test reproduce reproductoi steps

Can anybody give me a hint how to fix the duplicate key error without loosing data?

Steps to reproduce

1. Create a note with a title like MyNote() #36 äöü
2. Get an error; try to rename
3. Regularily receive HTTP 500 errors on client side and error messages on server side

Expected behaviour

No errors should happen

Actual behaviour

• Client reports HTTP 500 on backround requests like https://myserver/apps/notes/notes?pruneBefore=1626858276
• Server contains error in regular interval

Server

• Notes app version: 4.1.0 (latest)
• Nextcloud version: 22.0.0 (latest)
• Web server: Apache in Docker with Postgres. See https://github.com/bentolor/docker-nextcloud-collabora-postgresql-letsencrypt

Log files

TypeError: array_values() expects parameter 1 to be array, null given
    /var/www/html/custom_apps/notes/lib/Controller/NotesController.php - line 83:
    array_values(null)
    /var/www/html/custom_apps/notes/lib/Service/Util.php - line 28:
    OCA\Notes\Controller\NotesController->OCA\Notes\Controller\{closure}("*** sensiti ... *")
    /var/www/html/custom_apps/notes/lib/Controller/Helper.php - line 145:
    OCA\Notes\Service\Util::retryIfLocked(Closure {})
    /var/www/html/custom_apps/notes/lib/Controller/NotesController.php - line 95:
    OCA\Notes\Controller\Helper->handleErrorResponse(Closure {})
   /var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 217:
    OCA\Notes\Controller\NotesController->index(1626858276)
    /var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 126:
    OC\AppFramework\Http\Dispatcher->executeController(OCA\Notes\Co ... {}, "index")
    /var/www/html/lib/private/AppFramework/App.php - line 156:
    OC\AppFramework\Http\Dispatcher->dispatch(OCA\Notes\Co ... {}, "index")
    /var/www/html/lib/private/Route/Router.php - line 301:
    OC\AppFramework\App::main("OCA\\Notes\ ... r", "index", OC\AppFramew ... {}, { _route: "notes.notes.index"})
    /var/www/html/lib/base.php - line 1000:
    OC\Route\Router->match("/apps/notes/notes")
    /var/www/html/index.php - line 36:
    OC::handleRequest()




OC\DB\Exceptions\DbalException: An exception occurred while executing a query: SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "notes_meta_file_user_index" DETAIL: Key (file_id, user_id)=(15518, ben) already exists.
    /var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php - line 335:
    OC\DB\Exceptions\DbalException::wrap(Doctrine\DBA ... {})
    /var/www/html/lib/public/AppFramework/Db/QBMapper.php - line 139:
    OC\DB\QueryBuilder\QueryBuilder->executeStatement()
    /var/www/html/custom_apps/notes/lib/Service/MetaService.php - line 126:
    OCP\AppFramework\Db\QBMapper->insert(OCA\Notes\Db\Meta { id: null})
    /var/www/html/custom_apps/notes/lib/Service/MetaService.php - line 82:
    OCA\Notes\Service\MetaService->createMeta("ben", OCA\Notes\Service\Note {})
    /var/www/html/custom_apps/notes/lib/Controller/Helper.php - line 83:
    OCA\Notes\Service\MetaService->getAll("ben", [ OCA\Notes\ ... "])
    /var/www/html/custom_apps/notes/lib/Controller/NotesController.php - line 69:
    OCA\Notes\Controller\Helper->getNotesAndCategories(1626858276, [ "etag","content"])
   /var/www/html/custom_apps/notes/lib/Service/Util.php - line 28:
    OCA\Notes\Controller\NotesController->OCA\Notes\Controller\{closure}("*** sensiti ... *")
    /var/www/html/custom_apps/notes/lib/Controller/Helper.php - line 145:
    OCA\Notes\Service\Util::retryIfLocked(Closure {})
    /var/www/html/custom_apps/notes/lib/Controller/NotesController.php - line 95:
    OCA\Notes\Controller\Helper->handleErrorResponse(Closure {})
    /var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 217:
    OCA\Notes\Controller\NotesController->index(1626858276)
    /var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 126:
    OC\AppFramework\Http\Dispatcher->executeController(OCA\Notes\Co ... {}, "index")
    /var/www/html/lib/private/AppFramework/App.php - line 156:
    OC\AppFramework\Http\Dispatcher->dispatch(OCA\Notes\Co ... {}, "index")
    /var/www/html/lib/private/Route/Router.php - line 301:
    OC\AppFramework\App::main("OCA\\Notes\ ... r", "index", OC\AppFramew ... {}, { _route: "notes.notes.index"})
    /var/www/html/lib/base.php - line 1000:
    OC\Route\Router->match("/apps/notes/notes")
    /var/www/html/index.php - line 36:
    OC::handleRequest()

Caused by Doctrine\DBAL\Exception\UniqueConstraintViolationException: An exception occurred while executing a query: SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "notes_meta_file_user_index" DETAIL: Key (file_id, user_id)=(15518, ben) already exists.
    /var/www/html/3rdparty/doctrine/dbal/src/Connection.php - line 1728:
    Doctrine\DBAL\Driver\API\PostgreSQL\ExceptionConverter->convert(Doctrine\DBA ... {}, Doctrine\DBAL\Query {})
    /var/www/html/3rdparty/doctrine/dbal/src/Connection.php - line 1667:
    Doctrine\DBAL\Connection->handleDriverException(Doctrine\DBA ... {}, Doctrine\DBAL\Query {})
    /var/www/html/3rdparty/doctrine/dbal/src/Connection.php - line 1146:
    Doctrine\DBAL\Connection->convertExceptionDuringQuery(Doctrine\DBA ... {}, "INSERT INTO ... )", [ "ben",1551 ... "], [ 2,2,2,2,2, ... "])
    /var/www/html/lib/private/DB/Connection.php - line 262:
    Doctrine\DBAL\Connection->executeStatement("INSERT INTO ... )", [ "ben",1551 ... "], [ 2,2,2,2,2, ... "])
    /var/www/html/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php - line 213:
    OC\DB\Connection->executeStatement("INSERT INTO ... )", { 0: "And 1 ... "}, { 0: "And 1 ... 2})
    /var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php - line 287:
    Doctrine\DBAL\Query\QueryBuilder->execute()
   /var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php - line 333:
    OC\DB\QueryBuilder\QueryBuilder->execute()
    /var/www/html/lib/public/AppFramework/Db/QBMapper.php - line 139:
    OC\DB\QueryBuilder\QueryBuilder->executeStatement()
    /var/www/html/custom_apps/notes/lib/Service/MetaService.php - line 126:
    OCP\AppFramework\Db\QBMapper->insert(OCA\Notes\Db\Meta { id: null})
    /var/www/html/custom_apps/notes/lib/Service/MetaService.php - line 82:
    OCA\Notes\Service\MetaService->createMeta("ben", OCA\Notes\Service\Note {})
    /var/www/html/custom_apps/notes/lib/Controller/Helper.php - line 83:
    OCA\Notes\Service\MetaService->getAll("ben", [ OCA\Notes\ ... "])
    /var/www/html/custom_apps/notes/lib/Controller/NotesController.php - line 69:
    OCA\Notes\Controller\Helper->getNotesAndCategories(1626858276, [ "etag","content"])
    /var/www/html/custom_apps/notes/lib/Service/Util.php - line 28:
    OCA\Notes\Controller\NotesController->OCA\Notes\Controller\{closure}("*** sensiti ... *")
    /var/www/html/custom_apps/notes/lib/Controller/Helper.php - line 145:
    OCA\Notes\Service\Util::retryIfLocked(Closure {})
    /var/www/html/custom_apps/notes/lib/Controller/NotesController.php - line 95:
    OCA\Notes\Controller\Helper->handleErrorResponse(Closure {})
    /var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 217:
    OCA\Notes\Controller\NotesController->index(1626858276)
    /var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 126:
    OC\AppFramework\Http\Dispatcher->executeController(OCA\Notes\Co ... {}, "index")
    /var/www/html/lib/private/AppFramework/App.php - line 156:
    OC\AppFramework\Http\Dispatcher->dispatch(OCA\Notes\Co ... {}, "index")
    /var/www/html/lib/private/Route/Router.php - line 301:
    OC\AppFramework\App::main("OCA\\Notes\ ... r", "index", OC\AppFramew ... {}, { _route: "notes.notes.index"})
    /var/www/html/lib/base.php - line 1000:
    OC\Route\Router->match("/apps/notes/notes")
    /var/www/html/index.php - line 36:
    OC::handleRequest()

Caused by Doctrine\DBAL\Driver\PDO\Exception: SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "notes_meta_file_user_index" DETAIL: Key (file_id, user_id)=(15518, ben) already exists.
    /var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php - line 84:
    Doctrine\DBAL\Driver\PDO\Exception::new(PDOException ... ]})
    /var/www/html/3rdparty/doctrine/dbal/src/Connection.php - line 1136:
    Doctrine\DBAL\Driver\PDO\Statement->execute()
    /var/www/html/lib/private/DB/Connection.php - line 262:
    Doctrine\DBAL\Connection->executeStatement("INSERT INTO ... )", [ "ben",1551 ... "], [ 2,2,2,2,2, ... "])
    /var/www/html/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php - line 213:
    OC\DB\Connection->executeStatement("INSERT INTO ... )", { 0: "And 1 ... "}, { 0: "And 1 ... 2})
    /var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php - line 287:
    Doctrine\DBAL\Query\QueryBuilder->execute()
    /var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php - line 333:
    OC\DB\QueryBuilder\QueryBuilder->execute()
    /var/www/html/lib/public/AppFramework/Db/QBMapper.php - line 139:
    OC\DB\QueryBuilder\QueryBuilder->executeStatement()
    /var/www/html/custom_apps/notes/lib/Service/MetaService.php - line 126:
    OCP\AppFramework\Db\QBMapper->insert(OCA\Notes\Db\Meta { id: null})
    /var/www/html/custom_apps/notes/lib/Service/MetaService.php - line 82:
    OCA\Notes\Service\MetaService->createMeta("ben", OCA\Notes\Service\Note {})
    /var/www/html/custom_apps/notes/lib/Controller/Helper.php - line 83:
    OCA\Notes\Service\MetaService->getAll("ben", [ OCA\Notes\ ... "])
    /var/www/html/custom_apps/notes/lib/Controller/NotesController.php - line 69:
    OCA\Notes\Controller\Helper->getNotesAndCategories(1626858276, [ "etag","content"])
    /var/www/html/custom_apps/notes/lib/Service/Util.php - line 28:
    OCA\Notes\Controller\NotesController->OCA\Notes\Controller\{closure}("*** sensiti ... *")
    /var/www/html/custom_apps/notes/lib/Controller/Helper.php - line 145:
    OCA\Notes\Service\Util::retryIfLocked(Closure {})
    /var/www/html/custom_apps/notes/lib/Controller/NotesController.php - line 95:
    OCA\Notes\Controller\Helper->handleErrorResponse(Closure {})
    /var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 217:
    OCA\Notes\Controller\NotesController->index(1626858276)
    /var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 126:
    OC\AppFramework\Http\Dispatcher->executeController(OCA\Notes\Co ... {}, "index")
    /var/www/html/lib/private/AppFramework/App.php - line 156:
    OC\AppFramework\Http\Dispatcher->dispatch(OCA\Notes\Co ... {}, "index")
    /var/www/html/lib/private/Route/Router.php - line 301:
    OC\AppFramework\App::main("OCA\\Notes\ ... r", "index", OC\AppFramew ... {}, { _route: "notes.notes.index"})
    /var/www/html/lib/base.php - line 1000:
    OC\Route\Router->match("/apps/notes/notes")
    /var/www/html/index.php - line 36:
    OC::handleRequest()



Caused by PDOException: SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "notes_meta_file_user_index" DETAIL: Key (file_id, user_id)=(15518, ben) already exists.
    /var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php - line 82:
    PDOStatement->execute(null)
    /var/www/html/3rdparty/doctrine/dbal/src/Connection.php - line 1136:
    Doctrine\DBAL\Driver\PDO\Statement->execute()
    /var/www/html/lib/private/DB/Connection.php - line 262:
    Doctrine\DBAL\Connection->executeStatement("INSERT INTO ... )", [ "ben",1551 ... "], [ 2,2,2,2,2, ... "])
    /var/www/html/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php - line 213:
    OC\DB\Connection->executeStatement("INSERT INTO ... )", { 0: "And 1 ... "}, { 0: "And 1 ... 2})
    /var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php - line 287:
    Doctrine\DBAL\Query\QueryBuilder->execute()
    /var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php - line 333:
    OC\DB\QueryBuilder\QueryBuilder->execute()
    /var/www/html/lib/public/AppFramework/Db/QBMapper.php - line 139:
    OC\DB\QueryBuilder\QueryBuilder->executeStatement()
    /var/www/html/custom_apps/notes/lib/Service/MetaService.php - line 126:
   OCP\AppFramework\Db\QBMapper->insert(OCA\Notes\Db\Meta { id: null})
    /var/www/html/custom_apps/notes/lib/Service/MetaService.php - line 82:
    OCA\Notes\Service\MetaService->createMeta("ben", OCA\Notes\Service\Note {})
    /var/www/html/custom_apps/notes/lib/Controller/Helper.php - line 83:
    OCA\Notes\Service\MetaService->getAll("ben", [ OCA\Notes\ ... "])
    /var/www/html/custom_apps/notes/lib/Controller/NotesController.php - line 69:
    OCA\Notes\Controller\Helper->getNotesAndCategories(1626858276, [ "etag","content"])
    /var/www/html/custom_apps/notes/lib/Service/Util.php - line 28:
    OCA\Notes\Controller\NotesController->OCA\Notes\Controller\{closure}("*** sensiti ... *")
    /var/www/html/custom_apps/notes/lib/Controller/Helper.php - line 145:
    OCA\Notes\Service\Util::retryIfLocked(Closure {})
    /var/www/html/custom_apps/notes/lib/Controller/NotesController.php - line 95:
    OCA\Notes\Controller\Helper->handleErrorResponse(Closure {})
    /var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 217:
     OCA\Notes\Controller\NotesController->index(1626858276)
    /var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 126:
    OC\AppFramework\Http\Dispatcher->executeController(OCA\Notes\Co ... {}, "index")
    /var/www/html/lib/private/AppFramework/App.php - line 156:
    OC\AppFramework\Http\Dispatcher->dispatch(OCA\Notes\Co ... {}, "index")
    /var/www/html/lib/private/Route/Router.php - line 301:
    OC\AppFramework\App::main("OCA\\Notes\ ... r", "index", OC\AppFramew ... {}, { _route: "notes.notes.index"})
   /var/www/html/lib/base.php - line 1000:
    OC\Route\Router->match("/apps/notes/notes")
    /var/www/html/index.php - line 36:
    OC::handleRequest()

[korelstar]

Hey @bentolor ,

thanks for your detailed bug report! It looks like there was a critical change in Nextcloud 21 regarding database errors (see nextcloud/server#25091), that I wasn't aware of. This breaks a special error handling in the Notes app.

Unfortunately, I'm currently on vacation, so I can't release a fix in the next days. Maybe you could try a hotfix? You would have to change line 128 of lib/Service/MetaService.php: please replace \Doctrine\DBAL\Exception\UniqueConstraintViolationException with \OCP\DB\Exception.

With this hotfix, any duplicate key error should be ignored. That is okay, since it's just about a meta data cache and a duplicate key error can happen if there are multiple requests at the same time. But I'm unsure if this is true in your case.

You mention the title MyNote() #36 äöü as being problematic. In fact, this title should work without any problems. Are there any other log entries regarding this issue?

[bentolor]

Thanks, @korelstar for your helpful and extensive reply and your quick fix. I was also on the road so unable to test your hotfix. Today I already updated to the fresh release and it seems it fixes this issue.

Sorry for my late reply and really many many thanks for fixing so quickly!