DAV properties propertyvalue column has wrong column type #37754
Comments
db:convert-type from MySQL to PostgreSQL is broken
This comment was marked as resolved.
This comment was marked as resolved.
This comment was marked as resolved.
This comment was marked as resolved.
This comment was marked as resolved.
This comment was marked as resolved.
This comment was marked as resolved.
This comment was marked as resolved.
Sounds like the infamous PostgreSQL not accepting code zero/NULLs in strings issue. |
|
Apparently this is still broken also om 26. |
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 |
|
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? |
|
@ralfrupf1976 Is yours happening in the same table out of curiosity? |
|
That's the error during the migration: 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:
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. WarningThe 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 (
P.S. I'm currently making the assumption that the character is ending up in the |
|
|
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
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... |
The propertyvalue column can contain null 0x00 characters values because of serializing PHP objects since #30368. This truncates data in text fields, but not blob fields. We start by removing invalid value and altering the column to match the new type. That's what Sabre PDO's being doing in the first place 🙈 Closes #37754 Signed-off-by: Thomas Citharel <tcit@tcit.fr>
The propertyvalue column can contain null 0x00 characters values because of serializing PHP objects since #30368. This truncates data in text fields, but not blob fields. We start by removing invalid value and altering the column to match the new type. That's what Sabre PDO's being doing in the first place 🙈 Closes #37754 Signed-off-by: Thomas Citharel <tcit@tcit.fr>
The propertyvalue column can contain null 0x00 characters values because of serializing PHP objects since #30368. This truncates data in text fields, but not blob fields. We start by removing invalid value and altering the column to match the new type. That's what Sabre PDO's being doing in the first place 🙈 Closes #37754 Signed-off-by: Thomas Citharel <tcit@tcit.fr>
db:convert-type from MySQL to PostgreSQL is broken|
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:
Deleting the records with the offending |
How did you identify the offending record? |
|
You can simply remove all rows with |
The propertyvalue column can contain null 0x00 characters values because of serializing PHP objects since #30368. This truncates data in text fields, but not blob fields. We start by removing invalid value and altering the column to match the new type. That's what Sabre PDO's being doing in the first place 🙈 Closes #37754 Signed-off-by: Thomas Citharel <tcit@tcit.fr>
The propertyvalue column can contain null 0x00 characters values because of serializing PHP objects since #30368. This truncates data in text fields, but not blob fields. We start by removing invalid value and altering the column to match the new type. That's what Sabre PDO's being doing in the first place 🙈 Closes #37754 Signed-off-by: Thomas Citharel <tcit@tcit.fr>
|
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
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, |
|
Hi, You need to connect to the database and run: 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. |
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
./occ db:convert-type --password "redacted" pgsql nextcloud pg_db_name nextcloudas the www-data user within the nextcloud directoryExpected 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?
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
Nextcloud Signing status
Nextcloud Logs
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
The text was updated successfully, but these errors were encountered: