Acquia Drush Aliases sql-sync to VirtualBox

justinlevi commented 8 years ago

So one thing I've been struggling with is figuring out a workflow for getting the Drupal-VM database setup using sql-sync to an Acquia Cloud site. I'd like to get the Drupal-VM synchronized with an existing Drupal project checked out from Acquia (via DevDesktop although it could just as well be a git clone via command line).

Here's how I'm thinking it should work:

Create a Drupal-VM, w/ vagrant up
pull down the dev database using the provided Acquia drush aliases,
update the VM Database. --- Ideally this could all be done after vagrant up completes, but I'd be happy if I could just get the workflow sorted out manually to start...

So, here's where I'm at:

My VM is setup and working great. I can vagrant ssh in and verify that ssh-agent is forwarding correctly to the host by running ssh -T git@github.com as I get the expected message: Hi xxx! You've successfully authenticated, but GitHub does not provide shell access.

I can create a new drush alias to the Drupal-VM no problem by adding the following into <SITE ROOT>/../drush/virtualbox.aliases.drushrc.php:

$aliases['local'] = array(
  'remote-host' => '192.168.88.88',
  'remote-user' => 'vagrant',
  'root' => '/var/www/drupalvm/drupal',
  'ssh-options' => '-o PasswordAuthentication=yes',
);

I can then run drush @virtualbox.local status, enter vagrant for my pw and everything works as expected.

haha - In doing the above and running drush sa I realized aliases were already being created to all of the virtual hosts defined in the config. oops.

Regardless though, this is where I get stuck. If I then try to sync with the Acquia DB running

drush sql-sync @<ACQUIA-DRUSH-ALIAS>.dev @drupalvm.drupalvm.dev

I consistently get a rsync error. I think this might have something to do with trying to rsync between two remote hosts. I feel like I've read somewhere that rsync doesn't like that.

Here's the full error I'm seeing:

Host key verification failed.
rsync: connection unexpectedly closed (0 bytes received so far) [Receiver]
rsync error: error in rsync protocol data stream (code 12) at io.c(226) [Receiver=3.1.0]
Could not rsync from @<ACQUIA-DRUSH-ALIAS>.dev:/home/xxxx/drush-backups/xxxx/20160130012449/xxxx_20160130_012450.sql.gz to          [error]
/tmp/xxxx_20160130_012450.sql.gz
Copying dump file from Source to Destination.                                                                                                 [ok]
core-rsync failed.

I also tried drush @<ACQUIA-DRUSH-ALIAS>.dev sql-dump | drush @drupalvm.drupalvm.dev sql-cli with no luck even though I can create the sql file no problem with

drush @<ACQUIA-DRUSH-ALIAS>.dev sql-dump > sqldump.sql

I found a project called Drush SQL Sync Pipe that looks promising but wanted to check here before I start going down that road. https://www.drupal.org/project/drush_sql_sync_pipe

@geerlingguy - Any help is greatly appreciated. Thanks!

Oh, a few more notes.

I think I should be running this sql-sync on the host and not within the vagrant ssh. Mainly because I don't have access to all of my Acquia aliases.
I was thinking I could copy in the aliases but then I feel like that might open up a bunch of issues with the ssh key of my virtual box not being added to my Acquia account, etc.
UPDATE

I ended up installing Drush SQL Sync Pipe and was able to get the DB from the Acquia Dev site and push it up to the Virtual Box.

drush sql-sync-pipe @<ACQUIA-DRUSH-ALIAS>.dev @drupalvm.drupalvm.dev --progress

I'm not sure if this is the correct approach though and now I'm running into some php issues that I'm not seeing on dev desktop or on Acquia Dev oddly enough.

oxyc commented 8 years ago

drush sql-sync is definitely supposed to be able to sync between two remotes (unlike plain drush core-rsync). So the task should work running from the host (where you would run it in most setups).

First of all, you mention you ran drush sql-sync@<ACQUIA-DRUSH-ALIAS>.dev @drupalvm.adminer.drupalvm.dev which is incorrect. It's supposed to be drush sql-sync @<ACQUIA-DRUSH-ALIAS>.dev @drupalvm.drupalvm.dev but I assume that's just a typo because you mentioned you tried piping it (with the correct alias) as well.

