Solved

Problem executing shell script for mysql connection

Posted on 2006-07-18
13
462 Views
Last Modified: 2008-01-09
I have written the following .sh script to upload several .txt files to mysql dbase. The problem is that it will not execute the commands. When I type each of these seperately in my ssh client it works perfectly. I need to automate this script though so that I can create a cron to run every night...

Thanks so much for your help
#!/bin/bash



mysql --local-infile --host=db162.perfora.net  --user=dbo116102287 --password=wavechaser db116102287
LOAD DATA LOCAL INFILE  '/kunden/homepages/23/d116101914/htdocs/web/mls/data/listings-vacant\ land.txt' INTO TABLE  `temp_listing` FIELDS TERMINATED BY  '\t' ENCLOSED BY  '\"' ESCAPED BY  '\\' LINES TERMINATED BY  '\r\n'


LOAD DATA LOCAL INFILE  '/kunden/homepages/23/d116101914/htdocs/web/mls/data/listings-fractional\ interest.txt' INTO TABLE  `temp_listing` FIELDS TERMINATED BY  '\t' ENCLOSED BY  '\"' ESCAPED BY  '\\' LINES TERMINATED BY  '\r\n';


LOAD DATA LOCAL INFILE  '/kunden/homepages/23/d116101914/htdocs/web/mls/data/listings-multi\ unit.txt' INTO TABLE  `temp_listing` FIELDS TERMINATED BY  '\t' ENCLOSED BY  '\"' ESCAPED BY  '\\' LINES TERMINATED BY  '\r\n';


LOAD DATA LOCAL INFILE  '/kunden/homepages/23/d116101914/htdocs/web/mls/data/listings-single\ family.txt' INTO TABLE  `temp_listing` FIELDS TERMINATED BY  '\t' ENCLOSED BY  '\"' ESCAPED BY  '\\' LINES TERMINATED BY  '\r\n';


LOAD DATA LOCAL INFILE  '/kunden/homepages/23/d116101914/htdocs/web/mls/data/listings-commercial.txt' INTO TABLE  `temp_listing` FIELDS TERMINATED BY  '\t' ENCLOSED BY  '\"' ESCAPED BY  '\\' LINES TERMINATED BY  '\r\n';


LOAD DATA LOCAL INFILE  '/kunden/homepages/23/d116101914/htdocs/web/mls/data/listings-condominium.txt' INTO TABLE  `temp_listing` FIELDS TERMINATED BY  '\t' ENCLOSED BY  '\"' ESCAPED BY  '\\' LINES TERMINATED BY  '\r\n';

exit

0
Comment
Question by:mtnwave
  • 5
  • 3
  • 3
  • +2
13 Comments
 
LVL 51

Expert Comment

by:ahoffmann
ID: 17133158
>  \t
do you expect a TAB character there?
If you want to pass a \t literal, you have to use \\t , same applies to \\r and \\n.
0
 

Author Comment

by:mtnwave
ID: 17133187
Tab Character

My problem has more to do with the shell script running. When I load it in terminal (mac) it starts a mysql session but does not execute the LOAD DATA... commands.

Any  thoughts?

Nate
0
 
LVL 24

Accepted Solution

by:
slyong earned 500 total points
ID: 17135564
Hi Nate,

