Craig McKendree asked 4 years ago
Hello,
I can usually debug and/or research error messages with good results but this one has me stumped.
I installed a LAMP server on a Raspberry Pi 4 and can successfully log into phpMyAdmin using both user ‘root’ and as user ‘admin’. All works with zero issues.
I followed tutorial ESP32/ESP8266 Publish Data to Raspberry Pi LAMP Server with a few issues I worked through but have one I’m failing to resolve.
After creating file esp-data.php and changing $dbname, $username and $password to my credentials I attempt to open the file in my web browser and get the following error message.
Connection failed: Access denied for user ‘root’@’localhost’ (using password: YES)
I’ve spent hours trying to resolve, checked spelling, checked mysql in the command window, tried both root user and admin user, searched stack overflow and can’t nail down what’s happening. It appears $conn is throwing a connect error and then jumps to the if statement.
$servername = "localhost";
// REPLACE with your Database name
$dbname = "esp_data";
// REPLACE with Database user
$username = "root";
// REPLACE with Database user password
$password = "my_password";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
MariaDB [(none)]> SELECT User, Host, plugin FROM mysql.user;
+————+———–+——–+
| User | Host | plugin |
+————+———–+——–+
| root | localhost | |
| phpmyadmin | localhost | |
| admin | localhost | |
+————+———–+——–+
Steve Mercer answered 4 years ago
If I’m understanding correctly you have a LAMP server on a RPi4 and you are trying to connect to it from an ESP32.
First off “localhost” means the computer you are running on. As this is the ESP32 that does not have MySQL server (MariaDB nowadays) running you will get a connect error. You need to change $servername = to the IP address of the RPi. You may still get an error if the user is not set to be able to logon from anywhere but we can get to that if you get that error.
Craig McKendree answered 4 years ago
@ Steve,
Thanks for the reply, l still have not resolved the connection issue but your response helped nudge me along to a better understanding of MariaDB. Nothing like a problem to help you understand what’s going on under the hood.
Will continue researching, albeit with a bit of frustration.
Rui Santos Staff answered 4 years ago
Hello Craig, sorry about that and I apologize for taking so long to get back to you.
Does that error happen when you open all URLs?
Does your admin or root user have all the permissions to access the database?
- The error “Connection failed: Access denied for user ‘root’@’localhost’ (using password: YES)” usually means that your database user doesn’t have the full permissions or that your password is wrong.
Thanks for your patience
_____________
About this guide: ESP32/ESP8266 Publish Data to Raspberry Pi LAMP Server
Craig McKendree answered 4 years ago
Hello Rui,
Thank you so much for the reply. I finally solved my issue by running my esp-data.php file from the command line. When doing this I received an error message “variable 12345 not recognized on line 21”. The password I was using was $12345. With php, variables start with the $ symbol and with my password starting with $ even though in double quotes, this caused the error. I changed the password and everything now works as is should.
Heads up to anyone writing php files, be aware of using $ symbol.
Thanks again! Much appreciation to all the hard work you and Sara put in. Wishing you success and will be purchasing another of your courses soon!
Steph answered 4 years ago
Hi Craig,
I’d rather make a small clarification so that you don’t get the wrong idea from the way the PHP code is written in Rui’s tutorial.
With PHP, the $ sign that prefixes variable names actually simplifies the interpolation of variables when they are injected into a literal string.
Therefore:
$name = 'Bob'; echo "Hello $name";
returns:
Hello Bob
Whereas:
$name = 'Bob'; echo 'Hello $name';
returns:
Hello $name
So don’t store your password in a string defined by double quotes but rather single quotes!
This way you can also use passwords with the $ sign 😉
If you still have to use double quotes, just escape the $ sign:
$name = 'Bob'; echo "Hello \$name";
returns:
Hello $name
Oh… and thanks for your password 😉
Craig McKendree answered 4 years ago
Steph, Wow! always good to learn something new. I’m used to Python and didn’t realize how using single or double quotes made a difference in PHP.
I created a simple PHP file and it worked just as you said using double and then single quotes.
<?php
$name = ‘Bob’;
echo “Hello $name”;
echo “<br>”;
echo ‘Hello $name’;
?>
So if I understand correctly, using the code as written in the tutorial, which shows double quotes, can be changed to single quotes and will then work without error when using $ symbol.
~ snippet taken from RNT code ~
// Your Database name
$dbname = “esp_data”;
// Your Database user
$username = “root”;
// Your Database user password
$password = “YOUR_USER_PASSWORD”;
My $… password has since been retired but now you have my worried that I may have missed changing it somewhere!
Thanks again, very much appreciated!
Steph answered 4 years ago
Hi Craig,
RNT’s PHP code may suggest that they don’t know the difference either… and this can lead to confusion, but also to errors like the case of your password.
To fix your ideas, I encourage you to have a look in the official doc about strings.
As a general rule, with PHP, use single quotes to delimit your strings. The use of double quotes is required when you want to be able to interpret special characters like \n, \r, \t, etc., or when you want to substitute a variable by its value.
When Rui writes:
$password = "REPLACE_WITH_YOUR_PASSWORD";
You should write:
$password = 'REPLACE_WITH_YOUR_PASSWORD';
And when Rui writes:
echo "Error: " . $sql . "<br>" . $conn->error;
You should write:
echo "Error: $sql<br />$conn->error";
When you use simple quotes, and your string also contains occurrences of simple quote, you must escape them:
echo 'I didn\'t know that';
When you use double quotes, and you don’t want to let PHP interpret special characters like the $ sign, or a double quote as such, you must also escape them:
echo "\"\$name refers to a variable\" he told me";
Craig McKendree answered 4 years ago
Steph,
Thank you for the valuable information!
Understanding what’s really going on with the code is what i strive to learn. Anyone can copy and past code from someone else’s work and have a completed project, but did they really learn anything? I would much rather keep my projects simple and fully understand how everything works then get in over my head. I’ll be sure to spend a good amount of time diving into PHP to develop a fuller understanding.
I’m 63, retired, and have enjoyed learning about electronics, microcontrollers and coding for the past 18 months. I see it as exercise for the brain to keep me actively learning. A good teacher when I was learning Arduino coding was Paul McWhorter / YouTube. In one tutorial he explained the math involved in plotting a rise over run slope and was using the math in the example code. Later on I learned about the map() function that would do this math, but learning how the math works… that was valuable… I learned instead of copied.
What you just provided was a learning opportunity and I greatly appreciate your input.
Thank you again,
Craig
Steph answered 4 years ago
Glad I could help you Craig.
Congratulations on your “conversion”, we are never done learning, and your approach seems to me to be the best. Understanding things, beyond knowing how to reproduce them, is to become independent and self-sufficient.
I myself discovered the world of electronics and micro-controllers only a year ago and I’m passionate about it. I devote all my free time to it. I progressed quickly thanks to the precious help of some passionate people who do not hesitate to share their knowledge on the Net, like Sara and Rui. But the more I learn, the more I realize that I know nothing 🙂
I still hope to have the time to fill in my ignorance…
Regards,
Steph