Create a SQL script within a bash script

Question

I'm trying to bootstrap a mysql using a rudimentary bash script

cat <<EoF > /tmp/mysql-load-table.sql
CREATE DATABASE  IF NOT EXISTS `web_customer_tracker`;
USE `web_customer_tracker`;

DROP TABLE IF EXISTS `customer`;

CREATE TABLE `customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) DEFAULT NULL,
  `last_name` varchar(45) DEFAULT NULL,
  `email` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

LOCK TABLES `customer` WRITE;

INSERT INTO `customer` VALUES 
    (1,'JeanLuc','Picard','picard@tng.com'),
    (2,'William','Riker','riker@tng.com'),
    (3,'Beverly','Crusher','crusher@tng.com'),
    (4,'Geordi','LaForge','laforge@tng.com'),
    (5,'Data','Android','data@tng.com');

UNLOCK TABLES;
EoF

The above works fine if I enter these commands directly into MySQL, however when I run it as part of a bash script, I get the below errors

-bash: web_customer_tracker: command not found
-bash: web_customer_tracker: command not found
-bash: customer: command not found
-bash: customer: command not found
-bash: customer: command not found
-bash: first_name: command not found
-bash: last_name: command not found
-bash: email: command not found
-bash: customer: command not found
-bash: customer: command not found
-bash: customer: command not found

Can't figure out what am I missing here, any help would be appreciated. I want to create this as SQL script and then pass it to mysql to create database and load it with data.

According to https://stackoverflow.com/a/4938197/10622916 you should quote the delimiter to avoid substitution: `cat <<"EoF" > /tmp/mysql-load-table.sql` — Bodo, Sep 16 '20 at 15:34

score 1 · Accepted Answer · answered Sep 16 '20 at 15:36

Quote your heredoc delimiter, e.g.

cat <<'EoF' > /tmp/mysql-load-table.sql
CREATE DATABASE  IF NOT EXISTS `web_customer_tracker`;
USE `web_customer_tracker`;
...
EoF

From man bash:

No parameter and variable expansion, command substitution, arithmetic expansion, or pathname expansion is performed on word. If any characters in word are quoted, the delimiter is the result of quote removal on word, and the lines in the here-document are not expanded. If word is unquoted, all lines of the here-document are subjected to parameter expansion, command substitution, and arithmetic expansion, the character sequence <newline> is ignored, and \ must be used to quote the characters , $, and `.

Create a SQL script within a bash script

1 Answers1