0

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.

8
  • Is there any special characters in your password?
    – quanta
    Aug 3, 2011 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, 2011 at 4:31
  • Did you try to wrap it with single quote or use --password=?
    – quanta
    Aug 3, 2011 at 4:41
  • Dumb question, but, you verified the working command from the server and not from a different device, right? Aug 3, 2011 at 4:55
  • @quanta - tried that, no good.
    – Nick
    Aug 3, 2011 at 5:06

4 Answers 4

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"

0

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.

5
  • Sorry, I don't understand what you're talking about. Both the script and the command are being run through SSH.
    – Nick
    Aug 3, 2011 at 5:08
  • And on MySQL server itself?
    – quanta
    Aug 3, 2011 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, 2011 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, 2011 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, 2011 at 20:39
0

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.

7
  • 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, 2011 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, 2011 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, 2011 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, 2011 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, 2011 at 17:16
0

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

### 

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .