DAV properties propertyvalue column has wrong column type

claywd commented 1 year ago

⚠️ 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 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.

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?

[X] 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 |
+----+---------+--------------------------------------------+--------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+

szaimen commented 1 year ago

Hi, please update to 25.0.7 or better 26.0.2 and report back if it fixes the issue. Thank you!

My goal is to add a label like e.g. 26-feedback to this ticket of an up-to-date major Nextcloud version where the bug could be reproduced. However this is not going to work without your help. So thanks for all your effort!

If you don't manage to reproduce the issue in time and the issue gets closed but you can reproduce the issue afterwards, feel free to create a new bug report with up-to-date information by following this link: https://github.com/nextcloud/server/issues/new?assignees=&labels=bug%2C0.+Needs+triage&template=BUG_REPORT.yml&title=%5BBug%5D%3A+

claywd commented 1 year ago

@szaimen Was any work done to address the issue in the convert command in recent releases?

szaimen commented 1 year ago

Possibly yes

claywd commented 1 year ago

yeah... I decided that shared calendars weren't worth risking an upgrade with my current setup. I dropped all the records in that table and the convert command worked. I think it might be a little unreasonable to blanket request people upgrade to check if the problem still exists. I would suggest pointing people to the exact fix they need to implement. Otherwise, you risk introducing so many more errors... Its just not worth it in the long run.

joshtrichards commented 1 year ago

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.

szaimen commented 1 year ago

Apparently this is still broken also om 26.

szaimen commented 1 year ago

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

Indeed. In best case should the occ command already take care of this and update the 0x00 values in text type columns to empty strings.

szaimen commented 1 year ago

Cc @nextcloud/server-backend for pointers on how to implement this. This should not be hard, or is it?

szaimen commented 1 year ago

Probably there would need to be adjustmenets in this file? https://github.com/nextcloud/server/blob/master/core/Command/Db/ConvertType.php How could a logic that fixes this look like?

ralfrupf1976 commented 1 year ago

I am affected by this Bug too. As long as it is not fixed I can't migrate to nextcloud aio. Is there a way I can help? Is a solution in sight? Probably with the next bugfix release of nextcloud?

joshtrichards commented 1 year ago

@ralfrupf1976 Is yours happening in the same table out of curiosity?

ralfrupf1976 commented 1 year ago

That's the error during the migration:

oc_properties
chunked query, 4 chunks
2800/3068 [=========================>--] 91% 1 sec/1 sec
In ExceptionConverter.php line 91:

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

In Exception.php line 30:

SQLSTATE[22021]: Character not in repertoire: 7 ERROR: invalid byte sequence for encoding "UTF8": 0x00

In Statement.php line 101:

SQLSTATE[22021]: Character not in repertoire: 7 ERROR: invalid byte sequence for encoding "UTF8": 0x00

joshtrichards commented 1 year ago

Ideally we track down the source of these null 0x00 characters ending up in there to start with and address it there. Of course we may still have to deal with existing in-place data.

What we really need are steps to reproduce the situation where the 0x00 character entries are created to start with.

The next best thing is to identify which existing database entries are triggering this. And the following can be ran from your MariaDB/MySQL client to do so:

SELECT * FROM oc_properties WHERE propertyvalue like concat('%', 0x00, '%');

If you feel comfortable sharing a few of the returned entries, that could be insightful to tracking down the true culprit. It is a read-only command so it should be safe (unlike the one below). But database backups are recommended nonetheless!

It may be possible to manually fix these entries (see below), but this is untested.

Warning

The side effect of doing this are untested. Make a backup. Don't try this unless you're comfortable with the ramifications, know how to restore from your backups, and/or are able to do it solely on a throwaway test instance!

The following will, in theory, change all occurrences of the invisible character (0x00) with an actual empty string.

UPDATE oc_properties SET propertyvalue = replace(propertyvalue, 0x00, '') WHERE propertyvalue like concat('%', 0x00, '%');

P.S. I'm currently making the assumption that the character is ending up in the propertyvalue column. I suppose it could be elsewhere, but that's my best guess.

ralfrupf1976 commented 1 year ago

SELECT * FROM oc_properties WHERE propertyvalue like concat('%', 0x00, '%');

SELECT * FROM oc_properties WHERE propertyvalue like concat('%', 0x00, '%');
+------+---------------+--------------------------------------------------------------+---------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
| id   | userid        | propertypath                                                 | propertyname                                            | propertyvalue                                                                                                                                                                                              | valuetype |
+------+---------------+--------------------------------------------------------------+---------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
| 3422 | michal.maurer | calendars/michal.maurer/personal_shared_by_marianne.schnyder | {urn:ietf:params:xml:ns:caldav}schedule-calendar-transp | O:48:"Sabre\CalDAV\Xml\Property\ScheduleCalendarTransp":1:{s:8:" * value";s:6:"opaque";}                                                                                                                   |         3 |
| 3439 | michal.maurer | calendars/michal.maurer/michal-pfarrerin                     | {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 |
+------+---------------+--------------------------------------------------------------+---------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
2 rows in set (0.01 sec)

ralfrupf1976 commented 1 year ago

YES - the UPDATE oc_properties command above helped! Now the conversation has completed and my nextcloud runs on postgrsql!! Thank you very much - i can continue now with the migration to Nextcloud AIO.

Above you see the 2 entries which made the error...

joshtrichards commented 1 year ago

Seems we're passing private and protected member variables unfettered through PHP serialization somewhere. PHP serialize() sticks null bytes around them in the serialized output:

Sabre\CalDAV\Xml\Property\ScheduleCalendarTransp Sabre\DAV\Xml\Property\ResourceType

https://www.php.net/manual/en/function.serialize.php

Limited to Calendar stuff so far it appears.

Since this is a TEXT rather than BLOB column... In MySQL it likely doesn't make much difference, but with PostgreSQL it's another matter.

I'm also kind of wondering if maybe this was a transient bug somewhere along the way. I only say that because it seems like it would have come up more. That or introduced relatively recently...

fuomag9 commented 5 months ago

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

Gyzie commented 2 months ago

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.

starlingfire commented 3 weeks ago

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

How did you identify the offending record?

tcitworld commented 3 weeks ago

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

nextcloud / server