Access denied for user 'user'@'localhost' to database

Question

I'm presently reading the book "Head First jQuery" and came across this bit of code :

mysql_connect('127.0.0.1', 'runner_db_user', 'runner_db_password')
    OR die( 'Could not connect to database.');
mysql_select_db('race_info');

But since mysql_connect is deprecated, I decided to use mysqli_connect via

mysqli_connect('127.0.0.1','runner_db_user','runner_db_password','race_info')
    or die('Error connecting to Database');

However I get an error saying

Warning: mysqli_connect(): (HY000/1044): Access denied for user 'runner_db_user'@'localhost' to database 'race_info' in E:\wamp\www\Z_jquery\ch9\service.php on line 2

I created the user 'runner_db_user'@'localhost' and a database for it to use, using:

create database race_info;

CREATE USER 'runner_db_user'@'localhost' IDENTIFIED BY 'runner_db_password';
GRANT SELECT,INSERT,UPDATE,DELETE ON race_info.* TO 'runner_db_user'@'localhost';

I've confirmed using phpMyAdmin that both the user exists and it has the privileges granted by the above commands. Why then am I not able to connect to the database?

NOTE : I also was able to manually connect to the database using said login credentials via command line:

mysql -u runner_db_user -p

Why am I then unable to connect to the database via mysqli_connect()? Are there any further permission that I need to provide to the user to connect successfully?

EDIT : This is the present condition of my users. Are there any anonymous users that may cause this problem, according to the best answer here? If so, how do I delete these users?

@Script47 OP: *"But since mysql_connect is deprecated, I decided to use mysqli_connect via..."* — Funk Forty Niner, Aug 14 '16 at 15:10
@Script47 Please read the second para of the question. I'm already using `mysqli_connect()`, which is where the problem arises. I can successfully connect using `mysql_connect()`. — Somenath Sinha, Aug 14 '16 at 15:11
`hfjq_race_info` was your db in mysql_ and you're using `race_info` for mysqli_. @SomenathSinha — Funk Forty Niner, Aug 14 '16 at 15:11
@Fred-ii- Consider it a typo on my part. I created the database with an altered name of `race_info`. — Somenath Sinha, Aug 14 '16 at 15:13
Try giving all access like - `GRANT ALL PRIVILEGES ON *.* TO 'runner_db_user'@'%' WITH GRANT OPTION;` — Abhishek Ginani, Aug 14 '16 at 15:21
@AbhishekGinani That would mean providing an user with previledges that I do not wish for it to possess, and would be counter productive. — Somenath Sinha, Aug 14 '16 at 15:22
I just want you to test it by giving all permission. If this not works then comment out `bind-address` in your mysql config. — Abhishek Ginani, Aug 14 '16 at 15:24
@AbhishekGinani Did it. Didn't have any effect on the connection., i.e., still can't connect. Also, how do I remove these permissions now? Do I have to delete this user and make a new one? — Somenath Sinha, Aug 14 '16 at 15:26
Change your host in `mysqli_connect` from `127.0.0.1` to `localhost`. — hjpotter92, Aug 14 '16 at 15:27
@hjpotter92 Did it. Query successful, but still can't connect. Problem still not solved. — Somenath Sinha, Aug 14 '16 at 15:33
@AbhishekGinani I haven't explicitly provided runner_db_user file previledges. Does that have something to do with my problem? — Somenath Sinha, Aug 14 '16 at 15:44
@hjpotter92 I haven't explicitly provided runner_db_user file previledges. Does that have something to do with my problem? — Somenath Sinha, Aug 14 '16 at 15:44
I did the same thing as you've shown on my local MySQL servers (both in Windows as well as debian), and I am facing no issues with connections. Please check spelling etc. mistakes. — hjpotter92, Aug 14 '16 at 15:50
@hjpotter92 something strange just happened. I temporarily granted "FILE" previledges to my user and suddenly it started working. I then dropped the user and made a new user (in an attempt to recreate the problem and the solution) and now it works even without any FILE previledges. I honestly have no idea what just happened. — Somenath Sinha, Aug 14 '16 at 15:52
It is not uncommon to have residue info in some of the info schema tables — Drew, Aug 14 '16 at 16:04

Access denied for user 'user'@'localhost' to database

0 Answers0