DAV properties propertyvalue column has wrong column type

⚠️ This issue respects the following points: ⚠️

This is a bug, not a question or a configuration/webserver/proxy issue.
This issue is not already reported on Github (I've searched it).
Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
Nextcloud Server is running on 64bit capable CPU, PHP and OS.
I agree to follow Nextcloud's Code of Conduct.

Bug description

converting database from mysql to postgres fails for (more than one) reason but specifically this time because of encoding issues between the tables with error

An exception occurred while executing a query: SQLSTATE[22021]: Character not in repertoire: 7 ERROR: invalid byte sequence for encoding "UTF8": 0x00
CONTEXT: unnamed portal parameter $5

Steps to reproduce

Setup nextcloud with remote mysql as the db source
Setup a remote postgresql, create nextcloud database, nextcloud user, grant all privs on the db to the nextcloud user
run the command ./occ db:convert-type --password "redacted" pgsql nextcloud pg_db_name nextcloud as the www-data user within the nextcloud directory

Expected behavior

New database tables and schema are configured and populated in new postgresql db with transformed from previous mysql db.

Installation method

Community Docker image

Nextcloud Server version

24

Operating system

Debian/Ubuntu

PHP engine version

PHP 8.1

Web server

Nginx

Database engine version

PostgreSQL

Is this bug present after an update or on a fresh install?

Fresh Nextcloud Server install

Are you using the Nextcloud Server Encryption module?

Encryption is Disabled

What user-backends are you using?

Default user-backend (database)
LDAP/ Active Directory
SSO - SAML
Other

Configuration report

{
    "system": {
        "memcache.local": "\\OC\\Memcache\\APCu",
        "apps_paths": [
            {
                "path": "\/var\/www\/html\/apps",
                "url": "\/apps",
                "writable": false
            },
            {
                "path": "\/var\/www\/html\/custom_apps",
                "url": "\/custom_apps",
                "writable": true
            }
        ],
        "memcache.distributed": "\\OC\\Memcache\\Redis",
        "memcache.locking": "\\OC\\Memcache\\Redis",
        "redis": {
            "host": "***REMOVED SENSITIVE VALUE***",
            "password": "***REMOVED SENSITIVE VALUE***",
            "port": 6379
        },
        "trusted_proxies": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "localhost",
            "test.fakedomain.dns",
            "localhost",
            "127.0.0.1",
            "127.0.0.1:10020",
            "10.0.10.10",
            "nextcloud-truechart-nextcloud",
            "nextcloud-truechart-nextcloud-backend",
            "cloud.dittmanfamily.com"
        ],
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "mysql",
        "version": "25.0.2.3",
        "overwrite.cli.url": "http:\/\/10.0.10.10:10020\/",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbport": "3306",
        "dbtableprefix": "oc_",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "preview_imaginary_url": "http:\/\/127.0.0.1:9090",
        "preview_max_x": "2048",
        "preview_max_y": "2048",
        "preview_max_memory": "512",
        "preview_max_filesize_image": "150",
        "0": [
            "spreed",
            "audioplayer_sonos",
            "video_converter",
            "impersonate",
            "files_fulltextsearch",
            "memories"
        ],
        "memories.ffmpeg_path": "\/usr\/local\/bin\/ffmpeg",
        "memories.ffprobe_path": "\/usr\/local\/bin\/ffprobe",
        "memories.transcoder": "\/usr\/local\/www\/nextcloud\/apps\/memories\/exiftool-bin\/go-vod-amd64",
        "memories.no_transcode": false,
        "memories.qsv": false,
        "default_phone_region": "US",
        "theme": "",
        "loglevel": 0,
        "maintenance": false,
        "enabledPreviewProviders": [
            "OC\\Preview\\Imaginary",
            "OC\\Preview\\PNG",
            "OC\\Preview\\JPEG",
            "OC\\Preview\\GIF",
            "OC\\Preview\\BMP",
            "OC\\Preview\\XBitmap",
            "OC\\Preview\\MP3",
            "OC\\Preview\\MarkDown",
            "OC\\Preview\\OpenDocument",
            "OC\\Preview\\TXT",
            "OC\\Preview\\Krita",
            "OC\\Preview\\HEIC",
            "OC\\Preview\\Movie"
        ]
    }
}

List of activated Apps

