LinuxQuestions.org
Help answer threads with 0 replies.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 10-30-2014, 06:26 PM   #1
kamran.ayub
Member
 
Registered: Jan 2012
Posts: 72

Rep: Reputation: Disabled
mysql insert issue in bash script


Dear All,

I am writing a bash script which can read data from file. File contains output of "last" command with some awk tricks.
Following is my file data format.

kamran pts/0 10.111.11.51 Thu-Oct-30 22:33 logged
kamran pts/0 10.111.11.51 Thu-Oct-30 21:06 22:33
kamran pts/0 10.111.11.51 Wed-Oct-29 21:29 05:00
kamran pts/1 10.111.11.51 Tue-Oct-28 22:48 04:33
kamran pts/0 10.111.11.51 Tue-Oct-28 21:21 00:21

Now my bash script is following to put data in mysql database.
Issue I am facing is that the below script inserted data from file with picking up the 1st row of my data file i.e all rows in mysql table has same records matching with row1 of data file.

#/!bin/bash
#Defining Variables
ssh_sessions_file="/home/kamran/ssh_logins.txt"

#Begin Code
# extrcting the Information of ssh logins from file ssh_logins.txt
#

NumberOfLines=$(cat $ssh_sessions_file | wc -l)
j=1
while [ $NumberOfLines -gt 0 ]
do
Login_Name=$(cat $ssh_sessions_file | awk '{print $1}')
Login_Terminal=$(cat $ssh_sessions_file | awk '{print $2}')
Login_IP=$(cat $ssh_sessions_file | awk '{print $3}')
Login_Date=$(cat $ssh_sessions_file | awk '{print $4}')
Login_Start_Time=$(cat $ssh_sessions_file | awk '{print $5}')
Login_End_Time=$(cat $ssh_sessions_file | awk '{print $6}')


echo $Login_Name
echo $Login_Terminal
echo $Login_IP
echo $Login_Date
echo $Login_Start_Time
echo $Login_End_Time
let "j +=1"
let "NumberOfLines -=1"
mysql -u root -ppassword ssh_logins <<- _END_
INSERT INTO ssh_logins (Login_Name, Login_Terminal, Login_IP, Login_Date, Login_Start_Time, Login_End_Time) VALUES ('$Login_Name','$Login_Terminal','$Login_IP','$Login_Date','$Login_Start_Time','$Login_End_Time');
_END_
done

##################

Please anybody can help why this script duplicating 1st row in all my mysql table rows. Why not inserting correct row records from file with help of loop in script.

regards,
kamran
 
Old 10-30-2014, 06:56 PM   #2
Guttorm
Senior Member
 
Registered: Dec 2003
Location: Trondheim, Norway
Distribution: Debian and Ubuntu
Posts: 1,335

Rep: Reputation: 362Reputation: 362Reputation: 362Reputation: 362
Looks complicated. Instead of the bash script, why not use SQL?

http://dev.mysql.com/doc/refman/5.1/en/load-data.html
 
1 members found this post helpful.
Old 10-31-2014, 01:38 AM   #3
eklavya
Member
 
Registered: Mar 2013
Posts: 633

Rep: Reputation: 141Reputation: 141
Your script says, database name is ssh_logins as well as table name is ssh_logins. Is it confirmed?

You should not run queries like this from a bash script

Try this code
Code:
mysql -u root -p'password' ssh_logins -B -e "INSERT INTO ssh_logins (Login_Name, Login_Terminal, Login_IP, Login_Date, Login_Start_Time, Login_End_Time) VALUES ('$Login_Name','$Login_Terminal','$Login_IP','$Login_Date','$Login_Start_Time','$Login_End_Time');"
 
1 members found this post helpful.
Old 10-31-2014, 10:22 AM   #4
TB0ne
LQ Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 22,113

Rep: Reputation: 5914Reputation: 5914Reputation: 5914Reputation: 5914Reputation: 5914Reputation: 5914Reputation: 5914Reputation: 5914Reputation: 5914Reputation: 5914Reputation: 5914
Quote:
Originally Posted by kamran.ayub View Post
Dear All,
I am writing a bash script which can read data from file. File contains output of "last" command with some awk tricks. Following is my file data format.

kamran pts/0 10.111.11.51 Thu-Oct-30 22:33 logged
kamran pts/0 10.111.11.51 Thu-Oct-30 21:06 22:33
kamran pts/0 10.111.11.51 Wed-Oct-29 21:29 05:00
kamran pts/1 10.111.11.51 Tue-Oct-28 22:48 04:33
kamran pts/0 10.111.11.51 Tue-Oct-28 21:21 00:21

Now my bash script is following to put data in mysql database. Issue I am facing is that the below script inserted data from file with picking up the 1st row of my data file i.e all rows in mysql table has same records matching with row1 of data file.

