**Critical Bug** ~ see below

I've done some extensive testing on this option in Window 7 with WAMP 2.2. It appears that the --create-db option is not working as intended.

Run:

$ drush sql-sync @mdj.prod @mdj.dev

will create a dump file on the remote, rsync it to the local alias and import it into site's current database

Running

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

According the the sql-sync help text, is supposed to delete the current database and 'Create a new database before importing the database dump on the target machine.', thus getting a clean identical copy on the local of the remote.

to test this, add a test table to the local database and run regular sql-sync:

Using PhpMyAdmin, I created a table called aasynctest to the database, then ran the regular sql-sync:

$ drush sql-sync @mdj.prod @mdj.dev

Upon completion, a check of the database shows the table remains after the sync, indicating that a merge is occurring.

Now, add the --create-db option flag to the command:

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

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 --data
base=information_schema --host=localhost --user=root; mysql --database=mydrupaljourney --host=localhost --user=root --password= --silent < c:/wamp/www/local.mydrupaljourney.com/assets/sqldumps/mydrupaljourneysync.sql);

Notice that Drush has identified the local database and all its parameters and is prepared to create a new one with those parameters, even though no database information added to the alias file.

Running the command for real (remove -s)

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

You will see on inspection of the local database that the test table is still in the database, indicating that a new database was not created as requested. There were not warnings or errors to indicate this.

**Critical Bug**

Just as a test (and stupidly without making a backup of my MySQL installation - who would have thought), I decided to delete my local Drupal database and run the above command to see if it would actually CREATE a database. It did not. In fact it merged the remote database dump to the default MySQL 'mysql' database, completely messing up my MySQL installation. It took hours to recover from that.

Comments

greg.1.anderson’s picture

It looks like Drush is specifying --database in all of the mysql commands, so I don't see how the import could default to the 'mysql' database. You could run with --debug to confirm that the non-simulated run is, in fact, running exactly the same commands as it is in --simulated mode. It certainly should be.

All of this stuff works pretty well on Mac and Linux, but it is entirely possible that the Windows implementation is still rough around the edges. None of the current Drush maintainers use Windows; contributions are welcome.

JSCSJSCS’s picture

StatusFileSize
new7.52 KB

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.

JSCSJSCS’s picture

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:

Before sql-sync

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

After sql-sync

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).

Database after

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

JSCSJSCS’s picture

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.

greg.1.anderson’s picture

In my (very limited) experience, Windows git bash works the same as bash on Linux, so I am unsure why the parameters would be dropped for you.

I would suggest as a next step that you make a symbolic link to /bin/echo. Call it 'mysql', and put it in some directory that appears in your $PATH before the actual mysql executable. Thus, once this is complete, you can run "mysql foo < c:/bar" and you will see "foo". Run sql-sync as before, and see if it prints out all of the parameters as you would expect. This will show you if bash is somehow munging the script parameters.

JSCSJSCS’s picture

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.

JSCSJSCS’s picture

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.

moshe weitzman’s picture

We have replaced several cases of ; with &&. Please submit more patches along these lines when you find them. Thanks for the research!

JSCSJSCS’s picture

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.

JSCSJSCS’s picture

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...

JSCSJSCS’s picture

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>

JSCSJSCS’s picture

Status:Active» Needs review
StatusFileSize
new844 bytes

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.

moshe weitzman’s picture

Title:drush sql-sync with --create-db does not work and can overwrite MySQL default databases » drush sql-sync can use ; to separate statements and thus is windows incompatible
Assigned:Unassigned» greg.1.anderson
Priority:Critical» Normal
Issue tags:+Windows

In the past, we've had bash quoting issues with -e. Perhaps it is safe here? Assigning to Greg for input.

greg.1.anderson’s picture

Status:Needs review» Needs work

#12 is not good for non-mysql databases, because it assumes that -e is always the "execute" option. Maybe you could change the function prototype of _drush_sql_connect to include a boolean $include_execute_flag that would add -c for psql, -e for mysql, etc.

As for the sql in question & it's use with -e or -c, it does seem a bit dangerous of a pattern, given that it contains "/*" and "!". Usually, it's safe to use ! if you wrap it in single-quotes, but you need to use single quotes with 'dbadmin'@'localhost' IDENTIFIED BY 'password', etc., so this complicates things. I'd almost guess that #12 doesn't work, but since it was posted, it must be at least nominally functional. I have not tested it yet.

JSCSJSCS’s picture

Title:drush sql-sync can use ; to separate statements and thus is windows incompatible» drush sql-sync with --create-db does not work and can overwrite MySQL default databases
Assigned:greg.1.anderson» Unassigned
Priority:Normal» Critical
Status:Needs work» Needs review
Issue tags:-Windows
StatusFileSize
new2.14 KB

Added some more helpful output text.

greg.1.anderson’s picture

Status:Needs review» Needs work

Still does not account for postgres & other database types.

JSCSJSCS’s picture

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.

greg.1.anderson’s picture

I tried #15 on the unit tests with mysql on Linux, and it worked.

It is not necessary to make all databases work with Windows sql-sync in an initial patch; however, it is not permissible to break postgres and other databases on Linux and Mac either.

mysql: -e ("execute")
postgres: -c ("command")
sqlsrv sqlcmd: -q ("query")

Sqlite is a little different. You can enter the cli like this:

sqlite3 mydata.db

Oracle sqlplus does not seem to have a way to pass sql via an argument.

I don't know if it would be more profitable to either (a) try to produce different bash output in sql sync for mysql (continue to use 'echo' on other platforms), or (b) return to #10 and try to make that work. (a) seems non-optimal, as it adds additional complexity; however, thanks to sqlplus, it does not seem to be easy to support all databases with this technique (unless I missed something). There was no patch on #10, so I could not verify what was done. The inline code looked correct, but the following typo appeared twice in the output:

--password= &&mysql

Hard to tell if that was just a transcription error, of if the space was also missing in the code; however, when I tried replacing the ; with &&, it worked for me on Linux.

greg.1.anderson’s picture

Status:Needs work» Needs review
StatusFileSize
new695 bytes

Here's a patch of what I tried.

JSCSJSCS’s picture

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.

JSCSJSCS’s picture

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;" |

greg.1.anderson’s picture

#6 shows that with the ;, everything is passed to the first mysql command (the separator is ignored by Bash and passed to mysql as an argument). Could you try the test in #6 with the patch in #19?

Also, have you tried executing all of the stuff inside of the 'echo' from within your mysql cli prompt, to see if the SQL syntax is correct for you? It should be, but would be good to confirm.

Another thought is, does the drush sql-create command work for you on Windows?

JSCSJSCS’s picture

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.

greg.1.anderson’s picture

Re: #21, the double quotes shown in the code there are for PHP; they do not appear in the output ($sql[]). It therefore does not matter that we wrap the results in double quotes; there are no double quotes inside the double-quoted section of the --simulated output, as you noted.

Try the tests in #22 next.

JSCSJSCS’s picture

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().

JSCSJSCS’s picture

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

greg.1.anderson’s picture

Re: #25, I meant to try executing the DROP DATABASE... ; FLUSH PRIVILEGES; from the mysql> prompt. You can't put a ! inside a double-quoted string in bash. It is a little bit of a mystery to me why it works from the system command on Linux. Anyway, I expect that that command should work from the mysql prompt, but it would be good to test and confirm.

The easiest way to put a ! in a command in Bash is to use single quotes instead of double quotes. It wouldn't be convenient to switch the whole string to single-quotes, as mentioned above. If you want to run the command from #15 in bash, I'd recommend using hybrid quoting, like 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

That's probably hard to read. What I did was, just before the /* !, I exited the double-quoted segment by adding in a ", and then I started a new single-quoted segment by adding a ' immediately after it, with no spaces. At the end of the string, after the */, I did the opposite, ending the single-quoted segment with a ' and then restarting the double-quoted segment with a ".

If that works for you in bash, you could try a similar modification in the php code.

greg.1.anderson’s picture

I also noticed the following comment in sql.drush.inc:

/*
* Build a SQL string for dropping and creating a database.
*
* @param array $db_spec
*   A database specification array.
*
* @param boolean $quoted
*   Quote the database name. Mysql uses backticks to quote which can cause problems
*   in a Windows shell. Set TRUE if the CREATE is not running on the bash command line.
*/
function drush_sql_build_createdb_sql($db_spec, $quoted = FALSE) {

Maybe this ($quoted = FALSE) is only an issue when the database name contains characters that require quoting.

My suggestion in #27 would not be easy to implement in the current php. However, try it out in bash anyway, and we'll work out a way to implement it if it works in the shell.

JSCSJSCS’s picture

#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.

JSCSJSCS’s picture

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

greg.1.anderson’s picture

Sounds like the $quoted code might be wrong; maybe there are other places where it is useful & correct. It probably does not affect us here, so it can be a separate issue, should some problem be discovered with it.

Here is a patch that adjusts the quoting around the !. This works on Linux, but I'm not sure it will help you.

I'm not sure I understood your comment in #29; do you still get the error in syntax when you run #27 from the bash prompt?

Also, does the drush sql-create command work for you?

Do you get the same results when you run 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; from the msql> cli prompt? I would guess that sql syntax would not vary from one platform to another under mysql, so this command should work. However, your comments above imply that it does not. Can you confirm what error message you get when you run these sql statements from the mysql cli prompt? If they give an error, try running each part of the command separately (split on each ;), again from the mysql prompt, and see if that works.

greg.1.anderson’s picture

StatusFileSize
new776 bytes

Oh, and the patch.

JSCSJSCS’s picture

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.

JSCSJSCS’s picture

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.

greg.1.anderson’s picture

Okay, can you re-run your test of #32 with mysql set up as you did in #6, to show the options that are being passed through to mysql?

JSCSJSCS’s picture

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.

JSCSJSCS’s picture

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

greg.1.anderson’s picture

#37 shows that && is correctly separating the two calls to mysql; we previously demonstrated that, as we thought, a ; did not, so the change from ; to && is necessary but insufficient.

#36 shows that it is not necessary to escape the !; those extra quote characters just end up getting passed through to mysql. #32 is therefore no good; our best patch to date is #19. A little more is necessary, though.

The big mystery is #34; if the drop / create works there, from bash, why does it not work when called from php?

It might be helpful to try and figure out if the SQL is being transformed in some way before being passed to mysql. Just as you replaced 'mysql' with 'echo' in #6, try replacing 'mysql' with 'cat' (make a symlink to 'cat' called 'mysql', etc.), and then run both the working bash line from #34, and also run drush sql-sync with the patch from #19 applied. This will produce a bunch of noise when you cat your entire sql dump, but if you can pick out the DROP / CREATE command from the head, that should tell us something.

JSCSJSCS’s picture

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

greg.1.anderson’s picture

Sorry, you're right -- the options make cat choke. Instead, make a shell script called 'mysql'; put the following contents in it, and then make it executable (chmod +x mysql):

#!/bin/bash

echo $*
cat <&0

This will show both the commandline options and stdin.

greg.1.anderson’s picture

#39 part 2 looks like it's still calling the "real" mysql.

JSCSJSCS’s picture

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 ~
$

greg.1.anderson’s picture

Hm. You could rename your actual 'mysql' binary to 'mysql-real'; then there should be no question that your mysql script will be called.

JSCSJSCS’s picture

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.

greg.1.anderson’s picture

Maybe something in your system is caching the fact that mysql == mysql.exe? The symlink to echo.exe worked, but the script of the same name did not; I'm not sure what to think of that. PHP can exec the 'drush' script, which is why I suspect it's just a cache problem, and not an innate limitation. Maybe rename your script to 'myecho', and change Drush to call myecho instead of mysql?

JSCSJSCS’s picture

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.

greg.1.anderson’s picture

Great work so far. Unfortunately, the quotes are necessary in Linux; without them, the first ; terminates the 'echo'. :(

Is it only system that is messed up on Windows? Maybe we can get more consistent behavior if we use drush_shell_exec, as in this patch. This uses exec instead of system. If that does not work, you could also try drush_shell_exec_interactive, which uses proc_open.

Patch in the next comment.

greg.1.anderson’s picture

StatusFileSize
new1.48 KB

Here is the patch that uses exec instead of system

JSCSJSCS’s picture

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

greg.1.anderson’s picture

Status:Needs review» Needs work

Hm. I guess we need #46, redone with a drush_is_windows() conditional to either apply or not apply the quotes in the echo as needed. A little awkward, perhaps, but awkward sure beats nonfunctional. Be sure to put in an appropriate explanatory comment as well.

JSCSJSCS’s picture

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

greg.1.anderson’s picture

The quoting must match the type of system where the code will run. This is what makes this a little awkward for Drush sql-sync; if the remote system is Windows, and the user forgets to set 'os' in their site alias, then the quoting will be wrong, and the command won't work. If you know that the code will run locally, you can use drush_is_windows(); doing this will insure that the right thing will happen for local imports, even if the user did not set 'os' in their site alias. Otherwise, you should use drush_is_windows($target_os). You can use isset($target_db_url['remote-host']) to determine if the code will run remotely.

Awkward, but necessary.

greg.1.anderson’s picture

It would also be nice if you could look for appropriate places in the README and/or example aliases file, and add documentation underscoring the importance of setting 'os' for remote aliases, esp. vis-a-vis sql-sync targets. The good news is that usually folks sql-sync from live to dev, with dev being local, so at least the common case is going to be more stable.

JSCSJSCS’s picture

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.

JSCSJSCS’s picture

I will work on #53

JSCSJSCS’s picture

* - '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.

greg.1.anderson’s picture

Status:Needs work» Needs review
StatusFileSize
new1.59 KB

I looked at the drush_os() function again, and it already has appropriate logic for local vs remote systems. Here's a trivial modification to your patch to account for that. As for the 'os' comment you quote above, that is only trying to say if the site alias is for a local site, then it is not necessary to set 'os' at all. The wording could be better.

JSCSJSCS’s picture

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

greg.1.anderson’s picture

It would be great if you could do that.

JSCSJSCS’s picture

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

greg.1.anderson’s picture

Status:Needs review» Fixed

That worked; committed cc24df1 to 8.x-6.x and 7.x-5.x.

You did all of the hard work here; we couldn't have gotten this working without all of your research and testing. Thanks for your help.

JSCSJSCS’s picture

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!

Status:Fixed» Closed (fixed)

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