I get this error when I do 'drush si' on a fresh D7 install.

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 '-7' at line 1
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 '-7' at line 1

I'm on Drush HEAD.

CommentFileSizeAuthor
#12 890672-12.drush_.si-escape-database-name.patch1.56 KBjoachim
#8 890672.drush_.si-escape-database-name.patch1.54 KBjoachim
#6 890672.drush_.si-query-file.patch1.93 KBjoachim

Comments

moshe weitzman’s picture

moshe weitzman commented
Status: Active » Postponed (maintainer needs more info)

I can't reproduce this. WHat params are you using?

I tried the new testing install profile and I get 


WD php: Notice: Undefined index: access content in user_role_grant_permissions() (line 2881 of    [notice]
/Users/mw/htd/fr/modules/user/user.module). [7.98 sec, 21.08 MB]
WD php: PDOException: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'module'    [error]
cannot be null: INSERT INTO {role_permission} (module, rid, permission) VALUES
(:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2); Array
(
    [:db_insert_placeholder_0] => 
    [:db_insert_placeholder_1] => 1
    [:db_insert_placeholder_2] => access content
)
 in user_role_grant_permissions() (line 2883 of /Users/mw/htd/fr/modules/user/user.module).
[8.02 sec, 21.07 MB]
PDOException: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'module' cannot be null: INSERT INTO {role_permission} (module, rid, permission) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2); Array
(
    [:db_insert_placeholder_0] => 
    [:db_insert_placeholder_1] => 1
    [:db_insert_placeholder_2] => access content
)
joachim’s picture

joachim commented

I'm doing just 'drush si'.

Granted, 'drush' on my system is a bash alias for 'php ~/Sites/_sandbox/drush-HEAD/drush.php' but that shouldn't change anything surely.

joachim’s picture

joachim commented

The problem is this line:

mysql  -hlocalhost -udrupal -phello information_schema -e  "DROP DATABASE IF EXISTS drupal-7"

and the problem is the hyphen.

The same line with a database called 'hello' works fine.

joachim’s picture

joachim commented
Status: Postponed (maintainer needs more info) » Active

The people in #mysql suggested to wrap the db name in ' or ` -- neither of which work for me on the command line.

Ultimately, was told to use mysqladmin rather than mysql.

moshe weitzman’s picture

moshe weitzman commented

mysqladmin would add a new requirement. folks would have to specify the path to that command if not on $PATH. could someone try to write a patch where we save our queries to a file and then redirect that into mysql command. thats much more common in drush than the -e param. something like:

mysql < tmpfile.sql

See _drush_sql_query() for an example.

joachim’s picture

joachim commented
StatusFileSize
new 890672.drush_.si-query-file.patch1.93 KB

Not sure that'll do it.

Really crappy patch to show what I was trying -- still get the same error message using a file.

joachim’s picture

joachim commented
Title: drush si gives SQL error » drush si gives SQL error if database name has a hyphen (-) in it

Better title.

joachim’s picture

joachim commented
Component: Code » PM (dl, en, up ...)
Status: Active » Needs review
StatusFileSize
new 890672.drush_.si-escape-database-name.patch1.54 KB

Asked on StackOverflow and got this answer:

$ mysql -hlocalhost -uuser -ppass -e "CREATE DATABASE \`foo-bar\`"

I've tested this on the commandline and it works perfectly! :D

Simple patch (on 3.x) which fixes the problem attached.

greg.1.anderson’s picture

greg.1.anderson commented

Undocumented backticks? Do regular single-quotes work?

joachim’s picture

joachim commented

Nope.

Undocumented in that when I googled and looked on MySQL I couldn't find anything about escaping on the commandline.

jonhattan’s picture

jonhattan
Primary language Spanish
Location Plasencia
commented
Component: PM (dl, en, up ...) » Core Commands
Status: Needs review » Reviewed & tested by the community

Backtick is the character to quote identifiers in mysql. It doesn't matter if your using -e in the command line or are inside mysql cli or using .sql files.

Not an undocumented thing though http://dev.mysql.com/doc/refman/5.0/en/identifiers.html

This needs extra quotation because backticks have a meaning in bash (or *sh?):
$ mysql -hlocalhost -uuser -ppass -e "CREATE DATABASE \`foo-bar\`"

but this is fine for bash:
$ mysql -hlocalhost -uuser -ppass -e 'CREATE DATABASE `foo-bar`'

joachim’s picture

joachim commented
StatusFileSize
new 890672-12.drush_.si-escape-database-name.patch1.56 KB

In light of that, here's a reroll with a more appropriate comment.

jonhattan’s picture

jonhattan
Primary language Spanish
Location Plasencia
commented
Status: Reviewed & tested by the community » Fixed

Finally did commit with that comment:

  // Quote database name with backticks, and add escaping for the shell.

...to fit 80 chars length for doxygen.

joachim’s picture

joachim commented
Version: » All-versions-3.x-dev
Status: Fixed » Patch (to be ported)

Great!

The patch should apply on 3.x too.

moshe weitzman’s picture

moshe weitzman commented
Status: Patch (to be ported) » Fixed

I'd rather not screw up someone's script by adding some escaping on 3.x

joachim’s picture

joachim commented

How would escaping just the DB name screw up a script?

moshe weitzman’s picture

moshe weitzman commented

Someone could be escaping earlier in their script.

Status: Fixed » Closed (fixed)

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