LinuxQuestions.org
Latest LQ Deal: Complete CCNA, CCNP & Red Hat Certification Training Bundle
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 05-13-2013, 01:22 PM   #1
jonaskellens
Member
 
Registered: Jul 2008
Location: Ghent, Belgium
Distribution: Fedora, CentOS
Posts: 632

Rep: Reputation: 33
MySQL-command OK from commandline, notOK from bash-script


Hello,

when I execute the following line inside a bash script :
Code:
mysql -u MyUser -pMyPassword -h ip_databaseserver -D My-DB <<EOF
I get the output :

ERROR 1045 (28000) at line 2: Access denied for user 'MyUser'@'ip_databaseserver' (using password: YES)


When I execute this same command on commandline, there is no problem :
Code:
[root@server logging]# mysql -u MyUser -pMyPassword -h ip_databaseserver -D My-DB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2692
Server version: 5.1.69 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
Why can't I use this command inside a bash script ?

Last edited by jonaskellens; 05-13-2013 at 01:32 PM.
 
Old 05-13-2013, 01:40 PM   #2
parnmatt
Member
 
Registered: Apr 2013
Location: Lancaster
Distribution: Mac OS X
Posts: 38

Rep: Reputation: 7
I maybe wrong, but my understanding of when you run a script, it is run in a subshell.
It's possible the subshell may not have the permission to access the database.

Hense the Access denied
 
Old 05-13-2013, 01:45 PM   #3
jonaskellens
Member
 
Registered: Jul 2008
Location: Ghent, Belgium
Distribution: Fedora, CentOS
Posts: 632

Original Poster
Rep: Reputation: 33
Is "the subshell" a user then ?

I find many examples with google where this works fine...

The script is executed as root by the way.
 
Old 05-13-2013, 01:48 PM   #4
parnmatt
Member
 
Registered: Apr 2013
Location: Lancaster
Distribution: Mac OS X
Posts: 38

Rep: Reputation: 7
No a subshell is a shell within a shell.

Every time you invoke
Code:
`command`
or
Code:
$(command)
, you are running this command within another subshell too.

You can think of it as a scope error.

If, however, you have seen other examples identical/similar to your script, it is unlikely that this is case.
I'm sorry I cannot be of further help.
 
Old 05-13-2013, 02:04 PM   #5
jonaskellens
Member
 
Registered: Jul 2008
Location: Ghent, Belgium
Distribution: Fedora, CentOS
Posts: 632

Original Poster
Rep: Reputation: 33
To use multiline MySQL-command inside bash-script you use EOF.

Like here : http://williamjxj.wordpress.com/2011...oad-csv-files/

Like here : http://stackoverflow.com/questions/8...in-shellscript
 
Old 05-13-2013, 07:27 PM   #6
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.8, Centos 5.10
Posts: 17,240

Rep: Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324
You might not need a here-doc
Code:
mysql -u MyUser -pMyPassword -h ip_databaseserver -D My-DB <mycmds.sql
and put all your SQL in that file.
Don't forget an exit; at the end.
 
Old 05-14-2013, 04:14 AM   #7
jonaskellens
Member
 
Registered: Jul 2008
Location: Ghent, Belgium
Distribution: Fedora, CentOS
Posts: 632

Original Poster
Rep: Reputation: 33
I do not have sql file, I have csv-file...

I thought I had found the solution with this :

Code:
mysql> GRANT file ON MyDB.MyTable TO MyUser@'ip_databaseserver' IDENTIFIED BY 'MyPassword';
But I get :

ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the manual to see which privileges can be used


It can be I have a problem with rights, which causes the error: ERROR 1045 (28000) at line 2: Access denied for user 'MyUser'@'ip_databaseserver' (using password: YES)

Last edited by jonaskellens; 05-14-2013 at 04:16 AM.
 
Old 05-14-2013, 06:00 AM   #8
jonaskellens
Member
 
