Notes app causes server exceptions (due orphaned favorites?)

[J0WI]

Steps to reproduce

I haven't figured out the STR this, but it seems to be related to the notes app and orphaned favorites.

1. Let a server run for some time and connect with the notes app to it
2. Play with the notes app for a while
3. After some time the errors will show up in the logs: ERROR: invalid input syntax for integer: ""

Server configuration

Operating system: Debian GNU/Linux 10

Web server: nginx

Database: PostgreSQL 10

PHP version: 7.3

Nextcloud version: 16.0.5.1

Updated from an older Nextcloud/ownCloud or fresh install: fresh install

Where did you install Nextcloud from: Docker

Signing status:

Signing status

No errors have been found.

List of activated apps: _defaults with encryption

Nextcloud configuration: defaults with redis

Are you using encryption: yes

Client configuration

Browser: Mozilla/5.0 (Linux) mirall/2.5.2git (Nextcloud)

Operating system: Android

Logs

Web server error log

``` app: core method: POST url: /index.php/apps/notes/api/v0.2/notes message: Exception: Doctrine\DBAL\Exception\DriverException Message: An exception occurred while executing 'DELETE FROM "oc_vcategory_to_object" WHERE "objid" = ? AND "categoryid" = ? AND "type" = ?' with params [*** ID ***,false,"files"]:

SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: ""
Code: 0
Trace:
file: /var/www/html/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php
line: 184
function: convertException
class: Doctrine\DBAL\Driver\AbstractPostgreSQLDriver
args: ["An exception occurred while executing 'DELETE FROM "oc_vcategory_to_object" WHERE "objid" = ? AND "categoryid" = ? AND "type" = ?' with params [*** ID ***,false,"files"]: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: """,{"errorInfo": ["22P02",7,"ERROR: invalid input syntax for integer: """],"class": "Doctrine\DBAL\Driver\PDOException"}]

	file: /var/www/html/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php
	line: 158,
	function: wrapException
	class: Doctrine\DBAL\DBALException
	args: [{"__class__": "Doctrine\DBAL\Driver\PDOPgSql\Driver"},{"errorInfo": ["22P02",7,"ERROR: invalid input syntax for integer: """],"__class__": "Doctrine\DBAL\Driver\PDOException"},"An exception occurred while executing 'DELETE FROM "oc_vcategory_to_object" WHERE "objid" = ? AND "categoryid" = ? AND "type" = ?' with params [*** ID ***,false,"files"]: 

SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: """]

	file: /var/www/html/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Statement.php
	line: 178
	function: driverExceptionDuringQuery
	class: Doctrine\DBAL\DBALException
	args: [{"__class__": "Doctrine\DBAL\Driver\PDOPgSql\Driver","errorInfo": ["22P02",7,"ERROR: invalid input syntax for integer: """],"__class__": "Doctrine\DBAL\Driver\PDOException"},"DELETE FROM "oc_vcategory_to_object" WHERE "objid" = ? AND "categoryid" = ? AND "type" = ?",{"1": "*** sensitive parameter replaced ***","2": "*** sensitive parameter replaced ***","3": "files"}]

	file: /var/www/html/lib/private/legacy/db/statementwrapper.php",
	line: 71,
	function: execute
	class: Doctrine\DBAL\Statement
	args: [["*** sensitive parameter replaced ***","*** sensitive parameter replaced ***","files"]]

	file: /var/www/html/lib/private/Tags.php
	line: 728
	function: execute
	class: OC_DB_StatementWrapper
	args: [["*** sensitive parameter replaced ***","*** sensitive parameter replaced ***","files"]]

	file: /var/www/html/lib/private/Tags.php
	line: 663
	function: unTag
	class: OC\Tags
	args: ["*** sensitive parameter replaced ***","_$!<Favorite>!$_"]

	file: /var/www/html/custom_apps/notes/lib/Service/NotesService.php
	line: 220
	function: removeFromFavorites
	class: OC\Tags
	args: ["*** sensitive parameter replaced ***"]

	file: /var/www/html/custom_apps/notes/lib/Controller/NotesApiController.php
	line: 166
	function: favorite
	class: OCA\Notes\Service\NotesService
	args: ["*** sensitive parameter replaced ***","*** sensitive parameter replaced ***","*** sensitive parameter replaced ***"]

	file: /var/www/html/custom_apps/notes/lib/Controller/NotesApiController.php
	line: 133
	function: updateData
	class: OCA\Notes\Controller\NotesApiController
	args: ["*** sensitive parameters replaced ***"]

	file: /var/www/html/custom_apps/notes/lib/Controller/Errors.php
	line: 22
	function: OCA\Notes\Controller\{closure}
	class: OCA\Notes\Controller\NotesApiController
	args: ["*** sensitive parameters replaced ***"]

	file: /var/www/html/custom_apps/notes/lib/Controller/NotesApiController.php
	line: 134
	function: respond
	class: OCA\Notes\Controller\NotesApiController
	args: [{"__class__": "Closure"}]

	file: /var/www/html/lib/private/AppFramework/Http/Dispatcher.php
	line: 166
	function: create
	class: OCA\Notes\Controller\NotesApiController
	args: ["*** sensitive parameter replaced ***","*** sensitive parameter replaced ***","*** sensitive parameter replaced ***","*** sensitive parameter replaced ***"]

	file: /var/www/html/lib/private/AppFramework/Http/Dispatcher.php
	line: 99
	function: executeController
	class: OC\AppFramework\Http\Dispatcher
	args: [{"__class__": "OCA\Notes\Controller\NotesApiController"},"create"]

	file: /var/www/html/lib/private/AppFramework/App.php
	line: 126
	function: dispatch
	class: OC\AppFramework\Http\Dispatcher
	args: [{"__class__": "OCA\Notes\Controller\NotesApiController"},"create"]

	file: /var/www/html/lib/private/AppFramework/Routing/RouteActionHandler.php
	line: 47
	function: main
	class: OC\AppFramework\App
	args: ["OCA\Notes\Controller\NotesApiController","create",{"__class__": "OC\AppFramework\DependencyInjection\DIContainer"},{"_route": "notes.notes_api.create"}]

	function: __invoke
	class: OC\AppFramework\Routing\RouteActionHandler
	args: [{"_route": "notes.notes_api.create"}]

	file: /var/www/html/lib/private/Route/Router.php
	line: 297
	function: call_user_func
	args: [{"__class__": "OC\AppFramework\Routing\RouteActionHandler"},{"_route": "notes.notes_api.create"}]

	file: /var/www/html/lib/base.php
	line: 975
	function: match
	class: OC\Route\Router
	args: /apps/notes/api/v0.2/notes

	file: /var/www/html/index.php
	line: 42
	function: handleRequest
	class: OC

</details>

[korelstar]

Play with the notes app for a while

Please be more specific: what did you do exactly before the error happened? Which app do you mean: the web app or the Android app?

Why do you think the error cause are orphaned favorites? Did the file with ID 520338 exist when the error happened?

Did the log entries occur only once or do they appear repeatedly? When do they occur?

[J0WI]

This error occurs quite frequently when editing existing notes with the Android app.

[korelstar]

Thanks for your feedback. Does the Android app show any error or is synchronization still possible?

Does the error log always contain the same file ID or are they different? Does that file exists in your Nextcloud?

@ all : Does someone had this issue, too?

[J0WI]

Does the Android app show any error or is synchronization still possible?

There is no error in the app.

Does the error log always contain the same file ID or are they different? Does that file exists in your Nextcloud?

There are various IDs, but all of them exist in the database and on the filesystem.

[korelstar]

Does the Notes app work in your browser? If yes: Could you please try to add a note to favorites using the browser Notes app and try synchronizing the Android Notes app again?

It looks that your Nextcloud user does not know anything about favorites (it's a special tag that is stored in the vcategory database table), but the Android app tries to remove some notes from favorites. I don't know why this happens, but maybe we can fix this by letting your Nextcloud user know the favorite tag.

---

Maybe we should do an additional check in server's OC\Tags::unTag: Only try to remove a tag from an object if the user has this tag (similar to nextcloud/server#10409).

[J0WI]

Does the Notes app work in your browser?

yes

Could you please try to add a note to favorites using the browser Notes app and try synchronizing the Android Notes app again?

no error occurred

[korelstar]

Thanks for the feedback. I did some improvements in #409 which should fix this issue then.