Cannot log in to account on mysql

Question

I am trying to log in using PHP and using PHPMyAdmin, but I can only log in using my root account. It doesn't matter what permissions the other account has, I just cannot log in to it, even if it has all the permissions. I am using Apache24 as web server.

Error message:

mysqli_real_connect(): (HY000/1045): Access denied for user 'noah'@'localhost' (using password: YES)

I have tried so many of the solutions I could find on Google, but not a single one is relevant to my situation, not did it help solve anything.

I have not edited any configuration files, except my PHP files to include all the extensions in there. So I already have mysqli there too.

Note: I also get the following error at the same time on the same screen: PHP was built without openssl extension, can't send password encrypted

That doesn't help, I'm not that dumb. I have tried multiple accounts with very complex passwords too. — Noah Verkaik, Oct 04 '18 at 15:42
It's not a duplicate as I said "I have tried so many of the solutions I could find on Google, but not a single one is relevant to my situation, not did it help solve anything." :/ — Noah Verkaik, Oct 04 '18 at 15:43
May not be duplicate in your case. Did you try out literally everything? The grants, processes, etc? Did it ever work? — Praveen Kumar Purushothaman, Oct 04 '18 at 15:45
I really tell you, if I am opening it now, it may be closed soon. I'll open the question and see if you get some response. — Praveen Kumar Purushothaman, Oct 04 '18 at 15:46
I can only log in to the root account no matter what I try. I tried using the commands, I followed a bunch of youtube fixes and read through a few sites. Nothing seems to solve the problem. — Noah Verkaik, Oct 04 '18 at 15:46
^ Dude. There you go. So you can log in as root but not as a user, right? — Praveen Kumar Purushothaman, Oct 04 '18 at 15:47
Check this out may be? https://webmasters.stackexchange.com/q/34594 — Praveen Kumar Purushothaman, Oct 04 '18 at 15:47
Uhhh... `:(` All the best man... I am definitely gonna follow up with this question. — Praveen Kumar Purushothaman, Oct 04 '18 at 15:50

M4ST3RX · Answer 1 · 2018-10-04T18:53:15.367

0

I had this problem too and searched the internet for a whole day till I found a solution. To connect from localhost you have to set the computer name to localhost instead of % when you create a new account in phpmyadmin. So you will have 2 users with the same name but different computer name.

EDIT: Here is a picture what should you have. Also make sure if you want to use password protection that config.inc.php has $cfg['Servers'][$i]['auth_type'] = 'cookie';

Image of phpMyAdmin

edited Oct 04 '18 at 18:53

answered Oct 04 '18 at 15:51

M4ST3RX

23
7

I cannot make a % when there's already a localhost and the other way around. – Noah Verkaik Oct 04 '18 at 16:05
You should be able to as you can see I have it. – M4ST3RX Oct 04 '18 at 18:53

score 0 · Answer 2 · answered Oct 04 '18 at 15:54

0

Be aware that if you are accessing from your machine you need an user 'user'@'localhost' while to access from external network you need a user 'user'@'%'

root has both access enabled by default, can it be your problem?

answered Oct 04 '18 at 15:54

truffolone

494
4
7

I cannot make a % when there's already a localhost and the other way around. – Noah Verkaik Oct 04 '18 at 16:05
Yes you can. you create a duplicate of the localhost user with the '%' host. May I ask you what is your MySQL version? are you using something like MariaDB? (It shouldn't matter but better be safe) – truffolone Oct 30 '18 at 14:04

Ninjakitteh · Answer 3 · 2018-10-04T16:04:52.907

0

if you try using the root credentials in your php application, does it work? Then after adding the permissions, did you remember to run 'flush privileges'?

can you also post a copy of the mysql users table so we can see how the permissions are assigned? (just ommit the password)

edited Oct 04 '18 at 16:04

answered Oct 04 '18 at 15:59

Ninjakitteh

21
7

with the openssl issue, try installing the openssl package for your OS – Ninjakitteh Oct 04 '18 at 16:03
never installed that in the first place. how do I do this on windows? – Noah Verkaik Oct 04 '18 at 16:04
are you using wamp or xampp or iis? – Ninjakitteh Oct 04 '18 at 16:05
I am using apache. I installed Apache, PHP and MySQL seperately, not using something like xxampp. – Noah Verkaik Oct 04 '18 at 16:05
on windows 7, 8 or 10? also can you send me the download links you used to download apache, mysql and php – Ninjakitteh Oct 04 '18 at 16:11
"can you".."are assigned?" - The root and test account are the exact same there except the user column – Noah Verkaik Oct 04 '18 at 16:13
and windows 10. – Noah Verkaik Oct 04 '18 at 16:13
also if you try logging in via cli to the mysql server, does it work with the user? – Ninjakitteh Oct 04 '18 at 16:15
I used the official websites. I used https://dev.mysql.com/downloads/windows/installer/8.0.html, the Apache24 files from http://httpd.apache.org/download.cgi and the PHP files from https://windows.php.net/download#php-7.2. – Noah Verkaik Oct 04 '18 at 16:18
command line interface. so like command prompt. you should be able to log into your msql server. usually 'mysql -u -p – Ninjakitteh Oct 04 '18 at 16:20
what i can rather recommend is download wamp and use that instead to develop on. This issue may be a wider issue than just mysql user permissions. You may be missing php modules needed to talk to the mysql server as well and i have no idea how you can recompile php on windows to add it. added benefit of wamp is it includes everything with an easy to use interface and sets up phpmyadmin so you can use it straight out of the box. – Ninjakitteh Oct 04 '18 at 16:25
I am able to log in using the CLI, yes. – Noah Verkaik Oct 04 '18 at 16:25
still kind of want to know what the problem is though :p – Noah Verkaik Oct 04 '18 at 16:26
ok. what you can try is to create a phpinfo file and see if the php-mysqli module is being loaded. – Ninjakitteh Oct 04 '18 at 16:27
where do I look? I only see loaded modules for apache – Noah Verkaik Oct 04 '18 at 16:30
search the page for mysqli. it should have a heading with mysqli and its settings. if you cannot find it, the issue is that the mysqli module is not compiled with your version of php – Ninjakitteh Oct 04 '18 at 16:33
if you log into the cli with the user and do a show databases; , does it show any databases? – Ninjakitteh Oct 04 '18 at 16:34
mysqli is there on the page – Noah Verkaik Oct 04 '18 at 16:35
I'm just going to use WAMP to not waste more of your time. Thanks for your help! – Noah Verkaik Oct 04 '18 at 16:36
dont forget to mark as resolved if you dont require any further feedback – Ninjakitteh Oct 11 '18 at 09:13

score 0 · Answer 4 · answered Oct 06 '18 at 12:40

can you try to log in to MySQL on machine from command line (such as mysql -u -p). If succeeded, please also try with -h option by using your private IP. It is important to understand if it is a '%','localhost' problem. Phpmyadmin may being use only one of them (% or localhost) which not exist in your users table for the specified user. Also please share your mysql.users table content.

Cannot log in to account on mysql

4 Answers4