SQL Syntax error in Bash Script

Question

I'm working on fixing a database and have a script for correcting several tables. For example, this works fine:

mysql -u $U -p$P -D$D <<< 'ALTER TABLE xyz_tablename DROP columnname ;'

I'm not really a SQL ace so I'm not sure how to fix this one, and I'm running into a syntax error when I try this

mysql -u $U -p$P -D$D <<< 'ALTER TABLE drupal_url_alias CHANGE language language VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '';'

This SQL statement works fine:

ALTER TABLE drupal_url_alias CHANGE language language VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT ''

Adam Zalcman · Accepted Answer · 2012-03-07 09:52:26Z

4

The problem is that your SQL statement contains single quotes and is placed in single quotes. This confuses the shell about where one quoted string ends and a new one starts.

Try this:

mysql -u $U -p$P -D$D <<< "ALTER TABLE drupal_url_alias CHANGE language language VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '';"

I have placed the statement in double quotes. Note that double quotes are not equivalent to single quotes (e.g. variable interpolation takes place in double quotes, but not in single quotes), but in your particular case the only difference is that single quotes after DEFAULT are kept intact.

answered Mar 7, 2012 at 9:52

Adam Zalcman

27.3k4 gold badges75 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

markwk Over a year ago

I figured it wasn't something simple I was missing. I had tried double quotes but in the wrong position. Cheers...

l0b0 · Accepted Answer · 2012-03-07 09:55:11Z

0

You can't have a single quote inside a single quote in Bash.

answered Mar 7, 2012 at 9:55

l0b0

59.6k32 gold badges155 silver badges247 bronze badges

Comments

Andrey Starodubtsev · Accepted Answer · 2012-03-07 10:17:40Z

-2

You must use ":

mysql -u $U -p$P -D$D <<< "ALTER TABLE drupal_url_alias CHANGE language language VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '';"

edited Mar 7, 2012 at 10:17

answered Mar 7, 2012 at 9:52

Andrey Starodubtsev

5,3523 gold badges35 silver badges50 bronze badges

3 Comments

l0b0 Over a year ago

Um, no. There is no way to put single quotes inside a single quoted string in Bash.

Karl Over a year ago

+1 for offering both the generic anwser (escape the character) and the mysql (and some other RDBMS) specific method to address this (mixing quote types). The top option will work in any RDBMS I have ever used as well as being the solution for hte same issue in almost every programming language. The bottom one is easy to use in some situations

Andrey Starodubtsev Over a year ago

@l0b0, shame on me, you're right. Sometimes i forget that perl and shell differ in this aspect

Collectives™ on Stack Overflow

SQL Syntax error in Bash Script

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related