How to fix shell bash mysql load query syntax error?

Question

I need a shell script to load data into mysql db. The script is the next:

# !bin/bash

qry="DROP TABLE IF EXISTS tmp_x;
    CREATE TEMPORARY TABLE tmp_x AS SELECT * FROM x.y LIMIT 0;
    LOAD DATA INFILE 'path/xxx.csv' 
    INTO TABLE tmp_x 
    FIELDS TERMINATED BY "\," 
    ENCLOSED BY "\""
    LINES TERMINATED BY "\\n"
    IGNORE 1 ROWS;"

mysql --host=xxx --user=xxx --password=xxx db << EOF 
$qry
EOF

I get the following error message:

ERROR 1064 (42000) at line 3: 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 '
ENCLOSED BY "

LINES TERMINATED BY \n

IGNORE 1 ROWS' at line 3

I think it is something to do escaping some character, I tried changing to single quotes but it does not work neither.

I am workin on Ubuntu 18.

Any help will be very grateful.

Why not just put the query directly in the heredoc? Quote the heredoc (<<'EOF' instead of <<EOF) and it's completely literal -- the shell doesn't change anything in its contents at all. — Charles Duffy
– Charles Duffy, Commented Feb 20, 2020 at 17:01
BTW, if you want to see how your variable was parsed with the current code, just printf '%s\n' "$qry" and then compare it to what you know is good/working SQL. — Charles Duffy
– Charles Duffy, Commented Feb 20, 2020 at 17:02
probably you need to escape double quotes of "\," , otherwise string will end after encountering first double quote. Also you shebang seems wrong. — P....
– P...., Commented Feb 20, 2020 at 17:03

Daniel Martin · Accepted Answer · 2020-02-20 17:07:53Z

4

Try this:

#!/bin/bash

mysql --host=xxx --user=xxx --password=xxx db << EOF 
    DROP TABLE IF EXISTS tmp_x;
    CREATE TEMPORARY TABLE tmp_x AS SELECT * FROM x.y LIMIT 0;
    LOAD DATA INFILE 'path/xxx.csv' 
    INTO TABLE tmp_x 
    FIELDS TERMINATED BY ',' 
    ENCLOSED BY '"'
    LINES TERMINATED BY '\\n'
    IGNORE 1 ROWS;
EOF

If you really must use a variable, you'll need to play with quoting:

#!/bin/bash

qry="DROP TABLE IF EXISTS tmp_x;
    CREATE TEMPORARY TABLE tmp_x AS SELECT * FROM x.y LIMIT 0;
    LOAD DATA INFILE 'path/xxx.csv' 
    INTO TABLE tmp_x 
    FIELDS TERMINATED BY \",\" 
    ENCLOSED BY \"\\\"\"
    LINES TERMINATED BY \"\\n\"
    IGNORE 1 ROWS;"

mysql --host=xxx --user=xxx --password=xxx db << EOF 
$qry
EOF

answered Feb 20, 2020 at 17:07

Daniel Martin

23.7k6 gold badges52 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bill Karwin · Accepted Answer · 2020-02-20 17:08:02Z

It can be troublesome to use double-quoted strings in your SQL, since you're using double-quotes as the string delimiter in bash. In other words, which is the double-quote that ends the bash string, and which should be treated as a literal double-quote character in the SQL?

To resolve this, use single-quotes for string delimiters in the SQL.

Another issue: There's no need to put a backslash before , for the field terminator.

Another issue: The \n needs another backslash.

Here's what I tried and it seems to work:

qry="DROP TABLE IF EXISTS tmp_x;
    CREATE TEMPORARY TABLE tmp_x AS SELECT * FROM x.y LIMIT 0;
    LOAD DATA INFILE 'path/xxx.csv'
    INTO TABLE tmp_x
    FIELDS TERMINATED BY ','
    ENCLOSED BY '\"'
    LINES TERMINATED BY '\\\n'
    IGNORE 1 ROWS;"

I only printed the query, I haven't tested running it.

Collectives™ on Stack Overflow

How to fix shell bash mysql load query syntax error?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related