How about putting all the LOAD statements into a file (let's call it load.txt).  Then in your bash script:

#!/bin/bash
mysql --local-infile --host=db162.perfora.net  --user=dbo116102287 --password=wavechaser db116102287 < load.txt

or try
#!/bin/bash
mysql --local-infile --host=db162.perfora.net  --user=dbo116102287 --password=wavechaser db116102287 << EOF
LOAD DATA LOCAL INFILE  '/kunden/homepages/23/d116101914/htdocs/web/mls/data/listings-vacant\ land.txt' INTO TABLE  `temp_listing` FIELDS TERMINATED BY  '\t' ENCLOSED BY  '\"' ESCAPED BY  '\\' LINES TERMINATED BY  '\r\n'
LOAD DATA LOCAL INFILE  '/kunden/homepages/23/d116101914/htdocs/web/mls/data/listings-fractional\ interest.txt' INTO TABLE  `temp_listing` FIELDS TERMINATED BY  '\t' ENCLOSED BY  '\"' ESCAPED BY  '\\' LINES TERMINATED BY  '\r\n';
LOAD DATA LOCAL INFILE  '/kunden/homepages/23/d116101914/htdocs/web/mls/data/listings-multi\ unit.txt' INTO TABLE  `temp_listing` FIELDS TERMINATED BY  '\t' ENCLOSED BY  '\"' ESCAPED BY  '\\' LINES TERMINATED BY  '\r\n';
LOAD DATA LOCAL INFILE  '/kunden/homepages/23/d116101914/htdocs/web/mls/data/listings-single\ family.txt' INTO TABLE  `temp_listing` FIELDS TERMINATED BY  '\t' ENCLOSED BY  '\"' ESCAPED BY  '\\' LINES TERMINATED BY  '\r\n';
LOAD DATA LOCAL INFILE  '/kunden/homepages/23/d116101914/htdocs/web/mls/data/listings-commercial.txt' INTO TABLE  `temp_listing` FIELDS TERMINATED BY  '\t' ENCLOSED BY  '\"' ESCAPED BY  '\\' LINES TERMINATED BY  '\r\n';
LOAD DATA LOCAL INFILE  '/kunden/homepages/23/d116101914/htdocs/web/mls/data/listings-condominium.txt' INTO TABLE  `temp_listing` FIELDS TERMINATED BY  '\t' ENCLOSED BY  '\"' ESCAPED BY  '\\' LINES TERMINATED BY  '\r\n';
EOF
0
 
LVL 24

Expert Comment

by:slyong
ID: 17135569
Sorry just noticed that the first line of your LOAD statement doesn't have ";" at the end.
0
 

Author Comment

by:mtnwave
ID: 17135574
Thanks Sly

I'l give this a try tomorrow as it is late here! This looks like a pretty good option though!

Nate
0
 
LVL 24

Expert Comment

by:slyong
ID: 17135580
Hey,

call me sl or yong.. sly is a bit umm.. :D.  Have a good night!
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:mtnwave
ID: 17135598
Yong;

No worries!

Nate
0
 
LVL 51

Expert Comment

by:ahoffmann
ID: 17136231
> doesn't have ";" at the end.
good eyes slyong, missed that the SQL commands are executed in mysql's interactive mode

mtnwave , either follow slyong's suggestion using a script for your SQL commands, or prepend the mysql call to each of your SQL commands
0
 
LVL 34

Expert Comment

by:Duncan Roe
ID: 17138124
Hi mtnwave,
When you say it doesn't run., does it actually start mysql at all? The PATH (where to look for commands) is very short in a CRON job. In your interactive session, type "env" - you'll see lots of stuff. "env" in a CRON job shows maybe 6 lines. You can set up necessary env items with the "export" bash builtin - type "help export" for a short description.
0
 

Author Comment

by:mtnwave
ID: 17138310
Duncan ;

It starts a mysql sessionbut nothing more. I've not even set a cron yet as the script will not work.

I am just geting up after working on non related issues 'till 2 AM.  Heh

I am going to  take a look at this is a bit. It could be the ':' on line 3 but I think that this was just a typo as I had tried around 25 iterations of the script to make it work. Typos have brought me down in the past though!

You guys are terrific by the way.

Nate
0
 
LVL 51

Expert Comment

by:ahoffmann
ID: 17139045
> .. as the script will not work.
did you follow the suggestions?
Your posted script is a mix of SQL and shell syntax. You either need to separate them, or use plain shell syntax as already explained.

> .. It could be the ':' on line 3 ..
there is no : in your posted script
0
 
LVL 3

Expert Comment

by:bryanlloydharris
ID: 17163867
what about:

echo -en "mysql comands\t with\t\t tabs\n" | mysql --etc. --etc. --etc.

I think maybe the tabs would work like that, it's very similar to yong's answer above with the << EOF thing.
0
 

Author Comment

by:mtnwave
ID: 17218154
Hi I am back...Thanks for the ideas, I was working on another aspect of the project and am only now returning to this problem.

I used slylong' s solution that seperated the scripts. Appreciate the help!

Nate
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Currently, there is not an RPM package available under the RHEL/Fedora/CentOS distributions that gives you a quick and easy way to allow PHP to interface with Oracle. As a result, I have included a set of instructions on how to do this with minimal …
Setting up Secure Ubuntu server on VMware 1.      Insert the Ubuntu Server distribution CD or attach the ISO of the CD which is in the “Datastore”. Note that it is important to install the x64 edition on servers, not the X86 editions. 2.      Power on th…
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now