SQL Errors since upgrade to 27.1.5

[tim_walls]

Hi,

I recently upgraded my NextCloud instance to 27.1.5; since then I’ve started seeing SQL errors in my PostgreSQL logs like this:

2023-12-29 06:00:01.624 UTC [153025] STATEMENT:  SELECT "a"."name" FROM "oc_filecache" "a" LEFT JOIN "oc_filecache" "b" ON CAST("a"."name" AS INT) = "b"."fileid" WHERE ("a"."storage" = $1) AND ("b"."fileid" IS NULL) AND ("a"."path" LIKE $2) AND ("a"."mimetype" = $3)
2023-12-29 07:00:06.333 UTC [153593] ERROR:  invalid input syntax for type integer: "2048-1152-max.png"

These happen roughly once an hour, which suggests it’s something being called in a cronjob.

It doesn’t look like a data error per-se; the query is just wrong - the oc_filecache.name field is a varchar, and this query seems to try to cast that to an integer. I’m not a PostgreSQL expert though, maybe that’s a thing that’s supposed to be possible :-).

Does anyone recognise where this query is coming from and what I can do to clean it up?

Installation details

Installed in K8s using the https://nextcloud.github.io/helm/ helm chart.

Operating System: Linux 5.15.0-91-generic x86_64

Database:

Type: pgsql
Version: PostgreSQL 14.10 (Debian 14.10-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
Size: 60.7 MB

PHP

Version: 8.2.14
Memory limit: 512 MB
Max execution time: 3600
Upload max size: 512 MB
Extensions: Core, date, libxml, openssl, pcre, sqlite3, zlib, ctype, curl, dom, fileinfo, filter, ftp, hash, iconv, json, mbstring, SPL, session, PDO, pdo_sqlite, standard, posix, random, Reflection, Phar, SimpleXML, tokenizer, xml, xmlreader, xmlwriter, mysqlnd, apache2handler, apcu, bcmath, exif, gd, gmp, imagick, intl, ldap, memcached, pcntl, pdo_mysql, pdo_pgsql, redis, sodium, sysvsem, zip, Zend OPcache

[jtr]

@tim_walls

2023-12-29 07:00:06.333 UTC [153593] ERROR: invalid input syntax for type integer: "2048-1152-max.png"

Yeah that looks like a bug.

Any chance there is a stack trace being generated in your Nextcloud Server log around the same time that seems correlated?

In any case, please report it to Issues · nextcloud/server · GitHub

[tim_walls]

Good question! Yes there is:

{"reqId":"DZI9Rup2jk8IiB2Sydfp","level":3,"time":"2024-01-01T08:10:01+00:00","remoteAddr":"","user":"--","app":"core","method":"","url":"--","message":"Error while running background job (class: OC\\Preview\\BackgroundCleanupJob, arguments: )","userAgent":"--","version":"27.1.5.1","exception":{"Exception":"Doctrine\\DBAL\\Exception\\DriverException","Message":"An exception occurred while executing a query: SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for type integer: \"2048-1152-max.png\"","Code":7,"Trace":[{"file":"/var/www/html/3rdparty/doctrine/dbal/src/Connection.php","line":1814,"function":"convert","class":"Doctrine\\DBAL\\Driver\\API\\PostgreSQL\\ExceptionConverter","type":"->","args":[["Doctrine\\DBAL\\Driver\\PDO\\Exception"],["Doctrine\\DBAL\\Query"]]},{"file":"/var/www/html/3rdparty/doctrine/dbal/src/Connection.php","line":1749,"function":"handleDriverException","class":"Doctrine\\DBAL\\Connection","type":"->","args":[["Doctrine\\DBAL\\Driver\\PDO\\Exception"],["Doctrine\\DBAL\\Query"]]},{"file":"/var/www/html/3rdparty/doctrine/dbal/src/Connection.php","line":1055,"function":"convertExceptionDuringQuery","class":"Doctrine\\DBAL\\Connection","type":"->","args":[["Doctrine\\DBAL\\Driver\\PDO\\Exception"],"SELECT \"a\".\"name\" FROM \"oc_filecache\" \"a\" LEFT JOIN \"oc_filecache\" \"b\" ON CAST(\"a\".\"name\" AS INT) = \"b\".\"fileid\" WHERE (\"a\".\"storage\" = ?) AND (\"b\".\"fileid\" IS NULL) AND (\"a\".\"path\" LIKE ?) AND (\"a\".\"mimetype\" = ?)",[2,"appdata\\_oc1042kcyi8o/preview/_/_/_/_/_/_/_/%",2],[2,2,2]]},{"file":"/var/www/html/lib/private/DB/Connection.php","line":262,"function":"executeQuery","class":"Doctrine\\DBAL\\Connection","type":"->","args":["SELECT \"a\".\"name\" FROM \"oc_filecache\" \"a\" LEFT JOIN \"oc_filecache\" \"b\" ON CAST(\"a\".\"name\" AS INT) = \"b\".\"fileid\" WHERE (\"a\".\"storage\" = ?) AND (\"b\".\"fileid\" IS NULL) AND (\"a\".\"path\" LIKE ?) AND (\"a\".\"mimetype\" = ?)",[2,"appdata\\_oc1042kcyi8o/preview/_/_/_/_/_/_/_/%",2],[2,2,2],null]},{"file":"/var/www/html/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php","line":345,"function":"executeQuery","class":"OC\\DB\\Connection","type":"->","args":["SELECT \"a\".\"name\" FROM \"oc_filecache\" \"a\" LEFT JOIN \"oc_filecache\" \"b\" ON CAST(\"a\".\"name\" AS INT) = \"b\".\"fileid\" WHERE (\"a\".\"storage\" = :dcValue1) AND (\"b\".\"fileid\" IS NULL) AND (\"a\".\"path\" LIKE :dcValue2) AND (\"a\".\"mimetype\" = :dcValue3)",[2,"appdata\\_oc1042kcyi8o/preview/_/_/_/_/_/_/_/%",2],[2,2,2]]},{"file":"/var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php","line":280,"function":"execute","class":"Doctrine\\DBAL\\Query\\QueryBuilder","type":"->","args":[]},{"file":"/var/www/html/lib/private/Preview/BackgroundCleanupJob.php","line":165,"function":"execute","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->","args":[]},{"file":"/var/www/html/lib/private/Preview/BackgroundCleanupJob.php","line":80,"function":"getNewPreviewLocations","class":"OC\\Preview\\BackgroundCleanupJob","type":"->","args":[]},{"file":"/var/www/html/lib/private/Preview/BackgroundCleanupJob.php","line":66,"function":"getDeletedFiles","class":"OC\\Preview\\BackgroundCleanupJob","type":"->","args":[]},{"file":"/var/www/html/lib/public/BackgroundJob/Job.php","line":81,"function":"run","class":"OC\\Preview\\BackgroundCleanupJob","type":"->","args":[null]},{"file":"/var/www/html/lib/public/BackgroundJob/TimedJob.php","line":103,"function":"start","class":"OCP\\BackgroundJob\\Job","type":"->","args":[["OC\\BackgroundJob\\JobList"]]},{"file":"/var/www/html/lib/public/BackgroundJob/TimedJob.php","line":93,"function":"start","class":"OCP\\BackgroundJob\\TimedJob","type":"->","args":[["OC\\BackgroundJob\\JobList"]]},{"file":"/var/www/html/cron.php","line":152,"function":"execute","class":"OCP\\BackgroundJob\\TimedJob","type":"->","args":[["OC\\BackgroundJob\\JobList"],["OC\\Log"]]}],"File":"/var/www/html/3rdparty/doctrine/dbal/src/Driver/API/PostgreSQL/ExceptionConverter.php","Line":91,"Previous":{"Exception":"Doctrine\\DBAL\\Driver\\PDO\\Exception","Message":"SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for type integer: \"2048-1152-max.png\"","Code":7,"Trace":[{"file":"/var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php","line":103,"function":"new","class":"Doctrine\\DBAL\\Driver\\PDO\\Exception","type":"::","args":[["PDOException",["22P02",7,"ERROR:  invalid input syntax for type integer: \"2048-1152-max.png\""]]]},{"file":"/var/www/html/3rdparty/doctrine/dbal/src/Connection.php","line":1045,"function":"execute","class":"Doctrine\\DBAL\\Driver\\PDO\\Statement","type":"->","args":[]},{"file":"/var/www/html/lib/private/DB/Connection.php","line":262,"function":"executeQuery","class":"Doctrine\\DBAL\\Connection","type":"->","args":["SELECT \"a\".\"name\" FROM \"oc_filecache\" \"a\" LEFT JOIN \"oc_filecache\" \"b\" ON CAST(\"a\".\"name\" AS INT) = \"b\".\"fileid\" WHERE (\"a\".\"storage\" = ?) AND (\"b\".\"fileid\" IS NULL) AND (\"a\".\"path\" LIKE ?) AND (\"a\".\"mimetype\" = ?)",[2,"appdata\\_oc1042kcyi8o/preview/_/_/_/_/_/_/_/%",2],[2,2,2],null]},{"file":"/var/www/html/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php","line":345,"function":"executeQuery","class":"OC\\DB\\Connection","type":"->","args":["SELECT \"a\".\"name\" FROM \"oc_filecache\" \"a\" LEFT JOIN \"oc_filecache\" \"b\" ON CAST(\"a\".\"name\" AS INT) = \"b\".\"fileid\" WHERE (\"a\".\"storage\" = :dcValue1) AND (\"b\".\"fileid\" IS NULL) AND (\"a\".\"path\" LIKE :dcValue2) AND (\"a\".\"mimetype\" = :dcValue3)",[2,"appdata\\_oc1042kcyi8o/preview/_/_/_/_/_/_/_/%",2],[2,2,2]]},{"file":"/var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php","line":280,"function":"execute","class":"Doctrine\\DBAL\\Query\\QueryBuilder","type":"->","args":[]},{"file":"/var/www/html/lib/private/Preview/BackgroundCleanupJob.php","line":165,"function":"execute","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->","args":[]},{"file":"/var/www/html/lib/private/Preview/BackgroundCleanupJob.php","line":80,"function":"getNewPreviewLocations","class":"OC\\Preview\\BackgroundCleanupJob","type":"->","args":[]},{"file":"/var/www/html/lib/private/Preview/BackgroundCleanupJob.php","line":66,"function":"getDeletedFiles","class":"OC\\Preview\\BackgroundCleanupJob","type":"->","args":[]},{"file":"/var/www/html/lib/public/BackgroundJob/Job.php","line":81,"function":"run","class":"OC\\Preview\\BackgroundCleanupJob","type":"->","args":[null]},{"file":"/var/www/html/lib/public/BackgroundJob/TimedJob.php","line":103,"function":"start","class":"OCP\\BackgroundJob\\Job","type":"->","args":[["OC\\BackgroundJob\\JobList"]]},{"file":"/var/www/html/lib/public/BackgroundJob/TimedJob.php","line":93,"function":"start","class":"OCP\\BackgroundJob\\TimedJob","type":"->","args":[["OC\\BackgroundJob\\JobList"]]},{"file":"/var/www/html/cron.php","line":152,"function":"execute","class":"OCP\\BackgroundJob\\TimedJob","type":"->","args":[["OC\\BackgroundJob\\JobList"],["OC\\Log"]]}],"File":"/var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Exception.php","Line":30,"Previous":{"Exception":"PDOException","Message":"SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for type integer: \"2048-1152-max.png\"","Code":"22P02","Trace":[{"file":"/var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php","line":101,"function":"execute","class":"PDOStatement","type":"->","args":[null]},{"file":"/var/www/html/3rdparty/doctrine/dbal/src/Connection.php","line":1045,"function":"execute","class":"Doctrine\\DBAL\\Driver\\PDO\\Statement","type":"->","args":[]},{"file":"/var/www/html/lib/private/DB/Connection.php","line":262,"function":"executeQuery","class":"Doctrine\\DBAL\\Connection","type":"->","args":["SELECT \"a\".\"name\" FROM \"oc_filecache\" \"a\" LEFT JOIN \"oc_filecache\" \"b\" ON CAST(\"a\".\"name\" AS INT) = \"b\".\"fileid\" WHERE (\"a\".\"storage\" = ?) AND (\"b\".\"fileid\" IS NULL) AND (\"a\".\"path\" LIKE ?) AND (\"a\".\"mimetype\" = ?)",[2,"appdata\\_oc1042kcyi8o/preview/_/_/_/_/_/_/_/%",2],[2,2,2],null]},{"file":"/var/www/html/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php","line":345,"function":"executeQuery","class":"OC\\DB\\Connection","type":"->","args":["SELECT \"a\".\"name\" FROM \"oc_filecache\" \"a\" LEFT JOIN \"oc_filecache\" \"b\" ON CAST(\"a\".\"name\" AS INT) = \"b\".\"fileid\" WHERE (\"a\".\"storage\" = :dcValue1) AND (\"b\".\"fileid\" IS NULL) AND (\"a\".\"path\" LIKE :dcValue2) AND (\"a\".\"mimetype\" = :dcValue3)",[2,"appdata\\_oc1042kcyi8o/preview/_/_/_/_/_/_/_/%",2],[2,2,2]]},{"file":"/var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php","line":280,"function":"execute","class":"Doctrine\\DBAL\\Query\\QueryBuilder","type":"->","args":[]},{"file":"/var/www/html/lib/private/Preview/BackgroundCleanupJob.php","line":165,"function":"execute","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->","args":[]},{"file":"/var/www/html/lib/private/Preview/BackgroundCleanupJob.php","line":80,"function":"getNewPreviewLocations","class":"OC\\Preview\\BackgroundCleanupJob","type":"->","args":[]},{"file":"/var/www/html/lib/private/Preview/BackgroundCleanupJob.php","line":66,"function":"getDeletedFiles","class":"OC\\Preview\\BackgroundCleanupJob","type":"->","args":[]},{"file":"/var/www/html/lib/public/BackgroundJob/Job.php","line":81,"function":"run","class":"OC\\Preview\\BackgroundCleanupJob","type":"->","args":[null]},{"file":"/var/www/html/lib/public/BackgroundJob/TimedJob.php","line":103,"function":"start","class":"OCP\\BackgroundJob\\Job","type":"->","args":[["OC\\BackgroundJob\\JobList"]]},{"file":"/var/www/html/lib/public/BackgroundJob/TimedJob.php","line":93,"function":"start","class":"OCP\\BackgroundJob\\TimedJob","type":"->","args":[["OC\\BackgroundJob\\JobList"]]},{"file":"/var/www/html/cron.php","line":152,"function":"execute","class":"OCP\\BackgroundJob\\TimedJob","type":"->","args":[["OC\\BackgroundJob\\JobList"],["OC\\Log"]]}],"File":"/var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php","Line":101}},"message":"Error while running background job (class: OC\\Preview\\BackgroundCleanupJob, arguments: )","exception":{},"CustomMessage":"Error while running background job (class: OC\\Preview\\BackgroundCleanupJob, arguments: )"}}

Edited to add - opened a ticket @ [Bug]: SQL errors generated by background job OC\\Preview\\BackgroundCleanupJob · Issue #42540 · nextcloud/server · GitHub

Thank you!