If you run drush sql-sync -vvv @<ACQUIA-DRUSH-ALIAS>.dev @drupalvm.drupalvm.dev you should be able to see the ssh/rsync commands that drush is attempting to issue.

As the error is rsync i assume you gets past this.

You will destroy data in drupalvm.dev/drupal and replace with data from remote.com/db. You might want to make a backup first, using the sql-dump command. Do you really want to continue? (y/n): y

Afterwards come some rsync commands. Maybe you can try running the failed rsync commands manually. What's the output?

Also are you on windows or linux? Which drush version do you have locally as well as in drupal vm?

justinlevi commented 8 years ago

Thanks for catching that typo above. I've corrected my post.

Drush Version   :  8.0.0  locally (mac)
Drush Version   :  7.1.0 (drupal vm)

I was wondering if this could be the problem but I think I need Drush 7 so that I can run drush rr on the vm due to some issues with my sites having non-standard contrib module folder structures.

Here's the output I'm seeing - (line breaks inserted so I could more easily trace through)

$drush sql-sync -vvv @<ACQUIA-DRUSH-ALIAS>.dev @drupalvm.drupalvm.dev

Initialized Drupal 7.41 root directory at /Users/<ME>/Sites/devdesktop/<ACQUIA-DRUSH-ALIAS>-dev/docroot                                             [notice]
Initialized Drupal site default at sites/default  
                                                                                               [notice]
Loaded alias @drupalvm.drupalvm.dev from file /Users/<ME>/.drush/drupalvm.aliases.drushrc.php                                            [notice]
Loaded alias @<ACQUIA-DRUSH-ALIAS>.dev from file /Users/<ME>/.drush/<ACQUIA-DRUSH-ALIAS>.aliases.drushrc.php                                                   [notice]

ssh -o PasswordAuthentication=no <ACQUIA-DRUSH-ALIAS>.dev@staging-XXXX.prod.hosting.acquia.com 'env COLUMNS=153 drush8  --backend=2 --verbose              [notice]
--root=/var/www/html/<ACQUIA-DRUSH-ALIAS>.dev/docroot --uri=<ACQUIA-DRUSH-ALIAS>dev.prod.acquia-sites.com  sql-conf   --all 2>&1' 2>&1

ssh -o PasswordAuthentication=no -i ~/.vagrant.d/insecure_private_key vagrant@drupalvm.dev 'env COLUMNS=153 drush  --backend=2 --verbose         [notice]
--uri=drupalvm.dev --root=/var/www/drupalvm  sql-conf   --all 2>&1' 2>&1

You will destroy data in drupalvm.dev/drupal and replace with data from staging-XXXX.prod.hosting.acquia.com/<ACQUIA-DRUSH-ALIAS>dev.
Do you really want to continue? (y/n): y
Starting to dump database on Source.                                                                                                          [ok]

ssh -o PasswordAuthentication=no <ACQUIA-DRUSH-ALIAS>.dev@staging-XXXX.prod.hosting.acquia.com 'env COLUMNS=153 drush8  --backend=2 --verbose --strict=0   [notice]
--root=/var/www/html/<ACQUIA-DRUSH-ALIAS>.dev/docroot --uri=<ACQUIA-DRUSH-ALIAS>dev.prod.acquia-sites.com  sql-dump   --gzip --result-file 2>&1' 2>&1

Initialized Drupal 7.41 root directory at /mnt/www/html/<ACQUIA-DRUSH-ALIAS>dev/docroot                                                                     [notice]
Initialized Drupal site <ACQUIA-DRUSH-ALIAS>dev.prod.acquia-sites.com at sites/default                                                                      [notice]

Executing: mysql --defaults-extra-file=/mnt/tmp/<ACQUIA-DRUSH-ALIAS>dev/drush_16ixvD --database=<ACQUIA-DRUSH-ALIAS>dev --host=staging-XXXX --port=3306 --silent  < /mnt/tmp/<ACQUIA-DRUSH-ALIAS>dev/drush_O7ScCY

Calling system(mysqldump --defaults-extra-file=/mnt/tmp/<ACQUIA-DRUSH-ALIAS>dev/drush_IgcdIi  <ACQUIA-DRUSH-ALIAS>dev --host=staging-XXXX --port=3306 --no-autocommit --single-transaction --opt -Q  | gzip -f > /home/<ACQUIA-DRUSH-ALIAS>/drush-backups/<ACQUIA-DRUSH-ALIAS>dev/20160130150834/<ACQUIA-DRUSH-ALIAS>dev_20160130_150835.sql.gz);
Database dump saved to /home/<ACQUIA-DRUSH-ALIAS>/drush-backups/<ACQUIA-DRUSH-ALIAS>dev/20160130150834/<ACQUIA-DRUSH-ALIAS>dev_20160130_150835.sql.gz                          [success]
Command dispatch complete                                                                                                                        [notice]

Starting to discover temporary files directory on Destination.                                                                                [ok]
ssh -o PasswordAuthentication=no -i ~/.vagrant.d/insecure_private_key vagrant@drupalvm.dev 'env COLUMNS=153 drush  --backend=2 --verbose         [notice]
--root=/var/www/drupalvm --uri=drupalvm.dev  php-eval '\''return drush_find_tmp();'\'' 2>&1' 2>&1

ssh -o PasswordAuthentication=no -i ~/.vagrant.d/insecure_private_key vagrant@drupalvm.dev 'env COLUMNS=153 drush  --backend=2 --yes --verbose   [notice]
--root=/var/www/drupalvm --uri=drupalvm.dev  core-rsync
'\''@<ACQUIA-DRUSH-ALIAS>.dev:/home/<ACQUIA-DRUSH-ALIAS>/drush-backups/<ACQUIA-DRUSH-ALIAS>dev/20160130150834/<ACQUIA-DRUSH-ALIAS>dev_20160130_150835.sql.gz'\''
'\''@drupalvm.drupalvm.dev:/tmp/<ACQUIA-DRUSH-ALIAS>dev_20160130_150835.sql.gz'\''   --remove-source-files 2>&1' 2>&1

Initialized Drupal 7.41 root directory at /var/www/drupalvm                                                                                      [notice]
Initialized Drupal site drupalvm.dev at sites/drupalvm.dev                                                                                       [notice]
Loaded alias @drupalvm.drupalvm.dev from file /home/vagrant/.drush/drupalvm.aliases.drushrc.php                                                  [notice]
You will destroy data from /tmp/<ACQUIA-DRUSH-ALIAS>dev_20160130_150835.sql.gz and replace with data from @<ACQUIA-DRUSH-ALIAS>.dev:/home/<ACQUIA-DRUSH-ALIAS>/drush-backups/<ACQUIA-DRUSH-ALIAS>dev/20160130150834/<ACQUIA-DRUSH-ALIAS>dev_20160130_150835.sql.gz
Do you really want to continue? (y/n): y
Calling system(rsync -e 'ssh ' -akzv --exclude=".git" --exclude=".gitignore" --exclude=".hg" --exclude=".hgignore" --exclude=".hgrags" --exclude=".bzr" --exclude=".bzrignore" --exclude=".bzrtags" --exclude=".svn" --stats --progress --remove-source-files @<ACQUIA-DRUSH-ALIAS>.dev:/home/<ACQUIA-DRUSH-ALIAS>/drush-backups/<ACQUIA-DRUSH-ALIAS>dev/20160130150834/<ACQUIA-DRUSH-ALIAS>dev_20160130_150835.sql.gz /tmp/<ACQUIA-DRUSH-ALIAS>dev_20160130_150835.sql.gz);
Host key verification failed.

rsync: connection unexpectedly closed (0 bytes received so far) [Receiver]
rsync error: error in rsync protocol data stream (code 12) at io.c(226) [Receiver=3.1.0]
Could not rsync from @<ACQUIA-DRUSH-ALIAS>.dev:/home/<ACQUIA-DRUSH-ALIAS>/drush-backups/<ACQUIA-DRUSH-ALIAS>dev/20160130150834/<ACQUIA-DRUSH-ALIAS>dev_20160130_150835.sql.gz to          [error]
/tmp/<ACQUIA-DRUSH-ALIAS>dev_20160130_150835.sql.gz
Command dispatch complete                                                                                                                        [notice]
Copying dump file from Source to Destination.                                                                                                 [ok]
core-rsync failed.                                                                                                                            [error]
Command dispatch complete

