Drush 9 sql-sync does not use ssh-options from target alias during database import

[gaelg]

Looks like it ignores at least paths.drush-script and ssh.options (-vvv shows a wrong ssh command). We use aliases on sql-sync for getting content from prod back to dev easily. We can't use them currently with Drush 9 (recommended for Drupal 8.4).

[weitzman]

The master branch supports both of those. A new beta should go out in next couple of days. Note that ssh-options should be nested under 'options' per example.aliases.yml

[gaelg]

Yes, I tested against the master branch, it's not working. The two alias options I gave work well for other commands such as drush status, but not for sql-sync. I guess this command is special because the syntax is drush command @alias1 @alias2 and not drush @alias command.

[weitzman]

Thanks for the report. Please post output with --debug, output of drush sa @alias1 and drush sa @alias2.

[gaelg]

`drush sa @test
'@test.test':
host: my.domain.fr
paths:
drush-script: /my/root/drupal-drush9/vendor/drush/drush/drush
root: /my/root/drupal-drush9/web
user: insite

ggosset@ggosset-desktop ~/workspace/insite_drupal (drush9 *=) $ drush sa @docker
'@docker.docker':
host: localdocker
ssh:
options: '-p 2200'
root: /var/www/html/web
uri: 'http://localdocker:8000'
user: www-data

ggosset@ggosset-desktop ~/workspace/insite_drupal (drush9 *=) $ drush --debug -y sql-sync @test @docker
Using the Drush script found at /home/ggosset/workspace/insite_drupal/vendor/drush/drush/drush using pcntl_exec
[bootstrap] Bootstrap to none [0.16 sec, 7.09 MB]
[bootstrap] Drush bootstrap phase 0 [0.16 sec, 7.15 MB]
[bootstrap] Try to validate bootstrap phase 0 [0.16 sec, 7.15 MB]
[command] Backend invoke: ssh -o PasswordAuthentication=no [email protected] 'drush --backend=2 --verbose --debug --yes --root=/my/root/drupal-drush9/web core-status 2>&1' 2>&1 [0.16 sec, 7.2 MB]
[info] ssh -o PasswordAuthentication=no [email protected] 'drush --backend=2 --verbose --debug --yes --root=/my/root/drupal-drush9/web core-status 2>&1' 2>&1 [0.16 sec, 7.2 MB]
[error] The command could not be executed successfully (returned: bash: drush : commande introuvable
, code: 127) [0.4 sec, 7.2 MB]
[command] Backend invoke: ssh -o PasswordAuthentication=no www-data@localdocker 'drush --backend=2 --verbose --debug --yes --root=/var/www/html/web --uri=http://localdocker:8000 core-status 2>&1' 2>&1 [0.4 sec, 7.2 MB]
[info] ssh -o PasswordAuthentication=no www-data@localdocker 'drush --backend=2 --verbose --debug --yes --root=/var/www/html/web --uri=http://localdocker:8000 core-status 2>&1' 2>&1 [0.4 sec, 7.2 MB]
[error] The external command could not be executed due to an application error. [0.41 sec, 7.2 MB]
[error] The command could not be executed successfully (returned: ssh: connect to host localdocker port 22: Connection refused
, code: 255) [0.41 sec, 7.2 MB]
[error] Error: no database record could be found for source @test [0.41 sec, 7.22 MB]
ggosset@ggosset-desktop ~/workspace/insite_drupal (drush9 *=) $
`

You can also get the results of drush status in #3045. By the way, I added ssh to my docker container, it's not docker-philosophy-compliant but it's easier to integrate with drush aliases. And "localdocker" points to 127.0.0.1 in my /etc/hosts.

[greg-1-anderson]

It's on our roadmap to support direct docker exec via aliases for drush @alias command. If you expect drush rsync @a1 @a2 to work, then you are going to need ssh anyway, so I would disagree with saying categorically that adding ssh to a Drupal container is not "docker-philosphy-compliant". Add the things to your container that you need. We're also in favor of more efficient ways of doing things, though, so if you would like to contribute an sql-sync &/or rsync command that uses docker exec and the shared filesystem to operate, by all means do.

The drush rsync @a @b command does not yet merge the alias definition into the active context. That is also a planned feature, and is going to be necessary before any sql-sync or rsync implementations are going to work on a docker container.

[gaelg]

All right. Do you mean that my command can't work currently because it's a docker container and docker is not yet supported for Drush 9? Or is it an unrelated comment? Because I think the way I use it (SSH into Docker) make drush docker-agnostic. My docker container could instead be any remote server accessed through SSH, and my command would break though.

So is this issue a real bug? A known issue currently in a TODO list? A consequence of #3045? A mis-usage?

[greg-1-anderson]

This is unrelated to #3045.

The scenario described in this issue does not work because drush rsync @a @b does not copy options in those aliases such as ssh-options into any context that would allow them to influence the behavior of backend invoke or rsync or etc., so any rsync or sql-sync scenario that requires custom options will not work. There is no PR for this feature; this issue is sufficient for tracking; it's already part of the "Nine" project, which is our official TODO list.

#1910 is the issue for docker exec.

[gaelg]

Understood, thank you for your clear answer and your work on drush 9.

[greg-1-anderson]

Working on this in #3075. Command-specific options are working, but other things (drush-script, ssh-options, etc.) probably still need work.

[greg-1-anderson]

@gaelg and anyone else tracking this issue: Please try #3075 and see if it meets your use-case. Even more importantly:

• If we deleted https://github.com/drush-ops/drush/pull/3075/files#diff-f9ab80007ea4f0d9f488a2e87fb7215dR270 and replaced it with $aliasOptions = [];, would it still meet your use-case?
• If we deleted https://github.com/drush-ops/drush/pull/3075/files#diff-f9ab80007ea4f0d9f488a2e87fb7215dR272 and the if block that followed, would it still meet your use-case?

It is our intention that ssh-options and drush-script from alias files should propagate to remote commands, but it is unclear at this time exactly which features in Drush 8 folks are using to control this at the moment. We are discussing removing some features that may be unneeded, as specified above.

[gaelg]

@greg-1-anderson It does not work because the SSH connection to @test takes the SSH options from @docker (port 2200):

drush --debug -y sql-sync @test @docker Drush Launcher Version: 0.4.2 ROOT: /home/ggosset/workspace/insite_drupal DRUSH VERSION: 9 DRUPAL ROOT: /home/ggosset/workspace/insite_drupal/web COMPOSER ROOT: /home/ggosset/workspace/insite_drupal VENDOR ROOT: /home/ggosset/workspace/insite_drupal/vendor [bootstrap] Bootstrap to none [0.14 sec, 8.24 MB] [bootstrap] Drush bootstrap phase 0 [0.14 sec, 8.24 MB] [bootstrap] Try to validate bootstrap phase 0 [0.14 sec, 8.24 MB] [command] Backend invoke: ssh -p 2200 [email protected] '/my/root/drupal-drush9/vendor/drush/drush/drush --backend=2 --verbose --debug --yes --root=/my/root/drupal-drush9/web core-status 2>&1' 2>&1 [0.14 sec, 8.25 MB] [info] ssh -p 2200 [email protected] '/my/root/drupal-drush9/vendor/drush/drush/drush --backend=2 --verbose --debug --yes --root=/my/root/drupal-drush9/web core-status 2>&1' 2>&1 [0.14 sec, 8.26 MB] ^C ggosset@insite-asus ~/workspace/insite_drupal $

[gaelg]

R270 change:

drush --debug -y sql-sync @test @docker
Drush Launcher Version: 0.4.2
ROOT: /home/ggosset/workspace/insite_drupal
DRUSH VERSION: 9
DRUPAL ROOT: /home/ggosset/workspace/insite_drupal/web
COMPOSER ROOT: /home/ggosset/workspace/insite_drupal
VENDOR ROOT: /home/ggosset/workspace/insite_drupal/vendor
 [bootstrap] Bootstrap to none [0.14 sec, 8.24 MB]
 [bootstrap] Drush bootstrap phase 0 [0.14 sec, 8.24 MB]
 [bootstrap] Try to validate bootstrap phase 0 [0.14 sec, 8.24 MB]
 [command] Backend invoke: ssh -o PasswordAuthentication=no [email protected] '/my/root/drupal-drush9/vendor/drush/drush/drush  --backend=2 --verbose --debug --yes --root=/my/root/drupal-drush9/web  core-status 2>&1' 2>&1 [0.14 sec, 8.25 MB]
 [info] ssh -o PasswordAuthentication=no [email protected] '/my/root/drupal-drush9/vendor/drush/drush/drush  --backend=2 --verbose --debug --yes --root=/my/root/drupal-drush9/web  core-status 2>&1' 2>&1 [0.15 sec, 8.25 MB]
 [command] Backend invoke: ssh -o PasswordAuthentication=no www-data@localdocker 'drush  --backend=2 --verbose --debug --yes --root=/var/www/html/web --uri=http://localdocker:8000  core-status 2>&1' 2>&1 [1.15 sec, 8.27 MB]
 [info] ssh -o PasswordAuthentication=no www-data@localdocker 'drush  --backend=2 --verbose --debug --yes --root=/var/www/html/web --uri=http://localdocker:8000  core-status 2>&1' 2>&1 [1.16 sec, 8.27 MB]
 [error]  The external command could not be executed due to an application error. [1.18 sec, 8.27 MB] 
 [error]  The command could not be executed successfully (returned: ssh: connect to host localdocker port 22: Connection refused
, code: 255) [1.18 sec, 8.27 MB] 
 [error]  Error: no database record could be found for target @docker [1.18 sec, 8.29 MB] 

R272 change:

drush --debug -y sql-sync @test @docker
Drush Launcher Version: 0.4.2
ROOT: /home/ggosset/workspace/insite_drupal
DRUSH VERSION: 9
DRUPAL ROOT: /home/ggosset/workspace/insite_drupal/web
COMPOSER ROOT: /home/ggosset/workspace/insite_drupal
VENDOR ROOT: /home/ggosset/workspace/insite_drupal/vendor
[bootstrap] Bootstrap to none [0.14 sec, 8.24 MB]
[bootstrap] Drush bootstrap phase 0 [0.14 sec, 8.24 MB]
[bootstrap] Try to validate bootstrap phase 0 [0.14 sec, 8.24 MB]
[command] Backend invoke: ssh -p 2200 [email protected] '/my/root/drupal-drush9/vendor/drush/drush/drush --backend=2 --verbose --debug --yes --root=/my/root/drupal-drush9/web core-status 2>&1' 2>&1 [0.14 sec, 8.25 MB]
[info] ssh -p 2200 [email protected] '/my/root/drupal-drush9/vendor/drush/drush/drush --backend=2 --verbose --debug --yes --root=/my/root/drupal-drush9/web core-status 2>&1' 2>&1 [0.14 sec, 8.25 MB]
^C

R270+R272 change:

drush --debug -y sql-sync @test @docker
Drush Launcher Version: 0.4.2
ROOT: /home/ggosset/workspace/insite_drupal
DRUSH VERSION: 9
DRUPAL ROOT: /home/ggosset/workspace/insite_drupal/web
COMPOSER ROOT: /home/ggosset/workspace/insite_drupal
VENDOR ROOT: /home/ggosset/workspace/insite_drupal/vendor
[bootstrap] Bootstrap to none [0.14 sec, 8.23 MB]
[bootstrap] Drush bootstrap phase 0 [0.14 sec, 8.24 MB]
[bootstrap] Try to validate bootstrap phase 0 [0.14 sec, 8.24 MB]
[command] Backend invoke: ssh -o PasswordAuthentication=no [email protected] '/my/root/drupal-drush9/vendor/drush/drush/drush --backend=2 --verbose --debug --yes --root=/my/root/drupal-drush9/web core-status 2>&1' 2>&1 [0.14 sec, 8.25 MB]
[info] ssh -o PasswordAuthentication=no [email protected] '/my/root/drupal-drush9/vendor/drush/drush/drush --backend=2 --verbose --debug --yes --root=/my/root/drupal-drush9/web core-status 2>&1' 2>&1 [0.14 sec, 8.25 MB]
[command] Backend invoke: ssh -o PasswordAuthentication=no www-data@localdocker 'drush --backend=2 --verbose --debug --yes --root=/var/www/html/web --uri=http://localdocker:8000 core-status 2>&1' 2>&1 [1 sec, 8.27 MB]
[info] ssh -o PasswordAuthentication=no www-data@localdocker 'drush --backend=2 --verbose --debug --yes --root=/var/www/html/web --uri=http://localdocker:8000 core-status 2>&1' 2>&1 [1 sec, 8.27 MB]
[error] The external command could not be executed due to an application error. [1.02 sec, 8.27 MB]
[error] The command could not be executed successfully (returned: ssh: connect to host localdocker port 22: Connection refused
, code: 255) [1.03 sec, 8.27 MB]
[error] Error: no database record could be found for target @docker [1.03 sec, 8.29 MB]

[greg-1-anderson]

OK, seems like we can't just inject base configuration from the alias files, because both aliases might have remote options which might be incompatible. After removing the base option injection, we'll need to confirm that backend invoke is still injecting options for the target alias. The R270+R272 change test above seems to indicate this isn't happening yet.

[greg-1-anderson]

@gaelg I believe that #3083 (now merged into master) fixes the problems you report above. Please confirm.

I'd also appreciate it if you could check and see that #3079 does not cause you any problems. We are thinking about removing some functionality. I don't expect this should affect you.

[gaelg]

It's better but still not working. The ssh options of the second alias are not taken.

ggosset@insite-asus ~/workspace/insite_drupal $ drush --debug -y sql-sync @test @docker
Drush Launcher Version: 0.4.2
ROOT: /home/ggosset/workspace/insite_drupal
DRUSH VERSION: 9
DRUPAL ROOT: /home/ggosset/workspace/insite_drupal/web
COMPOSER ROOT: /home/ggosset/workspace/insite_drupal
VENDOR ROOT: /home/ggosset/workspace/insite_drupal/vendor
[bootstrap] Bootstrap to none [0.14 sec, 8.24 MB]
[bootstrap] Drush bootstrap phase 0 [0.14 sec, 8.24 MB]
[bootstrap] Try to validate bootstrap phase 0 [0.14 sec, 8.24 MB]
[command] Backend invoke: ssh -o PasswordAuthentication=no [email protected] '/my/root/drupal-drush9/vendor/drush/drush/drush --backend=2 --verbose --debug --root=/my/root/drupal-drush9/web core-status 2>&1' 2>&1 [0.15 sec, 8.28 MB]
[info] ssh -o PasswordAuthentication=no [email protected] '/my/root/drupal-drush9/vendor/drush/drush/drush --backend=2 --verbose --debug --root=/my/root/drupal-drush9/web core-status 2>&1' 2>&1 [0.15 sec, 8.28 MB]
[command] Backend invoke: ssh -p 2200 www-data@localdocker 'drush --backend=2 --verbose --debug --root=/var/www/html/web --uri=http://localdocker:8000 core-status 2>&1' 2>&1 [1.19 sec, 8.29 MB]
[info] ssh -p 2200 www-data@localdocker 'drush --backend=2 --verbose --debug --root=/var/www/html/web --uri=http://localdocker:8000 core-status 2>&1' 2>&1 [1.19 sec, 8.29 MB]
You will destroy data in localdocker/drupal and replace with data from my.domain.fr/drupal_drush9_test.

// Do you really want to continue?: yes.