Enabled:
  - activity: 2.17.0
  - admin_audit: 1.15.0
  - bruteforcesettings: 2.5.0
  - calendar: 4.3.3
  - circles: 25.0.0
  - cloud_federation_api: 1.8.0
  - comments: 1.15.0
  - contacts: 5.2.0
  - contactsinteraction: 1.6.0
  - dashboard: 7.5.0
  - dav: 1.24.0
  - deck: 1.8.3
  - federatedfilesharing: 1.15.0
  - federation: 1.15.0
  - files: 1.20.1
  - files_accesscontrol: 1.15.1
  - files_automatedtagging: 1.15.3
  - files_fulltextsearch: 25.0.0
  - files_pdfviewer: 2.6.0
  - files_rightclick: 1.4.0
  - files_sharing: 1.17.0
  - files_trashbin: 1.15.0
  - files_versions: 1.18.0
  - firstrunwizard: 2.14.0
  - impersonate: 1.12.0
  - logreader: 2.10.0
  - lookup_server_connector: 1.13.0
  - memories: 4.13.1
  - notes: 4.7.2
  - notifications: 2.13.1
  - notify_push: 0.6.2
  - oauth2: 1.13.0
  - password_policy: 1.15.0
  - photos: 2.0.1
  - previewgenerator: 5.2.2
  - privacy: 1.9.0
  - provisioning_api: 1.15.0
  - ransomware_protection: 1.14.0
  - recognize: 3.7.0
  - related_resources: 1.0.3
  - serverinfo: 1.15.0
  - settings: 1.7.0
  - sharebymail: 1.15.0
  - spreed: 15.0.5
  - systemtags: 1.15.0
  - tasks: 0.14.5
  - text: 3.6.0
  - theming: 2.0.1
  - twofactor_backupcodes: 1.14.0
  - twofactor_nextcloud_notification: 3.6.0
  - twofactor_totp: 7.0.0
  - twofactor_webauthn: 1.1.2
  - updatenotification: 1.15.0
  - uppush: 1.3.0
  - user_status: 1.5.0
  - viewer: 1.9.0
  - weather_status: 1.5.0
  - workflowengine: 2.7.0
Disabled:
  - encryption
  - files_external
  - nextcloud_announcements: 1.12.0
  - recommendations: 1.2.0
  - support: 1.6.0
  - survey_client: 1.11.0
  - suspicious_login
  - user_ldap
  - video_converter: 1.0.5

Nextcloud Signing status

Technical information
=====================
The following list covers which files have failed the integrity check. Please read
the previous linked documentation to learn more about the errors and how to fix
them.

Results
=======
- core
	- EXTRA_FILE
		- photo-cleanup-whitney.sh
		- photo-cleanup-clayton.sh
	- INVALID_HASH
		- .htaccess

Raw output
==========
Array
(
    [core] => Array
        (
            [EXTRA_FILE] => Array
                (
                    [photo-cleanup-whitney.sh] => Array
                        (
                            [expected] => 
                            [current] => 94a0471763d324cf61553153f2c7be442b1a89d8b4abe4e2b4f1983c9a8b50ee33e887e43b2e9c97a4bd63bf69c24fd6ca2c07aaf8473ee15291d364fe3c2c6d
                        )

                    [photo-cleanup-clayton.sh] => Array
                        (
                            [expected] => 
                            [current] => bed2e98a414273526cbf44a0b744d7c5636e07f43fcd722bfa20b258786450190a1afdc8a54fdb91d7433f775b9c4122d8d791e444c387ead835bb2ee35c179d
                        )

                )

            [INVALID_HASH] => Array
                (
                    [.htaccess] => Array
                        (
                            [expected] => d9cab0f077161bf043d6d85f5bcacbdfaf82913968b7706df9cf7e6f98b11af363f3843b455175ae775567916822fec8f6ab95000ba064c71e05c86a02196d95
                            [current] => 54cf22f44b5e273d6ca00a13843a803df163891451a2289ca91d088aa9d8230175791937c69ab8728d89206008adeaf2fd836dc47d5543494ac04bb4c318ccde
                        )

                )

        )

)

Nextcloud Logs

www-data@nextcloud-truechart-dfbf65457-548bg:~/html$ ./occ db:convert-type --password "redacted" pgsql nextcloud nextcloud-postgres nextcloud
Creating schema in new database
The following tables will not be converted:
oc_fulltextsearch_index
oc_fulltextsearch_ticks
oc_group_folders
oc_group_folders_acl
oc_group_folders_groups
oc_group_folders_manage
oc_group_folders_trash
oc_mail_accounts
oc_mail_aliases
oc_mail_attachments
oc_mail_classifiers
oc_mail_coll_addresses
oc_mail_local_messages
oc_mail_mailboxes
oc_mail_message_tags
oc_mail_messages
oc_mail_provisionings
oc_mail_recipients
oc_mail_tags
oc_mail_trusted_senders
oc_souvenirs_shares
oc_twofactor_u2f_registrations
Please note that tables belonging to available but currently not installed apps
can be included by specifying the --all-apps option.
Continue with the conversion (y/n)? [n] y
 - oc_migrations
