help on a script that checks mysql db

kernel-P4N1C · 03-03-2010, 02:19 PM

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.

smoker · 03-03-2010, 02:45 PM

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

kernel-P4N1C · 03-03-2010, 02:56 PM

i've made the corrections you are mentioned and i get

Code:

ERROR 1054 (42S22) at line 1: Unknown column '3816253' in 'where clause'

smoker · 03-03-2010, 03:16 PM

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

kernel-P4N1C · 03-03-2010, 03:18 PM

yes
it is called accesscode

kernel-P4N1C · 03-03-2010, 03:34 PM

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'

smoker · 03-03-2010, 03:35 PM

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

?

smoker · 03-03-2010, 03:39 PM

Can you test the query using phpmysqladmin or something ?
You need to see what's in that dbase.

kernel-P4N1C · 03-03-2010, 03:45 PM

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

smoker · 03-03-2010, 03:49 PM

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\";

chrism01 · 03-03-2010, 06:58 PM

@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

smoker · 03-03-2010, 07:16 PM

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

kernel-P4N1C · 03-03-2010, 07:26 PM

chrism01
how do i send the $VALUE to the file/files (notice there is a loop for reading a file line by line)

smoker · 03-03-2010, 07:39 PM

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 ?

chrism01 · 03-03-2010, 08:15 PM

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