7

I want to run multiple MySQL queries from a shell script.

I'm using the following code:

mysql -u <redacted> -p<redacted> servermail << EOF

CREATE TABLE `virtual_domains` (
`id`  INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `virtual_users` (
`id` INT NOT NULL AUTO_INCREMENT,
`domain_id` INT NOT NULL,
`password` VARCHAR(106) NOT NULL,
`email` VARCHAR(120) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`),
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `virtual_aliases` (
`id` INT NOT NULL AUTO_INCREMENT,
`domain_id` INT NOT NULL,
`source` varchar(100) NOT NULL,
`destination` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

EOF

But I keep getting these error messages:

virtual_domains: command not found
No command 'name' found, did you mean:
 Command 'named' from package 'bind9' (main)
 Command 'namei' from package 'util-linux' (main)
 Command 'lame' from package 'lame' (universe)
 Command 'uname' from package 'coreutils' (main)
 Command 'nama' from package 'nama' (universe)
 Command 'mame' from package 'mame' (multiverse)
 Command 'nam' from package 'nam' (universe)
name: command not found
virtual_users: command not found
domain_id: command not found
password: command not found
No command 'email' found, did you mean:
 Command 'mail' from package 'mailutils' (universe)
 Command 'dmail' from package 'uw-mailutils' (universe)
 Command 'kmail' from package 'kmail' (main)
 Command 'cmail' from package 'xboard' (universe)
 Command 'tmail' from package 'uw-mailutils' (universe)
 Command 'rmail' from package 'rmail' (universe)
 Command 'rmail' from package 'exim4-daemon-heavy' (main)
 Command 'rmail' from package 'courier-mta' (universe)
 Command 'rmail' from package 'masqmail' (universe)
 Command 'rmail' from package 'postfix' (main)
 Command 'rmail' from package 'exim4-daemon-light' (main)
 Command 'wmail' from package 'wmail' (universe)
 Command 'emil' from package 'emil' (universe)
email: command not found
No command 'email' found, did you mean:
 Command 'mail' from package 'mailutils' (universe)
 Command 'dmail' from package 'uw-mailutils' (universe)
 Command 'kmail' from package 'kmail' (main)
 Command 'cmail' from package 'xboard' (universe)
 Command 'tmail' from package 'uw-mailutils' (universe)
 Command 'rmail' from package 'rmail' (universe)
 Command 'rmail' from package 'exim4-daemon-heavy' (main)
 Command 'rmail' from package 'courier-mta' (universe)
 Command 'rmail' from package 'masqmail' (universe)
 Command 'rmail' from package 'postfix' (main)
 Command 'rmail' from package 'exim4-daemon-light' (main)
 Command 'wmail' from package 'wmail' (universe)
 Command 'emil' from package 'emil' (universe)
email: command not found
No command 'email' found, did you mean:
 Command 'mail' from package 'mailutils' (universe)
 Command 'dmail' from package 'uw-mailutils' (universe)
 Command 'kmail' from package 'kmail' (main)
 Command 'cmail' from package 'xboard' (universe)
 Command 'tmail' from package 'uw-mailutils' (universe)
 Command 'rmail' from package 'rmail' (universe)
 Command 'rmail' from package 'exim4-daemon-heavy' (main)
 Command 'rmail' from package 'courier-mta' (universe)
 Command 'rmail' from package 'masqmail' (universe)
 Command 'rmail' from package 'postfix' (main)
 Command 'rmail' from package 'exim4-daemon-light' (main)
 Command 'wmail' from package 'wmail' (universe)
 Command 'emil' from package 'emil' (universe)
email: command not found
virtual_aliases: command not found
domain_id: command not found
-bash: source: filename argument required
source: usage: source filename [arguments]
destination: command not found
ERROR 1064 (42000) at line 2: 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 '(
uid=0(root) gid=0(root) groups=0(root)  INT NOT NULL AUTO_INCREMENT,
 VARCHAR(' at line 1

When I run the commands one by one inside mysql they work.

What should I try next?

2
  • Note: the ` quote in a bash shell is intended to evaluate the string between them. That is why it's complaining about unknown commands, it's trying to execute the contents whenever it finds strings between the reversed single quotes (`)
    – Marcelo
    Sep 15, 2014 at 13:20
  • Thanks. What would you suggest I try?
    – narzero
    Sep 15, 2014 at 13:22

2 Answers 2

5

You are using a "here document" introduced by << EOF. In this construct variable expansion and command substitution is performed unless the end delimiter (EOF in your case) is quoted. In your example the text between backticks like `virtual_domains` is executed as a command by the shell.

To get the expected result you have to quote the end delimiter EOF using single or double quotes like shown below. This will prevent the expansion and substitution of the shell.

mysql -u <redacted> -p<redacted> servermail << "EOF"

CREATE TABLE `virtual_domains` (
`id`  INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

See Here documents - Unix shells, Bash reference - Here documents

0
10

I would put the SQL statements in a file let's say sqldump.txt and then run the import like below. Create empty database the_database before if it doesn't exist.

mysql -u auser -p apassword the_database< sqldump.txt
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .