ERROR 1133: Can't find any matching row in the user table

Hello, I’m running OS X.3, with PHP (4.3 I think) on Apache (1.2 I think), and have installed MySql 4.0.15.
I’m going through the Kevin Yank book:build your own database… 2nd edition:

Have had a lot of trouble getting started, including starting MYSQL with

> Mysql -u root mysql

(which gives ERROR 1045: Access denied for user: ‘root@localhost’ (Using password: NO)

Though it seems to work with

> Mysql -u root -p

So, on page 22 of the book I’m trying to assign passwords to the root user, but when I enter the second line:

mysql> SET PASSWORD FOR root@localhost=PASSWORD(“beejay22”);

I get the error message:

ERROR 1133: Can’t find any matching row in the user table

Can anyone help? Please? This MySQL thing is proving inpenetrable!

Ben[COLOR=DarkRed]

You say you can start with: Mysql -u root -p from your shell. Do that. You will then see the mysql> prompt.


mysql> use database mysql
mysql> select user, hostname, password from user
  

the mysql database is where you keep your user profiles. The table they are kept in is called user.

You will see that if you don’t have a password set up, and you are allowing any access to your database, you can then be hacked if you are connected to the internet and someone would want to hack you.

To set a password you can do:


mysql> update user, set password=('password') where user=root
  

change the second password above to the password you actually want to use. This password is then encripted so it won’t show up the same if you did

You should set up a user aside from root as well. Don’t forget to use the flush privileges command too.

More details see:
http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Database_Administration.html#User_Account_Management

Thanks so much, but i only got to enter this first line at the mysql prompt:

mysql> use database mysql

and was returned:

ERROR 1049: Unknown database ‘database’

What am i missing?

i started mysql…

tried this line as before:

mysql> SET PASSWORD FOR root@localhost=PASSWORD(“password”);
Query OK, 0 rows affected (0.07 sec)

Then this didn’t work as before:

mysql> SET PASSWORD FOR root@“%”=PASSWORD(“password”);
ERROR 1133: Can’t find any matching row in the user table

so then I went to your code:

mysql> select user, hostname, password from user
-> update user, set password=(‘password’) where user=root

and as you suggested

-> FLUSH PRIVILEGES
->

I know you don’t really get feedback from this app, but have I successfully set the root password? ie was the correct database (mysql) selected by default or something?
Why didn’t this work?

mysql> use database mysql

Thankyou,

Ben

Ben, sorry about that, too early in the morning with my post. It should have just been


 mysql> use mysql
 

If you try to connect now use:

mysql -uroot -p***** and substitute your password for the ***** of course. If you connect successfully you have then set your root password. You won’t have connected to a database though.

This:


   mysql> SET PASSWORD FOR root@"%"=PASSWORD("password");
   ERROR 1133: Can't find any matching row in the user table
  

would have failed if you didn’t have % in the hosts field for user root. Unless you are trying to access your database from an outside server you don’t need to set that.