drush sql-sync with --create-db does not work and can overwrite MySQL default databases

I have attached the debug dump of the command WITH a database that does not actually delete and create a new database, as the test table is still there after the command is run.

I spent two days getting MySQL working again after I tried to run that command WITHOUT a database and I am not anxious to do it again because I am not sure of a way to recover when it happens again (git maybe?)

I remember watching my hard drive light flashing and saying "wow, it is working", but then I did not get a new mydrupaljourney database. So what was it writing to? It took some time to find out it merged into the 'mysql' database.

"contributions welcome"? How do I look for things in code to see if they won't work in Windows? I'm not a PHP expert, but I will try, with a bit of direction. I'm not alone in wanting to use Windows for development. 25,000 people have read my post on getting Drush rsync to work on Windows and they can't all be bots.

This is very interesting. I added a table to both the local and remote databases before doing the sql-sync. I made sure my default 'mysql' database was per the original install and ran:

$ drush -d sql-sync @mdj.prod @mdj.dev --create-db

This is what the 'mysql' data base loooked like before the command:

This is what it looked like after (***note the presence of the temp remote table at the top)

There was no change to the local mydrupaljourney database. All changes when to mysql

Now if I restore the 'mysql' database and run the command again without the '--create-db' option then changes from the remote are merged with the local (note both temp tables at top).

Of Course I want to be able to have identical databases, thus the reason for needing --create-db to work.

Help me if you can. When I review the output with the --create-db, I see sql-sync is trying to do three things:
1.) ssh to the remote and run mysqldump

Calling system(ssh -o PasswordAuthentication=no user@server.dreamhost.com "mysqldump --result-file(= this is missing but still works)/home/user/sqldumps/sqldump.sql-1364678392 --no-autocommit --single-transaction --opt -Q user_mydrupaljourney --host=mysql.jscs.dreamhosters.com --user=user_dbadmin --password=pass --skip-extended-insert --order-by-primary && mv -f /home/user/sqldumps/sqldump.sql-1364678392 /home/user/sqldumps/sqldump.sql 2>&1");

2.) rsync the remote file to the target site

Calling system(rsync -e 'ssh ' -rultvz --exclude=".git" --exclude=".gitignore" --exclude=".hg" --exclude=".hgignore" --exclude=".hgrags" --exclude=".bzr" --exclude=".bzrignore" --exclude=".bzrtags" --exclude=".svn" user@server.dreamhost.com:/home/user/sqldumps/sqldump.sql /cygdrive/c/wamp/www/local.mydrupaljourney.com/assets/sqldumps/mdjsqlsyncdump.sql);

3.) Create a new database and import the remote's file into it:

Calling system(echo "DROP DATABASE IF EXISTS mydrupaljourney; CREATE DATABASE mydrupaljourney /*!40100 DEFAULT CHARACTER SET utf8 */; GRANT ALL PRIVILEGES ON mydrupaljourney.* TO 'root'@'localhost' IDENTIFIED BY ''; FLUSH PRIVILEGES;" | mysql --database=information_schema --host=localhost --user=root --password=; mysql --database=mydrupaljourney --host=localhost --user=root --password= --silent < c:/wamp/www/local.mydrupaljourney.com/assets/sqldumps/mdjsqlsyncdump.sql);

In part three, these commands seem to work when run individually with mysql cli, but have drastic unintended effects when run through Git Bash in Windows using drush sql-sync. It is as if the last part:

mysql --database=mydrupaljourney --host=localhost --user=root --password= --silent < c:/wamp/www/local.mydrupaljourney.com/assets/sqldumps/mdjsqlsyncdump.sql);

Is being executed as:

mysql < c:/wamp/www/local.mydrupaljourney.com/assets/sqldumps/mdjsqlsyncdump.sql;

Because the MySQL default 'mysql' database is being merged with the rsync'd dump file when the '-a-create-db' option is used.

Here is the output from your suggestion.

