PHP connection to MySQL error

Question

I keep on getting this error : Warning: mysqli_connect(): (HY000/1045): Access denied for user '12345'@'localhost' (using password: YES). I have already tried to create a new user, using a password, using no password, granting privileges to new user. I can login to the mysql database using these credentials. I can run every command with them in MySQL, just not connect to PHP. If it matters, this is my php code

<? php 
$dbServername = "localhost";
$dbUsername = "12345";
$dbPassword = "12345";
$dbName = "loginsystem";

$conn = mysqli_connect($dbServername, $dbUsername, $dbPassword, $dbName);

When showing grants for user: '12345'@'localhost':

mysql> SHOW GRANTS FOR '12345'@'localhost';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for 12345@localhost                                                                                                                                                                                                                                                                                                                                                              |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `12345`@`localhost` |
| GRANT BACKUP_ADMIN,BINLOG_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN ON *.* TO `12345`@`localhost`                                                                                                  |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

/etc/my.cnf file (INSIDE MYSQL) :

[mysqld_safe]
[mysqld]
secure_file_priv="/Users/Steven/"

/etc/my.cnf file (INSIDE xAMPP):

#password   = your_password
port        = 3306
socket      = /Applications/XAMPP/xamppfiles/var/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
user = mysql
port=3307
socket      = /Applications/XAMPP/xamppfiles/var/mysql/mysql.sock
skip-external-locking
key_buffer = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
innodb_force_recovery = 1

Oh I forgot to mention I did flush privileges and restarts of server.

