Drush SQL Sync fails when --source server has old drush.

[jonpugh]

Describe the bug

We have an old Aegir server with ~10 sites, drupal 8.9.3 and drush8 installed locally.

We have an updated codebase, drupal 9.5, drush 11, with remote aliases to the old sites.

Remote alias drush @legacy.site sqlc works fine. Remote drush @legacy.site sql-dump works as expected.

However, drush sql-sync @legacy @new fails with the error below.

I traced it down. The SQL dump works fine, it's the syncing back of the file that fails.

I opened up the code, and it looks like support for older drush sources was removed: https://github.com/drush-ops/drush/commit/14d55983120db638889de0ed56f8f386cdddc5ce#

So the dump works, but the output isn't JSON, so this little line of code fails:

  $json = $process->getOutputAsJson();
                $source_dump_path = $json['path'];

To Reproduce

Call drush sql:sync @source @destination where @source is an older site with drush8 and @destination is a site with drush 11 or higher.

Expected behavior Successful sync.

Actual behavior

$ drush sql-sync @legacy.education @production.education
You will destroy data in productioneducat and replace with data from x.edu/educationx.

 Do you really want to continue? (yes/no) [yes]:
 > yes

 [notice] Starting to dump database on source.

In ProcessBase.php line 155:

  Output is empty.

Exception trace:
  at /var/aegir/projects/production/education/vendor/consolidation/site-process/src/ProcessBase.php:155
 Consolidation\SiteProcess\ProcessBase->getOutputAsJson() at /var/aegir/projects/production/education/vendor/drush/drush/src/Commands/sql/SqlSyncCommands.php:152
 Drush\Commands\sql\SqlSyncCommands->dump() at /var/aegir/projects/production/education/vendor/drush/drush/src/Commands/sql/SqlSyncCommands.php:59
 Drush\Commands\sql\SqlSyncCommands->sqlsync() at n/a:n/a
 call_user_func_array() at /var/aegir/projects/production/education/vendor/consolidation/annotated-command/src/CommandProcessor.php:276
 Consolidation\AnnotatedCommand\CommandProcessor->runCommandCallback() at /var/aegir/projects/production/education/vendor/consolidation/annotated-command/src/CommandProcessor.php:212
 Consolidation\AnnotatedCommand\CommandProcessor->validateRunAndAlter() at /var/aegir/projects/production/education/vendor/consolidation/annotated-command/src/CommandProcessor.php:176
 Consolidation\AnnotatedCommand\CommandProcessor->process() at /var/aegir/projects/production/education/vendor/consolidation/annotated-command/src/AnnotatedCommand.php:391
 Consolidation\AnnotatedCommand\AnnotatedCommand->execute() at /var/aegir/projects/production/education/vendor/symfony/console/Command/Command.php:255
 Symfony\Component\Console\Command\Command->run() at /var/aegir/projects/production/education/vendor/symfony/console/Application.php:1039
 Symfony\Component\Console\Application->doRunCommand() at /var/aegir/projects/production/education/vendor/symfony/console/Application.php:275
 Symfony\Component\Console\Application->doRun() at /var/aegir/projects/production/education/vendor/symfony/console/Application.php:149
 Symfony\Component\Console\Application->run() at /var/aegir/projects/production/education/vendor/drush/drush/src/Runtime/Runtime.php:118
 Drush\Runtime\Runtime->doRun() at /var/aegir/projects/production/education/vendor/drush/drush/src/Runtime/Runtime.php:48
 Drush\Runtime\Runtime->run() at /var/aegir/projects/production/education/vendor/drush/drush/drush.php:72
 require() at /var/aegir/projects/production/education/vendor/drush/drush/drush:4

Workaround

I thought downgrading to drush 10 would work, but it failed because this call fails:

                $return = drush_backend_parse_output($process->getOutput());

I noticed that the $options passed didn't include "backend". So I added it (from 10.x), and it worked!

    /**
     * Perform sql-dump on source unless told otherwise.
     *
     * @param $options
     * @param $global_options
     * @param $sourceRecord
     *
     * @return string
     *   Path to the source dump file.
     * @throws \Exception
     */
    public function dump($options, $global_options, $sourceRecord)
    {
        $dump_options = $global_options + [
            'gzip' => true,
            'result-file' => $options['source-dump'] ?: 'auto',
            'backend' => true,
        ];

https://github.com/drush-ops/drush/commit/14d55983120db638889de0ed56f8f386cdddc5ce#diff-9a61aa126053b0759426c41b2cee36682a94d23c85de26f869b469ca47d28c08L151-L161

It is my impression that because this stuff was removed, any SQL sync from a site/server with drush8 on it will fail?

Meaning, if you are upgrading from a drush8 site and want to use SQLSync with source being the old site, you will have to use Drush 10 with this 'backend' patch?

[weitzman]

Thanks for sharing the patch link. That may be helpful for folks.

drush sql-sync @legacy.education @production.education

See https://www.drush.org/11.x/site-aliases/. Especially see the requirement that each app be in one alias file (usually named self.site.yml) and you list each environment within. So the drush command I expect is drush sql-sync @legacy @production. If these two environments are not really the same site, then you are alas on your own. sql:sync is not a general purpose database shuffling tool.

[jonpugh]

They are the same site, it's a multisite so I created legacy.sites.yml in addition to self.sites.yml so we can pull the old database into the new site.

[weitzman]

OK, but thats not supported. You need a site.yml for each of the multisites, and one or more envs in each file.

[jonpugh]

This method is working pretty great for us. Having one file for all the legacy sites is nice and organized, and it's no problem to run the fully qualified drush aliases manually or in CI depending on what site we're working with.

We can't use @self because it's 16 sites. But we can use @self.$SITE and @legacy.$SITE. It's been super helpful. Less files.

This is one repo for 15 sites. Can't use @self.prod because self.site.yml is the same file in all 15.

[jonpugh]

Sorry, I digress...

Aliases aside, I think I came up with a solution. See #5746.

If we can just catch the empty JSON exception and return the default path, would that be ok?