Resetting root password fails.

Hi,

I'm trying to setup a mysql server, but for some reason it refuses to
let me login as root.
I've tried resetting the password with (server running with
--skip-grant-tables):

UPDATE mysql.user SET password=PASSWORD("password") WHERE user="root";
FLUSH PRIVILEGES;

But this does not work (the commands do not update any rows).

Also tried using an init file with (mysqld_safe -–init-file="/tmp/rstpwd"):

SET PASSWORD FOR ‘root’@'localhost’ = PASSWORD("password");

This did not work either.

Does anyone have an idea?

Thanks,

Sytse.

On Sun, 06 Jul 2008 10:14:17 +0200, Sytse
<ssrree00@xxss44aallll.nnll> wrote:

>Hi,
>
>I'm trying to setup a mysql server, but for some reason it refuses to
>let me login as root.
>I've tried resetting the password with (server running with
>--skip-grant-tables):
>
>UPDATE mysql.user SET password=PASSWORD("password") WHERE user="root";
>FLUSH PRIVILEGES;
>
>But this does not work (the commands do not update any rows).
>
>Also tried using an init file with (mysqld_safe -–init-file="/tmp/rstpwd"):
>
>SET PASSWORD FOR ‘root’@'localhost’ = PASSWORD("password");
>
>This did not work either.

Form the manual:
The password format changed in MySQL 4.1, and under
certain circumstances it is possible that if you change
your password, you might not be able to connect to the
server afterward.

>Does anyone have an idea?

This seems to work for me:
GRANT USAGE ON *.* TO 'root'@'localhost' IDENTIFIED BY
'password';
GRANT ALL ON *.* TO 'root'@'localhost' IDENTIFIED BY
'password' WITH GRANT OPTION;

>Thanks,
>
>Sytse.

HTH
--
( Kees
)
c[_] You are about to enter another dimension, a dimension not only of
sight and sound but of mind. A journey into a wondrous land of
imagination. Next stop, Usenet (#163)

Kees Nuyt wrote:
> On Sun, 06 Jul 2008 10:14:17 +0200, Sytse
> <ssrree00@xxss44aallll.nnll> wrote:
>
>> Hi,
>>
>> I'm trying to setup a mysql server, but for some reason it refuses to
>> let me login as root.
>> I've tried resetting the password with (server running with
>> --skip-grant-tables):
>>
>> UPDATE mysql.user SET password=PASSWORD("password") WHERE user="root";
>> FLUSH PRIVILEGES;
>>
>> But this does not work (the commands do not update any rows).
>>
>> Also tried using an init file with (mysqld_safe -–init-file="/tmp/rstpwd"):
>>
>> SET PASSWORD FOR ‘root’@'localhost’ = PASSWORD("password");
>>
>> This did not work either.
>
> Form the manual:
> The password format changed in MySQL 4.1, and under
> certain circumstances it is possible that if you change
> your password, you might not be able to connect to the
> server afterward.
>
>> Does anyone have an idea?
>
> This seems to work for me:
> GRANT USAGE ON *.* TO 'root'@'localhost' IDENTIFIED BY
> 'password';
> GRANT ALL ON *.* TO 'root'@'localhost' IDENTIFIED BY
> 'password' WITH GRANT OPTION;
>
>> Thanks,
>>
>> Sytse.
>
> HTH

Sorry, this didn't work either.
I've got no idea what it was, but I made a rather messy freebsd
installation. I think it must have been something with permissions, or a
bad config file. I reinstalled freebsd, this seemed to work.

Regards,

Sytse