--database=information_schema --host=localhost --user=root --password=; mysql --database=mydrupaljourney --host=localhost --user=root --password= --silent

No sure this is very helpful.

In the sequence below:

Calling system(echo "DROP DATABASE IF EXISTS mydrupaljourney; CREATE DATABASE mydrupaljourney /*!40100 DEFAULT CHARACTER SET utf8 */; GRANT ALL PRIVILEGES ON mydrupaljourney.* TO 'root'@'localhost' IDENTIFIED BY ''; FLUSH PRIVILEGES;" | mysql --database=information_schema --host=localhost --user=root --password=; mysql --database=mydrupaljourney --host=localhost --user=root --password= --silent < c:/wamp/www/local.mydrupaljourney.com/assets/sqldumps/mdjsqlsyncdump.sql);

I've tried this locally and I believe I may have found the issue. I think Windows may have a problem with multiple commands separated by ";" after a pipe (|).

I can run the two parts separately, but together as one line, I am having the issues described previously. If I separate the two sections with '&&' instead of ';', it appears to work correctly.

I want to change the ';' (highlighted in bold) to ' && " in the code for further testing, but I cannot find how it gets in there.

I can't find it. I've been looking for a couple of hours. Will gladly submit patch, but need to know where that semi-colon is generated.

I found it finally - line 397 of sync.sql.inc
$pre_import_commands = sprintf('echo "%s" | %s<strong>;</strong> ', $pre_import_sql, $db_su_connect);

If I replace ';' with '&&' as:
$pre_import_commands = sprintf('echo "%s" | %s && ', $pre_import_sql, $db_su_connect);

I get this error:

Calling system(echo "DROP DATABASE IF EXISTS mydrupaljourney;  CREATE DATABASE mydrupaljourney /*!40100 DEFAULT CHARACTER SET utf8 */; GRANT ALL PRIVILEGES ON mydrupaljourney.* TO 'root'@'localhost' IDENTIFIED BY ''; FLUSH PRIVILEGES;" | mysql --database=information_schema --host=localhost --user=root --password= &&mysql --database=mydrupaljourney --host=localhost --user=root --password= --silent < c:/wamp/www/local.mydrupaljourney.com/assets/sqldumps/mdjsqlsyncdump.sql);
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"DROP DATABASE IF EXISTS mydrupaljourney;  CREATE DATABASE mydrupaljourney /*!40' at line 1

If I replace ';' with '; &&' as:
$pre_import_commands = sprintf('echo "%s" | %s; && ', $pre_import_sql, $db_su_connect);

I get this error:

Calling system(echo "DROP DATABASE IF EXISTS mydrupaljourney;  CREATE DATABASE mydrupaljourney /*!40100 DEFAULT CHARACTER SET utf8 */; GRANT ALL PRIVILEGES ON mydrupaljourney.* TO 'root'@'localhost' IDENTIFIED BY ''; FLUSH PRIVILEGES;" | mysql --database=information_schema --host=localhost --user=root --password=; &&mysql --database=mydrupaljourney --host=localhost --user= root --password= --silent < c:/wamp/www/local.mydrupaljourney.com/assets/sqldumps/mdjsqlsyncdump.sql);
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

But I can log into mysql an connect with that database without a password for root in the command line.

I am sure I am heading in the right direction...

I figured you would ask if I could try connecting with a user with a password. I created a 'dbadmin' user with password 'password' and put those settings into the alias file too. Same result:

Calling system(echo "DROP DATABASE IF EXISTS mydrupaljourney;  CREATE DATABASE mydrupaljourney /*!40100 DEFAULT CHARACTER SET utf8 */; GRANT ALL PRIVILEGES ON mydrupaljourney.* TO 'dbadmin'@'localhost' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;" | mysql --database=information_schema --host=localhost --user=dbadmin --password=password; && mysql --database=mydrupaljourney --host=localhost --user=dbadmin --password=password --silent < c:/wamp/www/local.mydrupaljourney.com/assets/sqldumps/mdjsqlsyncdump.sql);
ERROR 1045 (28000): Access denied for user 'dbadmin'@'localhost' (using password: YES)
Command dispatch complete [23.37 sec, 8.91 MB]                                                                       [notice]

Peak memory usage was 9.93 MB [23.37 sec, 8.91 MB]                                                                   [memory]


JSCS@PRODUCTION /c/programdata/drush (8.x-6.x)
$ mysql -u dbadmin -ppassword mydrupaljourney
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.5.24-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

I gave up trying to find out why Windows Git Bash was behaving badly. I believe it has something to do with the pipe. I am using 'mysql -e' to replace the more sophisticated original command structure with this patch. It solved my issues in Windows and I am hoping it will not affect *nix users, as it is a standard MySQL option.

Added some more helpful output text.

I am sorry that I am not learned enough to get drush sql-sync work in Windows for all databases. Perhaps someone who knows PHP a lot better and all the other SQL databases will come across this threads and figure that out.

When I run #19 in Git Bash, I get this error (same as #10)

Calling system(echo "DROP DATABASE IF EXISTS mydrupaljourney; CREATE DATABASE mydrupaljourney /*!40100 DEFAULT CHARACTER SET utf8 */; GRANT ALL PRIVILEGES ON mydrupaljourney.* TO 'root'@'localhost' IDENTIFIED BY ''; FLUSH PRIVILEGES;" | mysql --database=information_schema --host=localhost --user=root --password= && mysql --database=mydrupaljourney --host=localhost --user=root --password= --silent < c:/wamp/www/local.mydrupaljourney.com/assets/sqldumps/mdjsqlsyncdump.sql);
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"DROP DATABASE IF EXISTS mydrupaljourney; CREATE DATABASE mydrupaljourney /*!40' at line 1

At first I thought it was having a problem with the !, but i removed that comment section from the code and got the same error really that just ended farther down the code with GRA. In the absence of the ';', the bash thinks there is a problem with the sql syntax and with it and the &&, the bash thinks there is a problem with mysql database permissions.

Looking at this output more closely, part of the problem may be with sql.drush.inc (about line 1210)

$sql[] = sprintf("IDENTIFIED BY '%s';", $db_spec['password']);

We are using double Quotes to echo to the pipe, but part of what is echoed also has double quotes (above)

See output above has:

IDENTIFIED BY ''; FLUSH PRIVILEGES;" |

Sorry, I was on the worng branch for #20/#21

Output from 8.x-6.x branch with #19

Calling system(echo "DROP DATABASE IF EXISTS mydrupaljourney; CREATE DATABASE mydrupaljourney /*!40100 DEFAULT CHARACTER SET utf8 */; GRANT ALL PRIVILEGES ON mydrupaljourney.* TO 'root'@'localhost' IDENTIFIED BY ''; FLUSH PRIVILEGES;" | mysql --database=information_schema --host=localhost --user=root --password= && mysql --database=mydrupaljourney --host=localhost --user=root --password= --silent < c:/wamp/www/local.mydrupaljourney.com/assets/sqldumps/mdjsqlsyncdump.sql);
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"DROP DATABASE IF EXISTS mydrupaljourney; CREATE DATABASE mydrupaljourney /*!40' at line 1

The '' after INDENTIFIED BY is really two single quotes, so the output is correct. I do still wonder if the issue is not with echo double quoteing a double quoted string variable from sql.drush.inc as mentioned above.

When I run the section in question directly in the bash:

$ echo "DROP DATABASE IF EXISTS mydrupaljourney; CREATE DATABASE mydrupaljourney /*!40100 DEFAULT CHARACTER SET utf8 */; GRANT ALL PRIVILEGES ON mydrupaljourney.* TO 'root'@'localhost' IDENTIFIED BY ''; FLUSH PRIVILEGES;" | mysql --database=information_schema --host=localhost --user=root --password= && mysql --database=mydrupaljourney --host=localhost --user=root --password= --silent < c:/wamp/www/local.mydrupaljourney.com/assets/sqldumps/mdjsqlsyncdump.sql

I get the error

sh.exe": !40100: event not found

Git Bash thinks ! is some kind of history variable

If I remove the section /*! ...*/

Then #19 works correctly. (except for the mysql version check, which is deleted)

It appears that #19 allows the echo to be passed correctly to the pipe, but now with that done, git Bash is balking at the comments in the quoted echo command...at least when run directly in the bash command line. It may behave differently when called through system().

Confirming that removeing the /*! comments form code and running drush sql-sync does not fix the issue of sql syntax error with #19 and no comment:

Calling system(echo "DROP DATABASE IF EXISTS mydrupaljourney; CREATE DATABASE mydrupaljourney; GRANT ALL PRIVILEGES ON mydrupaljourney.* TO 'root'@'localhost' IDENTIFIED BY ''; FLUSH PRIVILEGES;" | mysql --database=information_schema --host=localhost --user=root --password= && mysql --database=mydrupaljourney --host=localhost --user=root --password= --silent < c:/wamp/www/local.mydrupaljourney.com/assets/sqldumps/mdjsqlsyncdump.sql);

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"DROP DATABASE IF EXISTS mydrupaljourney; CREATE DATABASE mydrupaljourney; GRAN' at

#27 method works in the shell. It effectively lets the comments pass. But like I mentioned previously, even if I remove the comments from code, I still get the sql syntax error when i run drush sql-sync without the comment, so I'm not sure fixing the way the comment passes though is the issue.

Per#28, I'm not sure the $quoted part of the function was ever going to really work because the way this line is written:

$sql[] = sprintf('GRANT ALL PRIVILEGES ON %s.* TO \'%s\'@\'%s\'', $dbname, $db_spec['username'], $db_spec['host']);

When quoted is TRUE, the database input becomes:

'mydrupaljourney`.*

And I don't think that will work in mysql

Drush @mdj.dev sql-create works as it should from the command line. No problems, or errors. Creates a database if there isn't one, deletes a current database and creates a new one if there was.

With #32, here is the applicable part of the debug output:

Calling system(echo "DROP DATABASE IF EXISTS mydrupaljourney; CREATE DATABASE mydrupaljourney /*"'!'"40100 DEFAULT CHARACTER SET utf8 */; GRANT ALL PRIVILEGES ON mydrupaljourney.* TO 'root'@'localhost' IDENTIFIED BY ''; FLUSH PRIVILEGES;" | mysql --database=information_schema --host=localhost --user=root --password= && mysql --database=mydrupaljourney --host=localhost --user=root --password= --silent < c:/wamp/www/local.mydrupaljourney.com/assets/sqldumps/mdjsqlsyncdump.sql);

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"DROP DATABASE IF EXISTS mydrupaljourney; CREATE DATABASE mydrupaljourney /*"'!' at line 1

As far as my coment in #29, I was trying to say that the code works when run in the command line with your comment fix but that the command line was working by just deleting the comment in the code, so I did not think the problem was the comment syntax structure. In fact, with #32, this also works when running from the command line:

$ echo "DROP DATABASE IF EXISTS mydrupaljourney; CREATE DATABASE mydrupaljourney /*"'!'"40100 DEFAULT CHARACTER SET utf8 */; GRANT ALL PRIVILEGES ON mydrupaljourney.* TO 'root'@'localhost' IDENTIFIED BY ''; FLUSH PRIVILEGES;" | mysql --database=information_schema --host=localhost --user=root --password= && mysql --database=mydrupaljourney --host=localhost --user=root --password= --silent < c:/wamp/www/local.mydrupaljourney.com/assets/sqldumps/mdjsqlsyncdump.sql

All I did was take applicable output from 'drupal sql-sync @mdj.prod @mdj.dev --create-db and remove the front 'Calling system();' wrapper.

It works fine in the command line and errors out as described when run with drush sql-sqync.

Drush @mdj.dev sql-create works as it should from the command line. No problems, or errors. Creates a database if there isn't one, deletes a current database and creates a new one if there was.

With #32, here is the applicable part of the debug output:

Calling system(echo "DROP DATABASE IF EXISTS mydrupaljourney; CREATE DATABASE mydrupaljourney /*"'!'"40100 DEFAULT CHARACTER SET utf8 */; GRANT ALL PRIVILEGES ON mydrupaljourney.* TO 'root'@'localhost' IDENTIFIED BY ''; FLUSH PRIVILEGES;" | mysql --database=information_schema --host=localhost --user=root --password= && mysql --database=mydrupaljourney --host=localhost --user=root --password= --silent < c:/wamp/www/local.mydrupaljourney.com/assets/sqldumps/mdjsqlsyncdump.sql);

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"DROP DATABASE IF EXISTS mydrupaljourney; CREATE DATABASE mydrupaljourney /*"'!' at line 1

As far as my coment in #29, I was trying to say that the code works when run in the command line with your comment fix but that the command line was working by just deleting the comment in the code, so I did not think the problem was the comment syntax structure. In fact, with #32, this also works when running from the command line:

$ echo "DROP DATABASE IF EXISTS mydrupaljourney; CREATE DATABASE mydrupaljourney /*"'!'"40100 DEFAULT CHARACTER SET utf8 */; GRANT ALL PRIVILEGES ON mydrupaljourney.* TO 'root'@'localhost' IDENTIFIED BY ''; FLUSH PRIVILEGES;" | mysql --database=information_schema --host=localhost --user=root --password= && mysql --database=mydrupaljourney --host=localhost --user=root --password= --silent < c:/wamp/www/local.mydrupaljourney.com/assets/sqldumps/mdjsqlsyncdump.sql

All I did was take applicable output from 'drupal sql-sync @mdj.prod @mdj.dev --create-db and remove the front 'Calling system();' wrapper.

It works fine in the command line and errors out as described when run with drush sql-sqync.

I set it up again with #32 patch and it ran getting the same errors we have been getting of late.

JSCS@PRODUCTION /c/wamp/www
$ mysql 'hello world!'
hello world!

JSCS@PRODUCTION /c/wamp/www
$ mysql "hello world!" //shows choke on ! with double-quotes
sh.exe": !": event not found

JSCS@PRODUCTION /c/wamp/www
$ drush sql-sync @mdj.prod @mdj.dev --create-db

You will destroy data in mydrupaljourney and replace with data from user.dreamhost.com/user_mydrupaljourney.

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

Do you really want to continue? (y/n): y
receiving incremental file list
sqldump.sql

sent 31871 bytes received 14210 bytes 13166.00 bytes/sec
total size is 27932331 speedup is 606.16
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"DROP DATABASE IF EXISTS mydrupaljourney; CREATE DATABASE mydrupaljourney /*"'!' at line 1

JSCS@PRODUCTION /c/wamp/www
$ ls | echo

JSCS@PRODUCTION /c/wamp/www
$

With the symlink, we are trying to pipe output from and echo to another echo to check the code output, but I think echo takes STDIN, not STDOUT, so we get nothing back as illustrated in my last command input.

Running #32 as set up in #6, but just in the command line gives this:

$ echo "DROP DATABASE IF EXISTS mydrupaljourney; CREATE DATABASE mydrupaljourney /*"'!'"40100 DEFAULT CHARACTER SET utf8 */; GRANT ALL PRIVILEGES ON mydrupaljourney.* TO 'root'@'localhost' IDENTIFIED BY ''; FLUSH PRIVILEGES;" | mysql --database=information_schema --host=localhost --user=root --password= && mysql --database=mydrupaljourney --host=localhost --user=root --password= --silent < c:/wamp/www/local.mydrupaljourney.com/assets/sqldumps/mdjsqlsyncdump.sql

--database=information_schema --host=localhost --user=root --password=
--database=mydrupaljourney --host=localhost --user=root --password= --silent

I think cat is looking for a file name for input:

Command Line:

c:\Users\JSCS\bin\mysql: unrecognized option `--database=information_schema'

Drush sql-sync:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"DROP DATABASE IF EXISTS mydrupaljourney; CREATE DATABASE mydrupaljourney /*"'!' at line 1

Not what we hoped for either; Not getting any output and like you said, looks like it is finding the real mysql when the php is run vs the bash.

JSCS@PRODUCTION ~
$ which mysql
/c/Users/JSCS/bin/mysql

JSCS@PRODUCTION ~
$ cat /c/Users/JSCS/bin/mysql
#!/bin/bash

echo $*
cat <&0
JSCS@PRODUCTION ~
$ drush sql-sync @mdj.prod @mdj.dev --create-db

You will destroy data in mydrupaljourney and replace with data from server.dreamhost.com/user_mydrupaljourney.

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

Do you really want to continue? (y/n): y
receiving incremental file list
sqldump.sql

sent 33701 bytes received 131540 bytes 47211.71 bytes/sec
total size is 26975872 speedup is 163.25
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"DROP DATABASE IF EXISTS mydrupaljourney; CREATE DATABASE mydrupaljourney /*"'!' at line 1

JSCS@PRODUCTION ~
$

I'm not getting any love from this end, sorry.

'mysql' is not recognized as an internal or external command, operable program or batch file.

Tried creating a symlink in the mysql dir from and new mysql.exe to the batch file mysql:

The system cannot find the file c:\wamp\bin\mysql\mysql5.5.24\bin\mysql.exe.

Even though that file is clearly where it says it cannot find it.

This post is more of a running chronology for future readers, including myself.

I REALLY appreciate your assistance Greg. I can't tell you how frustrating it is to have an issue in the queue that only sees a response every couple of weeks. People are very busy and can't always get to the issue queue as much as they would like. I just want to say thanks!

I believed the sql syntax error is a byproduct of something else going on. Clearly from the --simulate output, the command sequence is getting through as you would like it.

What do we know?
1.) There is no problem in Linux.
2.) The command sequence works from the Git Bash command line (with the ! fix).
3.) It does not work when sent through the php system() function (with or without the ! fix).
4.) Php system() function works for other commands okay (ssh, rsync, etc.) used by drush sql-sync in Windows.
5.) The command line, rewritten per #19 works.
6.) We would both like for Drush sql-sync to work in Windows.

I would suggest that given what we have learned so far, the problem is probably in the way the php system() function works in Windows. I would expect the function does something different depending on what OS it is running on. Last night's research indicates that on Windows, PHP calls 'cmd /C'.

So what happens when we run the command sequence from a command prompt?

C:\Users\JSCS>echo "DROP DATABASE IF EXISTS mydrupaljourney;  CREATE DATABASE mydrupaljourney /*"'!'"40100 DEFAULT CHARACTERSET utf8 */; GRANT ALL PRIVILEGES ON mydrupaljourney.* TO 'root'@'localhost' IDENTIFIED BY ''; FLUSH PRIVILEGES;" | mysql --database=information_schema --host=localhost --user=root --password= && mysql --database=mydrupaljourney --host=localhost --user=root --password= --silent < c:/wamp/www/local.mydrupaljourney.com/assets/sqldumps/mdjsqlsyncdump.sql

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"DROP DATABASE IF EXISTS mydrupaljourney;  CREATE DATABASE mydrupaljourney /*"'!' at line 1

Notice that this is the EXACT SAME ERROR we get when we run drush sql-sync in Git Bash. Why the command string by itself runs in Git BASH is a mystery but that is not the problem at hand. The problem is why won't it run in Windows CMD line?

I compared drushes sql-dump code to sql-sync and noticed that is did not use "--database=" and even had a comment about it. I searched at http://dev.mysql.com/doc/refman/5.5/en/connecting.html and can find no mention of the '--database='. In fact, it says the "first nonoption argument is taken as the name of the default database."

I was excited to run the command in CMD without "--database":

C:\Users\JSCS>echo "DROP DATABASE IF EXISTS mydrupaljourney;  CREATE DATABASE mydrupaljourney /*"'!'"40100 DEFAULT CHARACTERSET utf8 */; GRANT ALL PRIVILEGES ON mydrupaljourney.* TO 'root'@'localhost' IDENTIFIED BY ''; FLUSH PRIVILEGES;" | mysql information_schema --host=localhost --user=root --password= && mysql mydrupaljourney --host=localhost --user=root --password= --silent < c:/wamp/www/local.mydrupaljourney.com/assets/sqldumps/mdjsqlsyncdump.sql

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"DROP DATABASE IF EXISTS mydrupaljourney;  CREATE DATABASE mydrupaljourney /*"'!' at line 1

CRAP!

Then I started researching "cmd /C" and read that it can be pretty quirky when it comes to double-quotes.

In testing, I found I could not echo "something" | sometarget. But if I removed the double-quotes, it worked. So I tried taking out the quotes in the drush sql-sync echo part:

C:\Users\JSCS>echo DROP DATABASE IF EXISTS mydrupaljourney;  CREATE DATABASE mydrupaljourney /*"'!'"40100 DEFAULT CHARACTER SET utf8 */; GRANT ALL PRIVILEGES ON mydrupaljourney.* TO 'root'@'localhost' IDENTIFIED BY ''; FLUSH PRIVILEGES; | mysql information_schema --host=localhost --user=root --password= && mysql mydrupaljourney --host=localhost --user=root --password= --silent < c:/wamp/www/local.mydrupaljourney.com/assets/sqldumps/mdjsqlsyncdump.sql

SUCCESS in CMD!!

Conclusions:
1.) We can drop the ! fix, it works without it.
2.) We keep the replace ; with && fix, or else the default mysql database gets merges with the imported remote database
3.) We remove the outer quotes from the echo part of the command
4.) There is some undocumented support in MySQL for "--database" because it works, even though is not referenced in the documentation, but this is a separate issue.

I have tested this patch with and without the database being present and in Git Bash and CMD prompt. Hopefully it will work for you in *Unix

This patch does not deal with the matter of the "--database=", but I have tested from the command line and it works with or without the "--database" in the command sequence.

Please test.

I get the old sql syntax error with #48 patch and also with the suggested alternative function.

Does it matter if the target is linux or Windows or just WHERE the command is run locally?

I'm sorry Greg, but that is all past my experience level. Here its a starting patch. If you could fix it so it will work per #52, I would appreciate it and will test it.

I will work on #53

* - 'os': The operating system of the remote server. Valid values
* are 'Windows' and 'Linux'. Default value is PHP_OS if 'remote-host'
* is not set, and 'Linux' (or $options['remote-os']) if it is.

The bold type should be something like "that this alias point to", not always a remote server, right? Just for my clarification.

Okay, great. I guess you want me to roll the comment changes into this patch so it will all be on one commit?

Here you are. I also took out a space that has been bothering me all week.

Thanks for all your assistance! There are over 60 comments in this issue and with some other modules, this conversation could have taken place over a year.

I have given kudo's to you in my post:

http://www.mydrupaljourney.com/articles/2013/03/26/using-drush-sql-sync-...

Believe it or not, I am very close to almost full functionality of Drush in Windows. I have a couple of issues with %dump and %dump-dir in the alias files that may only be a matter of interpretation, but once overcome, I will be satisfied... for a time.

Thanks again!