1

I have a script with a hard-coded bash array of mysql connection variables. The script right now is basically like this:

declare -a dbarr=($devdb1 $devdb2)

for db in "${dbarr[@]}"; do
    mysql $db -NBe "show variables like 'key_buffer_size';"
done

This works without any problems.

However, I would like to instead move these into a file instead of hard-coding them (there are several hundred), like:

dev|$devdb1
dev|$devdb2
tst|$tstdb1
..

each variable in the above list resolves to a parameter like:

--defaults-extra-file=/home/mysql/env/devserver.devdb1
--defaults-extra-file=/home/mysql/env/devserver.devdb2
--defaults-extra-file=/home/mysql/env/testserver.tstdb1

You can then simply connect to mysql like:

mysql $devdb1

if I read these in (via readarray or a while loop), the only way I can get this to work is to use eval

envfile="/home/mysql/env/envfile"

readarray -t envarr < <(gawk -F'|' '/^dev/{print $2}' "${_envfile}")

for db in "${envarr[@]}"; do
    eval "connstr=$db"
    mysql $connstr -NBe "show variables like 'key_buffer_size';"
done

Without the eval, it fails with:

+ mysql '$devdb1' -NBe 'show variables like '\''key_buffer_size'\'';'
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
+ mysql '$devdb2' -NBe 'show variables like '\''key_buffer_size'\'';'
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

The only way I have found to have the $devdb1, etc. "resolved" is with eval - which I understand the dangers of - but I have not found any other way.

I tried using ${!db} instead of the eval above, but it returns nothing.

dlivings
  • 73
  • 1
  • 8
  • The single quotes are only part of the output generate by `set -x`; they are not literally in the value. The problem would appear to be that you expected `$db` to expand to `$devdb1`, then expand *that* to an actual host name. You original array expanded `$devdb1` et al. when you declared the array, not when you expand `$db`. – chepner Feb 27 '20 at 18:02
  • so instead something like: ``` for db in $(gawk -F'|' '/^dev/{print $2}' "${_envfile}"); do eval "db2=$db" mysql $db2 -NBe 'select @@hostname;' done ```? – dlivings Feb 27 '20 at 18:05
  • Why don't you just store the hostnames in the file? There's way more indirection here than you need (and `eval` should be avoided whenever possible) – chepner Feb 27 '20 at 18:06
  • a defaults-extra-file contains 5-6 variables for connecting to a mysql server. I suppose I could just have a file containing the ultimate file names. I am fleshing this out and the above was something I was banging my head on – dlivings Feb 27 '20 at 18:10
  • Found this, which gets to where I am looking at: http://mywiki.wooledge.org/BashFAQ/006 – dlivings Feb 27 '20 at 19:06
  • This seems very strange; it sounds like you want a data-file to contain e.g. `dev|$devdb1`, where `$devdb1` refers to a variable defined in the Bash script that *uses* the text-file. Is that correct? If so, then -- that whole approach seems wrong. Data-files shouldn't refer to variables in script files, that's completely the wrong direction of reference. – ruakh Feb 27 '20 at 22:27
  • Right now we have hundreds of variables named like "" - each one pointing to a connection file (sometimes the same file, depending on the same). so "devdb1", connects to the database "db1" in the "dev" environment and so on. this works 100% fine on the command line. my intention was to try and find a way to use what is already there and put all of these into a single file and then be able to use them in a script a bit more easily. However, yes, this entire investigation apparently is the wrong approach. – dlivings Feb 27 '20 at 22:37
  • @dlivings: Re: "Right now we have hundreds of variables [...]": Where and how are those variables being set? Can those definitions be moved to a data-file that your script can read as data? – ruakh Feb 28 '20 at 07:35
  • They are in 4 different files that are sourced, depending on the environment being connected to. This is the direction I am probably going: creating a file with the commands in them and create another script to update *that* file periodically, since the files that are sourced gets updated fairly continuously (via ansible), so will probably just add another step to the playbook, perhaps ... – dlivings Feb 28 '20 at 14:21

1 Answers1

0

You could use Bash "indirect expansion" here. See What is indirect expansion? What does ${!var*} mean?. Instead of storing $varname in the file just store varname (e.g. dev|devdb1). When processing a line of the file put the variable name in a variable, dbv say, and then use indirect expansion to get the value of the referenced variable. E.g.

mysql "${!dbv}" -NBe "show variables like 'key_buffer_size';"

Note that your example code is missing quotes in some places where it would be safer to have them, if only to avoid having mysterious error messages if you get unexpected values in variables. Use Shellcheck to check your code for missing quotes, and many other problems.

pjh
  • 6,388
  • 2
  • 16
  • 17
  • I tried that, but I cannot get the ${!db} to print *anything* The only way I have found to have this work is to use "eval" – dlivings Feb 27 '20 at 21:06
  • My best guess is that the 'db' variable contains something that is not the name of a (set) variable. If you try to print `$db` instead of `${!db}` what do you get? If it is the name of a variable (e.g. `devdb1`) what do you get if you try to print that variable's value (e.g. `$devdb1`)? Finally, it would help if you could put more complete code in the question. In particular, the code that sets `db` is missing from the `readarray` code example (it iterates with `key` but uses `$db`). [Shellcheck](https://www.shellcheck.net/) will report the use of variables that haven't been set. – pjh Feb 27 '20 at 21:18
  • I updated the question to more accurately reflect the problem I am having - sorry ... – dlivings Feb 27 '20 at 21:27
  • I think the problem is the `$` preceding the variable name in the file (e.g. `$devdb1`). When `$db` expands to `$devdb1` then the `eval` executes `connstr=$devdb1`, which is fine. However, `${!db}` tries to get the value of a variable called `$devdb1`, which is not valid. To get `${!db}` to work you could either remove the `$` from the file or strip it from the value of `db` before you use it (e.g. db=${db#\$}). – pjh Feb 27 '20 at 21:51
  • Oddly enough, this works completely fine with the file as I have it ("dev|$devdb1"): gawk -F'|' '/^dev/{print $2}' "$(pwd)/envfile" | xargs -I {} -i bash -c 'mysql {} -NBe "select @@hostname;"' not sure why xargs is able to do the same thing as "eval" in this situation... – dlivings Feb 27 '20 at 22:39
  • It't not `xargs`, it's `bash -c`. See [Variable as command; eval vs bash -c](https://unix.stackexchange.com/q/124590/264812). – pjh Feb 28 '20 at 16:59
  • Thanks - I later figured that out. I have redone this by creating a file of all the "actual" values behind the variables in the file that is sourced (what I was trying to use through indrection), and this now works like a charm. I have a cron job to keep this file updated every 5 minutes (takes about .2 seconds to generate) – dlivings Feb 29 '20 at 17:17