[Bug]: db:convert-type uses systemConfig instead of command line parameters for connection to new target database

[ksmonkey123]

⚠️ 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 OR Nextcloud Community Forum (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

The OCC-Command db:convert-type seems to ignore the DB properties (type, user, host, database, port, password) provided through the CLI and instead operates on the database configured in config.php

I believe this may be related to the commit 79c4986 as this reworked the parameter handling in ConnectionFactory.php.

If I read this correctly, the default parameters are now always generated based on the system config instead of the passed arguments.

Running db:convert-type --clean-schema drops the tables in the "old" database.

Steps to reproduce

1. Have Nextcloud 29.0.0 running with a MySQL database, and an empty PostgreSQL database
2. run php occ db:convert-type --password=<PASSWORD> --port=5432 pgsql nextcloud_app postgres nextcloud (note: the host postgres is a Kubernetes service name)
3. The command executes no matter if the Postgres Database is running or not, the command throws Constraint Violation errors in the table oc_accounts, I assume because it tries to migrate the content of the old database into itself.

Expected behavior

db:convert-type

Installation method

Community Docker image

Nextcloud Server version

29

Operating system

Debian/Ubuntu

PHP engine version

None

Web server

None

Database engine version

MySQL

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

None

Are you using the Nextcloud Server Encryption module?

None

What user-backends are you using?

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

Configuration report

{
    "system": {
        "htaccess.RewriteBase": "\/",
        "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
            }
        ],
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "192.168.178.32",
            "cloud.awae.ch"
        ],
        "trusted_proxies": "***REMOVED SENSITIVE VALUE***",
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "mysql",
        "version": "29.0.0.19",
        "overwrite.cli.url": "https:\/\/cloud.awae.ch",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbport": "",
        "dbtableprefix": "oc_",
        "mysql.utf8mb4": true,
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "overwriteprotocol": "https",
        "theme": "",
        "loglevel": 2,
        "maintenance": false,
        "mail_smtpmode": "smtp",
        "mail_sendmailmode": "smtp",
        "mail_smtpauth": 1,
        "mail_smtpauthtype": "LOGIN",
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpport": "25",
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpname": "***REMOVED SENSITIVE VALUE***",
        "mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpsecure": "tls",
        "default_language": "de",
        "default_phone_region": "CH",
        "app_install_overwrite": [
            "polls"
        ]
    }
}

List of activated Apps

Enabled:
  - activity: 2.21.1
  - circles: 29.0.0-dev
  - cloud_federation_api: 1.12.0
  - comments: 1.19.0
  - contactsinteraction: 1.10.0
  - dashboard: 7.9.0
  - dav: 1.30.1
  - federatedfilesharing: 1.19.0
  - federation: 1.19.0
  - files: 2.1.0
  - files_downloadlimit: 2.0.0
  - files_pdfviewer: 2.10.0
  - files_reminders: 1.2.0
  - files_sharing: 1.21.0
  - files_trashbin: 1.19.0
  - files_versions: 1.22.0
  - firstrunwizard: 2.18.0
  - logreader: 2.14.0
  - lookup_server_connector: 1.17.0
  - nextcloud_announcements: 1.18.0
  - notes: 4.10.0
  - notifications: 2.17.0
  - oauth2: 1.17.0
  - password_policy: 1.19.0
  - photos: 2.5.0
  - polls: 7.0.3
  - privacy: 1.13.0
  - provisioning_api: 1.19.0
  - recommendations: 2.1.0
  - related_resources: 1.4.0
  - serverinfo: 1.19.0
  - settings: 1.12.0
  - sharebymail: 1.19.0
  - support: 1.12.0
  - survey_client: 1.17.0
  - systemtags: 1.19.0
  - text: 3.10.0
  - theming: 2.4.0
  - twofactor_backupcodes: 1.18.0
  - updatenotification: 1.19.1
  - viewer: 2.3.0
  - workflowengine: 2.11.0
Disabled:
  - admin_audit: 1.19.0
  - bruteforcesettings: 2.9.0 (installed 2.4.0)
  - encryption: 2.17.0
  - extract: 1.3.6 (installed 1.3.6)
  - files_external: 1.21.0
  - files_rightclick: 0.15.1 (installed 1.6.0)
  - maps: 1.1.0 (installed 1.1.0)
  - suspicious_login: 7.0.0
  - twofactor_totp: 11.0.0-dev
  - user_ldap: 1.20.0
  - user_status: 1.9.0 (installed 1.4.0)
  - weather_status: 1.9.0 (installed 1.4.0)

Nextcloud Signing status

No errors have been found.

Nextcloud Logs

No response

Additional info

• Docker Image: nextcloud:29.0.0
• MySQL Database: mysql:8.4.0
• Postgres Databse: postgres:16.3

[ksmonkey123]

Logging the Parameter $additionalConnectionParams in ConnectionFactory.php on Line 166 shows the following values for the "fromDB" and the "toDB":

FromDB

Array
(
    [adapter] => OC\DB\AdapterMySQL
    [charset] => utf8mb4
    [driver] => pdo_mysql
    [wrapperClass] => OC\DB\Connection
    [driverOptions] => Array
        (
            [1005] => 1
        )

    [user] => nextcloud
    [password] => *************
    [host] => nextcloud-db
    [dbname] => nextcloud
    [tablePrefix] => oc_
    [sqlite.journal_mode] => WAL
    [defaultTableOptions] => Array
        (
            [collate] => utf8mb4_bin
            [charset] => utf8mb4
            [tablePrefix] => oc_
        )

    [primary] => Array
        (
            [adapter] => OC\DB\AdapterMySQL
            [charset] => utf8mb4
            [driver] => pdo_mysql
            [wrapperClass] => OC\DB\Connection
            [driverOptions] => Array
                (
                    [1005] => 1
                )

            [user] => nextcloud
            [password] => ****************
            [host] => nextcloud-db
            [dbname] => nextcloud
            [tablePrefix] => oc_
            [sqlite.journal_mode] => WAL
            [defaultTableOptions] => Array
                (
                    [collate] => utf8mb4_bin
                    [charset] => utf8mb4
                    [tablePrefix] => oc_
                )

        )

    [replica] => Array
        (
            [0] => Array
                (
                    [adapter] => OC\DB\AdapterMySQL
                    [charset] => utf8mb4
                    [driver] => pdo_mysql
                    [wrapperClass] => OC\DB\Connection
                    [driverOptions] => Array
                        (
                            [1005] => 1
                        )

                    [user] => nextcloud
                    [password] => *************
                    [host] => nextcloud-db
                    [dbname] => nextcloud
                    [tablePrefix] => oc_
                    [sqlite.journal_mode] => WAL
                    [defaultTableOptions] => Array
                        (
                            [collate] => utf8mb4_bin
                            [charset] => utf8mb4
                            [tablePrefix] => oc_
                        )

                )

        )

)

ToDB

Array
(
    [adapter] => OC\DB\AdapterMySQL
    [charset] => utf8mb4
    [driver] => pdo_mysql
    [wrapperClass] => OC\DB\Connection
    [driverOptions] => Array
        (
            [1005] => 1
        )

    [user] => nextcloud_app
    [password] => ***************
    [host] => postgres
    [dbname] => nextcloud
    [tablePrefix] => oc_
    [sqlite.journal_mode] => WAL
    [defaultTableOptions] => Array
        (
            [collate] => utf8mb4_bin
            [charset] => utf8mb4
            [tablePrefix] => oc_
        )

    [primary] => Array
        (
            [adapter] => OC\DB\AdapterMySQL
            [charset] => utf8mb4
            [driver] => pdo_mysql
            [wrapperClass] => OC\DB\Connection
            [driverOptions] => Array
                (
                    [1005] => 1
                )

            [user] => nextcloud
            [password] => *************
            [host] => nextcloud-db
            [dbname] => nextcloud
            [tablePrefix] => oc_
            [sqlite.journal_mode] => WAL
            [defaultTableOptions] => Array
                (
                    [collate] => utf8mb4_bin
                    [charset] => utf8mb4
                    [tablePrefix] => oc_
                )

        )

    [replica] => Array
        (
            [0] => Array
                (
                    [adapter] => OC\DB\AdapterMySQL
                    [charset] => utf8mb4
                    [driver] => pdo_mysql
                    [wrapperClass] => OC\DB\Connection
                    [driverOptions] => Array
                        (
                            [1005] => 1
                        )

                    [user] => nextcloud
                    [password] => **********
                    [host] => nextcloud-db
                    [dbname] => nextcloud
                    [tablePrefix] => oc_
                    [sqlite.journal_mode] => WAL
                    [defaultTableOptions] => Array
                        (
                            [collate] => utf8mb4_bin
                            [charset] => utf8mb4
                            [tablePrefix] => oc_
                        )

                )

        )

    [port] => 5432
)

It is notable that in the second case (which should be the postgres-database configured via the CLI arguments), the host, dbname and port in the top-level array are set correctly from the CLI, Both the primary and the replica fields and even some top-level fields obviously use the DB-configs from config.php

[joshtrichards]

Possibly related: #45097

I don't have time to look tonight, but your analysis seems sound (and, given the added evidence from the log output, even more so).

Cc: @juliushaertl

[intersectRaven]

I can verify but can't contribute logs. I used the --clear-schema option in my attempt and my original MariaDB DB got deleted. Good thing I have backups.

[Delagen]

I modified https://github.com/nextcloud/server/blob/master/core/Command/Db/ConvertType.php#L247
to

$connectionParams = $this->connectionFactory->getDefaultConnectionParams($type);

and seems all converted

Also have to remove mysql.utf8mb4 parameter from config.php to perform conversion to remove client_encoding from params of DbFactory

[HammyHavoc]

Got hit by this a week ago. Is that particular Nextcloud instance completely screwed?

[joshtrichards]

Yes, unfortunately. 😢 The end result is the parameters get swapped so it likely dropped the source database.

Fortunately, recovery should be a simple matter of restoring from backup (just the db portion): https://docs.nextcloud.com/server/latest/admin_manual/maintenance/restore.html#restore-database (or whatever other mechanism you utilize in your environment for database backup/recovery)

[joshtrichards]

@Delagen Mind pushing that as PR? I know there may be additional changes needed, but it'll start things in motion.

[Delagen]

@joshtrichards I don't mind that my changes solve the problem correctly. It only makes possible for me successful conversion from MySQL to Postgres

[chimpboy]

I have just experienced this problem on a new installation.

[rotdrop]

Would not be it the simplest solution, if the ConnectionFactory::getConnection() method would first check if the $additionalConnectionParams argument contains explicit connection parameters and in that case simply skip that fancy primary / replica stuff? At least for the case were the explicitly specified connection differs from the "internal" database connection configured in config/config.php. In principle this should restore the previous behaviour when using the factory for alternate database connections.