drush sql:sync query failed. SQLCommands.php (missing application/gzip mime type)

[thetwentyseven]

I have an issue trying to use "drush sql:sync". The strange thing is, it is working from my-local to live-environment, but not the other way around. Whereas my-local is Arch Linux, my live-environment is CentOS, but both have the same Drupal and Drush version:

my-local: PHP 7.4.1
live-environment: PHP 7.1.33

 Drupal version   : 8.8.1                                    
 Site URI         : http://d8.local                          
 DB driver        : mysql                                    
 DB hostname      : localhost                                
 DB port          : 3306                                     
 DB username      : d8                                    
 DB name          : d8                                    
 Database         : Connected                                
 Drupal bootstrap : Successful                               
 Default theme    : bartik                                   
 Admin theme      : seven                                    
 PHP binary       : /usr/bin/php                             
 PHP config       : /etc/php/php.ini                         
 PHP OS           : Linux                                    
 Drush script     : /usr/local/bin/drush                     
 Drush version    : 10.1.1                                   
 Drush temp       : /tmp                                     
 Drush configs    : /var/www/d8/vendor/drush/drush/drush.yml 
                             /var/www/d8/web/drush/drush.yml          
 Install profile  : standard                                 
 Drupal root      : /var/www/d8/web                          
 Site path        : sites/default                            
 Files, Public    : sites/default/files                      
 Files, Temp      : /tmp   

Error with debug and verbose

[preflight] Config paths: /var/www/d8/vendor/drush/drush/drush.yml,/var/www/d8/web/drush/drush.yml
 [preflight] Alias paths: /var/www/d8/web/drush/sites,/var/www/d8/drush/sites
 [preflight] Commandfile search paths: /var/www/d8/vendor/drush/drush/src,/var/www/d8/web/drush
 [debug] Starting bootstrap to none [0.04 sec, 8.09 MB]
 [debug] Drush bootstrap phase 0 [0.04 sec, 8.09 MB]
 [debug] Try to validate bootstrap phase 0 [0.04 sec, 8.09 MB]
 [info] Executing: ssh -o PasswordAuthentication=no root@********** 'drush core-status --fields=db-name --format=json --uri=http://d8.********* --root=/var/www/d8/web' [0.04 sec, 8.55 MB]
 [info] Executing: /usr/local/bin/drush core-status --fields=db-name --format=json --uri=http://d8.local --root=/var/www/d8/web [0.56 sec, 8.59 MB]
You will destroy data in su_d8 and replace with data from /su_d8.

 // Do you really want to continue?: yes.                                                                               

 [notice] Starting to dump database on source. [0.75 sec, 8.6 MB]
 [info] Executing: ssh -o PasswordAuthentication=no root@********** 'drush sql-dump --strict=0 --gzip --result-file=auto --format=json --uri=http://d8.********* --root=/var/www/d8/web' [0.75 sec, 8.6 MB]
 [notice] Starting to discover temporary files directory on target. [1.46 sec, 8.6 MB]
 [info] Executing: /usr/local/bin/drush core-status drush-temp --format=string --uri=http://d8.local --root=/var/www/d8/web [1.46 sec, 8.6 MB]
 [notice] Copying dump file from source to target. [1.61 sec, 8.61 MB]
 [info] Executing: /usr/local/bin/drush core-rsync @d8.test:/root/drush-backups/su_d8/20200211120500/su_d8_20200211_120500.sql.gz @d8.local:/tmp/su_d8_20200211_120500.sql.gz --yes --uri=http://d8.local --root=/var/www/d8/web -- --remove-source-files [1.61 sec, 8.68 MB]

 // Copy new and override existing files at /tmp/su_d8_20200211_120500.sql.gz.  
 // The source is                                                               
 // root@**********:/root/drush-backups/su_d8/20200211120500/su_d8_20200211_1
 // 20500.sql.gz?: yes.          

 [notice] Starting to import dump file onto target database. [1.95 sec, 8.67 MB]
 [info] Executing: /usr/local/bin/drush sql-query --strict=0 --file=/tmp/su_d8_20200211_120500.sql.gz --file-delete --uri=http://d8.local --root=/var/www/d8/web [1.95 sec, 8.67 MB]

In Process.php line 235:
                                                                                                                                                                                                                                             
  [Symfony\Component\Process\Exception\ProcessFailedException]                                                                                                                                                                               
  The command "/usr/local/bin/drush sql-query --strict=0 --file=/tmp/su_d8_20200211_120500.sql.gz --file-delete --uri=http://d8.local --root=/var/www/d8/web" failed.                                                                        
                                                                                                                                                                                                                                             
  Exit Code: 1(General error)                                                                                                                                                                                                                
                                                                                                                                                                                                                                             
  Working directory:                                                                                                                                                                                                                         
                                                                                                                                                                                                                                             
  Output:                                                                                                                                                                                                                                    
  ================                                                                                                                                                                                                                           
                                                                                                                                                                                                                                             
                                                                                                                                                                                                                                             
  Error Output:                                                                                                                                                                                                                              
  ================                                                                                                                                                                                                                           
                                                                                                                                                                                                                                             
  In SqlCommands.php line 201:                                                                                                                                                                                                               
                                                                                                                                                                                                                                             
    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] [--n  
  o] [--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]] [--xh-link XH-LIN  
  K] [--druplicon] [--] <command> [<query>]            