Haven't yet had a chance to run each of these commands manually.

caschbre commented 8 years ago

I've also had issues trying to use sql-sync between two remote hosts. Check out the answers on this drupal.stackexchange post on the topic. http://drupal.stackexchange.com/questions/135401/how-to-sql-sync-database-between-two-remotes-which-share-the-same-host

Also... you can still use drush registry_rebuild with drush 8... you just need to manually download it to your ~/.drush folder. At least that is what I've done and it "seems" to work for me. :)

oxyc commented 8 years ago

Worth a try using drush 8 just to see if it's a backwards compatibility issue. I've had issues using two different versions but not sure if it was between 7 and 8 specifically.

oxyc commented 8 years ago

I feel like the alias should have been expanded in the failing command

Calling system(rsync -e 'ssh ' -akzv --exclude=".git" --exclude=".gitignore" --exclude=".hg" --exclude=".hgignore" --exclude=".hgrags" --exclude=".bzr" --exclude=".bzrignore" --exclude=".bzrtags" --exclude=".svn" --stats --progress --remove-source-files @<ACQUIA-DRUSH-ALIAS>.dev:/home/<ACQUIA-DRUSH-ALIAS>/drush-backups/<ACQUIA-DRUSH-ALIAS>dev/20160130150834/<ACQUIA-DRUSH-ALIAS>dev_20160130_150835.sql.gz /tmp/<ACQUIA-DRUSH-ALIAS>dev_20160130_150835.sql.gz);

Note the rsync ... @<ACQUIA-DRUSH-ALIAS>.dev/home/... /tmp/...

vs the ssh commands:

ssh ... <ACQUIA-DRUSH-ALIAS>.dev@staging-XXXX.prod.hosting.acquia.com

When I run drush sql-sync both paths are expanded equally

oxyc commented 8 years ago

Aha so it fails because the rsync command runs on the VM, which does not have access to your acquia site alias (while it is available for the initial SSH command on your host machine). On my own projects I have my production aliases defined within sites/all/drush, this way they get transfered to the VM as well.

With Drush v6.7 it did the following:

SSH into production and creates the dump
Rsync the file from production to host machine (runs on your host machine)
Rsync from host machine to the vm machine (runs on your host machine)

Which would work. But they apparently have changed it since then...

geerlingguy commented 8 years ago

The simplest way to synchronize the database from a remote source into a site in a VM from outside the VM is:

drush @remote-alias sql-dump | drush @drupal-vm-alias sql-cli

geerlingguy commented 8 years ago

(Which is also mentioned in the linked answer from @caschbre)

justinlevi commented 8 years ago

Thank you guys so much for the helpful feedback. @geerlingguy - Piping the sql-dump directly to the vm alias via sql did the trick. drush @remote-alias sql-dump | drush @drupal-vm-alias sql-cli

I'm still not sure why the sql-syc won't work, but I'm going to move on to a few other unrelated registry rebuild issues now.

oxyc commented 8 years ago

Did you try moving the alias definitions to the VM as well? I feel like that should have fixed the issue. And if it did, could be worth adding to the docs.

geerlingguy commented 8 years ago

That could work, yeah... I might try this out for some testing I'm doing today, if I get a chance.

justinlevi commented 8 years ago

Based on some further research I'm thinking this actually might be a larger issue with vagrant and rsync on windows.

Based on this post: https://github.com/mitchellh/vagrant/issues/4073#issuecomment-47164942

It treats c:\Users as /c/Users, which doesn't work on Windows. It should use the colon: c:/Users. (Actually should not use colon for rsync command or else it will think that is a remote path. Cygwin rsync needs /cygdrive/c/ in front.)

Further Reference: https://github.com/mitchellh/vagrant/issues/4586 https://github.com/mitchellh/vagrant/issues/4073

I came back to this issue when exploring using rsync instead of NFS on my windows host and now I'm seemingly getting the same error:

There was an error when attempting to rsync a synced folder.                                                   
Please inspect the error message below for more info.                                                          

Host path: /c/drupal/sites/example-site/                                                                      
Guest path: /var/www/devdesktop/example-site                                                                      
Command: rsync --verbose --archive --delete -z --chmod=ugo=rwX --no-owner --no-group --rsync-path sudo rsync -e
 ssh -p 2222 -o StrictHostKeyChecking=no -o IdentitiesOnly=true -o UserKnownHostsFile=/dev/null -i 'C:/Users/jw
inter/.vagrant.d/insecure_private_key' --exclude .vagrant/ /c/drupal/sites/example-site-dev/ vagrant@127.0.0.1:/va
r/www/example-site                                                                                     
Error: Warning: Permanently added '[127.0.0.1]:2222' (ECDSA) to the list of known hosts.                       
dup() in/out/err failed                                                                                        
rsync: connection unexpectedly closed (0 bytes received so far) [sender]                                       
rsync error: error in rsync protocol data stream (code 12) at io.c(226) [sender=3.1.1]

I don't know enough about Vagrant or the underlying mechanism that is doing the path rewriting but rsync is still totally not working for me.

geerlingguy commented 8 years ago

@justinlevi - Yeah, rsync only really works in some particular scenarios on windows... I've usually told people to stick with SMB or to use winnfsd if they can get it to work reliably, because rsync has so many weird corner cases and native shares are by far the slowest option.

ghost commented 8 years ago

In general we found issues in Windows with directories/files that are shared between the VM and host OS. Most especially if there are symlinks involved, because they are handled so differently, but also there is some "incompatibility" on the permissions/ownership site. I haven't tried winnfsd yet, maybe that is a solution...

geerlingguy commented 8 years ago

@itsgreggmarshall - Yeah, so far my best luck has been with native or SMB synced folders in Windows 10 at least. With winnfsd I've hit little issues with symlinks and permissions, and with rsync I run into similar issues as @justinlevi has mentioned.

But I'm not using Windows as my daily driver... just trying to help some poor souls who are forced to stay on Windows have 1-2s page load times instead of 5-10s :P

justinlevi commented 8 years ago

The hurdles you need to jump through in order to do Drupal, or web development in general, on a Windows host is really problematic. Time and time again it feels like you are severely handicapped if you are stuck with a Windows dev environment.

I realize I'm just venting but it's frustrating to see sub 1 second page load times with almost zero configuration on a mac and then try to do the same on Windows and just get chopped off at the knees time and time again.

geerlingguy commented 8 years ago

@justinlevi - Yeah, the main problem is that almost all the OSS webdev tool builders I know (anecdote, but probably pretty accurate) use either Mac OS X or some flavor of Linux and presume you have a POSIX-like environment, SSH, etc.

Therefore, of the few tools that do support Windows, most of them have support that's maintained by a downstream user of the tool, and not necessarily by the main maintainers.

Drupal VM used to be like that until I went and bought a used Lenovo with Windows 8 and Windows 10 on it (all on my own dime) so I could do sanity checks in Windows and share the pain of the devs who use Drupal VM on Windows. Honestly, I've considered throwing in the towel and dropping official Windows support from time to time. I spend maybe 1-2 hours a month dealing with environment issues for Fedora, Ubuntu, Arch, Mac OS X, etc... and probably 20-80 hours a month with the same for Windows.

Supporting Windows at all comes with a major cost. And with OSS, would you rather have a tool with more optimizations, features, speed, and accessibility, or have maintainers burn time just making it limp by on Windows.

That's the gist of the issue. And upstream from Drupal VM, a lot of the other tools are also developed with a Windows-last philosophy, which doesn't make it any easier on me :P

ghost commented 8 years ago

Ironically, our one trouble spot with the PSO generated Drupal VM is on a Ubuntu 14.04 machine, it appears to be permissions with Phing. I was going to just use Linux to see if it made more sense, but am back to Windows for now.

kevinquillen commented 8 years ago

Running this command on OSX command line:

drush @remote-alias sql-dump | drush @drupal-vm-alias sql-cli

results in:

Pseudo-terminal will not be allocated because stdin is not a terminal.

oxyc commented 8 years ago

@kevinquillen does it still work though? http://drupal.stackexchange.com/questions/169832/drush-sqlq-file-on-remote-server-with-local-file#comment203937_169842

kevinquillen commented 8 years ago

Hmm... I can't tell. I will try again after manually destroying the local db.

On Mon, Feb 29, 2016 at 10:40 AM, Oskar Schöldström < notifications@github.com> wrote:

@kevinquillen https://github.com/kevinquillen does it still work though? http://drupal.stackexchange.com/questions/169832/drush-sqlq-file-on-remote-server-with-local-file#comment203937_169842

— Reply to this email directly or view it on GitHub https://github.com/geerlingguy/drupal-vm/issues/406#issuecomment-190261318 .

kevinquillen commented 8 years ago

Yes, I deleted my local db and this command did in fact, work. There is just a message on the terminal that looks like an error.

oxyc commented 8 years ago

What exactly should be documented here? I'm a bit lost to be honest :) A clarification about drush @remote-alias sql-dump | drush @drupal-vm-alias sql-cli instead of just drush sql-sync under http://docs.drupalvm.com/en/latest/deployment/local-codebase/#build-the-vm-import-your-database?

Is something about rsync with Windows necessary too? I dont own a windows laptop so cant really help with the docs on this :/

geerlingguy commented 8 years ago

Yes, basically :)

For sync, let's leave things be for now. I still have more testing to do before recommending anything particular.

oxyc commented 8 years ago

I really need to get a new Drupal project at work so I can do some more testing as well. Currently all our sites use an older drush version where drush sql-sync works fine.

pierremarceldev commented 8 years ago

Hey all, I had some issues with drush sql-sync as well. I initially got the "it cannot sync between to remotes" error and the after messing around and updating things I got a permissions issue.

Anyways so I decided to move my Acquia drush.alias inside the VM, now things work great. I don't even bother doing drush commands outside the VM.

Here is what I'm using drush sql-sync @project-name.prod @self I'm using drush 8.0.5 I also have no problems syncing db or files from Pantheon as well.

dalin- commented 8 years ago

Here's something that I've been working on today.

First there's a setup script to update the Acquia Drush aliases and copy them to the VM:

#!/bin/bash
# vagrant-db-sync-setup
# Setup the box to use Acquia aliases.

# @todo get these by parsing the config.yml file instead of hard-coding.
VAGRANT_MACHINE_NAME='poets-vagrant'

# Move to the root of DrupalVM.
dir="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
cd $dir/../advovm

# Update Acquia Drush aliases.
drush acquia-update
if [ $? != 0 ]; then
  echo 'First download your Acquia drush aliases.  You only need to do this once.'
  echo 'https://docs.acquia.com/cloud/drush-aliases#download'
  echo "Once you've downloaded the aliases this setup script will attempt to run:"
  echo '  drush acquia-update'
  exit 1
fi

# Ensure we have the plugin to copy files/directories.
vagrant plugin install vagrant-scp

