MySQL Bugs: #464: mysqladmin root password setup problem

Bug #464	mysqladmin root password setup problem
Submitted:	20 May 2003 14:12	Modified:	1 Jul 2003 5:47
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.13	OS:	Windows (windows xp)
Assigned to:	Venu Anuganti	CPU Architecture:	Any

Description:
Hi,

I've got a problem when i try to set the root password for my mysql server.

Here is how i proceed to install mysql 4.0.13:
> mysqld-max-nt --install

( i save 'my.cnf' file in C:\ using 'my-large.cnf' file provided with mysql 4.0.13 )
I start the mysql service :
> net start mysql

I follow exactly the documentation procedure to set the root password :
> mysqladmin -u root password <my_password>
> mysqladmin reload
> mysqladmin -u root -h localhost password <my_password>
> mysqladmin reload

then i try to shutdown server using mysqladmin :
> mysqladmin --user=root --password=<my_password> shutdown

but here i've got the following error message :
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user: 'root@127.0.0.1' (Using password: YES)'

What is wrong

How to repeat:

Thank you for the bug report. The sequence you had used in our Manual not
works as expected with the introduction of new privileges tables.
Below an example I did for to set the root password in a fresh
install:

C:\mysql\bin>mysql -uroot mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.13-max-nt-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> delete from user where user="";
Query OK, 2 rows affected (0.01 sec)

mysql> delete from user where host="localhost";
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE user SET Password=PASSWORD('mypass') WHERE User='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

C:\mysql\bin>mysqladmin -uroot -pmypass shutdown 

Also please read the item using GRANT/REVOKE commands.

thanks a lot ! It is working well now.

Mysql rocks.

To make mysqld work as documented you can apply the following fix to
the function check_connections(THD *thd) in .../sql/sql_parse.cpp

#if !defined(HAVE_SYS_UN_H) || defined(HAVE_mit_thread)
    /* Fast local hostname resolve for Win32 */
    if (!strcmp(thd->ip,"127.0.0.1"))
/* fix by Orjan Petersson, Logcode SARL
   the following line used to be 
      thd->host=(char*) localhost;
   but we need to set the host_or_ip field as well to avoid problems
   when e.g. changing passwords from mysqladm
 */
      thd->host_or_ip= thd->host=(char*) localhost;
    else
#endif

Btw, the status field of this bug is empty

Note that the fix above also makes SET PASSWORD work. Without the patch we have:

C:\mysql\bin>mysql -u root mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.0.13-max-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select user, host, password from user;
+------+-----------+----------+
| user | host      | password |
+------+-----------+----------+
| root | localhost |          |
+------+-----------+----------+
1 row in set (0.00 sec)

mysql> SET PASSWORD=PASSWORD('tata');
ERROR 1133: Can't find any matching row in the user table
mysql> quit

Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html

Will be fixed in 4.0.14
(Thanks to Örjan Petersson to find the reason for the bug)

i have tried

C:\mysql\bin>mysql -u root mysql
i get the error 

1130: host 127.0.0.1 is not allowed to connect to this mysql server

Any suggestions anyone

cheers

Hmm, this is supposed to be fixed but I have the same problem (v5.0.18)...
I can change the password of the root but not of users that I added myself

I've created a new data base in MySQL and can you tell me Is it possible to have a password for that data base. ..and  Is there any difference between user, root password?

I've created a new data base in MySQL and can you tell me Is it possible to have a password for that data base. ..and  Is there any difference between user, root password?

@miguel: thanks a lot.. u r  a saviour