MySQL-command OK from commandline, notOK from bash-script

jonaskellens · 05-13-2013, 12:22 PM

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 ?

parnmatt · 05-13-2013, 12:40 PM

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

jonaskellens · 05-13-2013, 12:45 PM

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.

parnmatt · 05-13-2013, 12:48 PM

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.

jonaskellens · 05-13-2013, 01:04 PM

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

chrism01 · 05-13-2013, 06:27 PM

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.

jonaskellens · 05-14-2013, 03:14 AM

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)

jonaskellens · 05-14-2013, 05:00 AM

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 ?

chrism01 · 05-14-2013, 05:11 AM

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

chrism01 · 05-14-2013, 05:18 AM

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

jonaskellens · 05-14-2013, 05:18 AM

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.

jonaskellens · 05-14-2013, 05:20 AM

Quote:

Originally Posted by chrism01

3. use a lang like Perl that has a DB API

Php is also good ?

chrism01 · 05-14-2013, 05:30 AM

If that's what you want to use, sure.

jonaskellens · 05-14-2013, 05:41 AM

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) ?

chrism01 · 05-14-2013, 06:12 AM

bash is interpreted, I believe php is not, so php should be faster & less resources.