Unable to change `dbhost` using `occ config:system:set` if existing `dbhost` isn't functioning

⚠️ 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 OR Nextcloud Community Forum (I've searched it).
[X] Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
[X] I agree to follow Nextcloud's Code of Conduct.

Bug description

In the "Auto configuration via environment variables" section of the Docker Readme, one can use MYSQL_HOST to set the database connection info, and NEXTCLUOD_UPDATE=1 to automate installation.

The problem is when we want to change that MySQL_HOST into something else. We change the environment variable, restart the container, and we get this error: SQLSTATE[HY000] [2006] MySQL server has gone away.

We investigate and find that, while the autoconfig.php reads environment variables, the config.php is hard-coded at installation time with the actual IP/PORTS:

<?php
$CONFIG = array (
...
  'dbhost' => '10.66.60.1:9971',
...

OK, let's try to use the CLI command to edit that hard-coded file, like this:

php occ config:system:set dbhost --value 1.2.3.4

We then get this error again: SQLSTATE[HY000] [2006] MySQL server has gone away

So the config:system:set can't fix the dbhost variable without trying to connect to the old, faulty dbhost!

That means the only option is either to manually edit the config file, to overwrite it with sed, or to overwrite it entirely (which sounds complex because it has to keep all the generated secrets already present in it).

Steps to reproduce

Trivial Docker-compose example that renames "db" into "db2": https://gist.github.com/gabriel-v/c6e5a1e18686f39649546c1161fadd64

Install docker container using auto-configuration environment variable for MYSQL
Change the IP/PORT of the MYSQL server
Update the environment variables for auto-configuration and restart the Nextcloud server

Expected behavior

Nextcloud starts successfully with the new DB connection info

Installation method

Community Docker image

Nextcloud Server version

confirmed on 26, 27, latest docker images

Operating system

Debian/Ubuntu

PHP engine version

None

Web server

Apache (supported)

Database engine version

MySQL

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": {
        "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
            }
        ],
        "overwriteprotocol": "http",
        "objectstore": {
            "class": "\\OC\\Files\\ObjectStore\\S3",
            "arguments": {
                "bucket": "nextcloud27",
                "region": "optional",
                "hostname": "10.66.60.1",
                "port": "9970",
                "objectPrefix": "urn:oid:",
                "autocreate": true,
                "use_ssl": false,
                "use_path_style": true,
                "legacy_auth": false,
                "key": "***REMOVED SENSITIVE VALUE***",
                "secret": "***REMOVED SENSITIVE VALUE***"
            }
        },
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "localhost",
            "10.66.60.1",
            "ZZZZZZZZZZZZZZZZZZZ.ZZZZZZZZZ.ZZZ"
        ],
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "mysql",
        "version": "27.0.1.2",
        "overwrite.cli.url": "http:\/\/localhost",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbport": "",
        "dbtableprefix": "oc_",
        "mysql.utf8mb4": true,
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "social_login_auto_redirect": "true",
        "loglevel": 2,
        "maintenance": false
    }
}

List of activated Apps

Enabled:
  - activity: 2.19.0
  - calendar: 4.4.3
  - circles: 27.0.1
  - cloud_federation_api: 1.10.0
  - comments: 1.17.0
  - contacts: 5.3.2
  - contactsinteraction: 1.8.0
  - dashboard: 7.7.0
  - dav: 1.27.0
  - federatedfilesharing: 1.17.0
  - federation: 1.17.0
  - files: 1.22.0
  - files_pdfviewer: 2.8.0
  - files_rightclick: 1.6.0
  - files_sharing: 1.19.0
  - files_trashbin: 1.17.0
  - files_versions: 1.20.0
  - firstrunwizard: 2.16.0
  - logreader: 2.12.0
  - lookup_server_connector: 1.15.0
  - nextcloud_announcements: 1.16.0
  - notifications: 2.15.0
  - oauth2: 1.15.1
  - onlyoffice: 8.1.0
  - password_policy: 1.17.0
  - photos: 2.3.0
  - privacy: 1.11.0
  - provisioning_api: 1.17.0
  - recommendations: 1.6.0
  - related_resources: 1.2.0
  - richdocumentscode: 23.5.104
  - serverinfo: 1.17.0
  - settings: 1.9.0
  - sharebymail: 1.17.0
  - sociallogin: 5.4.3
  - support: 1.10.0
  - survey_client: 1.15.0
  - systemtags: 1.17.0
  - text: 3.8.0
  - theming: 2.2.0
  - twofactor_backupcodes: 1.16.0
  - updatenotification: 1.17.0
  - user_status: 1.7.0
  - viewer: 2.1.0
  - weather_status: 1.7.0
  - workflowengine: 2.9.0
