Add --database option to sql:sync

[harhoo]

According to SqlSyncCommands, --database should be passable as an option.

     * @usage drush sql:sync @source @self --database=foo --strict=0
     *   Copy a secondary database whose $databases key is named 'foo'. Additional options to sql:dump may also be passed.

Instead, this fails:

$ drush sql:sync @self @stage --database=migration

The "--database" option does not exist.

Passing --strict=0 effectively ignores the database option so it just uses the default database.

System Configuration

Q	A
Drush version?	9.2
Drupal version?	8.7
PHP version	7.2
OS?	Mac

[harhoo]

Just to confirm, I've seen: https://github.com/drush-ops/drush/pull/3951

Running

$ drush sql:sync @self @stage --database=migrate --strict=0 You will destroy data in $REMOTE_DEFAULT_DB and replace with data from $LOCAL_DEFAULT_DB.

whereas I'd expect it to be the database specified in --database

I haven't been able to check whether if it's just an error in the warning message (i.e. it will use the correct db, but just reporting the default)

[weitzman]

Can you paste the debug output when you run with -vvv. Also, Drush 9.2 is very old, and not supported. Please upgrade to 9.7 or 10.0.

[harhoo]

Sorry, am running drush 9.7.0. Dunno why I put 9.2.

$ drush version Drush version : 9.7.0

$ drush sql:sync @stage @self --database=migrate --strict=0 -vvv [preflight] Redispatch to site-local Drush: /path/to/site/vendor/drush/drush/drush. [preflight] Config paths: /path/to/site/vendor/drush/drush/drush.yml,/path/to/site/drush/drush.yml [preflight] Alias paths: /path/to/site/web/drush/sites,/path/to/site/drush/sites [preflight] Commandfile search paths: /path/to/site/vendor/drush/drush/src,/path/to/site/drush [bootstrap] Starting bootstrap to none [0.37 sec, 8.55 MB] [bootstrap] Drush bootstrap phase 0 [0.37 sec, 8.55 MB] [bootstrap] Try to validate bootstrap phase 0 [0.37 sec, 8.55 MB] [info] Executing: ssh -o PasswordAuthentication=no user@remotesite '/opt/www/sitedir/drupal/vendor/bin/drush core-status db-name --format=json --uri=https://remotesite --root=/opt/www/sitedir/drupal' [0.41 sec, 9.01 MB] [info] Executing: /path/to/site/vendor/drush/drush/drush core-status db-name --format=json --uri=default --root=/path/to/site/web [3.12 sec, 9.05 MB]

You will destroy data in $local_default_db and replace with data from remote/$remote_default_db.

Slightly redacted, but you get the drift.

In both sites (@stage and @self) the settings.local.php has

$databases['default']['default'] and $databases['migrate']['default']

[weitzman]

I see how to fix The "--database" option does not exist. by adding @optionset_sql annotation. But as you point out, it still needs more work after that. And we don't really want to support --db-url and --target. Its also ambiguous which DB these option would refer to: source or target or both. In your migrate example I think you want both.

I've gone ahead and removed the misleading sql-sync Example for now 63f28484. Also removed in 9.x

[weitzman]

This was possible in older versions of Drush so I'll leave this open as a feature request in case anyone gets the urge.

[ayalon]

It was possible in Drupal 8 to sync a secondary database: drush sql-sync @live @self --source-database=other_db --target-database=other_db

Is this somehow possible with Drush 10? You removed the example but I still think is is a very important use case to allow to sync another database.

[weitzman]

Not as easy with Drush 10. You can do it by running sql:dump, rsync (drush's or standard), and then sqlq. PRs for sql:sync will be considered.

[oleksandr-acerta]

if you have db backup locally, you could use

drush sql-sync @self @target --no-dump --source-dump=db.sql

[ksenzee]

I haven't done much if any Drush development, but I did need at least the part of this feature that lets you specify the source database (which in my case is a read replica). Is this a reasonable approach? If so, I can add the target database option similarly and turn it into a proper PR.

index 84276ded6a..f453860b84 100644
--- a/src/Commands/sql/SqlSyncCommands.php
+++ b/src/Commands/sql/SqlSyncCommands.php
@@ -46,13 +46,14 @@ public function __construct(
     #[CLI\Option(name: 'db-su', description: 'Account to use when creating a new database (e.g. <info>root</info>).')]
     #[CLI\Option(name: 'db-su-pw', description: 'Password for the db-su account.')]
     #[CLI\Option(name: 'source-dump', description: 'The path for retrieving the sql-dump on source machine.')]
+    #[CLI\Option(name: 'source-database', description: 'The name of the database to use on source machine.')]
     #[CLI\Option(name: 'target-dump', description: 'The path for storing the sql-dump on target machine.')]
     #[CLI\Option(name: 'extra-dump', description: 'Add custom arguments/options to the dumping of the database (e.g. mysqldump command).')]
     #[CLI\Usage(name: 'drush sql:sync @source @self', description: "Copy the database from the site with the alias 'source' to the local site.")]
     #[CLI\Usage(name: 'drush sql:sync @self @target', description: "Copy the database from the local site to the site with the alias 'target'.")]
     #[CLI\Usage(name: 'drush sql:sync #prod #dev', description: 'Copy the database from the site in /sites/prod to the site in /sites/dev (multisite installation).')]
     #[CLI\Topics(topics: [DocsCommands::ALIASES, DocsCommands::POLICY, DocsCommands::CONFIGURATION, DocsCommands::EXAMPLE_SYNC_VIA_HTTP])]
-    public function sqlsync($source, $target, $options = ['no-dump' => false, 'no-sync' => false, 'runner' => self::REQ, 'create-db' => false, 'db-su' => self::REQ, 'db-su-pw' => self::REQ, 'target-dump' => self::REQ, 'source-dump' => self::OPT, 'extra-dump' => self::REQ]): void
+    public function sqlsync($source, $target, $options = ['no-dump' => false, 'no-sync' => false, 'runner' => self::REQ, 'create-db' => false, 'db-su' => self::REQ, 'db-su-pw' => self::REQ, 'target-dump' => self::REQ, 'source-dump' => self::OPT, 'source-database' => self::OPT, 'extra-dump' => self::REQ]): void
     {
         $sourceRecord = $this->siteAliasManager->get($source);
         $targetRecord = $this->siteAliasManager->get($target);
@@ -139,6 +140,9 @@ public function dump(array $options, array $global_options, SiteAlias $sourceRec
             'gzip' => true,
             'result-file' => $options['source-dump'] ?: 'auto',
         ];
+        if ($options['source-database']) {
+            $dump_options['database'] = $options['source-database'];
+        }
         if (!$options['no-dump']) {
             $this->logger()->notice(dt('Starting to dump database on source.'));
             $process = $this->processManager()->drush($sourceRecord, SqlCommands::DUMP, [], $dump_options + ['format' => 'json']);