Getting 'Query failed' while executing blt:drupal:sync:db command

[Vinay15]

I want to... sync local database with the remote

It's not working because... Executing blt:drupal:sync:db command results in Query failed error.

Detailed error output `[debug] Drupal VM is not initialized. [Robo\Common\ProcessExecutor] Running /Users/vinay/Sites/ezblt/vendor/bin/drush @self site:alias @self --format=json in /Users/vinay/Sites/ezblt/docroot { "@self": { "root": "/Users/vinay/Sites/ezblt/docroot", "uri": "http://local.ezcontent.com" } } [Acquia\Blt\Robo\Tasks\DrushTask] Running /Users/vinay/Sites/ezblt/vendor/bin/drush cache-clear drush --no-interaction -vvv --ansi && /Users/vinay/Sites/ezblt/vendor/bin/drush sql-sync @ezcontent.dev @self --target-dump=/var/folders/rs/xfy3789n0m16gsy2cbnpqzf80000gn/T/tmp.target.sql.gz --structure-tables-key=lightweight --create-db --no-interaction -vvv --ansi && /Users/vinay/Sites/ezblt/vendor/bin/drush cr --no-interaction -vvv --ansi && /Users/vinay/Sites/ezblt/vendor/bin/drush sql-sanitize --no-interaction -vvv --ansi in /Users/vinay/Sites/ezblt/docroot [preflight] Config paths: /Users/vinay/Sites/ezblt/vendor/drush/drush/drush.yml,/Users/vinay/Sites/ezblt/drush/drush.yml,/Users/vinay/Sites/ezblt/docroot/sites/default/local.drush.yml [preflight] Alias paths: /Users/vinay/Sites/ezblt/docroot/drush/sites,/Users/vinay/Sites/ezblt/drush/sites [preflight] Commandfile search paths: /Users/vinay/Sites/ezblt/vendor/drush/drush/src,/Users/vinay/Sites/ezblt/drush [bootstrap] Starting bootstrap to max [0.15 sec, 9.61 MB] [debug] Trying to bootstrap as far as we can [0.16 sec, 9.61 MB] [bootstrap] Drush bootstrap phase: bootstrapDrupalRoot() [0.16 sec, 9.61 MB] [bootstrap] Change working directory to /Users/vinay/Sites/ezblt/docroot [0.16 sec, 9.61 MB] [bootstrap] Initialized Drupal 8.7.11 root directory at /Users/vinay/Sites/ezblt/docroot [0.16 sec, 9.75 MB] [bootstrap] Drush bootstrap phase: bootstrapDrupalSite() [0.16 sec, 10.14 MB] [bootstrap] Initialized Drupal site local.ezcontent.com at sites/default [0.16 sec, 10.33 MB] [bootstrap] Drush bootstrap phase: bootstrapDrupalConfiguration() [0.16 sec, 10.33 MB] [debug] Add service modifier [0.19 sec, 10.62 MB] [bootstrap] Drush bootstrap phase: bootstrapDrupalDatabase() [0.2 sec, 11.1 MB] [bootstrap] Successfully connected to the Drupal database. [0.2 sec, 11.1 MB] [bootstrap] Drush bootstrap phase: bootstrapDrupalFull() [0.2 sec, 11.1 MB] [debug] Start bootstrap of the Drupal Kernel. [0.2 sec, 11.1 MB] [debug] Finished bootstrap of the Drupal Kernel. [0.27 sec, 20.54 MB] [debugnotify] Add a commandfile class: Drush\Drupal\Commands\config\ConfigCommands [0.4 sec, 29.96 MB] [debugnotify] Add a commandfile class: Drush\Drupal\Commands\config\ConfigExportCommands [0.4 sec, 30 MB] [debugnotify] Add a commandfile class: Drush\Drupal\Commands\config\ConfigImportCommands [0.41 sec, 30 MB] [debugnotify] Add a commandfile class: Drush\Drupal\Commands\core\BatchCommands [0.41 sec, 30.01 MB] [debugnotify] Add a commandfile class: Drush\Drupal\Commands\core\CliCommands [0.41 sec, 30.01 MB] [debugnotify] Add a commandfile class: Drush\Drupal\Commands\core\DrupalCommands [0.41 sec, 30.02 MB] [debugnotify] Add a commandfile class: Drush\Drupal\Commands\core\EntityCommands [0.41 sec, 30.03 MB] [debugnotify] Add a commandfile class: Drush\Drupal\Commands\core\ImageCommands [0.42 sec, 30.03 MB] [debugnotify] Add a commandfile class: Drush\Drupal\Commands\core\JsonapiCommands [0.42 sec, 30.04 MB] [debugnotify] Add a commandfile class: Drush\Drupal\Commands\core\LanguageCommands [0.42 sec, 30.05 MB] [debugnotify] Add a commandfile class: Drush\Drupal\Commands\core\LocaleCommands [0.42 sec, 30.06 MB] [debugnotify] Add a commandfile class: Drush\Drupal\Commands\core\MessengerCommands [0.43 sec, 30.07 MB] [debugnotify] Add a commandfile class: Drush\Drupal\Commands\core\QueueCommands [0.43 sec, 30.07 MB] [debugnotify] Add a commandfile class: Drush\Drupal\Commands\core\RoleCommands [0.43 sec, 30.09 MB] [debugnotify] Add a commandfile class: Drush\Drupal\Commands\core\StateCommands [0.43 sec, 30.11 MB] [debugnotify] Add a commandfile class: Drush\Drupal\Commands\core\TwigCommands [0.44 sec, 30.12 MB] [debugnotify] Add a commandfile class: Drush\Drupal\Commands\core\UserCommands [0.44 sec, 30.14 MB] [debugnotify] Add a commandfile class: Drush\Drupal\Commands\core\ViewsCommands [0.44 sec, 30.18 MB] [debugnotify] Add a commandfile class: Drush\Drupal\Commands\core\WatchdogCommands [0.45 sec, 30.21 MB] [debugnotify] Add a commandfile class: Drush\Drupal\Commands\pm\PmCommands [0.46 sec, 30.23 MB] [debugnotify] Add a commandfile class: Drush\Drupal\Commands\pm\ThemeCommands [0.46 sec, 30.25 MB] [debugnotify] Add a commandfile class: Drush\Drupal\Commands\sql\SanitizeCommands [0.46 sec, 30.25 MB] [debugnotify] Add a commandfile class: Drush\Drupal\Commands\sql\SanitizeCommentsCommands [0.46 sec, 30.25 MB] [debugnotify] Add a commandfile class: Drush\Drupal\Commands\sql\SanitizeSessionsCommands [0.46 sec, 30.26 MB] [debugnotify] Add a commandfile class: Drush\Drupal\Commands\sql\SanitizeUserFieldsCommands [0.47 sec, 30.26 MB] [debugnotify] Add a commandfile class: Drush\Drupal\Commands\sql\SanitizeUserTableCommands [0.47 sec, 30.26 MB] [debugnotify] Add a commandfile class: Drupal\scheduler\Commands\SchedulerCommands [0.47 sec, 30.27 MB] [debugnotify] Add a commandfile class: Drupal\simple_oauth\Commands\SimpleOauthCommands [0.47 sec, 30.27 MB] [debugnotify] Add a commandfile class: Drupal\simple_sitemap\Commands\SimplesitemapCommands [0.47 sec, 30.27 MB] [debugnotify] Add a commandfile class: Drupal\token\Commands\TokenCommands [0.47 sec, 30.28 MB] [debugnotify] Add a commandfile class: Drupal\webform\Commands\WebformCommands [0.48 sec, 30.28 MB] [debugnotify] Add a commandfile class: Drupal\webform\Commands\WebformSanitizeSubmissionsCommands [0.48 sec, 30.36 MB] [debugnotify] Add a commandfile class: Drupal\pathauto\Commands\PathautoCommands [0.48 sec, 30.37 MB] [debug] Calling Drush\Commands\core\CacheCommands::clearDrush(array) [0.49 sec, 30.42 MB] [success] 'drush' cache was cleared. [0.5 sec, 30.42 MB] [preflight] Config paths: /Users/vinay/Sites/ezblt/vendor/drush/drush/drush.yml,/Users/vinay/Sites/ezblt/drush/drush.yml,/Users/vinay/Sites/ezblt/docroot/sites/default/local.drush.yml [preflight] Alias paths: /Users/vinay/Sites/ezblt/docroot/drush/sites,/Users/vinay/Sites/ezblt/drush/sites [preflight] Commandfile search paths: /Users/vinay/Sites/ezblt/vendor/drush/drush/src,/Users/vinay/Sites/ezblt/drush [bootstrap] Starting bootstrap to none [0.25 sec, 9.7 MB] [bootstrap] Drush bootstrap phase 0 [0.25 sec, 9.77 MB] [bootstrap] Try to validate bootstrap phase 0 [0.25 sec, 9.77 MB] [info] Executing: ssh -p 22 ezcontent.dev@srv-7825.devcloud.hosting.acquia.com 'drush core-status db-name --format=json --uri=ezcontent53esqcn36d.devcloud.acquia-sites.com --root=/var/www/html/ezcontent.dev/docroot' [0.27 sec, 10.26 MB] [info] Executing: /Users/vinay/Sites/ezblt/vendor/drush/drush/drush core-status db-name --format=json --uri=http://local.ezcontent.com --root=/Users/vinay/Sites/ezblt/docroot [4.23 sec, 10.31 MB] You will destroy data in drupal_ez and replace with data from srv-7825.devcloud.hosting.acquia.com/ezcontent53esqcn36d. [notice] Starting to create database on target. [4.86 sec, 10.36 MB] [info] Executing: /Users/vinay/Sites/ezblt/vendor/drush/drush/drush sql-create --create-db --target-dump=/var/folders/rs/xfy3789n0m16gsy2cbnpqzf80000gn/T/tmp.target.sql.gz --ansi --no-interaction --structure-tables-key=lightweight --strict=0 --uri=http://local.ezcontent.com --root=/Users/vinay/Sites/ezblt/docroot [4.87 sec, 10.36 MB] [notice] Starting to dump database on source. [6.39 sec, 10.36 MB] [info] Executing: ssh -p 22 ezcontent.dev@srv-7825.devcloud.hosting.acquia.com 'drush sql-dump --create-db --target-dump=/var/folders/rs/xfy3789n0m16gsy2cbnpqzf80000gn/T/tmp.target.sql.gz --ansi --no-interaction --structure-tables-key=lightweight --strict=0 --gzip --result-file=auto --backend=json --uri=ezcontent53esqcn36d.devcloud.acquia-sites.com --root=/var/www/html/ezcontent.dev/docroot' [6.39 sec, 10.36 MB] [notice] Copying dump file from source to target. [14.26 sec, 10.36 MB] [info] Executing: /Users/vinay/Sites/ezblt/vendor/drush/drush/drush core-rsync @ezcontent.dev:/home/ezcontent/drush-backups/ezcontent53esqcn36d/20200227155738/ezcontent53esqcn36d_20200227_155738.sql.gz @self:/var/folders/rs/xfy3789n0m16gsy2cbnpqzf80000gn/T/tmp.target.sql.gz --yes --uri=http://local.ezcontent.com --root=/Users/vinay/Sites/ezblt/docroot -- --remove-source-files [14.27 sec, 10.38 MB]

// Copy new and override existing files at // /var/folders/rs/xfy3789n0m16gsy2cbnpqzf80000gn/T/tmp.target.sql.gz. The // source is // ezcontent.dev@srv-7825.devcloud.hosting.acquia.com:/home/ezcontent/drush-bac // kups/ezcontent53esqcn36d/20200227155738/ezcontent53esqcn36d_20200227_155738. // sql.gz?: yes.

[notice] Starting to import dump file onto target database. [19.88 sec, 10.37 MB] [info] Executing: /Users/vinay/Sites/ezblt/vendor/drush/drush/drush sql-query --create-db --target-dump=/var/folders/rs/xfy3789n0m16gsy2cbnpqzf80000gn/T/tmp.target.sql.gz --ansi --no-interaction --structure-tables-key=lightweight --strict=0 --file=/var/folders/rs/xfy3789n0m16gsy2cbnpqzf80000gn/T/tmp.target.sql.gz --file-delete --uri=http://local.ezcontent.com --root=/Users/vinay/Sites/ezblt/docroot [19.88 sec, 10.38 MB]

In Process.php line 235:

[Symfony\Component\Process\Exception\ProcessFailedException] The command "/Users/vinay/Sites/ezblt/vendor/drush/drush/drush sql-query - -create-db --target-dump=/var/folders/rs/xfy3789n0m16gsy2cbnpqzf80000gn/T/t mp.target.sql.gz --ansi --no-interaction --structure-tables-key=lightweight --strict=0 --file=/var/folders/rs/xfy3789n0m16gsy2cbnpqzf80000gn/T/tmp.tar get.sql.gz --file-delete --uri=http://local.ezcontent.com --root=/Users/sri jan/Sites/ezblt/docroot" failed.

Exit Code: 1(General error)

Working directory:

Output:

Error Output:

In SqlCommands.php line 199:

Query failed.

sql:query [--result-file [RESULT-FILE]] [--file FILE] [--file-delete] [--extra EXTRA] [--db-prefix] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose ] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-d|--debug] [- y|--yes] [--no] [--remote-host REMOTE-HOST] [--remote-user REMOTE-USER] [-r |--root ROOT] [-l|--uri URI] [--simulate] [--pipe] [-D|--define DEFINE] [-- database [DATABASE]] [--target [TARGET]] [--db-url DB-URL] [--notify [NOTIF Y]] [--druplicon] [--xh-link XH-LINK] [--] [ [39m]

Exception trace: at /Users/vinay/Sites/ezblt/vendor/symfony/process/Process.php:235 Symfony\Component\Process\Process->mustRun() at /Users/vinay/Sites/ezblt/vendor/drush/drush/src/Commands/sql/SqlSyncCommands.php:243 Drush\Commands\sql\SqlSyncCommands->import() at /Users/vinay/Sites/ezblt/vendor/drush/drush/src/Commands/sql/SqlSyncCommands.php:63 Drush\Commands\sql\SqlSyncCommands->sqlsync() at n/a:n/a call_user_func_array() at /Users/vinay/Sites/ezblt/vendor/consolidation/annotated-command/src/CommandProcessor.php:257 Consolidation\AnnotatedCommand\CommandProcessor->runCommandCallback() at /Users/vinay/Sites/ezblt/vendor/consolidation/annotated-command/src/CommandProcessor.php:212 Consolidation\AnnotatedCommand\CommandProcessor->validateRunAndAlter() at /Users/vinay/Sites/ezblt/vendor/consolidation/annotated-command/src/CommandProcessor.php:176 Consolidation\AnnotatedCommand\CommandProcessor->process() at /Users/vinay/Sites/ezblt/vendor/consolidation/annotated-command/src/AnnotatedCommand.php:302 Consolidation\AnnotatedCommand\AnnotatedCommand->execute() at /Users/vinay/Sites/ezblt/vendor/symfony/console/Command/Command.php:255 Symfony\Component\Console\Command\Command->run() at /Users/vinay/Sites/ezblt/vendor/symfony/console/Application.php:1001 Symfony\Component\Console\Application->doRunCommand() at /Users/vinay/Sites/ezblt/vendor/symfony/console/Application.php:255 Symfony\Component\Console\Application->doRun() at /Users/vinay/Sites/ezblt/vendor/symfony/console/Application.php:148 Symfony\Component\Console\Application->run() at /Users/vinay/Sites/ezblt/vendor/drush/drush/src/Runtime/Runtime.php:118 Drush\Runtime\Runtime->doRun() at /Users/vinay/Sites/ezblt/vendor/drush/drush/src/Runtime/Runtime.php:49 Drush\Runtime\Runtime->run() at /Users/vinay/Sites/ezblt/vendor/drush/drush/drush.php:72 require() at /Users/vinay/Sites/ezblt/vendor/drush/drush/drush:4

sql:sync [--no-dump] [--no-sync] [--runner RUNNER] [--create-db] [--db-su DB-SU] [--db-su-pw DB-SU-PW] [--target-dump TARGET-DUMP] [--source-dump [SOURCE-DUMP]] [--extra-dump] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-d|--debug] [-y|--yes] [--no] [--remote-host REMOTE-HOST] [--remote-user REMOTE-USER] [-r|--root ROOT] [-l|--uri URI] [--simulate] [--pipe] [-D|--define DEFINE] [--skip-tables-key SKIP-TABLES-KEY] [--structure-tables-key STRUCTURE-TABLES-KEY] [--tables-key TABLES-KEY] [--skip-tables-list SKIP-TABLES-LIST] [--structure-tables-list STRUCTURE-TABLES-LIST] [--tables-list TABLES-LIST] [--notify] [--druplicon] [--xh-link XH-LINK] [--]

[Acquia\Blt\Robo\Tasks\DrushTask] Exit code 1 Time 21.009s 21.843s total time elapsed.`