Skipping migrations table because it was already filled by running the migrations
 - oc_reactions
    0 [>---------------------------] < 1 sec
 - oc_appconfig
 276/276 [============================] 100% < 1 sec/< 1 sec
 - oc_storages
 5/5 [============================] 100% < 1 sec/< 1 sec
 - oc_file_metadata
chunked query, 8 chunks
 7160/7160 [============================] 100%  1 sec/1 sec
 - oc_user_transfer_owner
    0 [>---------------------------] < 1 sec
 - oc_mimetypes
 105/105 [============================] 100% < 1 sec/< 1 sec
 - oc_open_local_editor
    0 [>---------------------------] < 1 sec
 - oc_filecache
chunked query, 586 chunks
 585463/585463 [============================] 100% 4 mins/4 mins
 - oc_group_user
 4/4 [============================] 100% < 1 sec/< 1 sec
 - oc_group_admin
    0 [>---------------------------] < 1 sec
 - oc_preferences
 316/316 [============================] 100% < 1 sec/< 1 sec
 - oc_groups
 2/2 [============================] 100% < 1 sec/< 1 sec
 - oc_activity
chunked query, 39 chunks
 38360/38360 [============================] 100% 9 secs/9 secs
 - oc_properties
 0/7 [>---------------------------]   0% < 1 sec/< 1 sec
In ExceptionConverter.php line 83:

  An exception occurred while executing a query: SQLSTATE[22021]: Character not in repertoire: 7 ERROR:  invalid byte sequence for encoding "UTF8": 0x00
  CONTEXT:  unnamed portal parameter $5


In Exception.php line 26:

  SQLSTATE[22021]: Character not in repertoire: 7 ERROR:  invalid byte sequence for encoding "UTF8": 0x00
  CONTEXT:  unnamed portal parameter $5


In Statement.php line 92:

  SQLSTATE[22021]: Character not in repertoire: 7 ERROR:  invalid byte sequence for encoding "UTF8": 0x00
  CONTEXT:  unnamed portal parameter $5


db:convert-type [--port PORT] [--password PASSWORD] [--clear-schema] [--all-apps] [--chunk-size CHUNK-SIZE] [--] <type> <username> <hostname> <database>

Additional info

I think this may be an issue with the way characters were stored in mysql vs postgresql. Not just in this table but in general in all the tables.

table output

