LinuxQuestions.org
Help answer threads with 0 replies.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 03-03-2010, 03:19 PM   #1
kernel-P4N1C
Member
 
Registered: Nov 2007
Location: Panama city, Republic of Panmaa
Posts: 167

Rep: Reputation: 18
help on a script that checks mysql db


Hi

I recently got stocked with this script..

it was running fine on an amazon ec2 box (centos based) but when i moved to the actual box...



Code:
#!/bin/bash
#
# This script read access code files and verify against mysql table
#
# Declare Variables

folder="/home/ftp-docs/accesscodes/"
#file=`ls -l  $folder | tail -1 | awk '{print $8}'`
file=accesscodes.txt
mysqluser=root
mysqlpass=PASSWORD
mysqlhost=192.168.0.1

# reading the file line by line
N=0
while IFS= read -r LINE ; do
        N=$((N+1))
        VALUE=${LINE:16:27} # cropping the record and leaving access code

# checking against database
dbexist=`mysql -h$mysqlhost -u$mysqluser -p$mysqlpass -e "SELECT id FROM database_name.accesscodes where accesscode=' $VALUE ';"`
echo dbexist $dbexist
echo $VALUE
if [ -z "$dbexist"  ];then
        if [ "$VALUE">0 ]; then # avoiding the null values for EOF
        echo "access code $x doesn't exist on database."
fi
fi
done <$folder$file
aparently the
Code:
'$VALUE'
part of the mysql query is not getting the falue.

as you see; i've added echo $VALUE somewhere to verify if the value was picked up... and it is; but dbexist is null..


if i remove the single quote marks i have
Code:
ERROR 1054 (42S22) at line 1: Unknown column '3816253' in 'where clause'
where 3816253 is the value of $VALUE...

please help me as i'm having my brain melting here (specially by the fact that this is working on one box but not the other.
 
Old 03-03-2010, 03:45 PM   #2
smoker
Senior Member
 
Registered: Oct 2004
Distribution: Fedora Core 4, 12, 13, 14, 15, 17
Posts: 2,279

Rep: Reputation: 249Reputation: 249Reputation: 249
There are lots of errors in the syntax

where should be WHERE
accesscode=' $VALUE ' should be accesscode LIKE "$VALUE"

That might cause issues with the mysql command but the variable $value needs double quotes to release its contents. (AFAIK)

echo dbexist $dbexist should be echo $dbexist

Last edited by smoker; 03-03-2010 at 03:47 PM.
 
Old 03-03-2010, 03:56 PM   #3
kernel-P4N1C
Member
 
Registered: Nov 2007
Location: Panama city, Republic of Panmaa
Posts: 167

Original Poster
Rep: Reputation: 18
i've made the corrections you are mentioned and i get
Code:
ERROR 1054 (42S22) at line 1: Unknown column '3816253' in 'where clause'
 
Old 03-03-2010, 04:16 PM   #4
smoker
Senior Member
 
Registered: Oct 2004
Distribution: Fedora Core 4, 12, 13, 14, 15, 17
Posts: 2,279

Rep: Reputation: 249Reputation: 249Reputation: 249
What are the tables columns named as ?

The basic syntax is

SELECT field FROM table WHERE other_field LIKE $value

So what is other_field called in real life ?
accesscode ?

What happens if you run :
Code:
mysql -h$mysqlhost -u$mysqluser -p$mysqlpass -e "SELECT id FROM database_name.accesscodes where accesscode LIKE "$VALUE";"
Putting real values in of course.

from a command line

Last edited by smoker; 03-03-2010 at 04:29 PM.
 
Old 03-03-2010, 04:18 PM   #5
kernel-P4N1C
Member
 
Registered: Nov 2007
Location: Panama city, Republic of Panmaa
Posts: 167

Original Poster
Rep: Reputation: 18
yes
it is called accesscode
 
Old 03-03-2010, 04:34 PM   #6
kernel-P4N1C
Member
 
Registered: Nov 2007
Location: Panama city, Republic of Panmaa
Posts: 167

Original Poster
Rep: Reputation: 18
when running
Code:
mysql -h$mysqlhost -u$mysqluser -p$mysqlpass -e "SELECT id FROM database_name.accesscodes where accesscode LIKE "$VALUE";"
i get

Code:
ERROR 1054 (42S22) at line 1: Unknown column '3816253' in 'where clause'
 
Old 03-03-2010, 04:35 PM   #7
smoker
Senior Member
 
