Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Problem executing shell script for mysql connection

Posted on 2006-07-18
13
Medium Priority
?
475 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 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying 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

The purpose of this article is to demonstrate how we can use conditional statements using Python.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Suggested Courses
Course of the Month10 days, 4 hours left to enroll

569 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