Skip

Knocked Out Before The Bell Rang
May 16, 2006 7:18 AM   Subscribe

I'm encountering what I imagine is an entirely rudimentary SQL error, but am baffled as to how to solve it.

I've just installed MySQL 4.1.18-standard on my Mac (10.4.6) to work along with David Heinemeier Hansson's _Agile Web Development with Rails_. I'm absolutely new to MySQL and Ruby, and right out of the box (literally, like the fourth page after things really start getting instructive) I've encountered the following error after the command mysql -u root -p

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

During installation I never set a password. I've input 'admin', 'root' and other guesses in as the password - the error is always the same. If I provide a carriage return without entering anything, the error changes slightly:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

I've found interesting stuff by searching, but some sources seem to contradict others, or take for granted an expertise I don't possess - I have absolutely no background in MySQL or the command line. I'm looking for absolutely thorough and remedial steps for remedying this problem.

Local delicacies happily mailed to the home of the kind soul that gets me through this.
posted by boombot to Computers & Internet (80 answers total) 1 user marked this as a favorite
 
Did you try "mysql -u root" without the -p switch?
posted by justkevin at 7:24 AM on May 16, 2006


I have. It pops back an error similar (I'm not in front of my computer) to the second one listed:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
posted by boombot at 7:37 AM on May 16, 2006


Ahhh, the old MySQL default password problem. It will get you everytime (at least it gets me and I have to lookup the answer).

I'd start here:

From the link:

For Windows, do this:

shell> mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR 'root'@'%' = PASSWORD('newpwd');

For Unix, do this:

shell> mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR 'root'@'host_name' = PASSWORD('newpwd');

In the second SET PASSWORD statement, replace host_name with the name of the server host. This is the same hostname that you used when you assigned the anonymous account passwords.

To assign passwords to the root accounts using mysqladmin, execute the following commands:

shell> mysqladmin -u root password "newpwd"
shell> mysqladmin -u root -h host_name password "newpwd"

These commands apply both to Windows and to Unix. In the second command, replace host_name with the name of the server host. The double quotes around the password are not always necessary, but you should use them if the password contains spaces or other characters that are special to your command interpreter.

You can also use UPDATE to modify the user table directly. The following UPDATE statement assigns a password to both root accounts at once:

shell> mysql -u root
mysql> UPDATE mysql.user SET Password = PASSWORD('newpwd')
-> WHERE User = 'root';
mysql> FLUSH PRIVILEGES;

The UPDATE statement applies both to Windows and to Unix.

After the passwords have been set, you must supply the appropriate password whenever you connect to the server.
posted by McGuillicuddy at 7:39 AM on May 16, 2006


My guess is that you probably skipped any setup involved with MySQL. You should probably go through this part of the MySQL Reference Book to secure the initial accounts.

Also, in OS X you have to specify the username/password combo in your /config/database.yml file. You should probably not use the root username/password for your database.
posted by BioCSnerd at 7:40 AM on May 16, 2006


The problem with most approaches to this is that you need to connect as the root mysql user in order to change the password of the root mysql user. It seems like a catch 22.

But you can get around it because you have root access to your box. This lets you stop and start the MySQL process. On startup, you can tell it to let anyone in without checking their ID at the door.

I don't have MySQL on my mac, sadly, so the exact commands I give you might need some fudge. But here's what you need to do within a small margin of error.

First, stop the mysql process. Open a terminal, type

sudo su -

and give your user password to become root. Then do a

killall mysql

that should shut down all mysql processes. You can check this my doing a

ps -auxw | grep mysql

If nothing show's up you're all set. Next find the mysql startup script on your mac. I don't know where it is, so here's some fudge. You'll want to run it from the command line, and pass it the 'skip grant tables' option like so:

/path/to/script/your-startup-script-name --skip-grant-tables

(On my Unix box this would be '/etc/init.d/mysql --skip-grant-tables', though it is certainly different for you). Then you should be able to connect to mysql as root and follow the steps in McGuillicuddy's link. Sorry I can't give you exact commands.
posted by amery at 8:16 AM on May 16, 2006


There's a variation on the stop service/special init method I used when I forgot my root password described in the MySQL manual: Resetting Permissions. Give it a shot if amery's method doesn't work for you, although it looks like it should.
posted by moift at 8:26 AM on May 16, 2006


Would I be able to edit the file BioCSnerd refers to with BBEdit, or should I take the amery/McGuillicuddy hybrid approach?
posted by boombot at 9:09 AM on May 16, 2006


Or you can just uninstall mysql and re-install it again :)
posted by empath at 9:32 AM on May 16, 2006


