Skip to content

[Bug]: db:convert-type fails when converting MariaDB -> PostgreSQL | NC 23 #31207

Closed

Description

@Leptopoda

⚠️ 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.
  • I agree to follow Nextcloud's Code of Conduct.

Bug description

I tried to convert my MariaDB to Postgres (with occ db:convert-type pgsql --all-apps) and the migration failed with

 - oc_cards_properties
 10960/15853 [===================>--------]  69% 2 mins/4 mins
In ExceptionConverter.php line 83:
                                                                                                                                                          
  An exception occurred while executing a query: SQLSTATE[22001]: String data, right truncated: 7 ERROR:  value too long for type character varying(255)  
                                                                                                                                                          

In Exception.php line 26:
                                                                                                           
  SQLSTATE[22001]: String data, right truncated: 7 ERROR:  value too long for type character varying(255)  
                                                                                                           

In Statement.php line 92:
                                                                                                           
  SQLSTATE[22001]: String data, right truncated: 7 ERROR:  value too long for type character varying(255)  

The migration is really slow (16hrs for 1.9G on 32 cores and 32 gig 🤷‍♀️) but that's for a different issue

Steps to reproduce

  1. run php occ db:convert-type pgsql --all-apps --chunk-size=100000 --password="" nextcloud nextcloud_db_pg nextcloud

Expected behavior

The migration should not fail :)

Installation method

Official Docker image

Operating system

No response

PHP engine version

PHP 8.0

Web server

Nginx

Database engine version

MariaDB

Is this bug present after an update or on a fresh install?

No response

Are you using the Nextcloud Server Encryption module?

No response

What user-backends are you using?

  • Default user-backend (database)
  • LDAP/ Active Directory
  • SSO - SAML
  • Other

Configuration report

{
    "system": {
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "***REMOVED SENSITIVE VALUE***",
            "***REMOVED SENSITIVE VALUE***"
        ],
        "version": "23.0.0.10",
        "overwrite.cli.url": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "maintenance": false,
        "data-fingerprint": "18cd82cf6e70add3bd58f3a40debbf89",
        "apps_paths": [
            {
                "path": "\/var\/www\/html\/apps",
                "url": "\/apps",
                "writable": false
            },
            {
                "path": "\/var\/www\/html\/custom_apps",
                "url": "\/custom_apps",
                "writable": true
            }
        ],
        "dbtype": "mysql",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbport": "",
        "dbtableprefix": "oc_",
        "mysql.utf8mb4": true,
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "default_phone_region": "DE",
        "default_language": "de",
        "default_locale": "de_DE",
        "log_type": "syslog",
        "loglevel": 3,
        "logtimezone": "Europe\/Berlin",
        "log_query": false,
        "trusted_proxies": "***REMOVED SENSITIVE VALUE***",
        "overwriteprotocol": "https",
        "mail_smtpmode": "smtp",
        "mail_smtpsecure": "ssl",
        "mail_sendmailmode": "smtp",
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpauth": 1,
        "mail_smtpport": "465",
        "mail_smtpname": "***REMOVED SENSITIVE VALUE***",
        "mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpauthtype": "LOGIN",
        "app_install_overwrite": [
            "facerecognition",
            "previewgenerator",
            "files_photospheres",
            "ocsms",
            "groupfolders",
            "camerarawpreviews",
            "metadata"
        ],
        "theme": "",
        "trashbin_retention_obligation": "auto, 30",
        "syslog_tag": "Nextcloud",
        "logfile": "",
        "preview_max_x": "2048",
        "preview_max_y": "2048",
        "jpeg_quality": "60",
        "memcache.local": "\\OC\\Memcache\\APCu",
        "memcache.distributed": "\\OC\\Memcache\\Redis",
        "memcache.locking": "\\OC\\Memcache\\Redis",
        "redis": {
            "host": "***REMOVED SENSITIVE VALUE***",
            "port": 6379
        }
    }
}

List of activated Apps

