Access denied for user:

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Access denied for user:

Post by mikebr »

Something I can't seem to find a solution to,
I am trying to populate a table with some information from a simple text file on my hard drive, the file has it's permissions set to: Owner - "read & write", Group - "read only" and Others - "read only" but when I enter the following line:
LOAD DATA INFILE "/Users/user/personal.in.txt" INTO TABLE personal;
I get the error:
ERROR 1045: Access denied for user: '@localhost' (Using password: NO)
the only solution I can seem to find to this error is to set the file permissions to 644 which I believe they are set to?

Mike
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

probably not a file-system but a db-permission problem
'@localhost' needs write permission to perform LOAD DATA.
Did you use the mysql-client shipping with mysql and forgot the -u<user> -p parameters?
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Post by mikebr »

OK, I am using Jaguar with terminal to access MySQL, I don't know of any MySQL client that comes with the install, when setting up the first run of MySQL I enered the following:

mysqladmin -u root password a_password

then pressed return, nothing happened after doing that, no OK, or acknowledgment of the password that I can remember, I take it this is what you mean when you ask:
Did you use the mysql-client shipping with mysql and forgot the -u<user> -p parameters?
Mike
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

nope, I wanted to know from where you're giving the command 'LOAD DATA INTO....'.
I'm not familiar with macOS and uncertain what "terminal to access MySQL" is.
But ...user: '@localhost' (Using password: NO) means there was no authentication. you may change the GRANT-settings for that 'user' but I'd rather suggest to setup a user-account with password and use this

http://www.mysql.com/doc/en/Adding_users.html
User avatar
llimllib
Moderator
Posts: 466
Joined: Mon Jul 01, 2002 2:19 pm
Location: Baltimore, MD

Post by llimllib »

Mike, what happens when you open a terminal window and type 'mysql'? How about if you type 'mysql -uroot'?
Try reading the user notes on mysql's web page. Unfortunately, it doesn't look like they have mac install instructions. Let me know what happens when you try this.
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Post by mikebr »

MySQL seems to be working as I can make entries add tables etc with terminal.

When I enter ´mysql´and press return nothing happens, same with ´mysql -u root´ I also tried ´/usr/local/bin/mysql´ and then tried them all adding ´;´after each, the basics of MySQL seem straight forward but it's frustrating because there seems to be no info on using MySQL with Terminal.

Thanks
Mike
User avatar
llimllib
Moderator
Posts: 466
Joined: Mon Jul 01, 2002 2:19 pm
Location: Baltimore, MD

Post by llimllib »

if you type '/usr/local/bin/mysql' and get nothing, then mysql is not installed correctly (assuming that is the correct path). Try reinstalling; Hopefully somebody from phpmac will tell you how to do that.
As for documentation for the terminal mode, the documentation is extensive. All that happens once you have the terminal open is that you are allowed to enter SQL statements. Mysql is very well documented at mysql.org; your issue is with the install, not the documentation.
I see a tutorial on installing it here for OS X, and that was the second result from google. I'd try installing it again if I was you; it builds character :)
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Post by mikebr »

I have discovered that to make the command '/usr/local/bin/mysql' or '/usr/local/bin/mysql -u root' do something "start MySQL" that MySQL should not be running or nothing will happen so the problem before must have been that it was up and running.

I took a look at the link http://www.mysql.org but see that it is for OSX prior to Jaguar, I am only learning at this so who am I to know but I read and followed instructions from: http://www.entropy.ch/software/macosx/mysql/ where it states two different types of installers for OSX and Jaguar, also a slightly different way of setting each up, for example there is no need to set up a user "on your computer" with Jaguar as you must do for OSX 10.1, after the installer has been run and a couple of commands have been entered they tell the user to type in the following:
mysqladmin -u root password new_password_here
which I did as instructed on the install and nothing happened (I don't know if anything was meant to happen?) anyway I have just tried starting MySQL with:
/usr/local/bin/mysql -u root password 'a_new_password'
and termial sprung to life as never before with:
./bin/mysql Ver 11.18 Distrib 3.23.51, for apple-darwin6.0 (powerpc)
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Usage: ./bin/mysql [OPTIONS] [database]

[SHORTCUTS]

Default options are read from the following files in the given order:
/etc/my.cnf /usr/local/mysql/data/my.cnf ~/.my.cnf
The following groups are read: mysql client
The following options may be given as the first argument:
--print-defaults Print the program argument list and exit
--no-defaults Don't read default options from any options file
--defaults-file=# Only read default options from the given file #
--defaults-extra-file=# Read this file after the global files are read

Possible variables for option --set-variable (-O) are:
connect_timeout current value: 0
max_allowed_packet current value: 16777216
net_buffer_length current value: 16384
select_limit current value: 1000
max_join_size current value: 1000000
now I have discovered with a bit of trial and error that I must open a database which it seems I have just done by entering:
/usr/local/bin/mysql test_holding;
(a database I created earlier) then I entered the same line from before:
LOAD DATA INFILE "/Users/user/Linux_sales.sql" INTO TABLE linux_sales;
and again got the error:
ERROR 1045: Access denied for user: '@localhost' (Using password: NO)
Is there any way of getting a list of passwords or users from MySQL when using on localhost?

Mike
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post by Wayne »

this will give you a list of all users & there permissions on the database. but will only work if you have permissions to view the mysql database

Code: Select all

USE mysql;
SELECT * FROM user;
you might need to login with

Code: Select all

mysql -h ipaddress -u root -p
if your user isnt set up with a localhost address in the user table
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

Okay, command line stuff for mysql

In windows it would be

c:\mysql\bin\mysql -umyusername -pmypassword mydatabasename

This will log you into a mysql database (mydatabasename) with the supplied username (myusername) and password (mypassword), this is no different from what you are trying to acheive.

If you just do

/usr/local/bin/mysql test_holding

it is not supplying a username and password, and the default user does not have the rights to run that command, you must supply a username and password with the correct privileges (look it up in the manual).

If you log into the mysql command line (c:\mysql\bin\mysql -uroot -pPassword) without specifiying a database then from the commnand line you can

mysql>USE mydatabasename;

to use a particular database to issue commands against,

it is all here in the manual http://www.mysql.com/doc/en/mysql.html Enjoy :wink:
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Post by mikebr »

I can't seem to get anywhere here with this problem, every time I try to use GRANT command or populate a database table with a text file I get the same error:
ERROR 1045: Access denied for user: '@localhost' (Using password: NO)
I don't have a problem accessing any of the databases except "mysql" where I get the same error. I can open and make additions even delete records but I can't GRANT or populate tables with text files. I have tried logging in as
/usr/local/bin/mysql;
also
/usr/local/bin/mysql -u root;
and
/usr/local/bin/mysql -u user_i_created;
but the error comes up every time I try to do the above mentioned tnings. I have looked at the mysql site and can't find anything that seems to relate to my error.

If I use the option from the last post:
/usr/local/bin/mysql -umyusername -pmypassword test_holding;
the following text comes up
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Usage: ./bin/mysql [OPTIONS] [database]
-?, --help Display this help and exit.

etc. etc.
but if I now try to use any command I get "command not found" errors as follows when trying to show the tables in the "test_holding" database opened above:
SHOW: Command not found.
or when I try to load the data file:
LOAD DATA INFILE "/Users/user/linux_sales.sql" INTO TABLE linux_sales;
I then get:
LOAD: Command not found.
I'd love to have have a stiff whiskey now but unfortunatly I don't drink!!!!
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

Is this your database or one that a host provides?

If provided by a host then it is doubtful you can login using root. It is very unlikely you have the privileges to use GRANT, LOAD etc and I doubt if the provider will give you GRANT privilege, they should however be able to give you the relevant privileges to LOAD stuff, you will need to drop them an email asking for this.
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Post by mikebr »

I am using Apache, php and MySQL on my personal computer (iBook), I am learning php and MySQl and have not reached the stage of working with this all on my server.
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

the following text comes up Quote:
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Usage: ./bin/mysql [OPTIONS] [database]
-?, --help Display this help and exit.

etc. etc.
is that ./bin/mysql relevant maybe try with that

/bin/mysql -umyusername -pmypassword test_holding

Do not put a ; on the end of that command, you only use ; once you are actually in the mysql command line, you are not at that point.
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Post by mikebr »

This might seem stupid to all you experianced users but I think the problems where all to do with how I was logging in to the database, it is clear now that I didn't fully understand the privelages that where needed and how to use them properly, I now seem to understand this better and when I actually log in as the user I created the database under the problems I was having disappeared. This was a very good lesson for me on how important it is to understand the privelage side of database developement.

Thanks to everyone who helped on this problem.

Mike
Post Reply