Correct MySQL username/password, but getting Access Denied error when run from script

Question

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.

Answer 1

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"

Answer 2

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.

Answer 3

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.

Answer 4

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