Enabled:
  - accessibility: 1.9.0
  - activity: 2.15.0
  - admin_audit: 1.13.0
  - audioplayer: 3.2.4
  - calendar: 3.0.5
  - camerarawpreviews: 0.7.15
  - checksum: 1.1.3
  - circles: 23.0.0
  - cloud_federation_api: 1.6.0
  - comments: 1.13.0
  - contacts: 4.0.7
  - contactsinteraction: 1.4.0
  - dav: 1.21.0
  - federatedfilesharing: 1.13.0
  - federation: 1.13.0
  - files: 1.18.0
  - files_pdfviewer: 2.4.0
  - files_photospheres: 1.23.0
  - files_rightclick: 1.2.0
  - files_sharing: 1.15.0
  - files_trashbin: 1.13.0
  - files_versions: 1.16.0
  - files_videoplayer: 1.12.0
  - groupfolders: 11.1.2
  - impersonate: 1.10.0
  - logreader: 2.8.0
  - lookup_server_connector: 1.11.0
  - maps: 0.1.10
  - news: 17.0.1
  - nextcloud_announcements: 1.12.0
  - notes: 4.3.0
  - notifications: 2.11.1
  - notify_push: 0.3.0
  - oauth2: 1.11.0
  - password_policy: 1.13.0
  - photos: 1.5.0
  - previewgenerator: 4.0.0
  - privacy: 1.7.0
  - provisioning_api: 1.13.0
  - recommendations: 1.2.0
  - richdocuments: 5.0.1
  - serverinfo: 1.13.0
  - settings: 1.5.0
  - sharebymail: 1.13.0
  - spreed: 13.0.2
  - support: 1.6.0
  - systemtags: 1.13.0
  - text: 3.4.0
  - twofactor_backupcodes: 1.12.0
  - twofactor_totp: 6.2.0
  - updatenotification: 1.13.0
  - user_status: 1.3.1
  - viewer: 1.7.0
  - weather_status: 1.3.0
  - workflow_pdf_converter: 1.8.0
  - workflowengine: 2.5.0
Disabled:
  - apporder
  - dashboard
  - encryption
  - end_to_end_encryption
  - external
  - files_automatedtagging
  - files_external
  - firstrunwizard
  - guests
  - mail
  - metadata
  - ocsms
  - survey_client
  - theming
  - user_ldap

Nextcloud Signing status

No errors have been found.

Nextcloud Logs

NA although right after the crash there wasn't more information than the terminal output.

Additional info

I don't think it'll help further but there's the complete console output of the migration:

$ docker exec -it -u82 nextcloud_app sh
/var/www/html $ php occ db:convert-type pgsql --all-apps --chunk-size=100000 --password="xxx" --clear-schema nextcloud nextcloud_db_pg nextcloud
Creating schema in new database
 - systemtags
 - contactsinteraction
 - twofactor_backupcodes
 - files_external
 - files_versions
 - comments
 - settings
 - circles
 - updatenotification
 - firstrunwizard
 - files_pdfviewer
 - encryption
 - workflowengine
 - accessibility
 - notifications
 - recommendations
 - activity
 - provisioning_api
 - files_sharing
 - logreader
 - support
 - privacy
 - oauth2
 - files_trashbin
 - admin_audit
 - files
 - federatedfilesharing
 - viewer
 - lookup_server_connector
 - survey_client
 - files_videoplayer
 - password_policy
 - federation
 - nextcloud_announcements
 - dav
 - photos
 - text
 - user_status
 - user_ldap
 - theming
 - serverinfo
 - dashboard
 - files_rightclick
 - weather_status
 - cloud_federation_api
 - sharebymail
 - previewgenerator
 - notes
 - ocsms
 - files_automatedtagging
 - calendar
 - external
 - notify_push
 - workflow_pdf_converter
 - checksum
 - audioplayer
 - impersonate
 - end_to_end_encryption
 - richdocuments
 - files_photospheres
 - camerarawpreviews
 - mail
 - contacts
 - guests
 - twofactor_totp
 - spreed
 - apporder
 - metadata
 - maps
 - groupfolders
 - news
The following tables will not be converted:
oc_carnet_metadata
oc_deck_assigned_labels
oc_deck_assigned_users
oc_deck_attachment
oc_deck_board_acl
oc_deck_boards
oc_deck_cards
oc_deck_labels
oc_deck_stacks
oc_documentserver_changes
oc_documentserver_ipc
oc_documentserver_locks
oc_documentserver_sess
oc_facerecog_faces
oc_facerecog_images
oc_facerecog_models
oc_facerecog_persons
oc_ocdownloader_adminsettings
oc_ocdownloader_personalsettings
oc_ocdownloader_queue
oc_ocsms_config
oc_ocsms_conversation_read_states
oc_ocsms_sendmessage_queue
oc_ocsms_smsdatas
oc_ocsms_user_datas
oc_passwords_entity_challenge
oc_passwords_entity_folder
oc_passwords_entity_folder_revision
oc_passwords_entity_keychain
oc_passwords_entity_password
oc_passwords_entity_password_revision
oc_passwords_entity_registration
oc_passwords_entity_session
oc_passwords_entity_share
oc_passwords_entity_tag
oc_passwords_entity_tag_revision
oc_passwords_relation_password_tag
oc_retention
oc_twofactor_u2f_registrations
Continue with the conversion (y/n)? [n] y
 - oc_migrations
