This is related to #994702: Improve error message regarding mismatched tmp directories when sql-syncing between systems with different fs layout but appears to be a separate issue -- or rather a separate instance of the same bug.

$ drush sql-sync @foo.dev @foo.test

You will destroy data from foobar.baz.com/xxxxxx and replace with data from [local db name].

You might want to make a backup first, using the sql-dump command.

Do you really want to continue? (y/n): y
Could not open required defaults file: /private/tmp/drush_VzYq3r
Fatal error in defaults handling. Program aborted
ERROR 1045 (28000): Access denied for user 'xxxxxx'@'localhost' (using password: NO)

Apparently this commit introduced this bug as a regression -- sql-sync works fine for me when pushing drush back to the commit prior to that one.

CommentFileSizeAuthor
#9 sql-sync-mac.txt2.77 KBAnonymous (not verified)
#7 sql-dump-fail.txt4.34 KBchiebert
#4 sql-sync-fix.patch978 bytesanarcat

Comments

circuscowboy’s picture

circuscowboy commented

Same problem - linux to linux - back to 5.1 and all working fine.

I have an alias set for both aliases

'path-aliases' => array(
'%dump-dir' => '/var/drush/drush-dumps',
),

greg.1.anderson’s picture

greg.1.anderson commented
Assigned: Unassigned » anarcat

Looks like the credential-leak fix needs to be fixed some more for remote commands.

moshe weitzman’s picture

moshe weitzman commented
Status: Active » Fixed

I just reverted #671906: mysql credentials leak in drush sqlc.

anarcat’s picture

anarcat commented
Status: Fixed » Needs work
StatusFileSize
new sql-sync-fix.patch978 bytes

The correct patch is attached, sorry, my bad.

rickmanelius’s picture

rickmanelius commented

#4 didn't fix this for me (running drush 5.3)

I get
Could not open required defaults file: /private/tmp/drush_q3ZPZJ
Fatal error in defaults handling. Program aborted
Database dump failed

anarcat’s picture

anarcat commented

That is strange. I do not understand (1) why the tmpfile is in /private and (2) why the tmpfile is used in the first place... i thought I covered all the _connect() calls...

Can you provide a full log with --debug?

Thanks for the testing!

chiebert’s picture

chiebert commented
StatusFileSize
new sql-dump-fail.txt4.34 KB

I just ran into this after installing 5.3 using the pear method for the first time. I uninstalled 5.3 and installed 5.1 as #1 and it all works again. Attached is the output from sql-dump @live @dev --no-cache --debug (5.3, not patched with #4).

Notes:

  1. dev machine (the one with 5.3) is running under MAMP, and I notice that pear installed drush into the MAMP/bin folder. might be irrelevant
  2. host/live Linux machine is running drush 4.4
  3. My aliases on both machines for @mysite-live include:
    'path-aliases' => array(
    '%dump' => '/home/accountname/sqldumps/main/mysite-live-sql_dump.sql',
  ),
  4. My alias on the local machine for @mysite-dev includes:
      'path-aliases' => array(
    '%dump' => '/Applications/MAMP/htdocs/sqldumps/mysite/mysite-dev-sql_dump.sql',
  ),
fuzzy76’s picture

fuzzy76 commented

The patch in #4 doesn't make any difference for me on 5.3 either (had to apply it by hand). This is pretty crucial. :(

Anonymous’s picture

Anonymous (not verified) commented
StatusFileSize
new sql-sync-mac.txt2.77 KB

I have this problem too. I don't understand it, but I can attach a log and mention some things I don't see in the thread already.

The problem is in the command quoted:

Calling system(ssh -o PasswordAuthentication=no aegir@s.importantconsultants.net 'if [ ! -s /tmp/importantconsult.sql ] || [ $((`date "+%s"`-`stat --format="%Y" /tmp/importantconsult.sql`)) -gt 3600 ] ; then mysqldump  --defaults-file=/private/tmp/drush_obhGjB  importantconsult --host=localhost --port=3306 --user=importantconsult --result-file /tmp/importantconsult.sql-1338795686 --no-autocommit --single-transaction --opt -Q --skip-extended-insert --order-by-primary --ignore-table=importantconsult.cache --ignore-table=importantconsult.history --ignore-table=importantconsult.search_dataset --ignore-table=importantconsult.search_index --ignore-table=importantconsult.search_node_links --ignore-table=importantconsult.search_total --ignore-table=importantconsult.sessions --ignore-table=importantconsult.watchdog && mysqldump --no-data  --no-autocommit --single-transaction --opt -Q --skip-extended-insert --order-by-primary cache history search_dataset search_index search_node_links search_total sessions watchdog >> /tmp/importantconsult.sql-1338795686&& mv -f /tmp/importantconsult.sql-1338795686 /tmp/importantconsult.sql; fi 2>&1');

The important part I see is:

ssh .. 'if ... ; then mysqldump --defaults-file=/private/tmp/drush_obhGjB ....

I put a breakpoint at the top of _drush_sql_get_credentials(). The file with the password /private/tmp/drush_obhGjB is being created on my local environment. On Max OS X, /tmp is a symlink to /private/tmp. Presumably the defaults file has to be moved to the remote host or created there because the mysqldump command is being run there.

chiebert’s picture

chiebert commented
Status: Needs work » Reviewed & tested by the community

I notice that 5.4 is now available on the project page as well as the PEAR channel. I just tested it (same setup as in my original post in #7) and I was able to sql-sync both directions without a problem. So, whatever was committed between 5.3 and 5.4 seems to have done the trick in my situation (and the release notes reference the sql-* command bugs).

greg.1.anderson’s picture

greg.1.anderson commented
Status: Reviewed & tested by the community » Fixed

Please re-open if there is more to do that has not already been committed.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.