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
Solved

Problem executing shell script for mysql connection

Posted on 2006-07-18
13
469 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Network Interface Card (NIC) bonding, also known as link aggregation, NIC teaming and trunking, is an important concept to understand and implement in any environment where high availability is of concern. Using this feature, a server administrator …
SSH (Secure Shell) - Tips and Tricks As you all know SSH(Secure Shell) is a network protocol, which we use to access/transfer files securely between two networked devices. SSH was actually designed as a replacement for insecure protocols that sen…
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…
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…

809 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