Skipping migrations table because it was already filled by running the migrations
 - oc_addressbooks
 10/10 [============================] 100%  1 sec/1 sec 
 - oc_appconfig
 413/413 [============================] 100% 5 secs/5 secs
 - oc_cards
 2938/2938 [============================] 100% 42 secs/42 secs
 - oc_storages
 18/18 [============================] 100% < 1 sec/< 1 sec
 - oc_addressbookchanges
 4497/4497 [============================] 100%  1 min/1 min 
 - oc_mimetypes
 168/168 [============================] 100% 3 secs/3 secs
 - oc_filecache
chunked query, 37 chunks
 3684613/3684613 [============================] 100% 16 hrs/16 hrs
 - oc_calendarobjects
 3325/3325 [============================] 100% 55 secs/55 secs
 - oc_group_user
 9/9 [============================] 100% < 1 sec/< 1 sec
 - oc_group_admin
 2/2 [============================] 100% < 1 sec/< 1 sec
 - oc_groups
 3/3 [============================] 100% < 1 sec/< 1 sec
 - oc_preferences
 669/669 [============================] 100% 15 secs/15 secs
 - oc_calendars
 20/20 [============================] 100% < 1 sec/< 1 sec
 - oc_calendarchanges
 4308/4308 [============================] 100%  1 min/1 min 
 - oc_properties
 137/137 [============================] 100% 2 secs/2 secs
 - oc_users
 8/8 [============================] 100% < 1 sec/< 1 sec
 - oc_calendarsubscriptions
 5/5 [============================] 100% < 1 sec/< 1 sec
 - oc_schedulingobjects
 104/104 [============================] 100% 2 secs/2 secs
 - oc_cards_properties
 10960/15853 [===================>--------]  69% 2 mins/4 mins
In ExceptionConverter.php line 83:
                                                                                                                                                          
  An exception occurred while executing a query: SQLSTATE[22001]: String data, right truncated: 7 ERROR:  value too long for type character varying(255)  
                                                                                                                                                          

In Exception.php line 26:
                                                                                                           
  SQLSTATE[22001]: String data, right truncated: 7 ERROR:  value too long for type character varying(255)  
                                                                                                           

In Statement.php line 92:
                                                                                                           
  SQLSTATE[22001]: String data, right truncated: 7 ERROR:  value too long for type character varying(255)  
                                                                                                           

db:convert-type [--port PORT] [--password PASSWORD] [--clear-schema] [--all-apps] [--chunk-size CHUNK-SIZE] [--] <type> <username> <hostname> <database>

Activity

  1. szaimen commented on Feb 16, 2022

    @szaimen
    Contributor

    Hello, the speed problems might get addressed with #31219

  2. Leptopoda commented on Feb 18, 2022

    @Leptopoda
    MemberAuthor

    Thanks for looking into it. Are the provided logs enough for the other error I get or do I need to get more?

  3. szaimen commented on Feb 18, 2022

    @szaimen
    Contributor

    I suppose they should be enough for somebody that has the time and knowledge to look into this and fix it 👍

  4. solracsf commented on Mar 30, 2022

    @solracsf
    Member

    This has something to do with #9907

  5. Leptopoda commented on Sep 9, 2022

    @Leptopoda
    MemberAuthor

    Today I had some time to look deeper into the problems and although the issue is similar to #9907 it isn't anything in the oc_files table.
    The above mentioned should also fixed or at least mitigated by #19242.
    now I investigated my problem further and it is that the notes section in some contacts. I'm not sure how many contacts need a notes field with more than 255 chars.

    EDIT: Thought there was only limited use of the notes field but turns out to be that quite a few contacts have a footer stile message in there. This includes company stuff and I guess that those where vcards attached per mail and not manually created.
    The one the migration failed on has 371 chars in the notes field and I'm gonna search for other examples later.

    How much should the limit be raised? Also this limit should also be forced by the contacs app...

    P.S. The migration speedup from Carl where perfect. I already applied the patch back then before it was merged and in my testing today the migration time was around 50 min so a considerably faster. ❤️

  6. szaimen commented on Jan 23, 2023

    @szaimen
    Contributor

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

    My goal is to add a label like e.g. 25-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+

  7. Leptopoda commented on Jan 24, 2023

    @Leptopoda
    MemberAuthor

    Thank you for the bump :)
    I can't reproduce this on 25.0.3 anymore. This might be because the data changed or the bug is truly fixed.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    0. Needs triagePending check for reproducibility or if it fits our roadmapbugneeds info

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions