sql-sync ignores drush-script definition in alias

[jurgenhaas]

In my aliases for remote hosts I do have the drush-script being defined which is used instead of drush when building the command to be executed. This is working for all drush commands except the sql-sync command.

So, when I call drush @remote.site sql-dump --result-file=/tmp/test.sql, this is working correctly and when using debug, I can see that this is being executed:

Backend invoke: ssh -o PasswordAuthentication=no HOSTNAME 'env COLUMNS=149 MYSCRIPT  --debug --uri=www.example.com --root=/var/www/web --verbose  sql-dump   --result-file=/tmp/test.sql 2>&1' 2>&1

You can see that MYSCRIPT is being executed.

However, when I call drush sql-sync @remote.site @local.site this is being ignored and drush is being called instead of MYSCRIPT:

Backend invoke: ssh -o PasswordAuthentication=no HOSTNAME 'env COLUMNS=149 drush  --debug --uri=www.example.com --root=/var/www/web --verbose  sql-conf   --all 2>&1' 2>&1

That looks like a bug, isn't it?

[weitzman]

Seems so. Please also provide the Drush version and contents of drush sa @remote.site and drush sa @local.site

[jurgenhaas]

Drush version: 8.1.2

$aliases["remote.site"] = array (
  'uri' => 'www.example.com',
  'root' => '/var/www/web',
  'remote-host' => 'HOSTNAME',
  'drush-script' => 'MYSCRIPT',
);

$aliases["local.site"] = array (
  'root' => '/var/www/customer/web',
  'uri' => 'customer.localhost',
);

[weitzman]

I suspect that the sql-conf is being issued by the target machine during the sql-dump call. so MYSCRIPT is totally unknown there. If you want to affect how this sql-conf runs, you may have to do some drushrc work at @remote.site.

[weitzman]

Oh, you posted that sql-dump is working already. Can you post the full verbose output of that. Curious how the sql-conf call appears in the logs there

[jurgenhaas]

It's interesting, sql-dump doesn't require sql-conf, here is the full log:

Using the Drush script found at /var/www/customer/vendor/drush/drush/drush.launcher using pcntl_exec
Loading drushrc "/home/jurgenhaas/.drush/drushrc.php" into "home.drush" scope. [0 sec, 0 bytes]                                           [bootstrap]
Cache HIT cid: 8.1.2-commandfiles-0-5016dc31743758285e73995829789734 [0.01 sec, 0 bytes]                                                      [debug]
Loaded alias @remote.site from file /etc/drush/aliases.drushrc.php [0.05 sec, 0 bytes]                                   [notice]
Begin redispatch via drush_invoke_process(). [0.06 sec, 0 bytes]                                                                             [notice]
Backend invoke: ssh -o PasswordAuthentication=no HOSTNAME 'env COLUMNS=149 MYSCRIPT  --debug --uri=www.example.com --root=/var/www/web --verbose  sql-dump   --result-file=/tmp/test.sql 2>&1' 2>&1 [0.06
sec, 0 bytes]
Calling proc_open(ssh -o PasswordAuthentication=no HOSTNAME 'env COLUMNS=149 MYSCRIPT  --debug --uri=www.example.com --root=/var/www/web --verbose  sql-dump   --result-file=/tmp/test.sql 2>&1' 2>&1);
Cannot load Zend OPcache - it was already loaded
Using the Drush script found at /var/www/vendor/drush/drush/drush.launcher using pcntl_exec
Cannot load Zend OPcache - it was already loaded
Drush preflight prepare loaded autoloader at                         [preflight]
/var/www/vendor/autoload.php [0 sec, 3.46 MB]
Starting Drush preflight. [0 sec, 3.46 MB]                                                                                                [preflight]
Cache HIT cid: 8.1.2-commandfiles-0-347628286ff3d46d434bcce8cab1c2e9 [0.01 sec, 3.49 MB]                                                      [debug]
Scanning into /etc/drush for /.*aliases\.drush(8|)rc\.php$/ [0.03 sec, 5.55 MB]                                                               [debug]
Scanning into /etc/drush for /self\.alias\.drush(8|)rc\.php$/ [0.03 sec, 5.55 MB]                                                             [debug]
Scanning into /var/www/vendor/drush/drush/includes/.. for /.*aliases\.drush(8|)rc\.php$/ [0.03 sec, 5.55 MB]                                  [debug]
Scanning into /var/www/vendor/drush/drush/includes/.. for /self\.alias\.drush(8|)rc\.php$/ [0.03 sec, 5.55 MB]                                [debug]
Scanning into /home/jurgenhaas/.drush for /.*aliases\.drush(8|)rc\.php$/ [0.03 sec, 5.56 MB]                                                  [debug]
Scanning into /home/jurgenhaas/.drush for /self\.alias\.drush(8|)rc\.php$/ [0.03 sec, 5.56 MB]                                                [debug]
Scanning into /var/www/web/../drush for /.*aliases\.drush(8|)rc\.php$/ [0.03 sec, 5.56 MB]                                                    [debug]
Scanning into /var/www/web/../drush for /self\.alias\.drush(8|)rc\.php$/ [0.03 sec, 5.56 MB]                                                  [debug]
Scanning into /var/www/web/drush for /.*aliases\.drush(8|)rc\.php$/ [0.04 sec, 5.56 MB]                                                       [debug]
Scanning into /var/www/web/drush for /self\.alias\.drush(8|)rc\.php$/ [0.04 sec, 5.56 MB]                                                     [debug]
Scanning into /var/www/web/sites/all/drush for /.*aliases\.drush(8|)rc\.php$/ [0.04 sec, 5.56 MB]                                             [debug]
Scanning into /var/www/web/sites/all/drush for /self\.alias\.drush(8|)rc\.php$/ [0.04 sec, 5.56 MB]                                           [debug]
Scanning into /var/www/web/sites/www.example.com for /.*aliases\.drush(8|)rc\.php$/ [0.04 sec, 5.56 MB]                                    [debug]
Scanning into /var/www/web/sites/www.example.com for /self\.alias\.drush(8|)rc\.php$/ [0.04 sec, 5.56 MB]                                  [debug]
Bootstrap to phase 0. [0.06 sec, 6.69 MB]                                                                                                 [bootstrap]
Bootstrap to phase -1. [0.06 sec, 6.69 MB]                                                                                                [bootstrap]
Found command: sql-dump (commandfile=sql) [0.06 sec, 6.69 MB]                                                                             [bootstrap]
Calling hook drush_sql_dump [0.06 sec, 6.75 MB]                                                                                               [debug]
Drush bootstrap phase : bootstrap_drupal_root() [0.06 sec, 6.88 MB]                                                                       [bootstrap]
Initialized Drupal 8.1.3 root directory at /var/www/web [0.07 sec, 6.88 MB]                                                               [bootstrap]
Find command files for phase 1 (max=3) [0.07 sec, 5.76 MB]                                                                                    [debug]
Cache HIT cid: 8.1.2-commandfiles-1-19961844030358e5d8fd65e177e491e7 [0.07 sec, 5.76 MB]                                                      [debug]
Drush bootstrap phase : bootstrap_drupal_site() [0.08 sec, 6.62 MB]                                                                       [bootstrap]
Initialized Drupal site www.example.com at sites/default [0.08 sec, 6.62 MB]                                                           [bootstrap]
Find command files for phase 2 (max=3) [0.08 sec, 6.62 MB]                                                                                    [debug]
Cache HIT cid: 8.1.2-install_profile-66ecfeb9791a023150773849f1550c5d [0.08 sec, 6.62 MB]                                                     [debug]
Cache HIT cid: 8.1.2-commandfiles-2-4b61ef228a19df5ad80d17078b12eaa6 [0.08 sec, 6.62 MB]                                                      [debug]
Drush bootstrap phase : bootstrap_drupal_configuration() [0.08 sec, 6.63 MB]                                                              [bootstrap]
Find command files for phase 3 (max=3) [0.08 sec, 6.75 MB]                                                                                    [debug]
sql-query: SHOW TABLES; [0.08 sec, 6.88 MB]                                                                                                  [notice]
Executing: mysql --defaults-extra-file=/tmp/drush_xeiO2z --database=drupal_fimf --host=127.0.0.1 --port=3306 --silent  < /tmp/drush_HKH26W
  batch
  block_content
  block_content__body
  block_content_field_data

[weitzman]

Thanks. can you post full verbose log for drush sql-sync @remote.site @local.site?

[weitzman]

Is there Drush 8.1.2 on both remote and local? To be sure, you could post output of drush status for each machine

[jurgenhaas]

