Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I'm currently trying to run the following command from within a shell script.

/usr/bin/mysql -u username -ppassword -h localhost database

It works perfectly fine when executed manually, and not from within a script. When I try to execute a script that contains that command, I get the following error:

ERROR 1045 (28000) at line 3: Access denied for user 'username'@'localhost' (using password: YES)

I literally copied and pasted the working command into the script. Why the error? As a sidenote: the ultimate intent is to run the script with cron.

EDIT: Here is a stripped down version of my script that I'm trying to run. You can ignore most of it up until the point where it connects to MySQL around line 19.

#!/bin/sh
#Run download script to download product data
cd /home/dir/Scripts/Linux
/bin/sh script1.sh

#Run import script to import product data to MySQL
cd /home/dir/Mysql
/bin/sh script2.sh

#Download inventory stats spreadsheet and rename it
cd /home/dir
/usr/bin/wget http://www.url.com/file1.txt
mv file1.txt sheet1.csv

#Remove existing export spreadsheet
rm /tmp/sheet2.csv

#Run MySQL queries in "here document" format
/usr/bin/mysql -u username -ppassword -h localhost database << EOF

--Drop old inventory stats table
truncate table table_name1;

--Load new inventory stats into table
Load data local infile '/home/dir/sheet1.csv' into table table_name1 fields terminated by ',' optionally enclosed by '"' lines terminated by '\r\n';

--MySQL queries to combine product data and inventory stats here

--Export combined data in spreadsheet format
group by p.value into outfile '/tmp/sheet2.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\r\n';
EOF

EDIT 2: After some more testing, the issue is with the << EOF that is at the end of the command. This is there for the "here document". When removed, the command works fine. The problem is that I need << EOF there so that the MySQL queries will run.

share|improve this question
    
Is there any special characters in your password? –  quanta Aug 3 '11 at 4:28
    
Nope. And if there were any issues with the password, wouldn't it cause problems when manually executed? The password worked fine outside of the script. –  Nick Aug 3 '11 at 4:31
    
Did you try to wrap it with single quote or use --password=? –  quanta Aug 3 '11 at 4:41
    
Dumb question, but, you verified the working command from the server and not from a different device, right? –  Shane Madden Aug 3 '11 at 4:55
    
@quanta - tried that, no good. –  Nick Aug 3 '11 at 5:06

4 Answers 4

Are you using this same command / script on localhost? Or remote server, keep in mind each entry in mysql.users is set by host so if you did localhost only it won't work for remote access, and vice versa.

share|improve this answer
    
Sorry, I don't understand what you're talking about. Both the script and the command are being run through SSH. –  Nick Aug 3 '11 at 5:08
    
And on MySQL server itself? –  quanta Aug 3 '11 at 5:47
    
@Nick, MYSQL user access is controlled based on the HOST where you connect from. The mysql db and users table hold your authorized users (root/password) etc; –  Jakub Aug 3 '11 at 15:16
    
I still don't understand what it is that you're asking. MySQL is installed on the server I'm running the commands. I can't get access to the MySQL database when running the command via a script, but when I run the command manually, it connects properly. –  Nick Aug 3 '11 at 15:18
    
this isn't difficult, IT depends on WHERE YOU run the script from LOCAL/REMOTE.. as username/password is also tied to YOUR login location. Has nothing to do with where the mysql server is installed. So if your script calls the SAME server @ 127.0.0.1 and it ONLY allows localhost (not the ip) it will fail. etc; Also as a last ditch effort, verify your password/username, sometimes a typo wastes a lot of time. –  Jakub Aug 3 '11 at 20:39

If it's a bash shell script, you can set the #! line to #!/bin/bash -x, which makes the script output to the console the exact commands it's executing. You can then take that command and try it manually, or see how it might be different from what you were already running by hand.

Failing that, it would be helpful for you to post a snippet from the script where this command is run so we have more context to use to answer the question.

share|improve this answer
    
I've edited my code into the original question. Since I've copied and pasted the code from one to the other, shouldn't it be exactly the same? –  Nick Aug 3 '11 at 15:24
    
Have you tried the @Handyman5's suggestion? sh -x /path/to/your/script.sh will print the exactly password that use to connect. –  quanta Aug 3 '11 at 16:07
    
@quanta - I did this and copied the command that the script executed. Despite getting an access denied error when run through the script, simply copy+pasting and manually executing the same exact command worked perfectly fine. –  Nick Aug 3 '11 at 17:04
    
The problem is with the << EOF at the end of the command. Please see edits on the original question above. –  Nick Aug 3 '11 at 17:10
    
Ah, I see. I believe you can pipe that data into mysql, such as echo "select * from users;" | mysql -u username -ppassword database. That may work better from the command line. –  Handyman5 Aug 3 '11 at 17:16

Turns out the root cause was a command in the MySQL query that I was trying to execute without the proper permissions. That is why it says error at line 3, meaning line 3 of the sql query. I switched to root and everything works fine now. Here is the question more clearly asked and answered:

MySQL Access denied with "here document"

share|improve this answer

It is absolutely significant that last line contain ONLY "EOF\n". Single space symbol at the end of the line can make the marker different from one used at first line. In your case just add the empty string after the "EOF" line.

It is the good habit to add comment-line to the last line of code.

/usr/bin/mysql -u username -ppassword -h localhost database << EOF
. . . . . 
EOF

### 
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.