NarūnasK NarūnasK - 3 months ago 16
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

mysqldump
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
bash
variables:

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
echo
- it all seems normal, but if I try to run it in
bash
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
--where
option... How?

Answer

$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'"
    SRC_SRV="-u;root;-pPass;-h;host"
    SRC_SQL_OPT="--where=\"$WHERE\";--opt;--single-transaction;--skip-triggers;--no-create-db;--no-create-info;$DB;$TBL"
    
    IFS=';' mysqldump $SRC_SRV $SRC_SQL_OPT
    
Comments