MariaDB --init-command when invoking mysql from a BASH script

Question

I am a new user on Stack Overflow, so I apologize in advance for any potential breaches of site etiquette.

I am attempting to create a BASH script that will generate a command to invoke the MariaDB monitor, i.e. mysql. I want this mysql command to include the --init-command option. I want the --init-command option to set a list of user variables to a their values, as specified in a configuration file.

The script builds a string that appears to be correct for my purpose but, when it invokes mysql, an error is generated. If I print out the generated string from the script, it appears to be exactly what I was attempting to create.

I have boiled it down to the following code example:

#!/bin/sh
declare foo="name"
declare bar="value"
declare invoke="mysql -p -D information_schema"
declare opts=" --init-command='SET @$foo:=\"$bar\"'"
invoke+=$opts
echo $invoke
$invoke

When I execute this script, the result looks like:

$ example.sh
mysql -p -D information_schema --init-command='SET @name:="value"'
Enter password:
ERROR 1044 (42000): Access denied for user 'Bill'@'%' to database '@name:="value"''

This error message doesn't even seem to make sense.

However, if I copy the generated command, and paste it back into the command prompt, it requests my password, and proceeds as I would expect, as follows:

$ mysql -p -D information_schema --init-command='SET @name:="value"'
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 171
Server version: 10.3.11-MariaDB Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [information_schema]> SELECT @name;
+-------+
| @name |
+-------+
| value |
+-------+
1 row in set (0.000 sec)

MariaDB [information_schema]>

Demonstrating that the SET command in the --init-command option was successfully passed to the MariaDB monitor, and executed.

I do not know whether this is a Linux issue, a BASH issue, or a MariaDB issue. So, while I have spent a good amount of time trying to find the answer, I really don't know where the problem originates, and therefore, where to focus my research.

Please note: I only used the information_schema database in my example because I expect that anyone attempting to recreate this problem would have that database available to them.

Thank you in advance for your assistance.

See https://stackoverflow.com/questions/8055694/how-to-execute-a-mysql-command-from-a-shell-script for how to call mysql from within a script. You cannot interact with mysql once in the script (hence no password is given). — Nic3500, Oct 04 '19 at 03:23
Thank you, squareskittles. I have benefitted from reading many S.O. threads. So, I tried to be thorough, clear, and follow the examples I have read in the past. — base2boss, Oct 06 '19 at 19:56

score 1 · Answer 1 · answered Oct 04 '19 at 11:27

Some options:

Option 1:

#!/bin/sh

# USING BASH
# FILE: bash_mariadb.sh

foo="\`name\`"
bar="value"

mysql -p -D information_schema --init-command="SET @$foo:='$bar';"

Option 2:

#!/bin/sh

# USING BASH
# FILE: bash_mariadb.sh

foo="\`name\`"
bar="value"

opts=(--init-command="SET @$foo:='$bar';")
invoke=(mysql -p -D information_schema "$opts")
"${invoke[@]}"

Option 3:

#!/bin/sh

# USING BASH
# FILE: bash_mariadb.sh

foo="\`name\`"
bar="value"

#define
invoke() {
  opts=(--init-command="SET @$foo:='$bar'")

  if [[ -v opts ]]; then
    invoke=(mysql -p -D information_schema "$opts")
  else
    invoke=(mysql -p -D information_schema)
  fi

  "${invoke[@]}"
}

#call
invoke

Option 4: DANGER, option not recommended for safety reasons.

#!/bin/sh

# USING BASH
# FILE: bash_mariadb.sh

foo="\`name\`"
bar="value"

invoke="mysql -p -D information_schema"
opts=" --init-command='SET @$foo:=\"$bar\"'"
invoke+=$opts
eval $invoke

In all cases:

$ ./bash_mariadb.sh
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 10.3.11-MariaDB Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [information_schema]> SELECT @`name`;
+---------+
| @`name` |
+---------+
| value   |
+---------+
1 row in set (0.000 sec)

Thank you everyone, for both the encouragement and the answers. — base2boss, Oct 06 '19 at 19:58
Your answer was enlightening, and incredibly thorough. At this point, I have gotten my scrip to work, using the 'dangerous' eval method. I shall continue to work on it, and try to eliminate the eval. In any case, you got me back on track and I sincerely thank you. — base2boss, Oct 06 '19 at 20:00

score 0 · Answer 2 · answered Oct 04 '19 at 10:40

Welcome to SO.

From a script you have to indicate the password you want to use.

The option -p force it for a interactive introduction of the password, wich don't works from a script.

If you instead use -ppassword (notice that you still write the "p", only that you write it next to the password, without spaces), your connection will work.

So, you just have to modify the line:

declare invoke="mysql -ppassword -D information_schema"

(Don't forget to write your password where I wrote "password", of course :) )

MariaDB --init-command when invoking mysql from a BASH script

2 Answers2