Disabled:
  - admin_audit: 1.17.0
  - bruteforcesettings: 2.7.0
  - encryption: 2.15.0
  - files_external: 1.19.0
  - suspicious_login: 5.0.0
  - twofactor_totp: 9.0.0
  - user_ldap: 1.17.0

Nextcloud Signing status

server doesn't start

Nextcloud Logs

An unhandled exception has been thrown:
Doctrine\DBAL\Exception: Failed to connect to the database: An exception occurred in the driver: SQLSTATE[HY000] [2006] MySQL server has gone away in /var/www/html/lib/private/DB/Connection.php:140
Stack trace:
#0 /var/www/html/3rdparty/doctrine/dbal/src/Connection.php(1531): OC\DB\Connection->connect()
#1 /var/www/html/3rdparty/doctrine/dbal/src/Connection.php(1029): Doctrine\DBAL\Connection->getWrappedConnection()
#2 /var/www/html/lib/private/DB/Connection.php(262): Doctrine\DBAL\Connection->executeQuery('SELECT * FROM `...', Array, Array, NULL)
#3 /var/www/html/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php(345): OC\DB\Connection->executeQuery('SELECT * FROM `...', Array, Array)
#4 /var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php(280): Doctrine\DBAL\Query\QueryBuilder->execute()
#5 /var/www/html/lib/private/AppConfig.php(418): OC\DB\QueryBuilder\QueryBuilder->execute()
#6 /var/www/html/lib/private/AppConfig.php(184): OC\AppConfig->loadConfigValues()
#7 /var/www/html/lib/private/AppConfig.php(374): OC\AppConfig->getApps()
#8 /var/www/html/lib/private/legacy/OC_App.php(803): OC\AppConfig->getValues(false, 'installed_versi...')
#9 /var/www/html/lib/private/Server.php(733): OC_App::getAppVersions()
#10 /var/www/html/lib/private/AppFramework/Utility/SimpleContainer.php(171): OC\Server->OC\{closure}(Object(OC\Server))
#11 /var/www/html/3rdparty/pimple/pimple/src/Pimple/Container.php(122): OC\AppFramework\Utility\SimpleContainer->OC\AppFramework\Utility\{closure}(Object(Pimple\Container))
#12 /var/www/html/lib/private/AppFramework/Utility/SimpleContainer.php(138): Pimple\Container->offsetGet('OC\\Memcache\\Fac...')
#13 /var/www/html/lib/private/ServerContainer.php(171): OC\AppFramework\Utility\SimpleContainer->query('OC\\Memcache\\Fac...', true)
#14 /var/www/html/lib/private/AppFramework/Utility/SimpleContainer.php(65): OC\ServerContainer->query('OC\\Memcache\\Fac...')
#15 /var/www/html/lib/private/AppFramework/Utility/SimpleContainer.php(193): OC\AppFramework\Utility\SimpleContainer->get('OC\\Memcache\\Fac...')
#16 /var/www/html/lib/private/AppFramework/Utility/SimpleContainer.php(171): OC\AppFramework\Utility\SimpleContainer->OC\AppFramework\Utility\{closure}(Object(OC\Server))
#17 /var/www/html/3rdparty/pimple/pimple/src/Pimple/Container.php(118): OC\AppFramework\Utility\SimpleContainer->OC\AppFramework\Utility\{closure}(Object(Pimple\Container))
#18 /var/www/html/lib/private/AppFramework/Utility/SimpleContainer.php(138): Pimple\Container->offsetGet('OCP\\ICacheFacto...')
#19 /var/www/html/lib/private/ServerContainer.php(171): OC\AppFramework\Utility\SimpleContainer->query('OCP\\ICacheFacto...', true)
#20 /var/www/html/lib/private/AppFramework/Utility/SimpleContainer.php(65): OC\ServerContainer->query('OCP\\ICacheFacto...')
#21 /var/www/html/lib/private/Server.php(1115): OC\AppFramework\Utility\SimpleContainer->get('OCP\\ICacheFacto...')
#22 /var/www/html/lib/private/AppFramework/Utility/SimpleContainer.php(171): OC\Server->OC\{closure}(Object(OC\Server))
#23 /var/www/html/3rdparty/pimple/pimple/src/Pimple/Container.php(122): OC\AppFramework\Utility\SimpleContainer->OC\AppFramework\Utility\{closure}(Object(Pimple\Container))
#24 /var/www/html/lib/private/AppFramework/Utility/SimpleContainer.php(138): Pimple\Container->offsetGet('OCP\\Lock\\ILocki...')
#25 /var/www/html/lib/private/ServerContainer.php(171): OC\AppFramework\Utility\SimpleContainer->query('OCP\\Lock\\ILocki...', true)
#26 /var/www/html/lib/private/AppFramework/Utility/SimpleContainer.php(65): OC\ServerContainer->query('OCP\\Lock\\ILocki...')
#27 /var/www/html/lib/private/Server.php(2067): OC\AppFramework\Utility\SimpleContainer->get('OCP\\Lock\\ILocki...')
#28 /var/www/html/lib/private/Files/View.php(106): OC\Server->getLockingProvider()
#29 /var/www/html/lib/private/Server.php(467): OC\Files\View->__construct()
#30 /var/www/html/lib/private/AppFramework/Utility/SimpleContainer.php(171): OC\Server->OC\{closure}(Object(OC\Server))
#31 /var/www/html/3rdparty/pimple/pimple/src/Pimple/Container.php(122): OC\AppFramework\Utility\SimpleContainer->OC\AppFramework\Utility\{closure}(Object(Pimple\Container))
#32 /var/www/html/lib/private/AppFramework/Utility/SimpleContainer.php(138): Pimple\Container->offsetGet('OC\\Files\\Node\\H...')

Additional info

Found on the interwebs:

https://help.nextcloud.com/t/how-to-force-update-of-dbhost-value-in-config-php/116945 - no satisfactory answer
https://help.nextcloud.com/t/changing-database-url/125315 - solution was manually editing config file
https://www.reddit.com/r/NextCloud/comments/nj7ydh/how_to_force_update_of_dbhost_value_in_configphp/ - solution was manually editing config file

Questions

Can php occ config:system:set dbhost --value 1.2.3.4 be made not to connect to the previous (inaccessible) dbhost?
What is the supported procedure for changing the dbhost, besides manually editing config file after installation?
~~Is there some kind of override config file we can put in the config dir, to override only the dbhost value with the actual envrionment variable set on the container?~~

Work-Around

https://github.com/nextcloud/helm/tree/main/charts/nextcloud#multiple-configphp-file

Create file config/dbhostoverride.config.php with content:

<?php
$CONFIG = array (
  'dbhost' =>  getenv('MYSQL_HOST'),
);

And add keys to this file for each value expected to change (dbport, passwords, etc.)

Suggestions

This causes a great deal of confusion: one expects the environment variable to be the source of truth for configuring the image. Inspecting the autoconfig.php file also does not help, because it's not obvious the $AUTOCONFIG var is used only at install time, while the $CONFIG var is used at run time.

specify in readme autoconfiguration section that the environment variables are read only once, and persisted in config.php - and suggest duplicating all relevant values in a config file like the one above
make the autoconfig variables override the hardcoded config.php by default?

nextcloud / server