[notice] Starting to dump database on source. [1.69 sec, 8.41 MB]
[command] Backend invoke: ssh -o PasswordAuthentication=no [email protected] '/my/root/drupal-drush9/vendor/drush/drush/drush --backend=2 --debug --strict=0 --verbose --root=/my/root/drupal-drush9/web sql-dump --gzip --result-file 2>&1' 2>&1 [1.69 sec, 8.42 MB]
[info] ssh -o PasswordAuthentication=no [email protected] '/my/root/drupal-drush9/vendor/drush/drush/drush --backend=2 --debug --strict=0 --verbose --root=/my/root/drupal-drush9/web sql-dump --gzip --result-file 2>&1' 2>&1 [1.69 sec, 8.42 MB]
[bootstrap] Bootstrap to none [2.15 sec, 9.49 MB]
[bootstrap] Drush bootstrap phase 0 [2.15 sec, 9.5 MB]
[bootstrap] Try to validate bootstrap phase 0 [2.15 sec, 9.5 MB]
[bootstrap] Drush bootstrap phase: bootstrapDrupalRoot() [2.16 sec, 9.75 MB]
[bootstrap] Change working directory to /my/root/drupal-drush9/web [2.16 sec, 9.75 MB]
[bootstrap] Initialized Drupal 8.4.0 root directory at /my/root/drupal-drush9/web [2.16 sec, 9.76 MB]
[bootstrap] Drush bootstrap phase: bootstrapDrupalSite() [2.17 sec, 10.57 MB]
[bootstrap] Initialized Drupal site default at sites/default [2.17 sec, 10.57 MB]
[bootstrap] Drush bootstrap phase: bootstrapDrupalConfiguration() [2.17 sec, 10.57 MB]
[debug] Create from request [2.17 sec, 10.62 MB]
[debug] Add service modifier [2.17 sec, 10.97 MB]
[info] sql-query: SHOW TABLES; [2.18 sec, 11.12 MB]
[info] Executing: mysql --defaults-file=/tmp/drush_I4rvCj --database=drupal_drush9_test --host=localhost --silent < /tmp/drush_rPlbmc [2.18 sec, 11.12 MB]
cache_bootstrap
cache_config
[...]
users_data
users_field_data
Calling system(mysqldump --defaults-file=/tmp/drush_3NZ62q drupal_drush9_test --host=localhost --no-autocommit --single-transaction --opt -Q | gzip -f > /home/insite/drush-backups/drupal_drush9_test/20171026083744/drupal_drush9_test_20171026_083744.sql.gz);
[success] Database dump saved to /home/insite/drush-backups/drupal_drush9_test/20171026083744/drupal_drush9_test_20171026_083744.sql.gz [5.02 sec, 11.13 MB]
[notice] Starting to discover temporary files directory on target. [5.06 sec, 8.43 MB]
[command] Backend invoke: ssh -o PasswordAuthentication=no www-data@localdocker 'drush --backend=2 --verbose --debug --root=/var/www/html/web --uri=http://localdocker:8000 core-status 2>&1' 2>&1 [5.06 sec, 8.44 MB]
[info] ssh -o PasswordAuthentication=no www-data@localdocker 'drush --backend=2 --verbose --debug --root=/var/www/html/web --uri=http://localdocker:8000 core-status 2>&1' 2>&1 [5.06 sec, 8.44 MB]
[error] The external command could not be executed due to an application error. [5.07 sec, 8.45 MB]
[error] The command could not be executed successfully (returned: ssh: connect to host localdocker port 22: Connection refused
, code: 255) [5.07 sec, 8.45 MB]
[command] Backend invoke: ssh -o PasswordAuthentication=no www-data@localdocker 'drush --backend=2 --verbose --debug --root=/var/www/html/web --uri=http://localdocker:8000 core-rsync '''@test:/home/insite/drush-backups/drupal_drush9_test/20171026083744/drupal_drush9_test_20171026_083744.sql.gz''' '''@docker:/tmp/drupal_drush9_test_20171026_083744.sql.gz''' -- --remove-source-files 2>&1' 2>&1 [5.07 sec, 8.44 MB]
[info] ssh -o PasswordAuthentication=no www-data@localdocker 'drush --backend=2 --verbose --debug --root=/var/www/html/web --uri=http://localdocker:8000 core-rsync '''@test:/home/insite/drush-backups/drupal_drush9_test/20171026083744/drupal_drush9_test_20171026_083744.sql.gz''' '''@docker:/tmp/drupal_drush9_test_20171026_083744.sql.gz''' -- --remove-source-files 2>&1' 2>&1 [5.07 sec, 8.44 MB]
ssh: connect to host localdocker port 22: Connection refused
[error] The command could not be executed successfully (returned: ssh: connect to host localdocker port 22: Connection refused
, code: 255) [5.08 sec, 8.44 MB]
[notice] Copying dump file from source to target. [5.08 sec, 8.43 MB]
[notice] Starting to import dump file onto target database. [5.08 sec, 8.43 MB]
[command] Backend invoke: ssh -o PasswordAuthentication=no www-data@localdocker 'drush --backend=2 --debug --strict=0 --verbose --root=/var/www/html/web --uri=http://localdocker:8000 sql-query --file=/tmp/drupal_drush9_test_20171026_083744.sql.gz --file-delete 2>&1' 2>&1 [5.09 sec, 8.44 MB]
[info] ssh -o PasswordAuthentication=no www-data@localdocker 'drush --backend=2 --debug --strict=0 --verbose --root=/var/www/html/web --uri=http://localdocker:8000 sql-query --file=/tmp/drupal_drush9_test_20171026_083744.sql.gz --file-delete 2>&1' 2>&1 [5.09 sec, 8.44 MB]
ssh: connect to host localdocker port 22: Connection refused
[error] The command could not be executed successfully (returned: ssh: connect to host localdocker port 22: Connection refused
, code: 255) [5.09 sec, 8.44 MB]
ggosset@insite-asus ~/workspace/insite_drupal $

[gaelg]

And by the way, to answer to greg-1-anderson, we don't use "alias-parameters".

[greg-1-anderson]

OK, so from the above it looks like ssh-options et. al. are working for sql-dump and rsync, but not for the import.

[greg-1-anderson]

@gaelg I added a test that caught the failing scenario above and then fixed it in the commit above, which has been merged onto the master branch. Please confirm this is doing the right thing for you now.

[gaelg]

Thank you. Unfortunately there is still a problem:

ggosset@insite-asus ~/workspace/insite_drupal $ drush --debug -y sql-sync --structure-tables-list=watchdog,cache_*,search_api_db_* @test @docker
Drush Launcher Version: 0.4.2
ROOT: /home/ggosset/workspace/insite_drupal
DRUSH VERSION: 9
DRUPAL ROOT: /home/ggosset/workspace/insite_drupal/web
COMPOSER ROOT: /home/ggosset/workspace/insite_drupal
VENDOR ROOT: /home/ggosset/workspace/insite_drupal/vendor
 [bootstrap] Bootstrap to none [0.13 sec, 8.22 MB]
 [bootstrap] Drush bootstrap phase 0 [0.13 sec, 8.22 MB]
 [bootstrap] Try to validate bootstrap phase 0 [0.13 sec, 8.23 MB]
 [command] Backend invoke: ssh -o PasswordAuthentication=no [email protected] '/my/root/drupal-drush9/vendor/drush/drush/drush  --backend=2 --verbose --debug --root=/my/root/drupal-drush9/web  core-status 2>&1' 2>&1 [0.14 sec, 8.27 MB]
 [info] ssh -o PasswordAuthentication=no [email protected] '/my/root/drupal-drush9/vendor/drush/drush/drush  --backend=2 --verbose --debug --root=/my/root/drupal-drush9/web  core-status 2>&1' 2>&1 [0.14 sec, 8.27 MB]
 [command] Backend invoke: ssh -p 2200 www-data@localdocker 'drush  --backend=2 --verbose --debug --root=/var/www/html/web --uri=http://localdocker:8000  core-status 2>&1' 2>&1 [1.53 sec, 8.28 MB]
 [info] ssh -p 2200 www-data@localdocker 'drush  --backend=2 --verbose --debug --root=/var/www/html/web --uri=http://localdocker:8000  core-status 2>&1' 2>&1 [1.53 sec, 8.28 MB]
You will destroy data in localdocker/drupal and replace with data from my.domain.fr/drupal_drush9_test.

 // Do you really want to continue?: yes.                                                                               

 [notice] Starting to dump database on source. [2.04 sec, 8.4 MB]
 [command] Backend invoke: ssh -o PasswordAuthentication=no [email protected] '/my/root/drupal-drush9/vendor/drush/drush/drush  --backend=2 --debug --strict=0 --verbose --root=/my/root/drupal-drush9/web  sql-dump   --structure-tables-list='\''watchdog,cache_*,search_api_db_*'\'' --gzip --result-file 2>&1' 2>&1 [2.04 sec, 8.4 MB]
 [info] ssh -o PasswordAuthentication=no [email protected] '/my/root/drupal-drush9/vendor/drush/drush/drush  --backend=2 --debug --strict=0 --verbose --root=/my/root/drupal-drush9/web  sql-dump   --structure-tables-list='\''watchdog,cache_*,search_api_db_*'\'' --gzip --result-file 2>&1' 2>&1 [2.04 sec, 8.41 MB]
 [bootstrap] Bootstrap to none [2.56 sec, 9.49 MB]
 [bootstrap] Drush bootstrap phase 0 [2.56 sec, 9.5 MB]
 [bootstrap] Try to validate bootstrap phase 0 [2.56 sec, 9.5 MB]
 [bootstrap] Drush bootstrap phase: bootstrapDrupalRoot() [2.56 sec, 9.75 MB]
 [bootstrap] Change working directory to /my/root/drupal-drush9/web [2.56 sec, 9.76 MB]
 [bootstrap] Initialized Drupal 8.4.0 root directory at /my/root/drupal-drush9/web [2.56 sec, 9.76 MB]
 [bootstrap] Drush bootstrap phase: bootstrapDrupalSite() [2.57 sec, 10.57 MB]
 [bootstrap] Initialized Drupal site default at sites/default [2.57 sec, 10.57 MB]
 [bootstrap] Drush bootstrap phase: bootstrapDrupalConfiguration() [2.57 sec, 10.57 MB]
 [debug] Create from request [2.57 sec, 10.62 MB]
 [debug] Add service modifier [2.58 sec, 10.97 MB]
 [info] sql-query: SHOW TABLES; [2.58 sec, 11.12 MB]
 [info] Executing: mysql --defaults-file=/tmp/drush_AvPNAd --database=drupal_drush9_test --host=localhost --silent  < /tmp/drush_eyTfuf [2.58 sec, 11.12 MB]
  cache_bootstrap
  cache_config
[...]
  users_data
  users_field_data
Calling system((mysqldump --defaults-file=/tmp/drush_A9WgKb  drupal_drush9_test --host=localhost --no-autocommit --single-transaction --opt -Q --ignore-table=drupal_drush9_test.cache_bootstrap --ignore-table=drupal_drush9_test.cache_config --ignore-table=drupal_drush9_test.cache_container --ignore-table=drupal_drush9_test.cache_data --ignore-table=drupal_drush9_test.cache_default --ignore-table=drupal_drush9_test.cache_discovery --ignore-table=drupal_drush9_test.cache_dynamic_page_cache --ignore-table=drupal_drush9_test.cache_entity --ignore-table=drupal_drush9_test.cache_menu --ignore-table=drupal_drush9_test.cache_render --ignore-table=drupal_drush9_test.cache_signal --ignore-table=drupal_drush9_test.cache_toolbar --ignore-table=drupal_drush9_test.cache_ultimate_cron_logger && mysqldump --defaults-file=/tmp/drush_A9WgKb  drupal_drush9_test --host=localhost --no-data  --no-autocommit --single-transaction --opt -Q cache_bootstrap cache_config cache_container cache_data cache_default cache_discovery cache_dynamic_page_cache cache_entity cache_menu cache_render cache_signal cache_toolbar cache_ultimate_cron_logger) | gzip -f > /home/insite/drush-backups/drupal_drush9_test/20171027082416/drupal_drush9_test_20171027_082416.sql.gz);
 [success] Database dump saved to /home/insite/drush-backups/drupal_drush9_test/20171027082416/drupal_drush9_test_20171027_082416.sql.gz [3.17 sec, 11.13 MB]
 [notice] Starting to discover temporary files directory on target. [3.22 sec, 8.42 MB]
 [command] Backend invoke: ssh -o PasswordAuthentication=no www-data@localdocker 'drush  --backend=2 --verbose --debug --root=/var/www/html/web --uri=http://localdocker:8000  core-status 2>&1' 2>&1 [3.23 sec, 8.43 MB]
 [info] ssh -o PasswordAuthentication=no www-data@localdocker 'drush  --backend=2 --verbose --debug --root=/var/www/html/web --uri=http://localdocker:8000  core-status 2>&1' 2>&1 [3.23 sec, 8.43 MB]
 [error]  The external command could not be executed due to an application error. [3.24 sec, 8.43 MB] 
 [error]  The command could not be executed successfully (returned: ssh: connect to host localdocker port 22: Connection refused
, code: 255) [3.24 sec, 8.44 MB] 
 [command] Backend invoke: ssh -o PasswordAuthentication=no www-data@localdocker 'drush  --backend=2 --verbose --debug --root=/var/www/html/web --uri=http://localdocker:8000  core-rsync '\''@test:/home/insite/drush-backups/drupal_drush9_test/20171027082416/drupal_drush9_test_20171027_082416.sql.gz'\'' '\''@docker:/tmp/drupal_drush9_test_20171027_082416.sql.gz'\'' -- --remove-source-files 2>&1' 2>&1 [3.24 sec, 8.43 MB]
 [info] ssh -o PasswordAuthentication=no www-data@localdocker 'drush  --backend=2 --verbose --debug --root=/var/www/html/web --uri=http://localdocker:8000  core-rsync '\''@test:/home/insite/drush-backups/drupal_drush9_test/20171027082416/drupal_drush9_test_20171027_082416.sql.gz'\'' '\''@docker:/tmp/drupal_drush9_test_20171027_082416.sql.gz'\'' -- --remove-source-files 2>&1' 2>&1 [3.24 sec, 8.43 MB]
ssh: connect to host localdocker port 22: Connection refused
 [error]  The command could not be executed successfully (returned: ssh: connect to host localdocker port 22: Connection refused
, code: 255) [3.25 sec, 8.43 MB] 
 [notice] Copying dump file from source to target. [3.25 sec, 8.42 MB]
 [notice] Starting to import dump file onto target database. [3.25 sec, 8.42 MB]
 [command] Backend invoke: ssh -p 2200 www-data@localdocker 'drush  --backend=2 --debug --strict=0 --verbose --root=/var/www/html/web --uri=http://localdocker:8000  sql-query   --structure-tables-list='\''watchdog,cache_*,search_api_db_*'\'' --file=/tmp/drupal_drush9_test_20171027_082416.sql.gz --file-delete 2>&1' 2>&1 [3.25 sec, 8.43 MB]
 [info] ssh -p 2200 www-data@localdocker 'drush  --backend=2 --debug --strict=0 --verbose --root=/var/www/html/web --uri=http://localdocker:8000  sql-query   --structure-tables-list='\''watchdog,cache_*,search_api_db_*'\'' --file=/tmp/drupal_drush9_test_20171027_082416.sql.gz --file-delete 2>&1' 2>&1 [3.25 sec, 8.43 MB]
Using the Drush script found at /var/www/html/vendor/drush/drush/drush using proc_open
 [bootstrap] Bootstrap to none [3.58 sec, 6.76 MB]
 [bootstrap] Drush bootstrap phase 0 [3.58 sec, 6.76 MB]
 [bootstrap] Try to validate bootstrap phase 0 [3.58 sec, 6.76 MB]
 [error]  File(s) not found: /tmp/drupal_drush9_test_20171027_082416.sql.gz [3.58 sec, 6.79 MB] 
 [error]  Failed to rsync the database dump from source to target. [3.59 sec, 8.44 MB] 
ggosset@insite-asus ~/workspace/insite_drupal $

[greg-1-anderson]

That's a lot of lines of debug. I tried to reproduce your configuration in the unit tests; the tests are currently passing with the configuration I put together. Maybe you could submit a PR with a failing test.

[gaelg]

I looked at sqlSyncTest.php to see how you implement your test. It's not very clear to me but it looks like it misses the scenario I use: remote to remote (Docker through SSH is like remote, even if it's actually local...).
It would be something like this I guess:
// Test simulated simple rsync remote-to-remote $this->drush('sql:sync', ['@synctest.remote', '@synctest.remote2'], $options, '@synctest.local', NULL, self::EXIT_SUCCESS, '2>&1');
But I don't really know what assertions I should do...

I execute a local drush (because I use "drush sql-sync..." and not "drush @some-alias sql-sync..."). And this local drush knows 2 remote aliases, so that it should be able to sync databases by dumping the first one, downloading it locally, uploading it to the second remote and execute it there. It's how it works with Drush 8.

[weitzman]

Remote to Remote is no longer supported. You should get a validation error with a helpful message if you try it. The workaround should be straightforward - you just run the command on one of the remote servers:

drush @synctest.remote2 rsync @synctest.remote @self

[greg-1-anderson]

You might not be able to get to @docker from @Remote, but this should work:

drush @site.docker sql-sync @site.remote @self

In order for this to work, you'd need to ensure that your site alias is defined within your docker container.

[gaelg]

Thank you for your answer. It's a bad news that remote to remote is not supported anymore. We use sql-sync and rsync to get content from production instance to local dev instances but also to test or staging instances. The useful thing with remote to remote is that we only need to enable SSH between local and remotes (IP whitelists, SSH keys,...). It's longer to execute because of the double transfer but it's easier to set.
For example, I just tested drush @site.docker sql-sync @site.remote @self but it doesn't work because my Docker container is not configured to be able to SSH connect to my remote server.

Is there an easy way to do remote to local then local to remote2 (or docker) through several simple drush calls? It has to be noticed that local has drush and Drupal code but no DB.

[greg-1-anderson]

I don't think it would be impossible to support remote-to-remote again. This was taken out to keep the rsync and sql-sync code simpler and easier to maintain. The former sql-sync algorithm supported all sorts of things (e.g. no drush on the target system), which made it very complex and difficult to maintain. The current sql-sync implementation could already do remote-to-remote if the rsync command supported remote-to-remote.

To do remote-to-remote with the current implementation, you could run:

drush @source sql-dump > /path/to/dump.sql
drush sql-sync @self @docker --no-dump --source-dump=/path/to/dump.sql

I haven't tried that and there are no tests or documentation, so it is possible that there may be bugs preventing this scenario from working. The existing code does have the aspirational goal of supporting that two-step process. The above scenario should not require a database client to be installed on the local system.

[gaelg]

Yes, remote to remote was very useful. It would be great to have it back.
In the meanwhile, I managed to find a working* list of commands. Here they are:
drush @remote.source sql-dump --gzip > /tmp/dump.sql.gz
drush -y rsync @self:/tmp/dump.sql.gz @remote.destination:../private/
drush @remote.destination ssh "gunzip ../private/dump.sql.gz"
drush @remote.destination sql-query --file=../private/dump.sql

*It only works if this is fixed: #3131

NB: drush sql-sync @self @docker --no-dump --source-dump=/path/to/dump.sql did not work because the SSH options of @docker were not taken into account.

[grubshka]

Another option I use is to execute ssh on source to execute drush :

drush @remote.source ssh "drush sql-sync @self @remote.destination"