Score:1

How can I fix this query error with drush sql-sync?

lu flag

I crafted my drush/sites/self.site.yml like below* and verified that drush rsync @prod:%files @self:%files worked. However, drush sql-sync @prod @self results in the following error:

  The command "/usr/local/bin/drush sql-query --strict=0 --file=/tmp/termitral_db_20211011_110905.sql.gz --file-delete --uri=drupal --root=/var/www/drupalvm/drupal/web" 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 [NOTIFY]] [--druplicon] [--xh-link XH-LINK] [--] <command> [<query>]

On closer look (aka verbose), I noticed this** and I was told on Drupal Slack that --file only takes an uncompressed SQL file (I later verified this). So my question is, how can I fix this dreadful error?

*

prod:
  host: example.com
  user: user
  root: /data/web/public_html/web
  uri: default
  paths:
   - files: sites/default/files

**

// Copy new and override existing files at
// /tmp/termitral_db_20211011_103342.sql.gz. The source is
// [email protected]:/data/web/drush-backups/termitral_db/20211011103342/
// termitral_db_20211011_103342.sql.gz?: yes.

and

The command "/usr/local/bin/drush sql-query --strict=0 --file=/tmp/termitral_db_20211011_103342.sql.gz --file-delete --uri=drupal --root=/var/www/drupalvm/drupal/web" failed.
leymannx avatar
ne flag
I remember this issue from one DDEV project and the workaround was to simply check the /tmp directory, because the file gets downloaded but not imported somehow, and then gunzip and import the database from there. `ddev ssh` and there `cd /tmp`, then `ls -lhA` to get the latest database file name, and then `gunzip < test_20210928_110056.sql.gz | drush sqlc`. It's just a workaround though. And only happened in one single DDEV instance. Still not solved, yet.
leymannx avatar
ne flag
Which version of Drush BTW?
Kartagis avatar
lu flag
Yeah, sorry. 9.7.3.
leymannx avatar
ne flag
Can you confirm the workaround? That the downloaded database actually exists in the /tmp folder and just the import didn't work?
Kartagis avatar
lu flag
It exists in /tmp and import didn't work. It only works if I decompress it.
Stefanos Petrakis avatar
in flag
Just FYI: " --file=FILE. Path to a file containing the SQL to be run. Gzip files are accepted." from https://www.drush.org/latest/commands/sql_query/
Stefanos Petrakis avatar
in flag
And finally, this may be a workaround, https://github.com/drush-ops/drush/issues/4319#issuecomment-790558557
Score:1
in flag

More information about others with the same problem (https://github.com/drush-ops/drush/issues/4319#issuecomment-790558557)


There are two ways to solve this:

  • Upgrade drush to v10 (better even would be 11)
    (https://www.drush.org/latest/install/#drupal-compatibility)

  • Or, patch your current drush using something like the following:

    diff --git a/includes/drush.inc b/includes/drush.inc
    index a8f356ff8..85d56f617 100644
    --- a/includes/drush.inc
    +++ b/includes/drush.inc
    @@ -352,6 +352,7 @@ function drush_file_is_tarball($path) {
       $supported = [
         'application/x-bzip2',
         'application/x-gzip',
    +    'application/gzip',
         'application/x-tar',
         'application/x-zip',
         'application/zip',
    

Good luck!

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.