# Copy those Drush aliases into the VM, but avoid overwriting the pre-exising
# alias for the VM.
echo 'Attempting to copy Drush aliases into the VM.'
ls ~/.drush/*.aliases.drushrc.php | fgrep -v "$VAGRANT_MACHINE_NAME.aliases.drushrc.php" | xargs -n1 -I {} vagrant scp {} :~/.drush/
echo 'Drush alias copy is complete.'
echo
echo "You can now fetch a database from the dev server with the \`vagrant-db-sync\` script."

Then there's a script that wraps drush sql-sync with the proper arguments and options:

#!/bin/bash
# vagrant-db-sync
# Sync a DB from the dev site to the vagrant box.

# @todo get these aliases by parsing the config.yml file instead of hard-coding.
SOURCE_DRUSH_ALIAS='@poets.dev'
DESTINATION_DRUSH_ALIAS='@poets.local'
STRUCTURE_TABLES_LIST='accesslog,cache,cache_*,ctools_css_cache,ctools_object_cache,devel_queries,devel_times,forward_log,history,print_page_counter,queue,search_*,sessions,subscriptions_queue,troll_ip_track,views_object_cache,votingapi_cache,watchdog'

# Move to the root of the VM.
dir="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
cd $dir/../advovm

# Call drush sql-sync on the VM.
vagrant ssh --command "drush sql-sync -y --sanitize --create-db --db-su=drupal --db-su-pw=drupal --structure-tables-list='$STRUCTURE_TABLES_LIST' $SOURCE_DRUSH_ALIAS $DESTINATION_DRUSH_ALIAS"

dalin- commented 8 years ago

It occurs to me that sql-dump also accepts --structure-tables-list so drush @remote-alias sql-dump --structure-tables-list='...' | drush @drupal-vm-alias sql-cli might turn out to be faster. sanitization can be run as a second command.

oxyc commented 8 years ago

If you get your aliases to the VM you can also use

drush @dev ssh 'drush sql-sync @production @self'

This is what I've always done for rsync which never supported two remotes.

smallela1 commented 8 years ago

@oxyc @geerlingguy I ran drush @drupaldev.dev sql-dump > C:\Drupal8_Applications\test_sai.sql | drush @drupalvm.local sql-cli < C:\Drupal8_Applications\test_sai.sql

It ignores the second command after the pipe. Even when I run the commands individually (dump and restore), the sql-cli works only after I drop the destination db and then run sql-cli.

Then I would need to run http://drupalvm.dev/core/rebuild.php

Can I run import and restore without dropping the VM database.?

oxyc commented 8 years ago

I'm wondering if these were the options I had to add to get sql-sync working between two remotes: https://github.com/generoi/genero-conf/blob/master/drush/aliases.drushrc.php#L5:L6. I wrote the base for this file too long ago to remember the exact reason behind it.

oxyc commented 8 years ago

I just tested drush sql-sync @remote @drupalvm with drush 8.0.5 and it works fine for me with one modification.

The VMs alias installed on the host computer needs to avoid ~ because rsync doesn't expand it.

'ssh-options' => '-o PasswordAuthentication=no -i ' . $_SERVER['HOME'] . '/.vagrant.d/insecure_private_key'

That's all I needed to get it working.

smallela1 commented 8 years ago

Here is my drupalvm alias on my Windows 7 machine. Won't work for me...

Did you run it from the host or drupal vm?

oxyc commented 8 years ago

Hmm all I need is:

$aliases['drupalvm.dev'] = array(
  'uri' => 'drupalvm.dev',
  'root' => '/var/www/drupalvm/drupal',
  'remote-host' => 'drupalvm.dev',
  'remote-user' => 'vagrant',
  'ssh-options' => '-o PasswordAuthentication=no -i ' . $_SERVER['HOME'] . '/.vagrant.d/insecure_private_key',
);

and then the remote

$aliases['remote.fi'] = array(
  'uri' => 'remote.fi',
  'root' => '/var/www/staging/project/current',
  'remote-host' => 'remote.fi',
  'remote-user' => 'oxy',
);

oxyc commented 8 years ago

What happens if you remove db-url, remote-port?

Edit: I ran it from the host.

Went something like

ssh remote, mysqldump
rsync remote dump to /tmp on host
rsync from host to drupalvm
ssh drupalvm and import

smallela1 commented 8 years ago

No Luck

oxyc commented 8 years ago

At least in my case the dump file which fails for you is rsynced to my host computer. And window doesn't have /tmp I guess. Not sure where to set that though.

smallela1 commented 8 years ago

@oxyc Can you please send me the log of what you see when you run sql-sync. Add -vd flag.

oxyc commented 8 years ago

@smallela1 I'm having some issues with my connection at the moment. I'll post it once I get everything working again. Meanwhile I'm doing some research into this:

%dump and %dump-dir are deprecated.
core-rsync handles two remotes at least as of 8.0.3. There's even a runner option to specify if you want it to run on local, source or destination. https://github.com/drush-ops/drush/commit/7f663066bba1216ba3373f76c28ecbf0bdc7cbe9
sql-sync defaults to running locally https://github.com/drush-ops/drush/commit/dbae28c9a46c9c7336be838d433c9b529161106e, and officially handles two remotes.

Upstream issue for this original report: https://github.com/drush-ops/drush/issues/2047

@smallela1 which drush versions do you have running on the host, the guest and on acquia?

oxyc commented 8 years ago

Here's what I believe we should test:

Requirements:

Install latest drush on host, on guest and on remote
Use Drupal VM master branch, specifically we need commit https://github.com/geerlingguy/drupal-vm/commit/e1fea4bf44b3beacc2439b96becff824b1a64822

Test:

Check if it works on windows using drush sql-sync @remote @drupalvm.drupalvm.dev
If drush cant sql-sync with the default on windows. Try with moving the @remote alias to the VM, make sure it can connect (drush @drupalvm.drupalvm.dev ssh 'drush @remote status'), and then running drush sql-sync --runner=destination @remote @drupalvm.drupalvm.dev on the host machine.

pierremarceldev commented 8 years ago

@oxyc Do you we need these tests done on Mac as well or Windows only?

oxyc commented 8 years ago

I cant test them on Mac once I get my VM provisioned (major connection issues going on today). But would be great if we had more to test as well. I've never used acquia services either. Are they somehow locked to older drush versions? Seems that might be one of the issues that needs testing here as well.

For docs I think we need to figure out:

If it doesn't work with some commonly used host (eg acquia) because they have an outdated drush. The pipe command would be the solution: drush @remote sql-dump | drush @drupalvm sql-cli
If the ideal scenario where the latest drush is installed on host, guest and remote works for both mac and window.
Maybe document the runner option, and explain how drush does it by default?

oxyc commented 8 years ago

on OSX it works with mixed drush 8.0.5 and 9.0-dev versions. https://gist.github.com/oxyc/923e56fde9d38bac2fc2add78931375b

pierremarceldev commented 8 years ago

Ok great I'll test it too and report it back. I've been using it with Acquia and Pantheon and it all works for me, but I'll go through your test steps. BTW Acquia runs on Drush 8.0.1 or 7.1.0 and Pantheon runs on 8.0.5

oxyc commented 8 years ago

Using Drush 8.0.5 on host and guest sql-sync works with both 8.0.1 and 7.1.0 on remote. So both Acquia and Pantheon should work with OSX. Calling for windows testers.

pierremarceldev commented 8 years ago

Yes I can confirm it's working on Mac, I spin a new VM from latest master and I was able now to sql-sync from host drush sql-sync @remote @drupalvm.drupalvm.dev, but only if I use Drush8. If I try to use Drush7 on host it will though an error.

oxyc commented 8 years ago

Thanks for testing!

That makes sense yes. I added some WIP docs mentioning Drush 8.0.3 is a requirement for the host machine, which I think is a fairly easy dependency to follow right? The remote is the one which could be locked to an outdated version.

For Drush 7 on the host, it should still work if you transfer the @remote alias to the @destination (Drupal VM).

Here's the required change, which is unavailable in Drush 7 Drush 8.0.3 onwards: https://github.com/drush-ops/drush/blob/86b736a1d7d5b6014d4ddb88501dcd9e165a5349/commands/sql/sqlsync.drush.inc#L260 Drush 7: https://github.com/drush-ops/drush/blob/7.x/commands/sql/sqlsync.drush.inc#L269:L271 Commit: https://github.com/drush-ops/drush/commit/7f663066bba1216ba3373f76c28ecbf0bdc7cbe9

oxyc commented 8 years ago

Test cases for someone with windows:

This requires you to have the latest master of Drupal VM. More specifically commit e1fea4b.

[ ] Install Drush 8.x on your host machine, your guest machine, as well as the remote. Run drush sql-sync @remote @drupalvm.drupalvm.dev
[ ] Install Drush 8.x on your host machine and your guest machine, as well as Drush 7.1.0 on the remote. Run drush sql-sync @remote @drupalvm.drupalvm.dev

If one of those fails. Try copying your @remote alias to your guest machine (Drupal VM) as per ca349e2a83ca25703ebc47bf68fe8d8de5c98486. If we need this, it means drush has a bug.

[ ] Install Drush 8.x on your host machine, your guest machine, as well as the remote. Run drush sql-sync --runner=destination @remote @drupalvm.drupalvm.dev
[ ] Install Drush 8.x on your host machine and your guest machine, as well as Drush 7.1.0 on the remote. Run drush sql-sync --runner=destination @remote @drupalvm.drupalvm.dev

geerlingguy commented 8 years ago

If nothing else, I'll try to spin up my Win10 environment tomorrow or Tuesday before I head off for vacation. No promises!

geerlingguy commented 8 years ago

@oxyc - Can you submit a PR with these changes, and we'll run with them? I think we're good enough to go with what we have, and then we can adjust the commands/docs if needed moving forward. I don't want to let this issue sit forever :)

geerlingguy / drupal-vm

Acquia Drush Aliases sql-sync to VirtualBox #406

UPDATE