Drush sql-sync Error: no databse record could found for target @mysite.local

Question

I am attempting to sync a production database to a local environment. When I run something along the lines of:

drush sql-sync @mysite.prod @mysite.local --source-dump /path/to/dump --target-dump /path-to-dump --create-db --no-cache -y

I get the error:

Error: no database record could be found for target @mysite.local

However, drush @mysite.local sql-conf returns the correct connection information, and drush @mysite.local sql-cli connects to the correct database as expected.

Further, drush @mysite.local status and drush @mysite.prod status return the expected status reports.

Aliases file contents (for institutional reasons, making changes to this file is not ideal):

<?php

$aliases['local'] = array(
  'parent' => '@parent',
  'env' => 'local',
  'site' => 'mysite',
  'uri' => 'mysite.com',
  'ssh-options' => '-o StrictHostKeyChecking=no ',
  'sites-subdir' => 'mysite'
);

$aliases['prod'] = array(
  'parent' => '@mysite.local',
  'env' => 'prod',
  'root' => '/var/www/html/mysite.prod/docroot',
  'remote-host' => 'xxxxxx.myhost.com',
  'remote-user' => 'mysite',
  'path-aliases' => array(
    '%drush-script' => '/usr/local/drush5/drush',
  ),
  // #destinations is a custom property
  '#destinations' => array(
    'releases' => "[email protected]:mysite.git",
  ),
);

$aliases['stage'] = array(
  'parent' => '@mysite.prod',
  'env' => 'test',
  'root' => '/var/www/html/mysite.test/docroot',
  'remote-host' => 'xxxxxxxx.myhost.com',
);

$aliases['dev'] = array(
  'parent' => '@mysite.stage',
  'env' => 'dev',
  'root' => '/var/www/html/mysite.dev/docroot',
);

?>

Debugger output

Bootstrap to phase 0. [0 sec, 3.79 MB]                               [bootstrap]
Drush bootstrap phase : _drush_bootstrap_drush() [0.01 sec, 3.99 MB] [bootstrap]
Loading drushrc                                                      [bootstrap]
"/var/www/vhosts/docroot/sites/all/drush/drushrc.php" into
"drupal" scope. [0.01 sec, 4 MB]
Include /var/www/vhosts/docroot/sites/all/drush/commands     [notice]
[0.01 sec, 4.02 MB]
Cache HIT cid: 6.2.0-commandfiles-0-xxxxxxxxxxxxxxxxxxxxx     [debug]
[0.01 sec, 4.03 MB]
Bootstrap to phase 0. [0.06 sec, 9.36 MB]                            [bootstrap]
Bootstrap to phase 0. [0.08 sec, 9.36 MB]                            [bootstrap]
Found command: sql-sync (commandfile=sql) [0.08 sec, 9.36 MB]        [bootstrap]
Including /usr/share/pear/drush/commands/sql/sync.sql.inc [0.14 sec, [bootstrap]
9.38 MB]
Calling drush command init function: drush_sql_sync_init [0.14 sec,  [bootstrap]
9.58 MB]
Loaded alias @mysite.local from file                                    [notice]
/var/www/vhosts/docroot/sites/all/drush/aliases/mysite.aliases.drushrc.php
[0.25 sec, 9.6 MB]
Undefined index: root sitealias.inc:1168 [0.25 sec, 9.6 MB]             [notice]
Undefined index: root sitealias.inc:1168 [0.43 sec, 9.62 MB]            [notice]
Loaded alias @mysite.prod from file                                     [notice]
/var/www/vhosts/docroot/sites/all/drush/aliases/mysite.aliases.drushrc.php
[0.49 sec, 9.62 MB]
Loaded alias @mysite.stage from file                                    [notice]
/var/www/vhosts/docroot/sites/all/drush/aliases/mysite.aliases.drushrc.php
[0.56 sec, 9.62 MB]
Loaded alias @mysite.dev from file                                      [notice]
/var/www/vhosts/docroot/sites/all/drush/aliases/mysite.aliases.drushrc.php
[0.63 sec, 9.66 MB]
Undefined index: root sitealias.inc:1168 [0.63 sec, 9.66 MB]            [notice]
Backend invoke: ssh -o StrictHostKeyChecking=no                        [command]
[email protected] '/usr/local/drush6/drush 
--backend=2 --verbose --debug --yes --uri=mysite.com
--root=/var/www/html/mysite.dev/docroot  sql-conf   --all 2>&1' 2>&1
[0.74 sec, 9.68 MB]
ssh -o StrictHostKeyChecking=no                                         [notice]
[email protected] '/usr/local/drush6/drush 
--backend=2 --verbose --debug --yes --uri=mysite.com
--root=/var/www/html/mysite.dev/docroot  sql-conf   --all 2>&1' 2>&1
[0.74 sec, 9.68 MB]
Calling hook drush_sql_sync [3.34 sec, 9.72 MB]                          [debug]
Undefined index: root sitealias.inc:1168 [3.34 sec, 9.73 MB]            [notice]
Error: no database record could be found for target @mysite.local    [error]
[3.34 sec, 9.73 MB]
Returned from hook drush_sql_sync [3.34 sec, 9.73 MB]                    [debug]
Command dispatch complete [3.34 sec, 9.7 MB]                            [notice]
Peak memory usage was 11.99 MB [3.34 sec, 9.7 MB]                       [memory]

Any help debugging this would be greatly appreciated.

Answer 1

First, an unrelated problem: use --source-dump=/path/to/dump --target-dump=/path-to-dump, as Drush options always use the '=' form for specifying values.

As for why sql-sync would fail to find the database record when the corresponding sql-conf works, that's a more difficult question. Show the contents of your alias records, and include output with the --debug flag.

Update:

Are both 'local' and 'prod' remote machines? Drush is using ssh to fetch @mysite.local's database record. If that's not correct, try removing the remote-host option from the alias definition. Drush tries to ignore remote-host for the local machine so that you can use the same record on every machine, but it might be getting that wrong.

If your 'local' site is supposed to be remote, it looks like you are using drush5 on the remote machine, and drush6 on the local machine. This is supposed to work, but backend invoke is slightly different between these two versions, and there may be some problem. Try using the same version of Drush on both ends, and see if that helps.