Registered: Oct 2004
Distribution: Fedora Core 4, 12, 13, 14, 15, 17
Posts: 2,279

Rep: Reputation: 249Reputation: 249Reputation: 249
See if you can run that line I just added from a shell prompt.

If 3816253 is the value of $value it shouldn't be seen as a column. The column should be accesscode

?
 
Old 03-03-2010, 04:39 PM   #8
smoker
Senior Member
 
Registered: Oct 2004
Distribution: Fedora Core 4, 12, 13, 14, 15, 17
Posts: 2,279

Rep: Reputation: 249Reputation: 249Reputation: 249
Can you test the query using phpmysqladmin or something ?
You need to see what's in that dbase.
 
Old 03-03-2010, 04:45 PM   #9
kernel-P4N1C
Member
 
Registered: Nov 2007
Location: Panama city, Republic of Panmaa
Posts: 167

Original Poster
Rep: Reputation: 18
i've checked the query works fine

problem here is that it is not picking up the ''

i've always used this way to pull the query
Code:
mysql -u -p -h -e "SELECT field FROM table WHERE file='valye';"
when the '' are not present, it use the value of $VALUE as the column name instead of looking for the match on accesscodes
 
Old 03-03-2010, 04:49 PM   #10
smoker
Senior Member
 
Registered: Oct 2004
Distribution: Fedora Core 4, 12, 13, 14, 15, 17
Posts: 2,279

Rep: Reputation: 249Reputation: 249Reputation: 249
Hard code the 3816253 into the mysql query and see what happens then.
Also you could try escaping the quotes like

WHERE accesscode LIKE \"$value\";

Last edited by smoker; 03-03-2010 at 04:52 PM.
 
Old 03-03-2010, 07:58 PM   #11
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.9, Centos 7.3
Posts: 17,412

Rep: Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397
@smoker: for an exact match in SQL, use '='. 'like' is designed for wildcard matching; not as efficient in this case.
Quote:
Character Description
% Matches any number of characters, even zero characters
_ Matches exactly one character
http://dev.mysql.com/doc/refman/5.0/...functions.html

BTW SQL is not case sensitive for keywords like where vs WHERE. It's just a convention to use UPPERCASE for keywords.

@kernel-P4N1C: try putting the query in a file and using
mysql -uuser -ppwd -hhost <file.sql
 
Old 03-03-2010, 08:16 PM   #12
smoker
Senior Member
 
Registered: Oct 2004
Distribution: Fedora Core 4, 12, 13, 14, 15, 17
Posts: 2,279

Rep: Reputation: 249Reputation: 249Reputation: 249
@CHris - ok thanks. SQL's not my real domain. I have run a similar query line to the OPs on my system and it runs fine, so I don't see where the server's getting confused about the field name. It's ignoring the real one in favour of the queried term.
 
Old 03-03-2010, 08:26 PM   #13
kernel-P4N1C
Member
 
Registered: Nov 2007
Location: Panama city, Republic of Panmaa
Posts: 167

Original Poster
Rep: Reputation: 18
chrism01
how do i send the $VALUE to the file/files (notice there is a loop for reading a file line by line)
 
Old 03-03-2010, 08:39 PM   #14
smoker
Senior Member
 
Registered: Oct 2004
Distribution: Fedora Core 4, 12, 13, 14, 15, 17
Posts: 2,279

Rep: Reputation: 249Reputation: 249Reputation: 249
I think you're getting invisible characters being sent into $value.

Shouldn't a while loop start like
while [ IFS= read -r LINE ]; do

What does this do ?
N=$((N+1))


is 3816253 a sane value for accesscode to hold ?
 
Old 03-03-2010, 09:15 PM   #15
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.9, Centos 7.3
Posts: 17,412

Rep: Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397
Code:
t=0
mysql -u root -pnotmypasswd -e "select host from mysql.user where max_connections=$t"
works for me. Try that as an example. Note mysql.user => db.table
I had to install MySQL to try it ...
 
  


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
Bash script thats checks for faild login attempts. k1piee Programming 4 02-11-2009 10:46 PM
Script that checks for new files in a folder achtung_linux Programming 7 02-03-2007 10:18 AM
Script which checks Disk Volume - - on FC5 bskrakes Linux - General 1 10-11-2006 02:27 PM
shell script that checks for existence of files Rotwang Linux - General 3 12-02-2005 03:11 PM
Script that checks mapsize objorkum Linux - Software 1 07-06-2005 12:42 PM

LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie

All times are GMT -5. The time now is 07:13 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