[DX] Switch to a simplified array syntax for database connection information in settings.php

[olafgrabienski]

I just tried to install Backdrop 1.5 in a local AMPPS environment. During installation, after being on the language page, I got the message

Fatal error: Class 'DatabaseTasks_' not found in {...}/core/includes/install.inc on line 1362

and wasn't able to continue the installation.

Line 1362 concerns the database driver. Googling something like drupal error database driver led me to a comment on d.org regarding Backup and Migrate about a problem with special characters in the database password string. I had a look in my settings.php, noticed a forward slash in the password string, changed the database password in the relevant places and was able to continue the installation.

Are forward slashes (and possibly particular other special characters) in the database password string not supported by Backdrop for a certain technical reason? If so, we should document it somewhere. If not, can we fix that?

ADVOCATE: @yorkshire-pudding

[yorkshire-pudding]

I've added a PR for this issue. https://github.com/backdrop/backdrop/pull/4567

EDIT: now changed install.core.inc to write settings in this form.

Tugboat not working - I can close and reopen later if issue cleared up.

I've tested locally and it works with simplified string, this new simplified array and still works with old D7 style array. If testing locally in something like Lando, DDEV or Docksal, you may need to add this line to your settings.php

if (isset($_SERVER["BACKDROP_SETTINGS"])) unset($_SERVER["BACKDROP_SETTINGS"]);

I don't know how tugboat loads settings, so it may not be possible to test this in tugboat anyway, though it shouldn't break any tests. It has passed PHPCS and CSPELL, so that's a start.

I also took the liberty of updating the URL in the comments of this section of settings.php from api.b.org to docs.b.org. I also added an example if they needed to override 'port'.

Will likely need an update to https://docs.backdropcms.org/documentation/database-configuration but won't need a change record as it is still compatible with all methods.

[yorkshire-pudding]

Needs a change in backdrop_rewrite_settings() within install.inc but not sure how. Any ideas?

This is what happens currently using the installer form (this is in lando using the lando default db credentials):

$database = array (
  'driver' => 'mysql',
  'database' => 'backdrop',
  'username' => 'backdrop',
  'password' => 'backdrop',
  'host' => 'database',
  'prefix' => '',
);
  'driver' => 'mysql',
  'database' => 'database_name',
  'username' => 'user',
  'password' => 'pass',
  'host' => 'localhost',
  'prefix' => '',
);

[klonos]

@yorkshire-pudding re the last comment, it seems that backdrop_rewrite_settings() is expecting all settings in settings.php to be single lines, so in the case of

$database = array(
  'driver' => 'mysql',
  'database' => 'database_name',
  'username' => 'user',
  'password' => 'pass',
  'host' => 'localhost',
  'prefix' => '',
);

...it only replaces the $database = array ( bit and ignores the rest of the lines that belong to the same array declaration.

I'm working on other things now, but I can have a look later (unless someone else beats me to it).

[yorkshire-pudding]

@klonos - got an idea - just testing it.

[yorkshire-pudding]

Install runs, tests pass; by which of course I mean all tests pass except for testLayoutUpgrade() and cspell. The cspell issues are nowhere near what I've worked on.

[yorkshire-pudding]

@klonos - you mentioned on the PR:

Lets also mention which of these keys are optional, and what they default to if omitted.

As far as I can tell, port is optional but that is already made clear in the comment. Looking at the code before, if prefix is left blank it reverts to '' but we already have that anyway.

Interestingly, the code that checks what is added only says:

    $required_parts = array('driver', 'database', 'username', 'host');

I don't know why password wouldn't be required, but I wouldn't want to say it was optional as the vast majority will have a database password.

If there is anything I'm missing, please let me know.

[klonos]

I don't know why password wouldn't be required, but I wouldn't want to say it was optional as the vast majority will have a database password.

IIRC, in many OSes, MySQL and MariaDB do not have any "root" password specified at all (at least that was the case in older versions) - it is completely blank and you need to specify that yourself when setting up your db. I believe that the expectation was to not open the db port to the public, and restrict access to it by only allowing access from localhost (which meant that you were connecting from within the same host machine, which meant that you must have authenticated via a local user). Something like that. I'm getting old and it's been years since I have manually installed, configured and maintained a server 😅

Anyway, I would expect us to be listing all keys in the db array for which we are specifying defaults or they can be omitted, and stating what the default values are. Currently, these are the port and prefix, but I also thought that we were defaulting the host to localhost, but we aren't (shouldn't we?).

[yorkshire-pudding]

I've seen on functions the concept of optional and default, but I'm really not clear what we want here. What sort of format do we want to use. Keep in mind that there was no guidance before and there are no models for this elsewhere in settings.

This is what the default screen looks like:

So we do seem to default in the UI installer to 127.0.0.1 (see #2520) but from what I understand localhost is better if it works as it uses a socket rather than TCP/IP so is faster.

Required:

• driver
• database
• username
• host (this was a default of localhost in settings.php but is a default of 127.0.0.1 in the UI installer)

Optional

• port - normally standard (i.e. `3306) - this is the only one not actually included in the current example.
• prefix - normally '' and is set by default now, so I don't think we should leave it out.
• password - I guess defaults to '' but I'm not that keen on suggesting that is optional

[klonos]

Yeah, we allow adding that information either manually/directly in the settings.php file, or via the installer. It's not good that we have different defaults between the two - there should be consistency.

Also, driver will always be mysql. We don't expose this in the UI, so I think that we can omit it in the array in settings.php. We should still allow for it to be optionally specified I guess, but that would mean that the site is an instance of Silkscreen - not Backdrop. Right?

So perhaps we should simplify the array to this:

$database = array(
  'database' => 'database_name',
  'username' => 'user',
  'password' => 'pass',
  'host' => 'localhost',
);

And then in the comments note that there are other optional keys allowed, and what each one of them is used for.

[yorkshire-pudding]

@klonos - I've updated with guidance and reduced default required setting defaults as agreed.

Please could I ask for your support on https://github.com/backdrop/backdrop/pull/4567#discussion_r1394631098 It might be nice, if you have time, to do a video call where you can teach me, but if not, please can you suggest some changes. Thanks

[yorkshire-pudding]

@klonos and I have discussed the challenges with backdrop_rewrite_settings() in replacing the default database array with the entered settings. The primary challenge is that the current function is written for single line settings. My current approach would use the number of lines in the vanilla installation to know how many lines to remove, but Greg has flagged up the possible places where this may fall down. There are also a number of other possibilities that can cause this function to break.

Greg has started working on a solution (#6297) using the php function token_get_all(), which should provide a much more robust approach.

[izmeez]

I think there are two issues entangled here or else there is another issue I should address my comment to.

There is the issue of the use of special characters in mysql passwords.

And, in several comments by @jenlampton, there is the issue (explanation) that the D7 array syntax can be used in settings.php file. https://github.com/backdrop/backdrop-issues/issues/2231#issuecomment-306630110

This approach solved my case of trying to set the collation to utf8mb4_unicode_ci reliably where MySQL defaults to utf8mb4_general_ci and directives in my.ini to set server defaults were failing. I would suggest something like this should be in the settings.php file as a reminder.

/**
 * Like D7, each database connection can be specified as an array of settings.
 * May be used to override MySQL defaults like utf8mb4_general_ci collation.
 *
 * $databases['default']['default'] = array(
 *  'driver' => 'mysql',
 *  'database' => 'databasename',
 *  'username' => 'username',
 *  'password' => 'password',
 *  'host' => 'localhost',
 *  'charset' => 'utf8mb4',
 *  'collation' => 'utf8mb4_unicode_ci',
 *);
 */

I also noticed mention that localhost may perform faster because it is a socket rather than TCP/IP.

First, I thought to try $database_collation = 'utf8mb4_unicode_ci'; in settings.php only to discover it is not currently part of Backdrop core. Using the array format works but did issue a warning:

Deprecated function: md5(): Passing null to parameter #1 ($string) of type string is deprecated in backdrop_install_config_directories() (line 806 of \core\includes\install.inc). 

Does this need a PR to get it into the settings.php file?

[yorkshire-pudding]

@izmeez - there is a PR - look at the top, but it is not robust enough to merge; it is currently blocked by https://github.com/backdrop/backdrop-issues/issues/6297; hopefully @klonos will have some time to work on that issue soon.

[izmeez]

What I said in my comment doesn't need anything to be fixed. It already works. Using the array syntax like D7 works out of the box in Backdrop 1.27.0 release. It just needs to be known and the best way is to include a comment block in the settings.php file, and resolve the deprecation notice.

[izmeez]

It took me way too long to learn to try it.

[bugfolder]

I tend to agree that the "Database configuration" comment in settings.php needs some updating; at the very least, the URL of "advanced database documentation" should be updated to the docs.backdropcms.org URL (the current api.backdropcms.org redirects there). But I do think that should be a separate issue, something like "Improve the database configuration comments in settings.php."

So this: https://github.com/backdrop/backdrop-issues/issues/6378

[izmeez]

@bugfolder Thanks for opening a new issue. I've commented over there.

As for this issue, it helped me, but I guess I don't really know what it's about. The fix to support special characters in mysql passwords looks good and simple. But, I really don't understand what all the rest is about.

[yorkshire-pudding]

This was discussed at the Weekly Dev Meeting on 2024-09-05. See https://www.youtube.com/live/s9Y-wZLSWpM?si=EErsKR8C25QopmzB&t=2678

Key points:

• Should not be blocked by the desire to have a better method for replacing text in settings.php files so proceed.
• Minimise documentation in settings.php and put rest into documentation.

[yorkshire-pudding]

The PR has been rebased. I've shortened the comment to:

/**
 * Database configuration:
 *
 * Most sites can configure their database by entering the connection details
 * below. If you need to:
 *   - customize the 'port', 'prefix', or 'driver'
 *   - use primary/replica databases
 *   - use multiple connections
 * see the advanced database documentation at
 * https://docs.backdropcms.org/database-configuration
 */

To recap, this includes:

• changes to settings.php to give a much simplified database array
• change bootstrap to support this format as well as the string format and the D7 array format
• change the installer to write database setting in this format. It uses a line count to set how many lines to remove based on the default array being in settings.php.

@quicksketch @jenlampton @laryn @docwilmot Happy for any feedback. I can start to look at documentation changes if we're agreed mostly on the approach.

[izmeez]

@yorkshire-pudding Thank you for taking the time to rebase and update the PR. I have looked at the code changes. I am not convinced that this is necessary with all the code changes. As I mentioned in an earlier comment in this thread resulting in a separate issue #6378, all that may be needed is an extra comment in the settings.php file. To this end I will add a PR to that issue to allow feedback on the two approaches.

[izmeez]

Now that I have added a PR to the other issue I think I better understand that, at least part of the purpose of, this issue and PR is to eliminate the odd syntax $databases['default']['default'] =. However, I am not sure having three methods to specify the database connection is needed.

[izmeez]

On the subject of collation unicode is preferable and more accurate than general as detailed in the following post and others. Even then there are newer collation methods as discussed. https://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci

[izmeez]

I think I will open a new issue for collation method discussions as it has implications to other functions in Backdrop.

[quicksketch]

We discussed this today in the weekly meeting. Some changes discussed and agreed upon (both @yorkshire-pudding and @izmeez were present):

• Add charset and collation as allowed options in the new syntax, but provide reasonable defaults.
• Include charset and collation in the docblock list of advanced configuration options which can be set but link to the documentation for details.
• The default for charset should be changed to utf8mb4. So if you're using the simplified array syntax, that charset is implied.

[yorkshire-pudding]

@izmeez - by all means open a ticket for discussion on collation

In /core/includes/database/charset_converter.inc a default for collation is set of utf8mb4_general_ci so it makes sense to be that, at least for now. Also in utf8mb4IsSupported() within /core/includes/database/mysql/database.inc it is used

[izmeez]

@yorkshire-pudding Yes, I will open a separate issue, but the items you identify are all the more reason why users must have the knowledge and ability to change the collation, especially for different languages. Backdrop should likely move towards unicode_ci as default.

[izmeez]

I have created a new issue to "Make database collation consistent and configurable" #6711.

[yorkshire-pudding]

@quicksketch @laryn @docwilmot @jenlampton I have updated the PR. It is generating a failure for PHP 5.6 batch 1 but I don't think this is related.:

Detailed test results
---------------------
---- Image: Image style flood protection (ImageStyleFloodProtection) ----

Status    Group      Filename          Line    Function                            
------------------------------------------------------------------------------------------------------------------------
Fail      Other      image.test        1970    testFloodProtection()                                                   
    Correct number of images (1) return 403 access denied.
Error: Process completed with exit code 1.

@quicksketch - I did also have a look at /core/scripts/install.sh and thought it may make sense to move that on from using a db url too. As bee already collects the information separately it would be relatively simple to update that, but I don't know if anything else depends on the current setup. Also, I did try to find if any tests needed updating but I wasn't able to find it if there were (while I'm familiar with the bee testing regime, I am less familiar with Backdrop's testing approach).

[yorkshire-pudding]

I've updated the /.tugboat/settings.local.php to use a $database array, but it still didn't appear to be working, so I have made backdrop_install_config_location() more flexible so it can cope with either a string or an array. Now tugboat is working and showing that utf8mb4 is enabled:

[yorkshire-pudding]

I noticed that this wasn't working for CLI install. I have added these in as #hidden fields on the form. If there is a suitable source for these to be dropdown and we can also consider validation (i.e. ensure charset and collation are compatible) then could make configurable in the UI (and CLI) (@izmeez - I'm trying to work it towards the configurable element of your request at #6711)

To make configurable in the CLI, we would need to either move core/scripts/install.sh to using individual parameters for DB config (preferred) or stick with db url and add in additional options

Bee can (and will) be adapted as required.

EDIT: And now cSpell passes ????

EDIT: if you want to test on something like Lando or another system that provides the db settings at server level, you may need to add this line to your settings.php:

if (isset($_SERVER["BACKDROP_SETTINGS"])) unset($_SERVER["BACKDROP_SETTINGS"]);

[izmeez]

I'm having some difficulty understanding the instructions on testing this. I guess I may just have to experiment with it in conjunction with the PR.

EDIT: if you want to test on something like Lando or another system that provides the db settings at server level, you may need to add this line to your settings.php:

if (isset($_SERVER["BACKDROP_SETTINGS"])) unset($_SERVER["BACKDROP_SETTINGS"]);

[yorkshire-pudding]

I'm having some difficulty understanding the instructions on testing this. I guess I may just have to experiment with it in conjunction with the PR.

EDIT: if you want to test on something like Lando or another system that provides the db settings at server level, you may need to add this line to your settings.php:

if (isset($_SERVER["BACKDROP_SETTINGS"])) unset($_SERVER["BACKDROP_SETTINGS"]);

@izmeez - in Lando (and possibly in other docker-based systems) the database settings are never written to settings.php and the installer skips the database step unless that line is added to settings.php. You may not need this in your local environment.

[quicksketch]

I posted a review to the PR: https://github.com/backdrop/backdrop/pull/4567#pullrequestreview-2361092221

Overall, looking great!

[yorkshire-pudding]

Thanks @quicksketch - I learned a new bit of syntax and have implemented those changes. The change from implode to serialize enabled a small simplification which I've also added.

I have tested this through the UI and through bee (which uses the install.sh script) with a complex db password ('P@s$/W0rd#;') and both installed without a problem.