Solved

Problem executing shell script for mysql connection

Posted on 2006-07-18
13
470 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 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Suggested Solutions

Title # Comments Views Activity
can i read my emails on lamp ftp 4 27
parallel rsync issues with Ubuntu 1 41
UM7 Stop Sending packets with GPS Sensor 1 21
Shrink Linux Swap File Size CentOS 10 31
Using 'screen' for session sharing, The Simple Edition Step 1: user starts session with command: screen Step 2: other user (logged in with same user account) connects with command: screen -x Done. Both users are connected to the same CLI sessio…
Fine Tune your automatic Updates for Ubuntu / Debian
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…
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.

730 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