Exception trace:
  at /var/www/d8/vendor/symfony/process/Process.php:235
 Symfony\Component\Process\Process->mustRun() at /var/www/d8/vendor/drush/drush/src/Commands/sql/SqlSyncCommands.php:241
 Drush\Commands\sql\SqlSyncCommands->import() at /var/www/d8/vendor/drush/drush/src/Commands/sql/SqlSyncCommands.php:63
 Drush\Commands\sql\SqlSyncCommands->sqlsync() at n/a:n/a
 call_user_func_array() at /var/www/d8/vendor/consolidation/annotated-command/src/CommandProcessor.php:257
 Consolidation\AnnotatedCommand\CommandProcessor->runCommandCallback() at /var/www/d8/vendor/consolidation/annotated-command/src/CommandProcessor.php:212
 Consolidation\AnnotatedCommand\CommandProcessor->validateRunAndAlter() at /var/www/d8/vendor/consolidation/annotated-command/src/CommandProcessor.php:176
 Consolidation\AnnotatedCommand\CommandProcessor->process() at /var/www/d8/vendor/consolidation/annotated-command/src/AnnotatedCommand.php:302
 Consolidation\AnnotatedCommand\AnnotatedCommand->execute() at /var/www/d8/vendor/symfony/console/Command/Command.php:255
 Symfony\Component\Console\Command\Command->run() at /var/www/d8/vendor/symfony/console/Application.php:1000
 Symfony\Component\Console\Application->doRunCommand() at /var/www/d8/vendor/symfony/console/Application.php:255
 Symfony\Component\Console\Application->doRun() at /var/www/d8/vendor/symfony/console/Application.php:148
 Symfony\Component\Console\Application->run() at /var/www/d8/vendor/drush/drush/src/Runtime/Runtime.php:118
 Drush\Runtime\Runtime->doRun() at /var/www/d8/vendor/drush/drush/src/Runtime/Runtime.php:49
 Drush\Runtime\Runtime->run() at /var/www/d8/vendor/drush/drush/drush.php:72
 require() at /var/www/d8/vendor/drush/drush/includes/preflight.inc:18
 drush_main() at phar:///usr/local/bin/drush/bin/drush.php:141
 require() at /usr/local/bin/drush:10

[thetwentyseven]

If I extracted the .gz manually it is working.

drush sql-query --strict=0 --file=/tmp/su_d8_20200211_125713.sql --file-delete --uri=http://d8.local --root=/var/www/d8/web

I assume the problem is the extraction of .sql.gz file

[thetwentyseven]

In FsUtils.php line 93, it does not have application/gzip
$supported = [ 'application/x-bzip2', 'application/x-gzip', 'application/x-tar', 'application/x-zip', 'application/zip', 'application/gzip', ];

Now is working fine

[thetwentyseven]

I am sorry @weitzman but the same is happening for drush 8.3.2 in vendor/drush/drush/includes/drush.inc line 975 'application/gzip' is missing

  $supported = array(
    'application/x-bzip2',
    'application/x-gzip',
    'application/gzip',
    'application/x-tar',
    'application/x-zip',
    'application/zip',

[heby1]

I had the same error "Query failed" using drush sql-sync. Finally I found a solution.

Make sure your database user privileges include LOCK TABLES and CREATE TEMPORARY TABLES.

(In addition to SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP)

[johnpitcairn]

I know that Drush 9.x is now unsupported, but this also affects Drush 9.7.2 - see line 352 in drush.inc.

For one site, I'm in the unlucky client-mandated position of running production on php 7.0.x (thus drush 9.7.x) and dev on php 7.4. Misery.

[gareth-cohaesus]

I'm on drush 9.7.2 as well and am facing the same issue:
blt sync also doesn't work due to this

manually adding 'application/gzip', to the supported array in the drush.inc file in vendor/drush/drush/includes/drush.inc fixes the issue. (line 352)

[kviolka]

I can confirm this bug on drush 9.7.2

[guaneagler]

I have same issue with the drush version 9.7.2 and the PHP version is 7.4, but if the PHP version is 7.3 then it works correctly.
So I think the root cause is the return value of function 'drush_attempt_mime_content_type' changes after PHP upgrade from 7.3 to 7.4. It will return application/x-gzip when 7.3 and application/gzip when 7.4

I'm a little confused about why this issue is closed when the drush 9.7.2(also 9.7.3) does not include the fix code(I mean include the application/gzip)

[weitzman]

Drush 9 is no longer supported. See https://www.drush.org/latest/install/#drupal-compatibility

[normanlolx]

There's https://drupal.stackexchange.com/a/307885/15055 which contains a patch to fix this issue.