BLT doctor output +---------------------------+--------------------------------------------------------------+ | Property | Value | +---------------------------+--------------------------------------------------------------+ | %paths.%root | /Users/vinay/Sites/ezblt/docroot | | %paths.%site | sites/default | | %paths.%modules | sites/all/modules | | %paths.%themes | sites/all/themes | | %paths.%config-sync | /Users/vinay/Sites/ezblt/config/default | | %paths.%files | sites/default/files | | %paths.%temp | /tmp | | %paths.%private | /Users/vinay/Sites/ezblt/files-private | | admin-theme | ezcontent_admin | | alias-searchpaths.0 | /Users/vinay/Sites/ezblt/drush/sites | | blt-version | 10.7.2 | | bootstrap | Successful | | composer-version | Composer version 1.9.2 2020-01-14 16:30:31 | | config-sync | /Users/vinay/Sites/ezblt/config/default | | db-driver | mysql | | db-hostname | 127.0.0.1 | | db-name | drupal_ez | | db-password | root | | db-port | 3306 | | db-status | Connected | | db-username | root | | drupal-settings-file | sites/default/settings.php | | drupal-version | 8.7.11 | | drush-alias-files.0 | /Users/vinay/Sites/ezblt/drush/sites/ezcontent.site.yml | | drush-cache-directory | /Users/vinay/.drush/cache | | drush-conf.0 | /Users/vinay/Sites/ezblt/vendor/drush/drush/drush.yml | | drush-conf.1 | /Users/vinay/Sites/ezblt/drush/drush.yml | | drush-conf.2 | /Users/vinay/Sites/ezblt/docroot/sites/default/local.drush. | | | yml | | drush-script | /Users/vinay/Sites/ezblt/vendor/drush/drush/drush | | drush-temp | /tmp | | drush-version | 9.7.2 | | files | sites/default/files | | install-profile | ezcontent | | modules | sites/all/modules | | php-bin | /usr/local/Cellar/php/7.4.2/bin/php | | php-conf.0 | /usr/local/etc/php/7.4/php.ini | | php-os | Darwin | | private | /Users/vinay/Sites/ezblt/files-private | | root | /Users/vinay/Sites/ezblt/docroot | | site | sites/default | | stacks.drupal-vm.inited | false | | stacks.dev-desktop.inited | false | | temp | /tmp | | theme | ezcontent_theme | | themes | sites/all/themes | | uri | http://local.ezcontent.com | +---------------------------+--------------------------------------------------------------+ +----------------------------------------+------------------------------------------------------------+ | Check | Problem | +----------------------------------------+------------------------------------------------------------+ | ComposerCheck:checkPrestissimo:plugins | hirak/prestissimo plugin for composer is not installed. | | | Run composer global require | | | hirak/prestissimo to install it. | | | This will improve composer install/update performance by | | | parallelizing the download of dependency information. | | NodeCheck:checkNodeVersionFileExists | Neither .nvmrc nor .node-version file found in repo root. | | PhpCheck:checkPhpDateTimezone | PHP setting for date.timezone is not set. | | | Define date.timezone in /usr/local/etc/php/7.4/php.ini | +----------------------------------------+------------------------------------------------------------+ [error] BLT Doctor discovered one or more critical issues. For troubleshooting guidance and support, see https://docs.acquia.com/blt/support/

