root pass wont change

[7B4]

I have installed 5.0.18 on FreeBSD 5.3. from ports.
No root passord set for mysql on any host.
Please help. I have no idea. Can I just >make deinstall reinstall; or would
I need to delete some database files first?


shell>/usr/local/bin/mysqld_safe --skip-grant-tables &
shell>mysqladmin -u root password 'mypassword'
mysqladmin:
You cannot use 'password' command as mysqld runs
with grant tables disabled (was started with --skip-grant-tables).
Use: "mysqladmin flush-privileges password '*'" instead
shell> mysqladmin flush-privileges password 'mypassword'
mysqladmin: unable to change password; error: 'Can't find any matching row
in the user table'

I also try (with --skip-grant-tables)

mysql> UPDATE user SET Password=PASSWORD('mypassword') WHERE
Host='localhost' AND User='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> SELECT * from user WHERE User='root';
Empty set (0.00 sec)

mysql> select * from user;
+--------------------------------------------------------------+------------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
| Host | User
| Password | Select_priv | Insert_priv |
Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv |
Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv |
Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv
| Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv |
ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions |
max_updates | max_connections |
+--------------------------------------------------------------+------------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
| localhost | root
| | Y | Y | Y
| Y | Y | Y | Y | Y | Y
| Y | Y | Y | Y | Y

[Bill Karwin]

"7B4" <a@a.com> wrote in message news:43f40e94@dnews.tpgi.com.au...

>I have installed 5.0.18 on FreeBSD 5.3. from ports.
> No root passord set for mysql on any host.
> Please help. I have no idea.

There are examples of several methods to reset the MySQL root password on
this page:
[url]http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html[/url]

I think the third method may be the best for your situation. Remember to
FLUSH PRIVILEGES after every time you update the mysql.user table.

Also read the user comments, they're useful and informative.

Regards,
Bill K.

[Chander Ganesan]

First off, it seems odd that if this were a default installation there
would be only 1 user.

Secondly, your query should work - unless you've got something odd
going on (for example, there are some "extra" characters in the user or
host fields).

Why don't you start the server with the --skip-grant-tables option.
Then add a user (using an insert statement). Give the user all
privileges and a password - see if that works. You'll essentially
creating a new "root" user.

If that works, delete your current root user and start over.

Another possibility is that the user that the server runs as doesn't
have write access (for some reason..) to the user table (permissions
problem, perhaps?) Though when this sort of thing occurs you should
see some output in your error logs...and the server typically won't
start.

You can also just re-initialize the database (mysql_install_db) - once
again, make sure the permissions are set correctly for the user that
the server runs as before starting it.

--
Chander Ganesan
Open Technology Group, Inc.
Expert MySQL Training - at your site or ours
[url]http://www.otg-nc.com/training-courses/category.php?cat_id=1[/url]

[7B4]

Thanks for your direction.

As usual it was stupid user error. I was starting the server without the
user option. When I started with user mysql everything was good to go.

Again, thanks your input certainly sorted me out.

"7B4" <a@a.com> wrote in message news:43f40e94@dnews.tpgi.com.au...

>I have installed 5.0.18 on FreeBSD 5.3. from ports.
> No root passord set for mysql on any host.
> Please help. I have no idea. Can I just >make deinstall reinstall; or
> would I need to delete some database files first?
>
>
> shell>/usr/local/bin/mysqld_safe --skip-grant-tables &
> shell>mysqladmin -u root password 'mypassword'
> mysqladmin:
> You cannot use 'password' command as mysqld runs
> with grant tables disabled (was started with --skip-grant-tables).
> Use: "mysqladmin flush-privileges password '*'" instead
> shell> mysqladmin flush-privileges password 'mypassword'
> mysqladmin: unable to change password; error: 'Can't find any matching row
> in the user table'
>
> I also try (with --skip-grant-tables)
>
> mysql> UPDATE user SET Password=PASSWORD('mypassword') WHERE
> Host='localhost' AND User='root';
> Query OK, 0 rows affected (0.00 sec)
> Rows matched: 0 Changed: 0 Warnings: 0
>
> mysql> SELECT * from user WHERE User='root';
> Empty set (0.00 sec)
>
> mysql> select * from user;
> +--------------------------------------------------------------+------------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
> | Host | User |
> Password | Select_priv | Insert_priv |
> Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv |
> Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv |
> Index_priv | Alter_priv | Show_db_priv | Super_priv |
> Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv
> | Repl_client_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject |
> max_questions | max_updates | max_connections |
> +--------------------------------------------------------------+------------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
> | localhost | root |
> | Y | Y | Y | Y | Y | Y |
> Y | Y | Y | Y | Y | Y
> | Y | Y
>
>
>