Try `FLUSH PRIVILEGES` on the MySQL server to force the reload off the users and privileges in memory.. — Raymond Nijland, Jul 10 '18 at 19:57
Possible duplicate of [Warning: mysqli\_connect(): (HY000/1045): Access denied for user 'username'@'localhost' (using password: YES)](https://stackoverflow.com/questions/25174183/warning-mysqli-connect-hy000-1045-access-denied-for-user-usernameloca) — Will B., Jul 10 '18 at 19:58
If you have access to the command line, can you access the mysql server with `mysql -u 12345 -p`? — aynber, Jul 10 '18 at 19:59
yes I can @ aynber. And the duplicate @fyrye is because there is no answer on there that solves my problem. — Steven, Jul 10 '18 at 20:01
@Steven What does `SELECT \`user\`, \`host\` FROM \`mysql\`.\`user\`;` display when you run it from the console as `root` user? — Will B., Jul 10 '18 at 20:04
One last thing (and then I'm at a loss), once you're logged in on the command line, do you get any errors when you type `use loginsystem`? — aynber, Jul 10 '18 at 20:06
@fyrye. I get the mysql.infoschema, mysql.session, mysql.sys, root, 12345 for users. All of the host are localhost. — Steven, Jul 10 '18 at 20:09
@Steven please update your question with MySQL output of `SHOW GRANTS FOR '12345'@'localhost';` — Will B., Jul 10 '18 at 20:13
@Steven throwing these out there. What host operating system are you using for your web server/MySQL server? Where is the MySQL server service running in relation to your web server (are they on different networks/domains)? Additionally try replacing `localhost` with `127.0.0.1` in your PHP script, if that does not work, add `12345@127.0.0.1` as a mysql user with the same grants, leaving the `localhost` user account and try again. Also try with your local DHCP IP address e.g. `192.168.1.20`. Please post the contents of your system `hosts` file. — Will B., Jul 11 '18 at 02:34
@Steven as a last ditch effort, try creating the user with a wildcard `%`, so that MySQL will allow the connection from any host. `GRANT ALL PRIVILEGES ON *.* TO '12345'@'%' IDENTIFIED BY '12345';` and try again. Otherwise please post your MySQL configuration file. `/etc/my.cnf` — Will B., Jul 11 '18 at 02:41
@fyrye I am running on a mac, and I'm using xAMPP to connect to mySQL and it's hosting my PHP Server. Everything is hosted locally on my computer. I have tried all the grant privileges commands — Steven, Jul 11 '18 at 14:45
hello @Steven in my experience xampp gives error on Mac (at least in my opinion) so i used MAMP. You can check it if you want. BUT going to the point of ur error: try this connection code please: $db_user = "root"; $db_pass = "root"; $dbh = new PDO('mysql:host=localhost;dbname=login', $db_user, $db_pass); foreach($dbh->query('SELECT * from login') as $row) { print_r($row); } — Alejo_Blue, Jul 11 '18 at 15:07
@Alejo_Blue the rest of my php code is not pdo, would that matter? — Steven, Jul 11 '18 at 15:11
Try `mysqli_connect('localhost', '12345', '12345', 'loginsystem', 3307);` Though find it curious that the default argument is showing invalid user credentials instead of timing out. — Will B., Jul 11 '18 at 15:19
@fyrye I still get ' Warning: mysqli_connect(): (HY000/1045): Access denied for user 'root'@'localhost' (using password: YES) in ...' Connect Error: Access denied for user 'root'@'localhost' (using password: YES) — Steven, Jul 11 '18 at 15:23
do a test and find out. Also @Steven i was wondering if with your current code can you actually see if the connection is properly working fine? let us know — Alejo_Blue, Jul 11 '18 at 15:25
I'm pretty sure the connection is what's not working. It can't establish a connection between the mysql database and the code, but I shall try your code right now. @Alejo_Blue — Steven, Jul 11 '18 at 15:27
@Alejo_Blue Fatal error: Uncaught PDOException: SQLSTATE[HY000] [1045] Access denied for user — Steven, Jul 11 '18 at 15:29
@Steven let's try this code and let me know if it worked: $user = '12345'; $password = '12345'; $db = 'loginsystem'; $host = 'localhost'; $port = your_port-might-be-3306; $link = mysqli_init(); $success = mysqli_real_connect( $link, $host, $user, $password, $db, $port ); echo $success; — Alejo_Blue, Jul 11 '18 at 15:33
@Alejo_Blue Thanks I think my connection went through because the error no longer shows up, however my php code was supposed to insert information into the database but it doesn't seem to appear which makes me unsure. — Steven, Jul 11 '18 at 15:49
did the code i sent you shows a 1 ? the $success variable shows a 1 if the connections is working. If so, then your connection works. Now depends on your insert statement if the entried are saved on the database — Alejo_Blue, Jul 11 '18 at 15:50
Where is it supposed to display a 1? I'm kind of new to this but my php code is in combination with a website that allows me to sign up and log in. My error was happening when I tried signing up and that would add my information into the database. — Steven, Jul 11 '18 at 15:53
@Alejo_Blue I decided to purposely use the wrong password this time, and it still went through so I don't think it's actually connecting. — Steven, Jul 11 '18 at 15:59
@Steven it is because `mysqli_init` initializes the `mysqli` instance prior to connecting, which is what throws the error in `mysqli_connect`. You would need to display the error after `mysqli_real_connect();` by using `if ($err = mysqli_connect_error()) { echo $err . ' - ' . mysqli_connect_errno();}` See:http://php.net/manual/en/mysqli.real-connect.php You would also have to change references to `$conn` to use `$link` — Will B., Jul 11 '18 at 16:12
@fyrye Actually I created a new project and just tested the php file by itself and I got a 1. — Steven, Jul 11 '18 at 16:23
@Alejo_Blue I used MAMP, and I was able to connect successfully using the password "root" for some reason works... I retried it on xAMPP, and it won't work. Also, my databases using PHPMyADMIN on MAMP don't seem to connect with my databases in mySQL. — Steven, Jul 11 '18 at 16:32
`mysqli_real_connect` returns `true` or `false`, `mysqli_init()` returns a `mysqli` object., see: http://php.net/manual/en/mysqli.init.php — Will B., Jul 11 '18 at 16:35
glad it workd @Steven would you mark my response as ANSWER since it helps you to achieve this? lmk — Alejo_Blue, Jul 11 '18 at 16:53
Problem resolved. the xAMPP and MAMP passwords to connect using PHP have nothing to do with mySQL at all. They connect to their respective PHPMyAdmin databases. Anyone have a clue on how I can connect to mySQL instead? I need to access the databases and tables I have setup in mySQL. — Steven, Jul 11 '18 at 16:54
@Alejo_Blue Sure, but how do I mark your response as answer if it's a comment? — Steven, Jul 11 '18 at 16:56
@Steven Confused about your statement of "XAMPP/MAMP passwords to connect". XAMPP and MAMP are package installers of (A)pache, (M)ySQL, (P)HP, and user interfaces to manage the configuration settings of each service. [phpMyAdmin](https://www.phpmyadmin.net/) is a PHP based web application that connects to MySQL, to provide a management interface for MySQL databases, tables, records, and users. The MySQL server that phpMyAdmin connects to should be the same as PHP, which was installed by MAMP/XAMPP. I suggest checking your services to ensure you are running/connecting to the desired instances. — Will B., Jul 11 '18 at 18:30
@fyrye Sorry to be confusing but in phpMyAdmin, I can't see my database that I have created in mySQL. — Steven, Jul 11 '18 at 18:32
I am thinking that something is misconfigured on your system, like you have multiple instances of MySQL installed in different locations. In your console try `find / -name mysql` and see what shows up. — Will B., Jul 11 '18 at 18:33

score 0 · Accepted Answer · answered Jul 11 '18 at 16:57

Hello @Steven in my experience xampp gives error on Mac (at least in my opinion) so i used MAMP. You can check it if you want. BUT going to the point of ur error this seems to be a connection issue: try this connection code please:

$user = '12345'; 
$password = '12345'; 
$db = 'loginsystem'; 
$host = 'localhost'; 
$port = your_port-might-be-3306; 
$link = mysqli_init(); 
$success = mysqli_real_connect( $link, $host, $user, $password, $db, $port ); 
echo $success;

If the $success variable shows a 1 if the connections is working. If so, then your connection works.

PHP connection to MySQL error

1 Answers1