Yes, it's Drush 8.1.2 on both hosts, local and remote. The log for sql-sync looks like this:

Using the Drush script found at /opt/composer/vendor/drush/drush/drush.launcher using pcntl_exec
Loading drushrc "/home/jurgenhaas/.drush/drushrc.php" into "home.drush" scope. [0 sec, 0 bytes]                                           [bootstrap]
Cache HIT cid: 8.1.2-commandfiles-0-d4907599a225e57286ebb04928291fcb [0.01 sec, 0 bytes]                                                      [debug]
Bootstrap to phase 0. [0.06 sec, 0 bytes]                                                                                                 [bootstrap]
Bootstrap to phase -1. [0.06 sec, 0 bytes]                                                                                                [bootstrap]
Found command: sql-sync (commandfile=sqlsync) [0.06 sec, 0 bytes]                                                                         [bootstrap]
Calling drush command init function: drush_sql_sync_init [0.06 sec, 0 bytes]                                                              [bootstrap]
Loaded alias @local.site from file /home/jurgenhaas/.drush/aliases.drushrc.php [0.08 sec, 0 bytes]                                             [notice]
Loaded alias @remote.site from file /etc/drush/aliases.drushrc.php [0.09 sec, 0 bytes]                                   [notice]
Calling hook drush_sqlsync_sql_sync_validate [0.09 sec, 0 bytes]                                                                              [debug]
Backend invoke: ssh -o PasswordAuthentication=no HOSTNAME 'env COLUMNS=149 drush  --backend=2 --verbose --debug --uri=www.example.com --root=/var/www/web  sql-conf   --all 2>&1' 2>&1 [0.09 sec, 0
bytes]
ssh -o PasswordAuthentication=no HOSTNAME 'env COLUMNS=149 drush --backend=2 --verbose --debug --uri=www.example.com --root=/var/www/web  sql-conf   --all 2>&1' 2>&1 [0.09 sec, 0 bytes]
The external command could not be executed due to an application error. [0.72 sec, 0 bytes]                                               [error]
Backend invoke: env COLUMNS=149 /var/www/customer/web/../vendor/bin//drush.launcher  --backend=2 --verbose --debug                            [command]
--root=/var/www/customer/web --uri=customer.localhost  sql-conf   --all 2>&1 [0.72 sec, 0 bytes]
env COLUMNS=149 /var/www/customer/web/../vendor/bin//drush.launcher  --backend=2 --verbose --debug --root=/var/www/customer/web                  [notice]
--uri=customer.localhost  sql-conf   --all 2>&1 [0.72 sec, 0 bytes]
Error: no database record could be found for source @remote.site [0.85 sec, 0 bytes]                                              [error]
Returned from hook drush_sqlsync_sql_sync_validate [0.85 sec, 0 bytes]                                                                        [debug]
Command dispatch complete [0.85 sec, 0 bytes]                                                                                                [notice]

[weitzman]

Thanks. During drush_sqlsync_sql_sync_validate(), we get to this line which issues a sql-conf call to remote. Someone needs to debug why that call sql-conf call doesn't happen with MYSCRIPT.

[jurgenhaas]

I'll have a look.

[jurgenhaas]

So here is my analysis:

When calling drush sql-dump (or all of the other commands I tried), getting into drush_invoke_process() provides $backend_options with these values:

$backend_options = array(
    [drush-script] => MYSCRIPT
    [remote-host] => HOSTNAME
    [remote-user] =>
    [integrate] => 1
    [additional-global-options] => Array()
    [interactive] => 1
)

This is called from drush_do_command_redispatch() in file includes/drush.inc:1211.

Then, in contrast, drush sql-sync is calling drush_invoke_process() from drush_sitealias_add_db_settings in file includes/sitealias.inc:994 with these values:

$backend_options = array(
  [integrate] =>
  [override-simulated] => 1
)

So this is where e.g. `drush-script' is missing.

I'm not exactly sure how to fix this, because in drush_do_command_redispatch() there is a lot of magic going on to build the correct $backend_options even with values that are not available in drush_sitealias_add_db_settings() at all.

What I did to fix this for my current context is to replace

$values = drush_invoke_process($alias_record, "sql-conf", array(), array('all' => TRUE), array('integrate' => FALSE, 'override-simulated' => TRUE));

in drush_sitealias_add_db_settings() with

$options = array('integrate' => FALSE, 'override-simulated' => TRUE) + $alias_record;
$values = drush_invoke_process($alias_record, "sql-conf", array(), array('all' => TRUE), $options);

But I can imagine that this is probably not the correct way to go about this.

[weitzman]

Great analysis. Maybe @greg-1-anderson has some thoughts here. The right answer might be "wait for an upcoming site alias rewrite"

[weitzman]

One workaround would be to remove the database validation in drush_sqlsync_sql_sync_validate(). Thats the only reason we call sql-conf during sql-sync

[jurgenhaas]

Interesting idea, in fact I could certainly include the database settings in my alias file as they get auto-generated by Ansible already and it wouldn't be difficult to add that piece of information as well. THat would prevent that step too if I'm reading the code correctly.

[alex-moreno]

I'm working with Acquia Factory Cloud, and hit this issue. Removing the validation does the job, ie, commenting:

// Validate.
//  if (empty($source_db_spec)) {
//    if (empty($source_settings)) {
//      return drush_set_error('DRUSH_ALIAS_NOT_FOUND', dt('Error: no alias record could be found for source !source', array('!source' => $source)));
//    }
//    return drush_set_error('DRUSH_DATABASE_NOT_FOUND', dt('Error: no database record could be found for source !source', array('!source' => $source)));
//  }

although this is not a good fix obvs. I'll have a look and see if I can come with a patch

[IuriiKozhan]

One can make it work by passing correct parameters in alias:

For drush 8.x path to drush script should be in [path-aliases] [''%drush-script']

$aliases["remote.site"] = array (
  'uri' => 'www.example.com',
  'root' => '/var/www/web',
  'remote-host' => 'HOSTNAME',
  'path-aliases' => [
     '%drush-script' => 'MYSCRIPT' ,
  ],
);

For drush 9.x:

remote:
  host: server.domain.com
  user: www-admin
  root: /other/path/to/drupal
  uri: http://example.com
  ssh:
    options: '-o LogLevel=QUIET'
  paths:
    drush-script: '/path/to/drush'
  ssh:
    options: '-o LogLevel=QUIET'
  options:
    path-aliases:
      %drush-script: '/path/to/drush'

Note that you have to add options: path-aliases too.

[greg-1-anderson]

In Drush 9, nothing in the path-aliases context should affect drush-script, and drush-script should never begin with a %. If it does, it is a bug that should be fixed.

[IuriiKozhan]

This is work around I would say.
The problem is that function drush_invoke_process convert instance of AliasRecord into array using legacyRecord method. After that path to drush script would be lost.

includes/command.inc

  if ($site_alias_record instanceof AliasRecord) {
    $site_alias_record = $site_alias_record->legacyRecord();
  }

The problem is that exportConfig remap only 'user', 'host', 'root', 'uri', other options are lost.

AliasRecord.php

    /**
     * Export the configuration values in this alias record, and reconfigure
     * them so that the layout matches that of the global configuration object.
     */
    public function exportConfig()
    {
        $data = $this->export();

        foreach ($this->remapOptions() as $from => $to) {
            if (isset($data[$from])) {
                unset($data[$from]);
            }
            $value = $this->get($from, null);
            if (isset($value)) {
                $data['options'][$to] = $value;
            }
        }

        return new Config($data);
    }

    /**
     * Convert the data in this record to the layout that was used
     * in the legacy code, for backwards compatiblity.
     */
    public function legacyRecord()
    {
        return $this->exportConfig()->get('options', []);
    }

    /**
     * Conversion table from old to new option names. These all implicitly
     * go in `options`, although they can come from different locations.
     */
    protected function remapOptions()
    {
        return [
            'user' => 'remote-user',
            'host' => 'remote-host',
            'root' => 'root',
            'uri' => 'uri',
        ];
    }

One can make a workaround by putting drush 8 parameters in options like this:

remote.alias.yml

  options:
    path-aliases:
      %drush-script: path/to/drush

[greg-1-anderson]

From your diagnosis above, it sounds like perhaps Drush should be remapping from paths.drush-script to options.path-aliases.%drush-script. However, think we already have a passing test that demonstrates that this is passed through correctly. Are you using the latest HEAD of master?

[IuriiKozhan]

You are right, it is fixed in master.
I have 9.0.0-beta7, it was installed via composer. I will switch to master.