Go Down

Topic: [Connection Problem] Connecting to MySQL database remotely through WiFi (Read 908 times) previous topic - next topic

jythomaskim

Hi,

I am currently trying to connect to a MySQL DB server from the Arduino MKR1000 remotely, using Wifi (I'm using Wifi101.h). I am having no problems getting the Wifi connection, but I am having trouble connecting to the MySQL server (I'm using the MySQL Connector Arduino Library by Dr. Bell). Only one Wifi point seems to work, which is connected to the same router as that of the MySQL server. All other Wifi points cause connection errors to the server.

Note: I have set up the MySQL server to allow remote access, so bind-address was commented out, the port it's using is unblocked from the firewall settings, both incoming and outgoing, and the user on host % was created with all privileges. I confirmed that the remote connection is working by trying to connect to the server from another computer using a different Wifi point, and the connection was successful. But when I connect to the same Wifi using the MKR1000 and then connect to the DB server using the exact same credentials (host, port, username, and password), the connection fails.

Here is my example sketch:

Code: [Select]

#include <SPI.h>
#include <WiFi101.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include "server_credentials.h";

byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };

IPAddress server_addr(ip1,ip2,ip3,ip4);  // IP of the MySQL *server* here

// WiFi card example
char ssid[] = SECRET_SSID;    // your SSID
char pass[] = SECRET_PASS;       // your SSID Password

int status = WL_IDLE_STATUS;

WiFiClient client;
MySQL_Connection conn((Client *)&client);

void setup() {
    Serial.begin(115200);

  // check if the WiFi module works
  if (WiFi.status() == WL_NO_SHIELD) {
    Serial.println("WiFi shield not present");
    // don't continue:
    while (true);
  }

  // attempt to connect to WiFi network:
  while ( status != WL_CONNECTED) {
    Serial.print("Attempting to connect to SSID: ");
    Serial.println(ssid);
    // Connect to WPA/WPA2 network. Change this line if using open or WEP network:
    status = WiFi.begin(ssid, pass);
    delay(10000);
  }

  printWiFiStatus();

  Serial.println("Connecting...");
  if (conn.connect(server_addr, dbport, dbusername, dbpassword)) {
    delay(3000);
  }
  else {
    Serial.println("Connection failed.");
  }

}


// Sample query
char INSERT_SQL[] = "INSERT INTO test_arduino.hello_arduino (message) VALUES ('hello')";

void loop() {
  delay(2000);

  Serial.println("Recording data.");

  // Initiate the query class instance
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  // Execute the query
  cur_mem->execute(INSERT_SQL);
  // Note: since there are no results, we do not need to read any data
  // Deleting the cursor also frees up memory used
  delete cur_mem;
}

void printWiFiStatus() {
  // print the SSID of the network you're attached to:
  Serial.print("SSID: ");
  Serial.println(WiFi.SSID());

  // print your WiFi shield's IP address:
  IPAddress ip = WiFi.localIP();
  Serial.print("IP Address: ");
  Serial.println(ip);

  // print the received signal strength:
  long rssi = WiFi.RSSI();
  Serial.print("signal strength (RSSI):");
  Serial.print(rssi);
  Serial.println(" dBm");
}



Serial monitor output when connection fails (using other Wifi points):

Code: [Select]

SSID: *****
IP Address: ****
signal strength (RSSI):-43 dBm
Connecting...
Connection failed.
Recording data.
ERROR: Class requires connected server.


It stays at 'Connecting...' for a long time before it outputs 'Connection failed.'


Serial monitor output using the Wifi point that successfully connects to the DB server:

Code: [Select]

SSID: ***
IP Address: ****
signal strength (RSSI):-54 dBm
Connecting...
Connected to server version 5.7.19
Recording data.
Recording data.
Recording data.
...


What could be the problem? It seems like the MySQL server isn't the issue because I can successfully connect to it from a remote computer. The problem arises from trying to connect to the server from the MKR1000...

jythomaskim

It was my school network that was blocking all external IPs. I had to ask them to stop blocking them. It connects to the client fine now. Issue solved!

Go Up