Please anybody can help why this script duplicating 1st row in all my mysql table rows. Why not inserting correct row records from file with help of loop in script.
Similar to your other thread:
http://www.linuxquestions.org/questi...db-4175523722/

There are THOUSANDS of very easily found bash scripting tutorials...even one in my posting signature, and many others online that have examples on how to do MySQL inserts from files. Have you looked at ANY of them?

And I'll again suggest, as both Guttorm and eklavya have, that you just use the standard MySQL commands to load a file.
 
Old 10-31-2014, 11:42 AM   #5
kamran.ayub
Member
 
Registered: Jan 2012
Posts: 72

Original Poster
Rep: Reputation: Disabled
Dear eklavya,

Yes I have set DB name and table name ssh_logins. I have tried your provided query.

But result is same that it is inserting 1st row record of my data file in all rows of my mysql table.

Any other suggestion please.

Regards,
Kamran
 
Old 10-31-2014, 01:38 PM   #6
TB0ne
LQ Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 22,113

Rep: Reputation: 5914Reputation: 5914Reputation: 5914Reputation: 5914Reputation: 5914Reputation: 5914Reputation: 5914Reputation: 5914Reputation: 5914Reputation: 5914Reputation: 5914
Quote:
Originally Posted by kamran.ayub View Post
Dear eklavya,
Yes I have set DB name and table name ssh_logins. I have tried your provided query. But result is same that it is inserting 1st row record of my data file in all rows of my mysql table.
Any other suggestion please.
You got other suggestions; did you read and understand them??

AGAIN: you need to use the native MySQL commands both correctly, and use all of their capabilities. You were already provided links that tell you how to import a CSV file using nothing by MySQL...did you read that? Try it?

And again, it was suggested that you look at any of the bash scripting tutorials..did you? Because if you're only getting the first row of data, it would seem VERY obvious that you aren't looping through the file correctly.
 
Old 10-31-2014, 02:57 PM   #7
kamran.ayub
Member
 
Registered: Jan 2012
Posts: 72

Original Poster
Rep: Reputation: Disabled
Dear All,

Thanks for your replies and suggestions.

Issue resolved by reading output file in csv format. Following is now a simple bash script code for doing all.

#/!bin/bash

IFS=,
while read Login_Name Login_Terminal Login_IP Login_Date Login_Start_Time Login_End_Time
do
echo "INSERT INTO ssh_logins (Login_Name,Login_Terminal,Login_IP,Login_Date,Login_Start_Time,Login_End_Time) VALUES ('$Login_Name','$Login_Terminal','$Login_IP','$Login_Date','$Login_Start_Time','$Login_End_Time');"

done < /home/kamran/output.csv | mysql -u root -p asterisk ssh_logins;

#################

Regards,
Kamran
 
1 members found this post helpful.
Old 10-31-2014, 03:53 PM   #8
TB0ne
LQ Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 22,113

Rep: Reputation: 5914Reputation: 5914Reputation: 5914Reputation: 5914Reputation: 5914Reputation: 5914Reputation: 5914Reputation: 5914Reputation: 5914Reputation: 5914Reputation: 5914
Quote:
Originally Posted by kamran.ayub View Post
Dear All,
Thanks for your replies and suggestions. Issue resolved by reading output file in csv format. Following is now a simple bash script code for doing all.
Code:
#/!bin/bash

IFS=,
while read Login_Name Login_Terminal Login_IP Login_Date Login_Start_Time Login_End_Time
      do
        echo "INSERT INTO ssh_logins (Login_Name,Login_Terminal,Login_IP,Login_Date,Login_Start_Time,Login_End_Time) VALUES ('$Login_Name','$Login_Terminal','$Login_IP','$Login_Date','$Login_Start_Time','$Login_End_Time');"

done < /home/kamran/output.csv | mysql -u root -p asterisk ssh_logins;
Glad it's working, but:
  • You were given advice SEVERAL TIMES about how you are doing that insert. You were told it was a bad idea, yet you did it anyway.
  • You were given advice about how to use the built-in MySQL tools that already exist to do nothing BUT import CSV files, but didn't acknowledge or use them.
  • You were asked to use CODE tags around your code, and didn't
If you're going to ask for advice and suggestions, you might want to consider at least ACKNOWLEDGING them. If you're going to ignore what people tell you, there's little point in posting.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] mysql insert to table bash script tripialos Linux - General 19 05-15-2014 05:06 PM
How to insert header once in bash script manya Programming 1 08-24-2009 05:58 AM
bash script to insert id3 tags from file and directories jason7 Linux - General 15 07-20-2009 06:10 AM
How to insert images in MYSQL in C or BASH waqasdaar Programming 2 02-06-2009 03:27 PM
mysql insert using bash script venki Linux - General 3 07-07-2007 05:52 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 02:53 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration