The Basics
- Nextcloud Server version: 30.0.4
- Operating system and version: FreeBSD 14.2-RELEASE-p1
- Web server and version: Apache 2.4.62
- PHP version: 8.3.19
- Is this the first time you’ve seen this error? Yes
- When did this problem seem to first start? When I scanned my full set of files
- Installation method: pkg
- Are you using CloudfIare, mod_security, or similar? no
Summary of the issue you are facing:
I have a new installation, via binary packages, of Nextcloud on my FreeBSD home server. I got everything (fixed all the errors and warnings in the admin settings page) working, and then I copied in my files, including some full Android phone backups and multiple copies of lots of music files with weird characters in them.
When I run occ files:scan <user> I get this error output:
Exception during scan: An exception occurred while executing a query: SQLSTATE[25P02]: In failed sql transaction: 7 ERROR: current transaction is aborted, commands ignored until end of transaction block
#0 /usr/local/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php(1939): Doctrine\DBAL\Driver\API\PostgreSQL\ExceptionConverter->convert(Object(Doctrine\DBAL\Driver\PDO\Exception), Object(Doctrine\DBAL\Query))
#1 /usr/local/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php(1881): Doctrine\DBAL\Connection->handleDriverException(Object(Doctrine\DBAL\Driver\PDO\Exception), Object(Doctrine\DBAL\Query))
#2 /usr/local/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php(1106): Doctrine\DBAL\Connection->convertExceptionDuringQuery(Object(Doctrine\DBAL\Driver\PDO\Exception), 'SELECT "filecac...', Array, Array)
#3 /usr/local/www/nextcloud/lib/private/DB/Connection.php(415): Doctrine\DBAL\Connection->executeQuery('SELECT "filecac...', Array, Array, NULL)
#4 /usr/local/www/nextcloud/lib/private/DB/ConnectionAdapter.php(50): OC\DB\Connection->executeQuery('SELECT "filecac...', Array, Array)
#5 /usr/local/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php(289): OC\DB\ConnectionAdapter->executeQuery('SELECT \`filecac...', Array, Array)
#6 /usr/local/www/nextcloud/lib/private/DB/QueryBuilder/ExtendedQueryBuilder.php(285): OC\DB\QueryBuilder\QueryBuilder->executeQuery(Object(OC\DB\ConnectionAdapter))
#7 /usr/local/www/nextcloud/lib/private/DB/QueryBuilder/ExtendedQueryBuilder.php(53): OC\DB\QueryBuilder\ExtendedQueryBuilder->executeQuery(NULL)
#8 /usr/local/www/nextcloud/lib/private/Files/Cache/Cache.php(208): OC\DB\QueryBuilder\ExtendedQueryBuilder->execute()
#9 /usr/local/www/nextcloud/lib/private/Files/Cache/Scanner.php(401): OC\Files\Cache\Cache->getFolderContentsById(333312)
#10 /usr/local/www/nextcloud/lib/private/Files/Cache/Scanner.php(467): OC\Files\Cache\Scanner->getExistingChildren(333312)
#11 /usr/local/www/nextcloud/lib/private/Files/Cache/Scanner.php(425): OC\Files\Cache\Scanner->handleChildren('files/oldboxes/...', true, 3, 333312, true, 0, false)
#12 /usr/local/www/nextcloud/lib/private/Files/Cache/Scanner.php(430): OC\Files\Cache\Scanner->scanChildren('files/oldboxes/...', true, 3, 333312, true, 221298797, false)
#13 /usr/local/www/nextcloud/lib/private/Files/Cache/Scanner.php(430): OC\Files\Cache\Scanner->scanChildren('files/oldboxes/...', true, 3, 332971, true, 7808046588, 0)
#14 /usr/local/www/nextcloud/lib/private/Files/Cache/Scanner.php(430): OC\Files\Cache\Scanner->scanChildren('files/oldboxes/...', true, 3, 332715, true, -1, 1)
#15 /usr/local/www/nextcloud/lib/private/Files/Cache/Scanner.php(430): OC\Files\Cache\Scanner->scanChildren('files/oldboxes', true, 3, 136752, true, -1, 1)
#16 /usr/local/www/nextcloud/lib/private/Files/Cache/Scanner.php(430): OC\Files\Cache\Scanner->scanChildren('files', true, 3, 130, true, -1, 1)
#17 /usr/local/www/nextcloud/lib/private/Files/Cache/Scanner.php(331): OC\Files\Cache\Scanner->scanChildren('', true, 3, 126, true, 217451873563)
#18 /usr/local/www/nextcloud/lib/private/Files/Utils/Scanner.php(255): OC\Files\Cache\Scanner->scan('', true, 3)
#19 /usr/local/www/nextcloud/apps-pkg/files/lib/Command/Scan.php(153): OC\Files\Utils\Scanner->scan('/evan', true, NULL)
#20 /usr/local/www/nextcloud/apps-pkg/files/lib/Command/Scan.php(214): OCA\Files\Command\Scan->scanFiles('evan', '/evan', NULL, Object(Symfony\Component\Console\Output\ConsoleOutput), false, true, false)
#21 /usr/local/www/nextcloud/3rdparty/symfony/console/Command/Command.php(326): OCA\Files\Command\Scan->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#22 /usr/local/www/nextcloud/core/Command/Base.php(161): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#23 /usr/local/www/nextcloud/3rdparty/symfony/console/Application.php(1078): OC\Core\Command\Base->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#24 /usr/local/www/nextcloud/3rdparty/symfony/console/Application.php(324): Symfony\Component\Console\Application->doRunCommand(Object(OCA\Files\Command\Scan), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#25 /usr/local/www/nextcloud/3rdparty/symfony/console/Application.php(175): Symfony\Component\Console\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#26 /usr/local/www/nextcloud/lib/private/Console/Application.php(183): Symfony\Component\Console\Application->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#27 /usr/local/www/nextcloud/console.php(87): OC\Console\Application->run(Object(Symfony\Component\Console\Input\ArgvInput))
#28 /usr/local/www/nextcloud/occ(11): require_once('/usr/local/www/...')
#29 {main}
The PostgreSQL logs have this:
2025-03-25 19:47:58.145 PDT [40197] ERROR: 22021: invalid byte sequence for encoding "UTF8": 0xe9 0x29 0x2e
2025-03-25 19:47:58.145 PDT [40197] CONTEXT: unnamed portal parameter $8
2025-03-25 19:47:58.145 PDT [40197] LOCATION: report_invalid_encoding, mbutils.c:1727
2025-03-25 19:47:58.145 PDT [40197] STATEMENT: INSERT INTO "oc_filecache" ("mimepart", "mimetype", "mtime", "size", "etag", "storage_mtime", "permissions", "name", "parent", "checksum", "path_hash", "path", "storage") VALUES($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
so it looks to me like it’s trying to add a row to oc_filecache but the filename string isn’t valid unicode so the DB won’t take it. the problem is, I don’t know which file.
I figured out what folders were likely problematic (backed up Firefox profiles and cache) and zipped up the files into .tgz archives, but that just delayed hitting the same error.
I used a couple find commands looking for regexes from this page and this thread, and shell scripts to rename them with those characters stripped out. It definitely found and fixed filenames, but the scan is still getting stuck, just now in a different spot.
my questions:
- is my assessment of a borked filename reasonable? provable?
- if so, how can I locate the problematic file(s)? I’ve tried changing the logging settings in
postgresql.confbut I can’t get the actual SQL statement in the logs. (I don’t know Postgres super well.)
Configuration
Nextcloud
{
"system": {
"apps_paths": [
{
"path": "\/usr\/local\/www\/nextcloud\/apps",
"url": "\/apps",
"writable": true
},
{
"path": "\/usr\/local\/www\/nextcloud\/apps-pkg",
"url": "\/apps-pkg",
"writable": false
}
],
"logfile": "\/var\/log\/nextcloud\/nextcloud.log",
"loglevel": 0,
"memcache.local": "\\OC\\Memcache\\Redis",
"memcache.locking": "\\OC\\Memcache\\Redis",
"redis": {
"host": "***REMOVED SENSITIVE VALUE***",
"port": 6379,
"timeout": 1.5,
"read_timeout": 1.5
},
"instanceid": "***REMOVED SENSITIVE VALUE***",
"passwordsalt": "***REMOVED SENSITIVE VALUE***",
"secret": "***REMOVED SENSITIVE VALUE***",
"trusted_domains": [
"nc.tordek.net"
],
"0": [
"192.168.0.220"
],
"datadirectory": "***REMOVED SENSITIVE VALUE***",
"dbtype": "pgsql",
"version": "30.0.4.1",
"dbname": "***REMOVED SENSITIVE VALUE***",
"dbhost": "***REMOVED SENSITIVE VALUE***",
"dbport": "",
"dbtableprefix": "oc_",
"dbuser": "***REMOVED SENSITIVE VALUE***",
"dbpassword": "***REMOVED SENSITIVE VALUE***",
"installed": true,
"memories.db.triggers.fcu": true,
"memories.exiftool_no_local": true,
"memories.vod.path": "\/usr\/local\/www\/nextcloud\/apps\/memories\/bin-ext\/go-vod-amd64",
"maintenance": false,
"memories.vod.ffmpeg": "\/usr\/local\/bin\/ffmpeg",
"memories.vod.ffprobe": "\/usr\/local\/bin\/ffprobe",
"default_phone_region": "US",
"maintenance_window_start": 11,
"overwrite.cli.url": "https:\/\/nc.tordek.net",
"htaccess.RewriteBase": "\/",
"mail_smtpmode": "smtp",
"mail_smtphost": "***REMOVED SENSITIVE VALUE***",
"mail_sendmailmode": "smtp",
"mail_smtpport": "587",
"mail_from_address": "***REMOVED SENSITIVE VALUE***",
"mail_domain": "***REMOVED SENSITIVE VALUE***",
"mail_smtpauth": 1,
"mail_smtpname": "***REMOVED SENSITIVE VALUE***",
"mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
"overwritehost": "nc.tordek.net",
"overwriteprotocol": "https"
}
}
postgresql.conf (log section only)
#------------------------------------------------------------------------------
# REPORTING AND LOGGING
#------------------------------------------------------------------------------
# - Where to Log -
#log_destination = 'syslog,stderr'
log_destination = 'stderr' # Valid values are combinations of
# stderr, csvlog, jsonlog, syslog, and
# eventlog, depending on platform.
# csvlog and jsonlog require
# logging_collector to be on.
# This is used when logging to stderr:
logging_collector = on # Enable capturing of stderr, jsonlog,
# and csvlog into log files. Required
# to be on for csvlogs and jsonlogs.
# (change requires restart)
# These are only used if logging_collector is on:
log_directory = '/var/log/postgresql/' # directory where log files are written,
# can be absolute or relative to PGDATA
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
# can include strftime() escapes
#log_file_mode = 0600 # creation mode for log files,
# begin with 0 to use octal notation
log_rotation_age = 1d # Automatic rotation of logfiles will
# happen after that time. 0 disables.
log_rotation_size = 10MB # Automatic rotation of logfiles will
# happen after that much log output.
# 0 disables.
#log_truncate_on_rotation = off # If on, an existing log file with the
# same name as the new log file will be
# truncated rather than appended to.
# But such truncation only occurs on
# time-driven rotation, not on restarts
# or size-driven rotation. Default is
# off, meaning append to existing files
# in all cases.
# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
#syslog_sequence_numbers = on
#syslog_split_messages = on
# This is only relevant when logging to eventlog (Windows):
# (change requires restart)
#event_source = 'PostgreSQL'
# - When to Log -
log_min_messages = debug5 # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic
log_min_error_statement = debug5 # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic (effectively off)
log_min_duration_statement = 0 # -1 is disabled, 0 logs all statements
# and their durations, > 0 logs only
# statements running at least this number
# of milliseconds
#log_min_duration_sample = -1 # -1 is disabled, 0 logs a sample of statements
# and their durations, > 0 logs only a sample of
# statements running at least this number
# of milliseconds;
# sample fraction is determined by log_statement_sample_rate
#log_statement_sample_rate = 1.0 # fraction of logged statements exceeding
# log_min_duration_sample to be logged;
# 1.0 logs all such statements, 0.0 never logs
#log_transaction_sample_rate = 1.0 # fraction of transactions whose statements
# are logged regardless of their duration; 1.0 logs all
# statements from all transactions, 0.0 never logs
#log_startup_progress_interval = 10s # Time between progress updates for
# long-running startup operations.
# 0 disables the feature, > 0 indicates
# the interval in milliseconds.
# - What to Log -
debug_print_parse = off
debug_print_rewritten = off
debug_print_plan = off
debug_pretty_print = on
log_autovacuum_min_duration = 10min # log autovacuum activity;
# -1 disables, 0 logs all actions and
# their durations, > 0 logs only
# actions running at least this number
# of milliseconds.
log_checkpoints = on
#log_connections = off
#log_disconnections = off
log_duration = on
log_error_verbosity = verbose # terse, default, or verbose messages
#log_hostname = off
#log_line_prefix = '%m [%p] ' # special values:
# %a = application name
# %u = user name
# %d = database name
# %r = remote host and port
# %h = remote host
# %b = backend type
# %p = process ID
# %P = process ID of parallel group leader
# %t = timestamp without milliseconds
# %m = timestamp with milliseconds
# %n = timestamp with milliseconds (as a Unix epoch)
# %Q = query ID (0 if none or not computed)
# %i = command tag
# %e = SQL state
# %c = session ID
# %l = session line number
# %s = session start timestamp
# %v = virtual transaction ID
# %x = transaction ID (0 if none)
# %q = stop here in non-session
# processes
# %% = '%'
# e.g. '<%u%%%d> '
#log_lock_waits = off # log lock waits >= deadlock_timeout
#log_recovery_conflict_waits = off # log standby recovery conflict waits
# >= deadlock_timeout
#log_parameter_max_length = -1 # when logging statements, limit logged
# bind-parameter values to N bytes;
# -1 means print in full, 0 disables
#log_parameter_max_length_on_error = 0 # when logging an error, limit logged
# bind-parameter values to N bytes;
# -1 means print in full, 0 disables
log_statement = 'all' # none, ddl, mod, all
#log_replication_commands = off
#log_temp_files = -1 # log temporary files equal or larger
# than the specified size in kilobytes;
# -1 disables, 0 logs all temp files
log_timezone = PST8PDT
# - Process Title -
#cluster_name = '' # added to process titles if nonempty
# (change requires restart)
#update_process_title = on