nextcloud
DAV properties propertyvalue column has wrong column type
⚠️ 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 nextcloudas 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 |
+----+---------+--------------------------------------------+--------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
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+
@szaimen Was any work done to address the issue in the convert command in recent releases?
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.
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.
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.
Cc @nextcloud/server-backend for pointers on how to implement this. This should not be hard, or is it?
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?
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?
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
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.
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)
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...
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...
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.
