Creating a Database

jamesmont · June 11th, 2009, 10:14 PM

I'm having trouble with creating the database on page 87 of chapter 3. I downloaded the code from the site, so I know it's not a code issue. Also, if I use the MySQL root username and password I have no problems. If I use the book's suggested username (bp6am) and password (bp6ampass) I get the following message:

Access denied for user 'bp6am'@'localhost' to database 'moviesite'.

Can someone tell me what I'm doing wrong?

Thanks,
James

ribbet · March 14th, 2010, 06:29 PM

Quote:

Originally Posted by jamesmont

I'm having trouble with creating the database on page 87 of chapter 3. I downloaded the code from the site, so I know it's not a code issue. Also, if I use the MySQL root username and password I have no problems. If I use the book's suggested username (bp6am) and password (bp6ampass) I get the following message:

Access denied for user 'bp6am'@'localhost' to database 'moviesite'.

Can someone tell me what I'm doing wrong?

Thanks,
James

Hi James,

I am having the same problem as above. Did you work out how to fix this?
Cheers,
ribbt

PeterPeiGuo · March 14th, 2010, 06:36 PM

Did you create the user and give the proper grant?

ribbet · March 14th, 2010, 06:43 PM

Quote:

Originally Posted by PeterPeiGuo

Did you create the user and give the proper grant?

Hi Peter,

Entered the following in mysql>

GRANT SELECT, INSERT, UPDATE ON *.*
TO [email protected]
IDENTIFIED BY "bp6ampass";
(Query OK, 0 rows affected <0.05sec>)

Any other ideas?

Thanks,
ribbet

PeterPeiGuo · March 14th, 2010, 07:21 PM

I tested with this:

Code:

grant all privileges on *.* to 'bp6am' with grant option;

Login as root and do the above. I assume you already created user bp6am first.

ribbet · March 14th, 2010, 07:52 PM

Quote:

Originally Posted by PeterPeiGuo

I tested with this:

Code:

grant all privileges on *.* to 'bp6am' with grant option;

Login as root and do the above. I assume you already created user bp6am first.

tried the above and get "can't find any matching row in the user table".

does the input: GRANT SELECT,INSERT,UPDATE ON *.* TO [email protected] actually create the actual user account or is there something before this that i may have missed?

Thanks

PeterPeiGuo · March 14th, 2010, 08:00 PM

No, grant does not create account. You have to first do the following:

Code:

CREATE USER 'bp6am' IDENTIFIED BY 'bp6ampass'

Then grant.

Afterwards you can use mysql client to test whether you can login as bp6am and whether bp6am can see the table.

ribbet · March 14th, 2010, 11:15 PM

Okay, I tried the following so that i could start again:

Code:

drop user 'bp6am';
delete from mysql.user where host="localhost" and user="bp6am";

Logged in as 'root', and did the following:

Code:

CREATE USER 'bp6am' IDENTIFIED BY 'bp6ampass';
grant all privileges on *.* to 'bp6am' with grant option;
GRANT SELECT, INSERT, UPDATE ON *.*
TO [email protected]
IDENTIFIED BY "bp6ampass";

I can login successfully through a command prompt using the above and can also execute the following:

Code:

show tables from mysql;

I also copied the chapter code to rule out my input error.

However, I am still getting am still getting the error:
"Access denied for user 'bp6am'@'localhost' to database 'moviesite' "

Anything else i may have missed? Are there any other tests i could run that require administrative privileges?

ribbet · March 15th, 2010, 02:14 AM

Scouring through mysql online documentation and it occured to me that 'grant all' is a higher level than 'grant insert, select etc..

Seeing the "Movie database successfully created!" was like a dream come true!

Cheers,
ribbet

plussong · October 10th, 2010, 11:11 PM

GRANT CREATE,SELECT, INSERT, UPDATE ON *.*

add "CREATE" ,it works.