MySQL [nextcloud]> select * from oc_properties ;
+----+---------+--------------------------------------------+--------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
| id | userid  | propertypath                               | propertyname                                                 | propertyvalue                                                                                                                                                                                              | valuetype |
+----+---------+--------------------------------------------+--------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
|  1 | clayton | calendars/clayton/personal                 | {http://owncloud.org/ns}calendar-enabled                     | 1                                                                                                                                                                                                          |         1 |
|  2 | whitney | calendars/whitney                          | {urn:ietf:params:xml:ns:caldav}default-alarm-vevent-date     | BEGIN:VALARM
X-WR-ALARMUID:DF37FF26-34FD-4F3D-85C1-7229380F4CCF
UID:DF37FF26-34FD-4F3D-85C1-7229380F4CCF
TRIGGER:-PT15H
ATTACH;VALUE=URI:Chord
ACTION:AUDIO
END:VALARM
                             |         1 |
|  3 | whitney | calendars/whitney                          | {urn:ietf:params:xml:ns:caldav}default-alarm-vevent-datetime | BEGIN:VALARM
X-WR-ALARMUID:B3172EFF-1C5D-4DA1-8618-35FBB60470C2
UID:B3172EFF-1C5D-4DA1-8618-35FBB60470C2
TRIGGER;VALUE=DATE-TIME:19760401T005545Z
ACTION:NONE
END:VALARM
                            |         1 |
|  4 | whitney | calendars/whitney/whitney-and-clays-shared | {http://owncloud.org/ns}calendar-enabled                     | 1                                                                                                                                                                                                          |         1 |
|  5 | whitney | calendars/whitney/work                     | {DAV:}resourcetype                                           | O:35:"Sabre\DAV\Xml\Property\ResourceType":1:{s:8:" * value";a:3:{i:0;s:39:"{urn:ietf:params:xml:ns:caldav}calendar";i:1;s:43:"{http://calendarserver.org/ns/}shared-owner";i:2;s:16:"{DAV:}collection";}} |         3 |
|  6 | clayton | calendars/clayton/work_shared_by_whitney   | {urn:ietf:params:xml:ns:caldav}schedule-calendar-transp      | O:48:"Sabre\CalDAV\Xml\Property\ScheduleCalendarTransp":1:{s:8:" * value";s:6:"opaque";}                                                                                                                   |         3 |
|  7 | clayton | calendars/clayton/family_shared_by_whitney | {http://owncloud.org/ns}calendar-enabled                     | 1                                                                                                                                                                                                          |         1 |
+----+---------+--------------------------------------------+--------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+

An exception occurred while executing a query: SQLSTATE[22021]: Character not in repertoire: 7 ERROR: invalid byte sequence for encoding "UTF8": 0x00

Sounds like the infamous PostgreSQL not accepting code zero/NULLs in strings issue.

Does anyone have a fix for this? I have no null characters...

Not sure if exactly related, but it might help with reproducing this issue:

We ran into this error after my girlfriend tried to share a calendar with me from her iPhone calendar app. The calendar disappeared from her phone and web interface. Quite a dangerous bug for novice users.

It wasn't caused by the migration however since our database has already been migrated from MySQL to PostgreSQL a while ago.

It was quite easy to reproduce for me:

Create calendar in the web app or on iPhone
Invite someone to the calendar from your iPhone (via "Calendars" at the bottom of the app and pressing the i).
Watch the calendar disappear from your iPhone and web interface (refresh the page).

Deleting the records with the offending propertyvalue for that user's calendar made the calendars appear again.

Deleting the records with the offending propertyvalue for that user's calendar made the calendars appear again.

How did you identify the offending record?

You can simply remove all rows with valuetype = 3 (object type)

fix(dav): change type of propertyvalue column to blob

@Gyzie

Hi,

I've been having the problem posted by @Gyzie, also caused by sharing a calendar. I have no idea how to solve it, even after having read the comment by @tcitworld

You can simply remove all rows with valuetype = 3 (object type)

as I've never used any database software. I know it's not a fatal error, but I would like my logs to reflect real problems, and not to be full with the same issue (it prompts this error twice, any time any device wants to sync calendars).

Could any of you provide a dumbed-down explanation on how to fix this? I am using NC AIO on Docker, I know where the database is located both inside the container and the Linux machine running Docker, but I don't know anything about how to access it (do I need a username and password?) or apply the solutions that were given.

Thanks a lot.

Hi,

I've been having the problem posted by @Gyzie, also caused by sharing a calendar. I have no idea how to solve it, even after having read the comment by @tcitworld

You can simply remove all rows with valuetype = 3 (object type)

as I've never used any database software. I know it's not a fatal error, but I would like my logs to reflect real problems, and not to be full with the same issue (it prompts this error twice, any time any device wants to sync calendars).

Could any of you provide a dumbed-down explanation on how to fix this? I am using NC AIO on Docker, I know where the database is located both inside the container and the Linux machine running Docker, but I don't know anything about how to access it (do I need a username and password?) or apply the solutions that were given.

Thanks a lot.

Hi everyone,
I am in the same situation and a helping hand would indeed be welcome.

Hi,

You need to connect to the database and run: SELECT * FROM oc_properties WHERE valuetype = 3. Please use the forum https://help.nextcloud.com for help.

Or you can just wait for the upcoming versions 29.0.11 and 30.0.5 coming up in two weeks which contains a fix for the issue.

Hello, sorry for reopening this report but I'm having the same issue with v33.0.2. Related settings are imported ics calendars.

Also having similar issues with imported ics calendars.

In my case, its holding up a migration to postgresql from mariadb.

Navigation Menu

Repository navigation

DAV properties propertyvalue column has wrong column type #37754

⚠️ This issue respects the following points: ⚠️

Bug description

Steps to reproduce

Expected behavior

Installation method

Nextcloud Server version

Operating system

PHP engine version

Web server

Database engine version

Is this bug present after an update or on a fresh install?

Are you using the Nextcloud Server Encryption module?

What user-backends are you using?

Configuration report

List of activated Apps

Nextcloud Signing status

Nextcloud Logs

Additional info

17 remaining items

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

DAV properties propertyvalue column has wrong column type #37754

Description

⚠️ This issue respects the following points: ⚠️

Bug description

Steps to reproduce

Expected behavior

Installation method

Nextcloud Server version

Operating system

PHP engine version

Web server

Database engine version

Is this bug present after an update or on a fresh install?

Are you using the Nextcloud Server Encryption module?

What user-backends are you using?

Configuration report

List of activated Apps

Nextcloud Signing status

Nextcloud Logs

Additional info

Activity

szaimen commented on May 22, 2023

claywd commented on Jun 7, 2023

szaimen commented on Jun 7, 2023

claywd commented on Jun 7, 2023

joshtrichards commented on Jun 13, 2023

17 remaining items

fuomag9 commented on Jun 2, 2024

Gyzie commented on Aug 5, 2024

starlingfire commented on Oct 12, 2024

tcitworld commented on Oct 12, 2024

Xocko12 commented on Dec 31, 2024

Menzaah commented on Dec 31, 2024

tcitworld commented on Jan 2, 2025

gllmhyt commented on Apr 19, 2026

zombielinux commented on Oct 26, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions