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.

Nick
  • 315
  • 2
  • 7
  • 15

4 Answers4

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"

Community
  • 1
Nick
  • 315
  • 2
  • 7
  • 15
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.

Jakub
  • 380
  • 1
  • 11
  • Sorry, I don't understand what you're talking about. Both the script and the command are being run through SSH. – Nick Aug 03 '11 at 05:08
  • And on MySQL server itself? – quanta Aug 03 '11 at 05: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 03 '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 03 '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 03 '11 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.

Handyman5
  • 5,177
  • 25
  • 30
  • 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 03 '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 03 '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 03 '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 03 '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 03 '11 at 17:16
  • @Handyman5 - The query I'm trying to run is quite large, I've just replaced the vast majority of it with a comment for the purposes of this question. – Nick Aug 03 '11 at 17:23
  • You may also be able to save it to a file and run `cat | mysql ...`. I'm sorry I don't have any better solutions. – Handyman5 Aug 03 '11 at 19:32
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

###
Kondybas
  • 6,864
  • 2
  • 19
  • 24