alias db-url is ignored

Need more info here. It's not clear what you have tried.

There may be a bug that does not allow db-url to be used. Search the issue queue if you are having trouble with this. Regarding your comment on the code, I will say that the drush bootstrap process is rather complicated. If a site alias defines a variable e.g. 'databases', then it will be set in the 'alias' context during the bootstrap. This value is available via drush_get_option('databases'), which I believe is copied into DRUSH_DB_CREDENTIALS. Try it; I use drush sql commands with Bugzilla; it can be done.

I don't think that #2 is appropriate.

To be clear, the feature request seems reasonable, but direct access to the alias context is not correct.

No problem. Sorry the documentation did not help you.

I added a little more documentation to the example.aliases.drushrc.php file to show 'databases' usage, and to document the --with-db and --show-passwords options of the drush site-alias command. The use case described in #2 should be possible just by adding an appropriate 'databases' record; the patch from #2 is not necessary.

Perhaps you need an alias that only describes your database configuration for use with the sql commands. It's unclear exactly what you are trying to accomplish; rather than tunneling your SQL connection, perhaps you would be better off executing remote Drush commands on the machine you tunnel to?

If your settings.php configuration information somehow allows Drupal to use your SQL tunnel (?) but Drush is not working the same way, then more info on your settings.php contents would be helpful.

Have you tried adding a databases structure to your alias record? If you did that, then you should be able to set $alias_record['databases']['default']['default']['host'] directly.

To see what your alias should look like with a databases record, run drush sa with the --with-db option:

$ drush sa @gk.dev --with-db
$aliases['gk.dev'] = array (
  'root' => '/srv/www/dev.greenknowe.org',
  'uri' => 'greenknowe.org',
  '#loaded-config' => true,
  'databases' => 
  array (
    'default' => 
    array (
      'default' => 
      array (
        'driver' => 'pgsql',
        'username' => 'www-data',
        'port' => '',
        'host' => 'localhost',
        'database' => 'greenknowedb',
      ),
    ),
  ),
);

If you also add --show-passwords, then you could copy the output into your alias file exactly, and then edit the 'host' line to suit. Does that work for you?

I have to agree with the original poster, alias db-url is being ignored. The docs clearly state that you can set this value for an alias, and it will be used instead of the settings.php $db_url string. So we should either remove this from the docs, or actually make it work :)

I wasn't quite following his use case, but I can share mine. The remote server's $db_url string uses a short servername instead of a fully qualified one. That works fine on the remote server, but can't resolve properly when used locally. I should be able to set the db-url manually in my alias file to fix that.

There are some workarounds in the meantime, like editing my hosts file or adding a local search domain so that the server name resolves, but that's not a good long-term solution.

Okay, I'm hip to trying to fix this or correct the documentation, but in the meantime can you try the 'databases' record per #12 as a workaround? This should be equivalent to db-url.

This is my first attempt at fixing this. It is D7 only at this stage. Feedback welcome.

Attempting to alter the Drupal bootstrap process using information in an alias record or on the URL is not correct. Are you sure that --db-url on the command line does not already work? Moshe was just tweeting about that today. I am sure that the 'databases' record in a site alias works; I use that often, per #12 and #1. In any event, --db-url / 'databases' is only for the sql-* commands; bootstrapping the Drupal site is always done from info in settings.php. Otherwise, Drush would not be operating on the same site as Drupal.

After some trial and error I found that databases only works for sql* commands, but that isn't what I want to use this for.

My use case is that we include the database credentials as environment variables in the apache config and settings.php. In addition to this we will have multiple mysql users who can access the db, with different privileges. This gives us fine grain access controls. For example on prod write access to the db will be limited to prevent drush sqlc-sync @dev @prod and other such things.

The vhost files will have root:root 600 permissions so users can't read the db creds from there. Apache drops privileges after reading its config.

So my approach will allow drush to act on the same Drupal instance, just as a different user. This seems to be the best way to handle this.

Don't you think it would be better to set the environment variable prior to bootstrap, so that your settings.php file has the same environment as it does when executed from the web server?

alias drush='/usr/bin/env MYENV=whatever drush'

If you needed a feature to setenv based on values in a site alias record, I think that would be better than modifying $GLOBALS as in #15.

I have multiple sites I want to use this for and most of them are remote.

With the bash alias approach you've suggested it applies one set of environment variables every time drush is invoked. The alternative is to create a bash alias per site, which seems silly as we already have drush aliases.

I need to double check, but I'm pretty sure that bash aliases won't work with remote sites - which is my primary use case.

With remote sites, you definitely need to pass the value as a command-line argument. It would be possible to change backend invoke to pass environment variables by way of /usr/bin/env, but I think it would be more straightforward to use cli args.

Maybe add a feature to drush_bootstrap_drush that checked for --env=var:value,var2:value2? Then you could specify this on the cli or in a site alias record, and it would work locally or remotely.

Extra credit:

$aliases['site'] = array(
  'env' => array(
    'var' => 'value',
    'var2' => 'value2',
  ),
  'uri' => '...',
  'root' => '...',
);

This would require converting 'env' into whatever string format is supported in the cli, or convert var:value,var2:value2 to an array when it's specified from the cli, or as a string from any source. Of course, json would also be an option for encoding the name/value pairs.

True, this is more complicated than just jamming --db-url somewhere during bootstrap, but it would be more flexible, allowing for different kinds of environment-variable-based logic to appear in settings.php.

Drush already has db-url and database. The documentation suggests that they're there for overriding the values in settings.php.

* - 'db-url': The Drupal 6 database connection string from settings.php.
* For remote databases accessed via an ssh tunnel, set the port
* number to the tunneled port as it is accessed on the local machine.
* If 'db-url' is not provided, then drush will automatically look it
* up, either from settings.php on the local machine, or via backend invoke
* if the target alias specifies a remote server.
* - 'databases': Like 'db-url', but contains the full Drupal 7 databases
* record. Drush will look up the 'databases' record if it is not specified.

My patch is an attempt to make drush do what it says it does on the tin.

The proposed env solution only works when using environment variables. There may be other use cases where the db-url / database override is appropriate - such as trying to avoid an accidental drush sql-sync @dev @prod

The variables array in the drush alias changes the state of the drush site compared to how it is when accessed via a browser.

Sure, I can see your point; was concerned that unknown things might happen with $GLOBALS['databases'], either in Drupal or by some hook before your patch fixes it up. However, since the database connection is not initialized until the bootstrap database stage, I think this technique is safe.

We still need D6 support here, but the code should work the same way. I think I'm behind this patch now; do other maintainers have an opinion?

I found that my proposed patch causes an infinite loop when not pulling db info from the command line or an alias, that is fixed by the static $first_pass. I've also added D6 support, building the URL was always going to be a bit ugly.

static $first_pass is not acceptable. _drush_sql_get_db_spec() must be refactored into the portion prior to calling bootstrap drupal configuration, and the part that does bootstrap. Then the infinite loop can be avoided by calling the new factored-out portion of the routine that deals only with the immediately-available sources of --db-url.

We should have at least one unit test that shows that --db-url can be used to affect the database that Drush bootstraps to.

Related to #22, here is a tweet from @DrushCli earlier today:

Want drush to use a different db user? Make a fake "site" in your sites directory with that db user and put it's uri into an alias.

I think that's more of a workaround than a solution, but in absence of this patch, that is something that will work.

I discussed the "fake sites hack" with @msonnabaum on IRC yesterday before digging into this more and it doesn't really scale for my needs. That's what lead me to working on this patch.

This version of the patch does the following:

• removes the static hack
• sets the correct database for D7 sites
• moves most of the logic to commands/sql/sql.drush.inc - which handles most of the the db logic already
• changes the logic of _drush_sql_get_db_spec() to check the bootstrap level rather than setting it
• adds a new function _drush_sql_override_db_settings() which is called by _drush_sql_get_db_spec() to set the globals

I left the handling of this in _drush_bootstrap_drupal_configuration() as the docs suggest it is responsible for loading the settings.php. The existing code in that function handles loading the db config. At least to me it makes sense to handle it there.

I agree that we need tests for this, but I'm holding off on writing tests until I'm confident the implementation proposed will be accepted.

elseif (drush_bootstrap(DRUSH_BOOTSTRAP_DRUPAL_CONFIGURATION)) { will run all bootstrap phases that have not been run yet up to but not including DRUSH_BOOTSTRAP_DRUPAL_CONFIGURATION. elseif (drush_get_context('DRUSH_BOOTSTRAP_PHASE') === DRUSH_BOOTSTRAP_DRUPAL_CONFIGURATION) { will be false if the bootstrap has not reached, or has advanced past DRUSH_BOOTSTRAP_DRUPAL_CONFIGURATION. I imagine this change must break something. Do the tests pass with your patch?

It seems to me that _drush_sql_override_db_settings should be called during the configuration bootstrap, but not during sql-* command handling. I don't think that setting the database globals from an unbootstrapped site would cause any problems, so I won't get stuck on this point if there is some reason why it has to be this way. Seems the code would be cleaner without it.

Regarding the placement of the code, I think it would be better to put the sql functions that the bootstrap needs into bootstrap.inc. sql.drush.inc is for code that the sql-* commands needs; bootstrap.inc should not depend on it.

Does this code still work (esp. on remote sites) when --db-url is placed in a site alias? How about when a 'databases' array is placed in a site alias? You might need to adjust the information in drush_get_global_options() in includes/drush.inc to intruct Drush how to propagate these values in a backend invoke call. Adding 'context' => 'DRUSH_DB_CREDENTIALS' would do the trick, except that the backend invoke code does not know how to convert from an array to a string (propagated values are always sent to the remote system as commandline arguments). Maybe you need to stash the db-url string into a new Drush context. If 'databases' in a site record can be used to override the database settings in a remote settings.php, then some strategy for propagating this value will also be needed. Maybe the best thing would be to convert 'default' / 'default' from that 'databases' record to a --db-url, and ignore secondary databases.

Good work so far.

I looked quickly at this - need more time to fully evaluate it.

I did notice that it sets the override from within a function called _drush_sql_get_db_spec(). I would have thought the override can be called from _drush_bootstrap_drupal_configuration phase and let keep the get function unchanged.

The commit message says 'Prefer db-url/database values from aliases if they exist'. Are we not trying to support command line provided --db-url?

db-url/database values should be preferred over the values in settings.php, but cli arguments should be preferred over values found in a site alias record. I don't think that the provided patches directly handles aliases at all, but instead relies on the fact that the options in the alias will be set in the 'alias' context, which is overridden by the 'cli' context. So, I think that the commit message is not completely accurate in this regard.

I agree that calling the override from _drush_bootstrap_drupal_configuration should work. A previous attempt at doing this caused an infinite loop, since the get function will attempt to bootstrap to the configuration level in some instances. That could be handled by refactoring the get function.

I'm not exactly sure what your point or question is in #30. settings.php is parsed in DRUSH_BOOTSTRAP_CONFIGURATION; most of those other items are parsed up-front in DRUSH_BOOTSTRAP_DRUSH. See includes/context.inc for different ways to access them.

I'm at a conference this week, but I think I know what Greg and Moshe are after with this. I will try to work on this again on the weekend.

This still needs tests, but I think it is a lot closer than previous iterations. No infinite loops, no hacks and just a few lines of clean code.

updating status

So do we add db-url as a global option? I'm a little uncomfortable with this. We should look at commands that currently list --db-url as an option and see if they need changing.

Haven't tried this yet, but code is looking better.

One comment I have is that this turns --db-url into a global option, which might hurt people who set $options['db-url'] instead of using command-specific. $db_url is already a partially-global option, since it affects sql-* commands, but now it affects bootstrap. I am okay with this, but we will need a change notification record.

I assumed that --db-url was already in the global options, since it can be used with all of the sql-* commands; however, it is not. I currently use it by defining a site alias that points to a (non-Drupal) "site" that specifies the database to work on. In this way, I can use Drush to sql-sync etc. non-Drupal databases.

But to answer #36, yes, we would have to add db-url as a global option. Currently, the sql-* commands do not list it as a valid command option.

I have added the global options for db-url and database. The language probably needs some polish, but I wanted to get it in, then we can tweak it. For D6 db-prefix is also supported by this patch, does it need to be added as a documented global option too?

I don't know enough about drush internals to know the impact of making db-url and databases global options. Although the sql* actions use these options from the command line, alias or settings.php no where is it defined as an option. site-install defines db-url and db-prefix as options for installing a new site - both of which are supported by this patch.

I told skywashd that I would like to see more demand for this before we proceed. I'm not too comfortable with hacking Drupal bootstrap this way.

I agree with moshe, I don't see any use-case for this feature. I retract my comment in #13, that could be corrected by just using a FDQN in my own settings.php file.

Given this has been postponed, I have filed a bug report against the docs, which I think are misleading. See #1434074: Documentation for db-url and database in aliases is misleading.

I was looking forward this feature. Firstly because I used to run drush commands on different, virtual machine, but with shared code; this is hard now, because drush cannot access the database using 'localhost' on both, physical and virtual, environments and I must connect the DB using IP address, which I'd like to avoid. Secondly because if I want to run drush on production site (where I don't have a ssh access) at the moment I have to change db settings every time I checkout deployed branch from repo. So a possibility to alter db-url and database in drush alias would be much appreciated.

Could you try #18? If you added custom code to your settings.php file to select the database based on an environment record (either as part of your normal bootstrap, or with a DRUSH_* env record that is an exception to the normal process of running settings.php), then Drush could bootstrap Drupal as usual, without resorting to this potentially fragile method of hacking the bootstrap.

If that worked for you, I could envision a feature of Drush that set environment variables prior to bootstrap based on command line options. You could then put these into your site alias record. Like #20.

I just discovered this thread and would love to see this implemented for the same reasons as mentioned in #10.

Greg, your suggestion in #44 is a reasonable approach. I tested customizing settings.php (D6) and that worked well for me:

if (isset($_SERVER['DRUSH_DB_URL'])) {
  $db_url = $_SERVER['DRUSH_DB_URL'];
}
else {
  $db_url = 'mysqli://drupal:password@localhost/drupal';
}

Now I can avoid sockets by calling:
/usr/bin/env DRUSH_DB_URL=mysqli://drupal:password@127.0.0.1/drupal drush @dev status
(Is use of sockets in drush ever a good idea? Might it be wise for drush to replace 'localhost' with '127.0.0.1' automatically behind the scenes - perhaps as an option?)

Would it be prudent to have separate credentials for sql-* and for drupal? My "drupal" db user only has permissions on the "drupal" database. For instance, it could not create a new database, which requires an admin user. If this feature becomes adopted, it seems that "db_url" should not be used interchangeably for both sql-* usage and for drupal (settings.php). Perhaps "drupal_db_url" vs "sql_db_url" or similar might be better.

In other words, I could envision having an alias record having admin credentials set in db-url for doing sql-*admin work (e.g. backups, cloning), but these should be distinct from the regular "drupal" user credentials.

I'm not familiar with D7 so I'm not sure how this would work with its settings.php.

#45 is a reasonable workaround pending any potential future support of setting environment variables automatically in drush (e.g. from the contents of an alias record).

Regarding sockets, 'localhost' always works on my systems, so I have no opinion or suggestion on this subject.

Drush commands such that have options that require elevated permissions (e.g. sql-sync --create-db) also take options --db-su and --db-su-pw, which allow the user to supply alternate username/password pairs for a more privileged operations. You can set these options in your alias record (or in your drushrc.php file, if they are consistent for all sites), and they will be available as needed. Drush simply takes most of the db settings from db_url, and substitutes in the sql admin username and password in place of the drupal username and password when needed.

I'm running a drupal 6 site in a virtual machine (via Vagrant). To the VM, the database is on localhost, but on my host machine, the database is at db.vm.

So here's another workaround. Got the idea from #25 (using a fake sites/ directory), but instead of making a fake settings.php file, why not just detect if drush is running and give it a different db_url?

Here's a snippet from the settings.php file for the dev site:

$db_url = 'mysqli://root@localhost/db_name';
$db_prefix = '';

// Use the database on the vm if running drush
if (PHP_SAPI == 'cli' && function_exists('drush_main')) {
  $db_url = 'mysqli://root@db.vm/db_name';
}

Found the drush detection code in another drush issue.

So far, this solution works well.

It's not appropriate to re-open this patch and return all the way to #2, ignoring the work and discussion that followed, If this issue is to be re-opened, it should be based on the later patches, re-rolled against Drush 8.x-6.x-dev, and tested against at least Drupal 7 and Drupal 8 (probaby Drupal 6 too). If you don't want to do all of that, your alternative is to maintain #2 as your own local patch to Drush.

I really think that if you want to do this, you should make yourself another sites/foo folder, copy your settings.php file there, and then point your Drush alias to the alternate settings.php file. If you did this, Drupal could bootstrap as usual. I don't think it's a good idea to try to make Drush bootstrap Drupal in a different way than core does. Even if you get it to work today, it is likely to break in the future when things change. I think this issue is likely to remain "won't fix".

This issue was marked closed (won't fix) because Drush has moved to Github.

If desired, you may copy this bug to our Github project and then post a link here to the new issue. Please also change the status of this issue to closed (duplicate).

Please ask support questions on Drupal Answers.

I too was looking forward to the localhost override being a global option (rather than having to mess about with various settings.php files). I use several different types of local environments for different situations as well as extensive remote servers, and would find this feature, if it worked as it suggests in the docs, extremely useful. Just saying...