System information

• Operating system type: macOS
• Operating system version: 10.15.3
• BLT version: 10.7.2

[danepowell]

It looks like you are using Drush 9.7.2 locally, what version is running remotely? If it's different, follow the advice in https://support.acquia.com/hc/en-us/articles/360035203713-Permission-denied-or-other-errors-during-BLT-sync-or-drush-sql-sync

Try also running the Drush command without invoking BLT, i.e. drush sql-sync @ezcontent.dev @self. See if any of the particular Drush args are causing a problem.

Is MySQL actually running and accepting connections on the host using the credentials provided? Does the MySQL version locally match what you are running in Cloud?

The next step would be to find out why Drush is choking on the import: https://drupal.stackexchange.com/questions/193296/how-to-solve-query-failed-error-in-drush-sql-sync-output

[Vinay15]

@danepowell Thank you! Please have a look at my findings below:

• Drush version is same on local and remote i.e. 9.7.2

• Does the MySQL version locally match what you are running in Cloud? -- This was not matching, so I downgraded my local version from 8.0 to 5.6 (same as remote version) and still getting the same Query failed error as mentioned in the following steps

• drush sql-sync @ezcontent.dev @self results in the same Query failed error. I tried executing this command as drush sql-sync @ezcontent.dev @self -vvv and was able to see that internally this was the command that was failing: mysql --defaults-file=/private/tmp/drush_WLzVuA --database=drupal_ez --host=localhost --port=3306 --silent -A < /tmp/ezcontent53esqcn36d_20200311_132518.sql.gz

• Is MySQL actually running and accepting connections on the host using the credentials provided? -- Yes, I did gzip -d /tmp/ezcontent53esqcn36d_20200311_132518.sql.gz and mysql -uroot -p --database=drupal_ez --host=localhost --port=3306 --silent -A < /tmp/ezcontent53esqcn36d_20200311_132518.sql and was able to import the database.

[danepowell]

Since the error occurs when running the drush command on its own, it seems like some kind of Drush issue. Your best bet is to go to the Drush issue queue for support: https://github.com/drush-ops/drush

Also check the stackexchange link I posted above about query failed errors.