Unserialize(): Error at offset 40 of 43 bytes at /config/www/nextcloud/apps/dav/lib/DAV/CustomPropertiesBackend.php#403

massturd · July 15, 2023, 10:34pm

Since a long time (at least since I am running version 25 (now 27) I am seeing a large number of these in the log:

unserialize(): Error at offset 40 of 43 bytes at /config/www/nextcloud/apps/dav/lib/DAV/CustomPropertiesBackend.php#403

Any ideas what this is about and how to fix it?

tcit · July 16, 2023, 9:39am

Hi,

The issue is the following:

github.com/nextcloud/server

[Bug]: `db:convert-type` from MySQL to PostgreSQL is broken

opened 08:55PM - 15 Apr 23 UTC

Claywd

bug 2. developing feature: dav feature: caldav 26-feedback

### ⚠️ This issue respects the following points: ⚠️ - [X] This is a **bug**, …not a question or a configuration/webserver/proxy issue. - [X] This issue is **not** already reported on Github _(I've searched it)_. - [X] Nextcloud Server **is** up to date. See [Maintenance and Release Schedule](https://github.com/nextcloud/server/wiki/Maintenance-and-Release-Schedule) for supported versions. - [X] Nextcloud Server **is** running on 64bit capable CPU, PHP and OS. - [X] I agree to follow Nextcloud's [Code of Conduct](https://nextcloud.com/contribute/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 1. Setup nextcloud with remote mysql as the db source 2. Setup a remote postgresql, create nextcloud database, nextcloud user, grant all privs on the db to the nextcloud user 3. 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? - [X] Default user-backend _(database)_ - [ ] LDAP/ Active Directory - [ ] SSO - SAML - [ ] Other ### Configuration report ```shell { "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 ```shell 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 ```shell 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 ```shell 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 | +----+---------+--------------------------------------------+--------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+ ``` ![image](https://user-images.githubusercontent.com/6446939/232253527-6abcfacd-f44a-4661-9358-3b7fc43b4235.png)

That’s not a big issue, but fixing it for all supported databases is hard (see Fix dav properties column types by tcitworld · Pull Request #39084 · nextcloud/server · GitHub ).

massturd · July 17, 2023, 11:51am

I am reluctant to buy into it as I always used Postgres, never used Mysql/Maria

tcit · July 20, 2023, 11:50am

Yup, but the issue is actually not about converting from one database to another, but a database column not having the correct type.

laurentperrinet · April 5, 2024, 7:14am

it’s popping up about every 30 minutes in my log:

laurentperrinet · May 24, 2024, 6:13am

any idea how to solve this? I have updated my instance, and this still floods my logs.

maybe I missed some conversion command which would fix it? or with respect to Fix dav properties column types by tcitworld · Pull Request #39084 · nextcloud/server · GitHub should I just wait that version 29 is out?

cheers, Laurent

vatolin · June 8, 2024, 6:29pm

At my log this failure is popping up every time when I interact with my NC.