NarūnasK NarūnasK - 1 year ago 67
MySQL Question

Odd bash behaviour with nested variables

I like to put my connection and general dump settings into variables and then feed them to

like so:

# echo mysqldump ${SRC_SRV} ${SRC_SQL_OPT}
mysqldump -u root -pPass -h host --where="COL > '2016-08-14' AND COL <= '2016-08-15'" --opt --single-transaction --skip-triggers --no-create-db --no-create-info dbase table

There are my

WHERE="COL > '2016-08-14' AND COL <= '2016-08-15'"
SRC_SQL_OPT="--where=\"${WHERE}\" --opt --single-transaction --skip-triggers --no-create-db --no-create-info ${DB} ${TBL}"

Note, that when I
- it all seems normal, but if I try to run it in
I get an error:

mysqldump: Got error: 1049: Unknown database '>' when selecting the database

And here's the reason why (
bash -x

+ mysqldump -u root -pPass -h host '--where="COL' '>' ''\''2016-08-14'\''' AND COL '<=' ''\''2016-08-15'\''"' --opt --single-transaction --skip-triggers --no-create-db --no-create-info dbase table

Note: now there is a bunch of extra single quotes around the
option... How?

Answer Source

$SRC_SQL_OPT contains the following string:

--where="COL > '2016-08-14' AND COL <= '2016-08-15'" --opt --single-transaction --skip-triggers --no-create-db --no-create-info dbase table

When expanding that string in mysqldump ${SRC_SRV} ${SRC_SQL_OPT}, Bash splits the variables into separate arguments on its internal field separator.

Since, by default, the field separator is whitespace, $SRC_SQL_OPT gets split on every occurrence of whitespace—irrespective of whether that space occurred within a quote or not. One can prevent Bash from splitting variables by quoting them, but since you actually want that variable split elsewhere, you'd either need to:

  • separate out the where part (as @Pianosaurus has said), eg:

    WHERE="COL > '2016-08-14' AND COL <= '2016-08-15'"
    SRC_SQL_OPT="--opt --single-transaction --skip-triggers --no-create-db --no-create-info ${DB} ${TBL}"
    mysqldump $SRC_SRV --where="$WHERE" $SRC_SQL_OPT
  • use a different internal field separator, eg:

    WHERE="COL > '2016-08-14' AND COL <= '2016-08-15'"
    IFS=';' mysqldump $SRC_SRV $SRC_SQL_OPT
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download