Open
Description
openedon Apr 15, 2023
⚠️ 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 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?
- 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.5Nextcloud 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 |
+----+---------+--------------------------------------------+--------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment