pass parameter from bash to mysql script

Question

I'm trying to pass parameter from bash script to mysql script. The bash script is

#!/bin/bash
for file in `ls *.symbol`
do
path=/home/qz/$file
script='/home/qz/sqls/load_eval.sql'
mysql -u qz -h compute-0-10 -pabc -e "set @pred = '$path'; source $script;" 
done

The load_eval.sql is

use biogrid;
load data local infile @pred into table lasp
fields terminated by ','
lines terminated by '\n'
(score, symbols);

When running the bash script, I got error the message:

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 '@pred into table lasp ..

It seems the value of the parameter @pred is not passed into mysql script.

try to echo the mysql statement echo "mysql ..." and see if you see something wrong in the statement or probably post the echo here to help — a14m
– a14m, Commented Aug 22, 2013 at 0:42
@qing can you please run this script with -x and show the output? also change the mysql line to be mysql --verbose -u qz -h compute-0-10 -pabc -e "set \@pred = '$path'; source $script;" If you can post that to a pastebin i'm sure I can help out. — Malcolm Jones
– Malcolm Jones, Commented Aug 22, 2013 at 0:49
path is a bad choice for variable name, since it already has special meaning to the shell, use a different name, for example filepath or fullname — Miserable Variable
– Miserable Variable, Commented Aug 22, 2013 at 0:59
@MalcolmJones The output exceeds the size limit of comment box so I past them as two comments. Part I:ERROR 1064 (42000) at line 2 in file: '/home/qz/sqls/load_eval.sql': 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 '@pred into table lasp fields terminated by ',' lines terminated by '\n' (score, sym' at line 1 — Qing
– Qing, Commented Aug 22, 2013 at 2:03
@MalcolmJones Part II:+ for file in 'ls *.symbol' + filepath=/home/qz/lasp/61304.pair.symbol + script=/home/qz/sqls/load_eval.sql + mysql --verbose -u qz -h compute-0-10 -pabc -e 'set @pred='\''/home/qz/lasp/61304.pair.symbol'\''; source /home/qz/sqls/load_eval.sql;' -------------- set @pred='/home/qz/lasp/61304.pair.symbol' -------------- -------------- load data local infile @pred into table lasp fields terminated by ',' lines terminated by '\n' (score, symbols) -------------- — Qing
– Qing, Commented Aug 22, 2013 at 2:03

Bill Karwin · Accepted Answer · 2013-08-22 06:18:32Z

1

MySQL doesn't support session variables in a LOAD DATA INFILE statement like that. This has been recognized as a feature request for quite some time (http://bugs.mysql.com/bug.php?id=39115), but the feature has never been implemented.

I would recommend using mysqlimport instead of doing the complex steps with mysql that you're doing. The file's name must match the table's name, but you can trick this with a symbolic link:

#!/bin/bash
for file in *.symbol
do
  path="/home/qz/$file"
  ln -s -f "$path" /tmp/lasp.txt
  mysqlimport -u qz -h compute-0-10 -pabc \
    --local --columns "score,symbols" /tmp/lasp.txt 
done
rm -f /tmp/lasp.txt

PS: No need use `ls`. As you can see above, filename expansion works fine.

edited Aug 22, 2013 at 6:18

answered Aug 22, 2013 at 0:53

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

1 Comment

Aleks-Daniel Jakimenko-A. Over a year ago

Use quotes around $path like this ln -s -f "$path" /tmp/lasp.txt . This way things wont break if path contains a space.

Collectives™ on Stack Overflow

pass parameter from bash to mysql script

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related