Registered: Jul 2008
Location: Ghent, Belgium
Distribution: Fedora, CentOS
Posts: 632

Original Poster
Rep: Reputation: 33
[MySQL] alternative to import csv-file

Hello,

to import a csv-file into MySQL with command "load data infile", the user doing this load needs "file" permissions.

I find it very insecure to grant someone "file" permissions.

Is there an alternative to import a csv-file into MySQL database table ?
 
Old 05-14-2013, 06:11 AM   #9
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.8, Centos 5.10
Posts: 17,240

Rep: Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324
1. you can check grants http://www.f15ijp.com/2010/01/changi...r-for-crontab/

2. more importantly
Quote:
The CREATE USER, FILE, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, SHUTDOWN, and SUPER privileges are administrative and can only be granted globally.
https://dev.mysql.com/doc/refman/5.1/en/grant.html

3. which means your grant would look like
Code:
GRANT file ON *.* TO MyUser@'ip_databaseserver';
http://stackoverflow.com/questions/1...with-load-data

4. LOAD DATA https://dev.mysql.com/doc/refman/5.1/en/load-data.html
 
1 members found this post helpful.
Old 05-14-2013, 06:18 AM   #10
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.8, Centos 5.10
Posts: 17,240

Rep: Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324
In that case, you need to do one of these

1. write some code to generate insert statements

2. use en editor eg vim to generate insert statements

3. use a lang like Perl that has a DB API
 
Old 05-14-2013, 06:18 AM   #11
jonaskellens
Member
 
Registered: Jul 2008
Location: Ghent, Belgium
Distribution: Fedora, CentOS
Posts: 632

Original Poster
Rep: Reputation: 33
To grant "file" to a user other than root seems very dangerous...

I can't seem to find an alternative to import a csv-file into MySQL.
 
Old 05-14-2013, 06:20 AM   #12
jonaskellens
Member
 
Registered: Jul 2008
Location: Ghent, Belgium
Distribution: Fedora, CentOS
Posts: 632

Original Poster
Rep: Reputation: 33
Quote:
Originally Posted by chrism01 View Post
3. use a lang like Perl that has a DB API
Php is also good ?
 
Old 05-14-2013, 06:30 AM   #13
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.8, Centos 5.10
Posts: 17,240

Rep: Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324
If that's what you want to use, sure.
 
Old 05-14-2013, 06:41 AM   #14
jonaskellens
Member
 
Registered: Jul 2008
Location: Ghent, Belgium
Distribution: Fedora, CentOS
Posts: 632

Original Poster
Rep: Reputation: 33
But I understand that php will take more resources of the server than bash.

That's why I would like to use bash, maybe like this :
Code:
cat myCSVfile |  IFS=',' read -r a b c d; do mysql -uMyUser -pMyPassword -h ip_databaseserver -D My-DB -e "insert into MyTable (fieldA, fieldB, fieldC, fieldD) values ( $a, $b, $c, $d );"; done
You recon it would work (and be faster than php) ?
 
Old 05-14-2013, 07:12 AM   #15
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.8, Centos 5.10
Posts: 17,240

Rep: Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324
bash is interpreted, I believe php is not, so php should be faster & less resources.
 
1 members found this post helpful.
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Why does this work from the bash command line and then fails in a bash script? Rupadhya Linux - Newbie 5 09-26-2012 01:05 AM
[SOLVED] Using a long Bash command including single quotes and pipes in a Bash script antcore Linux - General 9 07-22-2009 12:10 PM
Can MySQL log on via SSH/bash? mysql:x:27:101:MySQL Server:/var/lib/mysql:/bin/bash Ujjain Linux - Newbie 2 04-24-2009 03:21 PM
Bash script run via cron not executing MYSQL command mackstar Linux - Server 4 04-23-2009 06:01 AM
Is 'nice' inherited to child processes? e.g. bash script/php script that calls MySQL SirTristan Linux - Newbie 1 12-04-2008 01:57 AM


All times are GMT -5. The time now is 10:23 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration