I am trying to set up a web app to set different parameters that will be read by my MKR1010 and used to control an irrigation system, as well as send out data from a temperature sensor that I can view online.
I set up a mySQL database on my webserver and I'm trying to use the mySQL Connector library. I can't get past my first experiment which is getting the arduino to connect to the server. I have successfully gotten connected to the WiFi, but when I try to connect to the database server it just hangs...
I'm using this example code from the above github link:
#include <SPI.h>
#include <WiFi101.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
IPAddress server_addr(86,34,31,183); // IP of the mySQL server
char user[] = "root"; // MySQL user login username
char password[] = ""; // MySQL user login password
// WiFi card example
char ssid[] = SECRET_SSID; // your SSID
char pass[] = SECRET_PASS; // your SSID Password
WiFiClient client;
MySQL_Connection conn((Client *)&client);
void setup() {
Serial.begin(115200);
while (!Serial); // wait for serial port to connect
// Begin WiFi section
int status = WiFi.begin(ssid, pass);
if ( status != WL_CONNECTED) {
Serial.println("Couldn't get a wifi connection");
while(true);
}
// print out info about the connection:
else {
Serial.println("Connected to network");
IPAddress ip = WiFi.localIP();
Serial.print("My IP address is: ");
Serial.println(ip);
}
// End WiFi section
Serial.println("Connecting...");
if (conn.connect(server_addr, 3306, user, password)) {
delay(1000);
}
else
Serial.println("Connection failed.");
conn.close();
}
void loop() {
}
When I run the code it just hangs indefinitely. I also tried switching from the WiFi101 to the WiFiNINA library but with the same result. I don't know if there's something else that needs to be changed.
I've had no other problems connecting to the mySQL database, I have a PHP based webpage set up that successfully allows me to insert values into a database table.
Can anyone see something wrong here? I did not expect to be stuck on this seemingly simple step for so long, I have a lot trickier coding yet to work on for this project.
IPAddress server_addr(86,34,31,183); // IP of the mySQL server
char user[] = "root"; // MySQL user login username
char password[] = ""; // MySQL user login password
so that the connection works ? if (conn.connect(server_addr, 3306, user, password)) {
IPAddress server_addr(86,34,31,183); // IP of the mySQL server
char user[] = "root"; // MySQL user login username
char password[] = ""; // MySQL user login password
so that the connection works ?` if (conn.connect(server_addr, 3306, user, password)) {`
I am 90% sure yes --
The IP address is correct because I successfully used the WiFi.ping command to ping the server from the arduino and got a reply. This also means -- I think -- there is no firewall issue on the server or from my router or anything like that.
The username and password... I have tried creating multiple databases on the server with multiple associated users, given full permissions. i.e.:
GRANT ALL ON *.* to arduino@[IP_address_of_arduino] IDENTIFIED BY 'password';
The port, as far as I can tell, is 3306.
I have been able to add values to the database thru a browser using a PHP script, so something works...
J-M-L:
Have you tried with a real pwd?
Do you have a way to track network activity on that server? (Logs)
Yes, I'm using a real pwd, just didn't want to post online. The server is running on a google compute engine virtual machine, I'm looking through the logs but can't say I see anything that looks at all related to my request from arduino, but there's a ton of entries there I don't understand.
If the server is not seeing the request at all, what could that mean?
I should also note that it's not actually hanging, it just takes quite a while to finally display the "Connection failed." message.
What IP address should I be using when I create the user account on the database?
I've also tried with this other code I borrowed from somewhere on these forums, to POST to the database using a PHP script, without using the mySQL connector library.
#include <SPI.h>
#include <WiFiNINA.h>
String yourdata ="";
char server[] = "34,69,39,183";
char user[] = "arduino"; // MySQL user login username
char password[] ="password"; // MySQL user login password
// WiFi card example
char ssid[] = SECRET_SSID; // your SSID
char pass[] = SECRET_PASS; // your SSID Password
WiFiClient client;
void setup() {
Serial.begin(9600);
while (!Serial); // wait for serial port to connect
// Begin WiFi section
int status = WiFi.begin(ssid, pass);
if ( status != WL_CONNECTED) {
Serial.println("Couldn't get a wifi connection");
while(true);
}
// print out info about the connection:
else {
Serial.println("Connected to network");
IPAddress ip = WiFi.localIP();
Serial.print("My IP address is: ");
Serial.println(ip);
}
// End WiFi section
DataToMySQL();
}
void loop() {
}
void DataToMySQL(){
yourdata = "testing123";
WiFiClient client;
if (client.connect("34.69.39.183", 3306))
{
Serial.print("Connected to MySQL server. Sending data...");
client.print("POST /insert_mysql.php HTTP/1.1\n");
client.print("Host: 34.69.39.183\n");
client.print("Connection: close\n");
client.print("Content-Type: application/x-www-form-urlencoded\n");
client.print("Content-Length: ");
client.print(yourdata.length());
client.print("\n\n");
client.print(yourdata);
Serial.println("Successfull");
}
else{
Serial.println("Connection failed");
Serial.println();
}
}
Strangely enough with this code if I change the port from 3306 to port 80, client.connect() returns TRUE, but the server log shows nothing and the POST does not work to add a value to the database. When I changed the port to 80 in the original code using the mySQL connector library, it still fails to connect. As far as I can tell, the port is 3306... so I don't get what's happening here.
when you say IP_address_of_arduino in GRANT ALL ON *.* to arduino@[IP_address_of_arduino] IDENTIFIED BY 'password';what do you mean ? if you are on different network, the server in the cloud won't see your local address but your router's WAN address. is that what you used ?
may be you can try with GRANT ALL ON *.* to user@'%' IDENTIFIED BY 'password';where the wild card will allow any IP (of course that has an impact on security)
also above you had char user[] = "root"; and now it's "arduino". So make sure you use the real user.
J-M-L:
when you say IP_address_of_arduino in GRANT ALL ON *.* to arduino@[IP_address_of_arduino] IDENTIFIED BY 'password';what do you mean ? if you are on different network, the server in the cloud won't see your local address but your router's WAN address. is that what you used ?
may be you can try with GRANT ALL ON *.* to user@'%' IDENTIFIED BY 'password';where the wild card will allow any IP (of course that has an impact on security)
also above you had char user[] = "root";and now it's "arduino". So make sure you use the real user.
I tried creating a new user:
GRANT ALL ON *.* to arduino@'%' IDENTIFIED BY 'password';
And edited the sketch to set the user "arduino" and password "password".
Still no beans. I disabled windows firewall. Any other ideas?
J-M-L:
I'm not using google compute engine virtual so would be best to double check with them how this is being accessed from the outside world (https ?)
yea, this is the first time I'm using google compute engine, it seemed like a great idea because for what I need it's free. Maybe it's just not he right approach though.
I have it set up to accept HTTP and HTTPS.
But if I am successfully pinging the server using WiFi.ping() doesn't that mean there is no firewall blocking the connection?
I may have to just take a totally different approach on this project. I just need to be able to modify a few different variables running in the sketch remotely, preferably using WiFi, and also keep track of a couple sensor outputs, also remotely. Building a little web app and keeping track of everything in an online database seemed like a decent idea, but maybe there is another way with less headaches...
Using any of the IOT cloud would give you this capability (Blynk and similar)
I have paid for my own domain and hosting space and virtual server so that’s how I handle my personal on line services: i can decide for almost everything.
Ping might work or basic http stuff because of known ports being opened and may be the other port is not opened or something need to be done there. I’m sure google had some docs or someone else Already did it..