sql-sync (and other operations) won't work after sql-drop

[RalfHendel]

When starting a "drush sql-sync @Local @Remote" I first want to remove all tables at remote-db to get a clean cloned database avoiding a merge of both databases on remote target stage.

After making a "drush @Remote sql-drop" a "drush sql-sync @Local @Remote" won't work as drush fires sql-error-messages and aborts operation.

In the drush training-session @ DC-Prague I talked to Moshe and Greg about this. They confirmed this behaviour and Moshe found out, that some variables (site-path?) are losing their content while running script (in the bootstrap-process?) and sql-sync cannot find remote settings.php to get access-data for db after this.

This behavior appears only by syncing database to a remote target. When syncing a remote db against a local db it is no problem starting with "drush @Local sql-drop" before syncing.

I found one similar Issue "sql-connect does not work if used after sql-drop" at Github , which may be caused by same reason.

There is also an issue "add a --delete-all-tables-first option for sql-sync ?" at d.o., which deals with an additional parameter to gain the option to drop tables on target-db.

[kenorb]

I've similar issue.

I'm trying to sync the database between two remotes as below:

drush -vy sql-sync @remote.dev @remote.test

But it's showing the following error (destination database exists and it's empty):

Creating database test. Any possible existing database will be dropped!
Do you really want to continue? (y/n): y
Calling system(mysql --database=information_schema --host=localhost --user=test --password='pass'  < /tmp/drush_vAJkkz);
ERROR 1227 (42000) at line 1: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation

I've seen separate option to create a new database:

--create-db  Create a new database before importing the database dump on the target machine. 

But it seems it's happening by default (when this option is not specified), even if the user doesn't have access to drop the existing database.

This is what it's executed on the remote:

$ inotifywait -m --format "%f" /tmp | grep --line-buffered ^drush | xargs -L1 -I% sudo cat /tmp/% 2> /dev/null
DROP DATABASE IF EXISTS `test`; CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */; GRANT ALL PRIVILEGES ON `test`.* TO 'test'@'localhost' IDENTIFIED BY 'pass'; FLUSH PRIVILEGES;DROP DATABASE IF EXISTS `test`; CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */; GRANT ALL PRIVILEGES ON `test`.* TO 'test'@'localhost' IDENTIFIED BY 'pass'; FLUSH PRIVILEGES;SHOW TABLES

Which points me to createdb_sql() in lib/Drush/Sql/Sqlmysql.php.

Is this is a correct behavior?
Shouldn't drush execute the same logic as for sql-drop (drop all tables in a given database), instead the whole database (which user could not have the permission?), if option --create-db is not specified?

[weitzman]

Please confirm the version of Drush on both sides. Two remote sites for sql-sync is only supported by master branch.

Please show the alias definitions and drushrc for both sides. I'm curious if create-db option is specified somewhere. If not, I'd like to know how we that code is being called. Consider adding a backtrace so that this can be determined.

Thanks for your help. This is relatively new code so is good to shake out any bugs.

[kenorb]

Local drush: 7.0-dev (where command is executed)
Remote drush: 6.2.0 (for both remotes which share the same machine).
Full output including alias definitions.

I've tried to print backtrace in createdb_sql(), but it's not get called. Maybe it's different function, or the code is executed on remote drush?

[weitzman]

I would be happy if this feature worked with Drush7 on all sides. I think we need to prove that, and then work on latest 6.x on both sides. You have an older version of 6.x and there is no way thats going to work.

[kenorb]

I've upgraded drush on remote to 7.0-dev, 'Access denied' disappeared and it went further. It log-in to the remote 3 times and on the 3rd one in stopped on rsync:

You will destroy data from @remote.test:/tmp/foo_20141030_003323.sql.gz and replace with data from @remote.dev:/home/drush/drush-backups/foo/20141030003323/foo_20141030_003323.sql.gz
The source and destination cannot both be remote.
rsync error: syntax or usage error (code 1) at main.c(1166) [Receiver=3.0.9]

Other notice:

Array to string conversion environment.inc:643 # on local, twice, just the notice

So it seems it's not possible to sql-sync the database between two remotes which are on the same remote. I'll double check this again tomorrow.

[weitzman]

If you have the aliases on the @Remote server, you could try drush @remote sql-sync @source @destination. Then it would look like a purely local sql-sync once the original dispatch to @Remote happenned.

[kenorb]

My remote doesn't have defined information about the aliases. However drush @remote sql-sync @source @destination fails for another reason:

Unknown options: --cache, --ordered-dump.

Probably I'm using these in my aliases somewhere.

Also I'm a bit confused, because as far as I remember in drush 6.x you could sql-sync the database between two remotes.
As for workaround, I'm using drush @remote.dev sql-dump | drush @remote.test sql-cli, but what's the purpose for sql-sync then if you can't do that.

I think rsync is happening on Destination (shouldn't be on local?) and it's doing something like:
core-rsync @remote.dev:~drush-backups/foo/x/foo_x.sql.gz @remote.test:/tmp/foo_x.sql.gz where it can't recognise that @remote.test is actually @self, so rsync complains that The source and destination cannot both be remote. This is how it should work?

I'm still confused, so I've documented what's happening in here: How to sql-sync database between two remotes which share the same host?, so maybe it would be more clear what's going on.
I'm not sure if it's specific only when two remotes share the same host, or it applies for different hosts as well.

[weitzman]

Those options used to be valid for sql-sync but are not valid anymore for Drush7.

In Drush6, either source or destination must be local.

drush @remote.dev sql-dump | drush @remote.test sql-cli - thats totally fine if you have enough memory to pipe a full DB dump. If you don't, then you sql-sync becomes more useful.

rsync can happen on source or on destination. We try to do it on the machine that originates the sql-sync regardless of whether it is source or destination. If both are remote, we arbitrarily do rsync on destination.

The source and destination cannot both be remote. This suggests that Drush6 is in use.