This is a follow-on to my earlier question on this site. I hope posting it as a new question is appropriate. I'm trying to clone an existing drupal installation into a place on my mac with the commands:

echo "Pushing private files"
/usr/local/bin/drush core-rsync @hpc.dev:%private @hpc.local:%private
echo "Pushing webroot files"
/usr/local/bin/drush -y core-rsync @hpc.dev @hpc.local
echo "Pushing database"
/usr/local/bin/drush -y sql-sync --no-cache @hpc.dev @hpc.local
echo "Clearing Drupal cache"
/usr/local/bin/drush -y @hpc.local cc all

The sql-sync bit is failing with the following error:

Copying dump file from Source to Destination.                                      [ok]
Starting to import dump file onto Destination database.                            [ok]
/usr/bin/php /usr/local/drush-master/drush.php --php=/usr/bin/php  --backend=2        [notice]
--verbose --yes --strict=0 --root=/Users/cook47/Sites/drupal-test/html
--uri='http://localhost/~cook47/drupal-test'  sql-query  
--file=/tmp/hpc-drupal-dev_20160302_175129.sql.gz --file-delete 2>&1
Executing: gzip -d /tmp/hpc-drupal-dev_20160302_175129.sql.gz
Executing: mysql --defaults-extra-file=/private/tmp/drush_wKQDuq --database=hpc-drupal-dev --host=localhost --silent  < /tmp/hpc-drupal-dev_20160302_175129.sql
Query failed.                                                                      [error]
Command dispatch complete                                                             [notice]
Command dispatch complete                                                             [notice]

The sql file does exist. I think the problems is that --defaults-extra-file part, because when I run that last command by hand, I get this:

cook47@rcmac (Downloads ):  mysql --defaults-extra-file=/private/tmp/drush_wKQDuq --database=hpc-drupal-dev --host=localhost --silent  < /tmp/hpc-drupal-dev_20160302_175129.sql
mysql: [ERROR] Could not open required defaults file: /private/tmp/drush_wKQDuq
mysql: [ERROR] Fatal error in defaults handling. Program aborted!

I have no idea where that --defaults-extra-file=/private/tmp/drush_wKQDuq comes from. It's nowhere earlier in the output. The directory /private/tmp/ exists and I can write to it. Here is the 'local' array alias in my hpc.aliases.drushrc.php file:

$aliases['local'] = array (
  'root' => '/Users/cook47/Sites/drupal-test/html', //CHANGEME
  'uri' => 'http://localhost/~cook47/drupal-test', //CHANGEME
  //'remote-host' => "localhost",
  //'remote-user' => 'cook47',
  'path-aliases' => array (
    '%drush' => '/usr/local/bin/drush', //CHANGEME
    '%site' => 'sites/default/',
    '%files' => 'sites/default/files',
    '%private' => '../private',
    '%dump' => '/Users/cook47/Sites/drupal-test/drush-dump.sql',//CHANGEME
  'target-command-specific' => array(
    'sql-sync' => array(//CHANGEME
      'enable' => array(),
      'disable' => array(
  'command-specific' => array(
    'rsync' => array(
      'mode' => 'vrlzOk',
      'exclude-conf' => 1,

Anyone able to resolve this or point me to how to resolve it?


The reason you get a 'file not found' error when you run the import by hand is that Drush automatically deletes its temporary files when the command completes. So, the error you are seeing there is different than the 'Query failed' error you are getting from Drush. Also, note that the --defaults-extra-file contains the credentials for your mysql connection, whereas the file shown at the end of the line is the actual sql dump.

If you'd like Drush to keep the sql file around, you can specify your own location for it; in this instance, Drush will not delete the file when it exits. Use the option --target-dump=/path/to/mydump.sql. It is unclear to me from your pasted output above whether or not your %dump option is being respected (since the path used is different than the one shown in your site alias). If %dump is not working, you could try moving this to your target-command-specific options, although if you do this, the key should be simply dump, not %dump. If your sql dump file is not being deleted, then disregard this advice.

If you want to try to re-import your local dump file, you could do this:

$(drush @hpc.local sql-connect) < /tmp/hpc-drupal-dev_20160302_175129.sql

If you do this, you should get the same 'Query failed' message. $(drush @hpc.local sql-connect -d) might give you more information.

The cause of the 'Query failed' message could be a number of things, but it's most likely a mismatch in the mysql version on your host vs. your local system, or (less likely) an encoding mismatch (e.g. if your dump is in utf-8, and that encoding is not available on your local system).

| improve this answer | |
  • I'm not sure I follow all your points. You give a way for me to keep the sql file around, but it already is. The file not being found is not the SQL file, it's a config file that mysql uses, which is due to the --defaults-extra-file argument being passed to it. A second point of confusion for me is the syntax you are using : $(drush @hpc.local sql-connect) which in the bash shell perhaps means something different than what you might be intending. At any rate, whcn I try it, I get this message: ERROR 1045 (28000): Access denied for user 'hpc-drupal-dev'@'localhost' (using password: YES) – Rich Mar 2 '16 at 23:58
  • The version of mysql on the remote host is "mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1" and on my Mac it's "mysql Ver 14.14 Distrib 5.7.11, for osx10.9 (x86_64) using EditLine wrapper" – Rich Mar 3 '16 at 0:03
  • Seems like utf-8 will be available on my mac. – Rich Mar 3 '16 at 0:03
  • The sql file appears OK. I did this: mysql -u hpc-drupal-dev -p </tmp/hpc-drupal-dev_20160302_175129.sql hpc-drupal-dev and the command completed successfully. So there is something off about the credentials being used by drush. – Rich Mar 3 '16 at 0:08
  • ERROR 1045 (28000): Access denied for user 'hpc-drupal-dev'@'localhost' (using password: YES) - There is your problem. Your local user does not have permissions to access the local sql database. Run drush sql-conf to see the configuration you are using, then adjust it appropriately, either by changing the conf to use a privileged user, or by adjusting your mysql permissions to allow the user you are using access. – greg_1_anderson Mar 3 '16 at 0:08

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.


| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.