SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 33
  1. #1
    SitePoint Member
    Join Date
    Apr 2003
    Location
    Atlanta
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Angry changing password for root MySQL user

    Hi...I am following Yank's Database Driven Website book and trying to install MySQL 4.0.12 and cannot seem to change the root password.

    I am following the procedure described on pages 27-28. I type in mysqladmin -u root password "anything" and it takes it. Then I type in mysqladmin -u root reload and am just returned back to the prompt. From there on...when I try mysqladmin -u root -p status, it prompts me for a status, when I type in anything, get "access denied for user....". If I type in the same command, but just hit enter for password, then I get the status.

    This tells me that the password is not getting assigned.

    I am on a Win2000 laptop. Could it be that somehow I don't have access on my laptop to assign a password?

    Any help would be appreciated...

    Thanks.

  2. #2
    SitePoint Member
    Join Date
    Apr 2003
    Location
    Austin, TX
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy

    Quote Originally Posted by easterbran
    Hi...I am following Yank's Database Driven Website book and trying to install MySQL 4.0.12 and cannot seem to change the root password.
    I'm having EXACTLY the same problem!

    Hope someone can help us.

    Thanks!

  3. #3
    SitePoint Member
    Join Date
    Apr 2003
    Location
    Brazil
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Me too. I can not change root password.
    I have tried everything available so I give up.

    I assume it is a bug in this version 4.01.2 running on Windows 2K.

  4. #4
    gingham dress, army boots... silver trophy redux's Avatar
    Join Date
    Apr 2002
    Location
    Salford / Manchester / UK
    Posts
    4,838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    have a read through the relevant mysql documentation http://www.mysql.com/doc/en/Default_privileges.html

    You should also add a password for the root user. The following example starts by removing the anonymous user that has all privileges, then sets a root user password:

    C:\> C:\mysql\bin\mysql mysql
    mysql> DELETE FROM user WHERE Host='localhost' AND User='';
    mysql> QUIT
    C:\> C:\mysql\bin\mysqladmin reload
    C:\> C:\mysql\bin\mysqladmin -u root password your_password
    re·dux (adj.): brought back; returned. used postpositively
    [latin : re-, re- + dux, leader; see duke.]
    WaSP Accessibility Task Force Member
    splintered.co.uk | photographia.co.uk | redux.deviantart.com

  5. #5
    SitePoint Member
    Join Date
    Apr 2003
    Location
    Brazil
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Redux. I tried all you pointed. Still unable to change the password.

    At this point I'm jamed. Wiil need Kevin Yank to take a close look at this.

  6. #6
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    It looks like the folks behind MySQL changed the default user setup in MySQL 4.0. The instructions in the book were written for MySQL 3.23.

    As of MySQL 4.0, there are two root users in the default user table -- one that handles connections from localhost, and one that handles all other exceptions. When you follow the instructions in my book to set the root password, the password is assigned to the second account, which leaves the localhost root account unprotected.

    To solve this problem, log into MySQL and delete the root user for localhost:

    mysql> DELETE FROM mysql.user WHERE Host='localhost' AND User='root';

    This should leave the password-protected root user in the database, which will then handle all connections -- including those from localhost.

    I'll look into this more closely in the next few days and put something up on the book's Errata page.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  7. #7
    gingham dress, army boots... silver trophy redux's Avatar
    Join Date
    Apr 2002
    Location
    Salford / Manchester / UK
    Posts
    4,838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    heh...pretty much what i said previously
    re·dux (adj.): brought back; returned. used postpositively
    [latin : re-, re- + dux, leader; see duke.]
    WaSP Accessibility Task Force Member
    splintered.co.uk | photographia.co.uk | redux.deviantart.com

  8. #8
    SitePoint Member
    Join Date
    Apr 2003
    Location
    illawong
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yep, i have tried all of the above and still no joy. my machine spits "unable to change password; error: 'cant find any matching row in the user table' back at me. Any more suggestions???

    senad

  9. #9
    SitePoint Member
    Join Date
    Apr 2003
    Location
    Austin, TX
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by hardactive
    yep, i have tried all of the above and still no joy. my machine spits "unable to change password; error: 'cant find any matching row in the user table' back at me. Any more suggestions???

    senad
    Same here...

  10. #10
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Just thought I should mention, the second print run of the 2nd edition of the book has just gone into production, and it features updated instructions in this area.

    I'll be posting this to the Errata page for the book shortly, but in the meantime, here are the updated instructions:
    To set a root password for MySQL, type the following command in the bin directory
    of your MySQL installation:
    Code:
    mysql -u root mysql
    This command connects you to your newly-installed MySQL server as the root user, and chooses the mysql database. After a few lines of introductory text, you should see the MySQL command prompt (mysql>). To assign a password to the root user, type the following three commands (pressing Enter after each one):
    Code:
    mysql>SET PASSWORD FOR root@localhost=PASSWORD("new password");
    Query OK, 0 rows affected (0.00 sec)
    mysql>SET PASSWORD FOR root@"%"=PASSWORD("new password");
    Query OK, 0 rows affected (0.00 sec)
    mysql>FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.00 sec)
    Be sure to replace both instances of new password with the password you want to assign to your root user. The first command sets the password required when connecting from the machine on which the server is running; the second sets the password for all other connections.

    With that done, disconnect from MySQL with the quit command:
    Code:
    mysql>quit
    Bye
    Now, to try out your new password, at the system command prompt again, request that the MySQL server tell you its current status:
    Code:
    mysqladmin -u root -p status
    Enter your new password when prompted. You should see a brief message that provides information about the server and its current status. The -u root argument tells the program that you want to be identified as the MySQL user called root. The -p argument tells the program to prompt you for your password before it tries to connect. The status argument just tells it that you're interested in viewing the system status.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  11. #11
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I was having the same problem and I was able to find this thread. It worked for me. Thanks.
    Community Guidelines | Community FAQ

    "He that is kind is free, though he is a slave;
    he that is evil is a slave, though he be a king." - St. Augustine

  12. #12
    SitePoint Member
    Join Date
    Jul 2003
    Location
    Boulder, CO
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am still trying to get this to work. (yes, I'm new this end of it) I have the newest edition of the book, and have tried the above method, with no success. On on Mac OS 10.2.6. When I try to connect to the MySQl server from the bin directory, as Kevin noted above:

    mysql -u root mysql

    I just get:

    mysql: Command not found.

    Typing: /usr/local/mysql/bin/mysql
    does get me to the mysql prompt. But, when I try to follow the SET PASSWORD command, I just get this error:

    ERROR 1044: Access denied for user: '@localhost' to database 'mysql'

    I'd really love to move on to the next chapter... any help would be appreciated.

    Thanks,
    J-

  13. #13
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Try connecting to MySQL with:

    /usr/local/mysql/bin/mysql -u root mysql

    Then use the SET PASSWORD command.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  14. #14
    SitePoint Member
    Join Date
    Jul 2003
    Location
    Boulder, CO
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Unfortunately that isn't working either. Now I'm getting this error:

    ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

    I also now get that error when I try to connect the way I had before. I don't know what changed, since I gave up right before making my first post on friday.

    Is there a way to totally remove the MySQL which I installed, and just start over?

  15. #15
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Deleting the /usr/local/mysql directory and all of its contents should do the trick.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  16. #16
    SitePoint Member
    Join Date
    May 2004
    Location
    Lisbon
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy ERROR 1045: Access denied for user:

    I am still trying to get this to work. I have the newest edition of the book, and have tried the above methods, with no success. I am using Mac OS X 10.3.4 When I try to connect to the MySQl server from the bin directory, like in the book:

    /usr/local/mysql/bin/mysql -u root mysql

    I get:

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

    Typing: /usr/local/mysql/bin/mysql
    does get me to the mysql prompt. But, when I try to follow the SET PASSWORD command it goes:

    ERROR 1044: Access denied for user: '@localhost' to database 'mysql'

    I really could use help!

    Thankz,
    Antonio

  17. #17
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Antonio, those results tell me that a password has been set for the root user on your MySQL server, and you need to provide it when connecting to the server.

    /usr/local/mysql/bin/mysql -u root -p mysql

    This will connect to the server, prompting you for the password.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  18. #18
    SitePoint Member
    Join Date
    May 2004
    Location
    Lisbon
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Another error

    Quote Originally Posted by Kevin Yank
    Antonio, those results tell me that a password has been set for the root user on your MySQL server, and you need to provide it when connecting to the server.

    /usr/local/mysql/bin/mysql -u root -p mysql

    This will connect to the server, prompting you for the password.
    Thank you Kevin, for your prompt reply.

    Ok, now, when I insert the password I get:

    ERROR 1049: Unknown database 'mysql'

    What should I do now?

    Thank you again for your time.

    Antonio

  19. #19
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Antonio,

    It sounds like you've done some things to your server that were not included in my installation instructions. If you had followed my instructions, for example, you wouldn't have been trying to connect to your server without a password after you had already set one.

    Strictly speaking, the error message you're seeing claims that the 'mysql' database (the database that tracks users and their permissions for the server) does not exist. That's a strange state of affairs, since the installation process on Mac OS X should create that database. If it doesn't exist, it suggests you deleted it.

    I'm not accusing you of anything, here, I just think the symptoms you're seeing indicate you're off the beaten track. I'm no Mac OS X expert, and there isn't much I can tell you that you can't read for yourself in the official MySQL installation instructions for Mac OS X.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  20. #20
    SitePoint Member
    Join Date
    May 2004
    Location
    Lisbon
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Kevin,

    I will delete all files and install it again. My total newbie state can't let me say for sure that I haven't deleted something inadvertedly.
    I will try it again.
    Thanks

  21. #21
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Best of luck. If my instructions don't work for you, do try following the official instructions. They should be identical, but who knows -- it may be a case of unclear wording on my part on some subtle point that will make the difference.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  22. #22
    SitePoint Member
    Join Date
    May 2004
    Location
    Lisbon
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I did everything by the book.
    When I got to the part:
    SET PASSWORD FOR root@"%"=PASSWORD("new password");

    I got this error:
    ERROR 1133: Can't find any matching row in the user table

    What could it be?
    Thanks

  23. #23
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Do a select * from mysql.user and see what user entries the table contains. Anything?
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  24. #24
    SitePoint Member
    Join Date
    May 2004
    Location
    california
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Kevin Yank
    Antonio, those results tell me that a password has been set for the root user on your MySQL server, and you need to provide it when connecting to the server.

    /usr/local/mysql/bin/mysql -u root -p mysql

    This will connect to the server, prompting you for the password.
    Kevin, I've been trying to get this going all day!!! This last comment did indeed promt me for a password which I was able to use and connect. THANK YOU. But now my question is what do I do from here. Is this the way to log on everytime? I'm incredibly confused....

  25. #25
    SitePoint Member
    Join Date
    May 2004
    Location
    california
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Kevin, I've been trying to get this going all day!!! This last comment did indeed promt me for a password which I was able to use and connect. THANK YOU. But now my question is what do I do from here. Is this the way to log on everytime? What have I done wrong in the set up? I'm incredibly confused....


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •