?
Solved

Problem executing shell script for mysql connection

Posted on 2006-07-18
13
Medium Priority
?
474 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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 35

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I. Introduction There's an interesting discussion going on now in an Experts Exchange Group — Attachments with no extension (http://www.experts-exchange.com/discussions/210281/Attachments-with-no-extension.html). This reminded me of questions tha…
Join Greg Farro and Ethan Banks from Packet Pushers (http://packetpushers.net/podcast/podcasts/pq-show-93-smart-network-monitoring-paessler-sponsored/) and Greg Ross from Paessler (https://www.paessler.com/prtg) for a discussion about smart network …
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.
Suggested Courses

650 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