Shell script: Launch Mysql script using shell variable

Question

I have a problem launching MySQL script from shell. I assign a value to my variable ${x}, using filename. So I have to launch a MySQL script using this variable. I would like to launch script without insert all MySQL code in shell (is too long) but using:

mysql -h localhost -uuser -ppsw DB < script.sql

My tentatives are:

mysql -h localhost -uuser -ppsw DB -e "set @x=${x}; source script.sql"

mysql -h localhost -uuser -ppsw DB -e "set @x=${x};"
mysql -h localhost -uuser -ppsw DB< script.sql

But not work for me. Could you help me?

Answer 1

I was surprised that your first solution didn’t work:

mysql -h localhost -uuser -ppsw DB -e "set @x=${x}; source script.sql"

Analysis

• source is a MySQL command
• set @x=${x}; is an SQL statement.

I thought that there may be an issue combining the two types as one statement as the MySQL --execute=statement, -e statement is supposed to execute the statement and quit.

The and quit part is why the redirected stdin is ignored when I tried my first idea:

mysql -h localhost -uuser -ppsw DB -e "set @x=${x};" < script.sql

Solution

After further experiments, I figured out that simply appending a semi-colon to the source command will prevent the syntax error. I can’t say why this works as a semi-colon isn’t usually required to terminate the last SQL statement of a list but there you have it:

mysql -h localhost -uuser -ppsw DB -e "set @x=${x}; source script.sql;"

As Glenn Jackman pointed out, if the shell variable is a non-numeric string, the shell variable will have to be wrapped in single quotes so that when the MySQL variable is being assigned, MySQL will treat the right hand side (the shell variable) as a string literal instead of as an identifier for a column name:

mysql -h localhost -uuser -ppsw DB -e "set @x='$x'; source script.sql;"

This version will also work safely with numeric strings as can be seen in the examples below. I’ve also removed the curly braces around the shell variable since they’re not necessary.

Examples

Contents of t.sql:

select now();
select @variable as 'Contents of variable';

Use a numeric string as the shell variable:

$ number=3
$ mysql -e "set @variable=$number; source t.sql;"
+---------------------+
| now()               |
+---------------------+
| 2015-10-02 13:06:45 |
+---------------------+
+----------------------+
| Contents of variable |
+----------------------+
| 3                    |
+----------------------+

Use a non-numeric string as the shell variable generates errors:

$ text=text
$ mysql -e "set @variable=$text; source t.sql;"
ERROR 1054 (42S22) at line 1: Unknown column 'text' in 'field list'

$ text="This is a string"
$ mysql -e "set @variable=$text; source t.sql;"
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a string' at line 1

Now wrap the shell variable in single quotes:

$ mysql -e "set @variable='$text'; source t.sql;"
+---------------------+
| now()               |
+---------------------+
| 2015-10-02 13:08:04 |
+---------------------+
+----------------------+
| Contents of variable |
+----------------------+
| This is a string     |
+----------------------+

$ text=text
$ mysql -e "set @variable='$text'; source t.sql;"
+---------------------+
| now()               |
+---------------------+
| 2015-10-02 13:10:53 |
+---------------------+
+----------------------+
| Contents of variable |
+----------------------+
| text                 |
+----------------------+

$ mysql -e "set @variable='$number'; source t.sql;"
+---------------------+
| now()               |
+---------------------+
| 2015-10-02 13:11:42 |
+---------------------+
+----------------------+
| Contents of variable |
+----------------------+
| 3                    |
+----------------------+

Using a non-existing shell variable will set the MySQL variable to an empty string:

$ mysql -e "set @variable='$nonexistent'; source t.sql;"
+---------------------+
| now()               |
+---------------------+
| 2015-10-02 13:06:14 |
+---------------------+
+----------------------+
| Contents of variable |
+----------------------+
|                      |
+----------------------+