(taking care to note what you set your root password to be, of course)
posted by empath at 9:32 AM on May 16, 2006


re. Absolutely. database.yml is just a flat text file. I use TextMate personally, but BBEdit will handle it just fine.
posted by BioCSnerd at 9:34 AM on May 16, 2006


amery, re: the MySQL startup script, it seems from your comment that I'd be looking for a file named mysql. Is that the case?
posted by boombot at 11:21 AM on May 16, 2006


Amery,
Also, after executing ps -auxw | grep mysql the following is returned. Your instructions seemed indicate that nothing should been returned.
root 357 0.0 -0.0 27804 656 ?? S 8:11PM 0:00.03 /bin/sh ./bin/mysqld_safe --datadir=/usr/local/mysql/data --pi

mysql 376 0.0 -0.6 61644 13136 ?? S 8:11PM 0:00.64 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/m

root 453 0.0 -0.0 27368 424 p1 S+ 8:28PM 0:00.01 grep mysql
Why is this being returned?
posted by boombot at 5:32 PM on May 16, 2006


Because mysqld_safe is a process that looks to see if mysql is running, and if it isn't, it starts up again. Try doing a

sudo killall -9 mysqld_save
sudo killall -9 mysqld

and then do the

ps -auxw | grep mysql

again. I'd foolishly forgotten that it will indeed return one result, namely itself (like your third line above).

Then, as root, do a

/usr/local/mysql/bin/mysqld --skip-grant-tables.

That should work for you.
posted by amery at 5:51 PM on May 16, 2006


Er, the first killall should be

sudo killall -9 mysqld_safe
posted by amery at 5:51 PM on May 16, 2006


McGillicuddy has it; mysqladmin -u root password nEwpAssWord in Terminal should do the trick if you've never set the root password in mysql before.

Alternatively, just get Locomotive for the love of God. You can learn how to fix the plumbing later, for now you're just trying to wash your dishes in the sink. Or something like that.
posted by evariste at 8:34 PM on May 16, 2006


You might have to prefix that with sudo, I imagine.

So sudo mysqladmin -u root password nEWpASsWOrd, and your mac will request your account's password.
posted by evariste at 8:37 PM on May 16, 2006


Just to second moift's link; I've been installing mysql on a FC5 server and that set of instructions was the only thing that allowed me to get off the ground. I feel your pain.
posted by primer_dimer at 4:36 AM on May 17, 2006


evariste, I'll try mysqladmin -u root password nEwpAssWord first, it's certainly simpler.
posted by boombot at 8:14 AM on May 17, 2006


I'd like to take a moment to itemize the Amery/McGuillicudy method, just so I'm clear on the particulars. In this block, since I'm running on my home machine, what is my host_name?
shell>sudo killall -9 mysqld_safe
shell>sudo killall -9 mysqld
shell>ps -auxw | grep mysql
shell>/usr/local/mysql/bin/mysqld --skip-grant-tables.
shell>mysql -u root
mysql>SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
mysql>SET PASSWORD FOR 'root'@'host_name' = PASSWORD('newpwd');
Have I left anything out, or is this series of inputs the entire process?
posted by boombot at 8:14 AM on May 17, 2006


Everiste,
sudo mysqladmin -u root password nEWpASsWOrd returned
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
Any thoughts?
posted by boombot at 6:42 PM on May 17, 2006


Amery,
/usr/local/mysql/bin/mysqld --skip-grant-tables now returns
060517 23:46:19 [Warning] Can't create test file /usr/local/mysql-standard-4.1.18-apple-darwin8.2.0-powerpc/data/Home.lower-test

/usr/local/mysql/bin/mysqld: Can't change dir to '/usr/local/mysql-standard-4.1.18-apple-darwin8.2.0-powerpc/data/' (Errcode: 13)

060517 23:46:19 [ERROR] Aborting

060517 23:46:19 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
Why would the Terminal be unable to create the files in question, or change the directory?
posted by boombot at 8:48 PM on May 17, 2006


boombot-I want to install the same version/distribution of mysql as you on my MacBook and try this for myself. Where did you get it?

Sorry you're having such a hard time.
posted by evariste at 12:04 AM on May 18, 2006


Well, I'm downloading 4.1.19 from mysql AB's website. Close enough :-)
posted by evariste at 12:13 AM on May 18, 2006


Straight from the manual:
In a Unix environment, the procedure for resetting the root password is as follows:

Log on to your system as either the Unix root user or as the same user that the mysqld server runs as.

Locate the .pid file that contains the server's process ID. The exact location and name of this file depend on your distribution, hostname, and configuration. Common locations are /var/lib/mysql/, /var/run/mysqld/, and /usr/local/mysql/data/. Generally, the filename has the extension of .pid and begins with either mysqld or your system's hostname.

You can stop the MySQL server by sending a normal kill (not kill -9) to the mysqld process, using the pathname of the .pid file in the following command:
shell> kill `cat /mysql-data-directory/host_name.pid`

Note the use of backticks rather than forward quotes with the cat command; these cause the output of cat to be substituted into the kill command.

Create a text file and place the following command within it on a single line:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPassword');

Save the file with any name. For this example the file will be ~/mysql-init.

Restart the MySQL server with the special --init-file=~/mysql-init option:
shell> mysqld_safe --init-file=~/mysql-init &

The contents of the init-file are executed at server startup, changing the root password. After the server has started successfully you should delete ~/mysql-init.

You should be able to connect using the new password.
posted by evariste at 12:16 AM on May 18, 2006


Everiste,
I have tried, and failed, to find the .pid file. Spotlight returns nothing (well it returns lots, but no file with the extension .pid). After installation of 4.1.19, were you able to find the file?
posted by boombot at 6:16 AM on May 18, 2006


boombot-I downloaded the disk image but never actually installed it; doing that now.
posted by evariste at 12:15 AM on May 20, 2006


boombot-I just installed MySQL, and it seems to be working out pretty good. I installed it, then I started System Preferences and dragged the .prefpane in the .dmg into it to install the Preferences Pane. I started MySQL from there, had to give my OS X account password twice, then went back to Terminal and did /usr/local/mysql/bin/mysql -uroot and got a mysql> prompt.

I don't know what to suggest you do other than install the same version I did and try to get it going; it seems to be working perfectly for me and I can set the root password if I choose to.
posted by evariste at 12:25 AM on May 20, 2006


\q gets you back out of the mysql prompt when you're done with it, of course.
posted by evariste at 12:28 AM on May 20, 2006


In other words, on installation the password was set to nothing, and I was able to log in to mysql as root with no problem and full rights to do whatever I want. Maybe there's a bug or misconfiguration-by-default in the version you installed?
posted by evariste at 12:32 AM on May 20, 2006


Everiste,
How would I uninstall the version I'm currently "running"?
posted by boombot at 7:38 PM on May 20, 2006


boombot-

cd /usr/local
sudo rm mysql [this deletes the symlink]
sudu rm -rf mysql- [and then hit tab so your Terminal autocompletes the rest of it (it's a long name with a version number and such).]
posted by evariste at 7:41 PM on May 20, 2006


On my system it came out to sudo rm -rf mysql-standard-4.1.19-apple-darwin8.5.1-i686/ but since you installed a different version, it'll be some other long string.
posted by evariste at 7:44 PM on May 20, 2006


Thanks for the quick response, and for keeping an eye on this question.
I wanted to confirm that the second command begins with sudu, rather than sudo, and also wanted to ask what website you're referring to when you mention you got your copy of MySQL 4.1.19 "from mysql AB's site"?
posted by boombot at 7:44 PM on May 20, 2006


Nope, it's sudo. I'm an idiot :-)
posted by evariste at 7:45 PM on May 20, 2006


This one right here, from this page. MySQL AB is the German (?) company that wrote MySQL, and mysql.com is their website.
posted by evariste at 7:47 PM on May 20, 2006


If I might ask where you downloaded your version, I'm curious to know...did it perhaps come with the book you bought on CD/DVD?
posted by evariste at 7:50 PM on May 20, 2006


I followed this. Speaking of, do I need to do anything to undo the post-MySQL install modifications he lists, which I did do, or will they not factor into anything during the uninstall and reinstall?
posted by boombot at 7:58 PM on May 20, 2006


Nope. The only modification I see on that page is adding mysql to your path. The Ruby mysql package he has you install should work with any version of mysql.
posted by evariste at 8:03 PM on May 20, 2006


The only modification I see on that page is adding mysql to your path.
Which is harmless to leave alone, since it's using the /usr/local/mysql symlink that will be recreated when you install the 4.1.19 version.
posted by evariste at 8:04 PM on May 20, 2006


Excellent. So, then:
  1. Run the Install Package
  2. Open System Preferences, drag the PrefPane file from the mounted image to the System Preferences window
  3. Start the MySQL Server from the MySQL Preference Pane
  4. I'll then be prompted to provide my account password
How then do I properly set the root password?
posted by boombot at 8:35 PM on May 20, 2006


In Terminal,

mysql -uroot

mysql>SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpassword');
posted by evariste at 8:44 PM on May 20, 2006


Should I delete the existing MYSQL PrefPane? If so, does it matter if I do that before or after the uninstall?
posted by boombot at 8:48 PM on May 20, 2006


Sure, delete it, and it shouldn't matter whether it was before or after. Right click and remove it.
posted by evariste at 8:53 PM on May 20, 2006


By the way, rereading the thread a bit, I saw you asked how to find out what your hostname is. Just type hostname into your Terminal and it'll tell you. If you want to change it, click on Sharing in System Preferences and change it there.
posted by evariste at 10:44 PM on May 20, 2006


The uninstall seemed to go well, I entered the commands and removed the PrefPane without incident. After a restart, I then downloaded and installed 4.1.19, which went fine, but I now have an issue with PrefPane.

I dragged the Pane into the Preferences, and chose to install it only for one user. A dialog box popped up, titled "Preferences Error", with the text "Could not load MySQL", with an OK button beneath. The Pane is in place, but this error is all that is returned when I click it. I haven't tried to do anything from the command line, I thought I would post first. Any thoughts?
posted by boombot at 2:23 PM on May 21, 2006


Hmm...
posted by evariste at 2:48 PM on May 21, 2006


Try starting it from your terminal like this:olivia:~ evariste$ sudo /usr/local/mysql/bin/mysqld_safe &
[1] 1010
olivia:~ evariste$ Starting mysqld daemon with databases from /usr/local/mysql/data
hit enter when you get this message to get your prompt back.
posted by evariste at 2:55 PM on May 21, 2006


[1] 1010 is a reply from the shell, not part of the command.

So basically, do

sudo /usr/local/mysql/bin/mysqld_safe &
posted by evariste at 2:56 PM on May 21, 2006


What I'm trying to determine is whether or not you can start the mysql server at all, from the command line. The Pref Pane is "nice" but not essential, and I have no idea why it's giving you that error.
posted by evariste at 3:02 PM on May 21, 2006


If that works, then try sudo mysql -uroot next to see if you can log in without a password.
posted by evariste at 3:04 PM on May 21, 2006


sudo /usr/local/mysql/bin/mysqld_safe & has a reply of

[1] 312

and then I'm back to the prompt.
posted by boombot at 3:25 PM on May 21, 2006


Perfect! So now sudo /usr/local/mysql/bin/mysql -uroot, and you should have a mysql prompt. I'm crossing my fingers that it doesn't give you the password error.
posted by evariste at 3:43 PM on May 21, 2006


I closed the terminal window. Starting from a new window, do I need to input both commands in succession, or can I now just input the second command?
posted by boombot at 3:51 PM on May 21, 2006


The server should still be running since you started it in the background (that's what the & at the end of the command was about), so you should be able to just do the second command now.
posted by evariste at 3:55 PM on May 21, 2006


After sudo /usr/local/mysql/bin/mysql -uroot, I recive the reply

/usr/local/mysql/bin/mysql: /usr/local/mysql/bin/mysql: cannot execute binary file
posted by boombot at 4:07 PM on May 21, 2006


Holy crap. Wha?

Try this:

olivia:~ evariste$ file /usr/local/mysql/bin/mysql

and tell me what you see. It should say this:

/usr/local/mysql/bin/mysql: Mach-O executable i386
posted by evariste at 4:11 PM on May 21, 2006


That does kick back /usr/local/mysql/bin/mysql: Mach-O executable i386
posted by boombot at 4:14 PM on May 21, 2006


OK, that's good. Then try this one:

olivia:~ evariste$ ls -alp /usr/local/mysql/bin/mysql

It should return:
-rwxr-xr-x 1 root wheel 1271176 Apr 30 07:15 /usr/local/mysql/bin/mysql
posted by evariste at 4:19 PM on May 21, 2006


What I was trying to determine before (with the file command) was whether or not you had the x86 executable; the only reason I could think of for the "cannot execute binary file" was that you somehow had the wrong kind of binary, perhaps PPC. Now I'm trying to figure out if your binary mysql executable file is the same size as the working file that's on my system; if it's not then something went wrong somewhere (and this probably starts to explain why the Pref Pane has an error).
posted by evariste at 4:21 PM on May 21, 2006


That's returns -rwxr-xr-x 1 root wheel 1271176 Apr 30 08:15 /usr/local/mysql/bin/mysql

But I think the problem is my mistake. Did you link to the Intel binary? I didn't think to check - I'm running a G5.
posted by boombot at 4:25 PM on May 21, 2006


OMG, I'm sorry. I did link to the Intel binary; I assumed that since I have an Intel Mac, then of course so do you.

I am a moron and I will sacrifice fifty chickens in atonement for wasting your time :-)
posted by evariste at 4:28 PM on May 21, 2006


No, no, it's my mistake. I'm a dumbass.

So, do I uninstall in the same way?
  • cd /usr/local
  • sudo rm mysql
  • sudo rm -rf mysql- [then hit tab]
  • Ecit the terminal, dump the PrefPane

posted by boombot at 4:33 PM on May 21, 2006


To uninstall this Intel binary, repeat the procedure above, then install the PowerPC binary from here.
posted by evariste at 4:33 PM on May 21, 2006


Yup, you got it.
posted by evariste at 4:34 PM on May 21, 2006


OK, so everything seems to have gone well. I installed the proper version of MySQL (I may never forgive myself for that mistake), entered mysql via mysql -uroot, then entered

SET PASSWORD FOR 'root'@'Home.local' = PASSWORD('***');

The terminal returned Query OK, 0 rows affected (0.01 sec). Before I try to see if I can execute my original command, I want to make sure that there's nothing else I need to do first.
posted by boombot at 5:46 PM on May 21, 2006


Nope. I think you're in business. Crack a beer!
posted by evariste at 5:49 PM on May 21, 2006


Oy. So, now, mysql -u root -p still returns the original error
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
But I'm able to enter MySQL via mysql -u root. After creating my tables, though, another command returns this error, which would seem related to the aforementioned issue
ERROR 1044 (42000): Access denied for user '@'localhost' to database 'depot_development'
If you run screaming from this thread, I'll certainly understand.
posted by boombot at 6:38 PM on May 21, 2006


OK, when you did this:

SET PASSWORD FOR 'root'@'Home.local' = PASSWORD('***');

did you also do this?

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('***');
posted by evariste at 6:44 PM on May 21, 2006


Cos if you're able to get into MySQL with mysql -uroot without typing a password, then you never assigned a password for root connecting from localhost.
posted by evariste at 6:46 PM on May 21, 2006


I thought I had to switch out 'localhost' with the actual name of the local host. I just entered the line with 'localhost' in place, and while it was accepted, I still get the same error (1044). I'll uninstall and try it again.

After starting up MySQL from the PrefPane, I should have done the following, exactly as it appears, save for *** being replaced with the pwd?
shell>mysql -urootmysql>SET PASSWORD FOR 'root'@'localhost' = PASSWORD('***');
Do I need to provide the SET PASSWORD FOR 'root'@'Home.local' = PASSWORD('***'); line, or anything else, or after that am I set?
posted by boombot at 7:20 PM on May 21, 2006


Don't uninstall, you're on the right track.
posted by evariste at 7:23 PM on May 21, 2006


I thought I had to switch out 'localhost' with the actual name of the local host.

Nah, "localhost" just means "127.0.0.1" or "this computer".

Looking at this error:

ERROR 1044 (42000): Access denied for user '@'localhost' to database 'depot_development'

I am puzzled. Was there really no user name there, just user '@'localhost'?
posted by evariste at 7:26 PM on May 21, 2006


Do I need to provide the SET PASSWORD FOR 'root'@'Home.local' = PASSWORD('***'); line, or anything else, or after that am I set?

I wouldn't bother with that, just localhost. When MySQL sees you connecting from a Terminal on the same machine using the socket (rather than a port), then you're root@localhost (or someuser@localhost).
posted by evariste at 7:28 PM on May 21, 2006


Yep, just 'user'. With that said, do you think I should reinstall? It's not sweat.
posted by boombot at 7:48 PM on May 21, 2006


Err..., no sweat.
posted by boombot at 7:52 PM on May 21, 2006


No, don't reinstall. You'll just get right back to the same place.

It should say "access denied for user [someuser]@localhost", not just access denied for user.

Try

mysql -uroot -p

Put in your password, then

mysql>\u depot_development

Does that give you an error?
posted by evariste at 7:53 PM on May 21, 2006


Ideally it would say this:

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>

posted by evariste at 7:55 PM on May 21, 2006


If that worked, then your next step would be to do

grant all on depot_development.* to 'your os x username'@'localhost' identified by 'somepassword';
posted by evariste at 8:07 PM on May 21, 2006


With no small amount of disbelief, it would seem that I'm up and running. Thank you so much, evariste, for hanging in there. I'll happily send you an email to arrange shipment of the long-ago promised local delicacies.
posted by boombot at 2:37 PM on May 22, 2006


Hooray! No need for the delicacies, I wish you good luck learning Rails :-)
posted by evariste at 3:25 PM on May 22, 2006


« Older Should we get our parents a gi...   |  Is it possible to move up and ... Newer »
This thread is closed to new comments.


Post