Connection (to server) problem based on IP

[jyaquinas]

Hi, I have used the example library for INSERT and WiFi101 connection to insert some data into my mySQL DB table (I have created the necessary table configurations as mentioned in the example library).

So I have successfully connected to the WiFi and saved some data to the mySQL table, but only when I'm connected to a certain Wifi (or IP). When I try from a different WiFi connection, the serial monitor outputs that the connection to the server failed. (Note: I have created a user with a host as % to and all privileges so that the server can be accessed remotely, from port 3307, and this port was unblocked from the firewall. I have also confirmed that the remote access was working by connecting to the server using mySQLWorkbench from a remote computer and was successfully able to connect to the server, from various Wifis/IPs)

Here is my sketch (SSID/PASS and the server credentials are in a separate tab, SECRET and "server_credentials.h", respectively, with the latter defining ip1, ip2, ip3, ip4,dbport, dbusername, dbpassword):

#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");
}

I wonder what could be the problem.. connection to the server works only from certain IPs using the arduino MKR1000, but those same IPs work when I try connecting from a remote computer. Help would be appreciated.

Thomas

[ChuckBell]

Hi,

Things like this are very difficult to diagnose remotely. So, I offer suggestions. You will need to do the legwork.

Without seeing the contents of your server_credentials.h file, I do not see anything in your sketch that could be the source of the problem. I would suggest that there are routing problems from those other WiFi connections.

You should consider connecting to each of those WiFi points with a laptop to ensure the routing is correct to permit connection to your server (if you haven’t already).

It is possible there are routing issues where the connector code is timing out before a connection is made. You can adjust the delay in the connector code directly (and recompile). Other than that, there is nothing in the connector code to cause such an issue.

On a whim, you could try placing your credentials and IP address for the server in your sketch (don’t use the .h file) and try it. If you have a typographical error, this may help reveal it (again, I can’t check for that without the file - but don’t send it to me for obvious reasons).

Another, remote possibility is the IP address your board is getting on the other WiFi networks is taken. Similarly, there may be another device with the same MAC address (like another Arduino running the same sketch). IP and MAC should be unique.

Perhaps even more remotely is your connection isn’t closing when you disconnect from one WiFi and try another. Be sure to check the processlist and kill any connections that are still open when switching networks.

Finally, what version of MySQL are you running?

Dr. Bell

On Nov 26, 2017, at 8:26 PM, jythomaskim notifications@github.com wrote:

Hi, I have used the example library for INSERT and WiFi101 connection to insert some data into my mySQL DB table (I have created the necessary table configurations as mentioned in the example library).

So I have successfully connected to the WiFi and saved some data to the mySQL table, but only when I'm connected to a certain Wifi (or IP). When I try from a different WiFi connection, the serial monitor outputs that the connection to the server failed. (Note: I have created a user with a host as % to and all privileges so that the server can be accessed remotely, from port 3307, and this port was unblocked from the firewall. I have also confirmed that the remote access was working by connecting to the server using mySQLWorkbench from a remote computer and was successfully able to connect to the server, from various Wifis/IPs)

[jyaquinas]

Hi, thanks for your quick response. I'm using mySQL Version 5.7.19. I'm not sure I understand when you say check the routing is correct from with a laptop. But what I did do is that I used the same Wifi points with the laptop and tried to connect to the mySQL DB, and it worked, for all of the Wifi points. It just doesn't seem to work on the arduino (except for one, for now). With the Wifi that works, inserting to the table and everything works. But with the other Wifi points, the serial monitor displays:

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 the rest.

Also, adding the credentials directly to the sketch didn't solve the problem. When you refer to adjusting the delay in the connector code, do you mean to simply change the delay(3000) to a longer time period? I tried that with no success; it just dwells at "Connecting..." for a longer time.

It does seem like it might have something to do with the timeout and the connection time... The Wifi that works connects to the DB server instantly whereas the others that don't work take quite a bit of time, and output an error.

UPDATE: This is the Serial monitor output with the Wifi/IP that works (I've only changed the Wifi credentials in the Secret tab, the rest of the sketch is the same):

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

Just for your reference.

[jyaquinas]

Extra question: I'm trying to execute the mySQL INSERT statement in a ISR(), do you think this would be possible? Or would it interfere with the mySQL connector library itself? (I've included in the setup() the necessary code to connect to Wifi and the DB server, just as the sketch I previously posted above).

This is the ISR for the timer interrupt, triggered roughly at 1.4Hz (essentially I'd like to insert at a faster rate, or simply to insert multiple rows at a time).

void TC5_Handler()                              // Interrupt Service Routine (ISR) for timer TC5
{
  if (TC5->COUNT8.INTFLAG.bit.OVF && TC5->COUNT8.INTENSET.bit.OVF)             
  {
    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;

    REG_TC5_INTFLAG = TC_INTFLAG_OVF;         // Clear the OVF interrupt flag
  }
}

But when I run the sketch with the main loop, it seems to get stuck since the Serial monitor outputs "Recording data." and the entire code stops. Any tips?

[ChuckBell]

Hi,

I am not sure if an interrupt would work. There are restrictions on memory access and such. You will need to make sure the interrupt can access the global variable conn. If it can't, you're likely to see the hang you described. Curiosity suggests checking to see if the insert succeeded. Sometimes there are issues communicating to the serial monitor for such code. I would not put the insert inside the interrupt. I'd make the insert a separate method and call it from the interrupt. But that may be 6 of one... ;)

Dr. Bell

On 11/27/17 12:05 AM, jythomaskim wrote:

Extra question: I'm trying to execute the mySQL INSERT statement in a ISR(), do you think this would be possible? Or would it interfere with the mySQL library itself?

This is the ISR for the timer interrupt, roughly at 1.4Hz.

|void TC5_Handler() // Interrupt Service Routine (ISR) for timer TC5 { if (TC5->COUNT8.INTFLAG.bit.OVF && TC5->COUNT8.INTENSET.bit.OVF) { 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; counter1 = 0; REG_TC5_INTFLAG = TC_INTFLAG_OVF; // Clear the OVF interrupt flag } } |

But when I run the sketch with the main loop, it seems to get stuck since the Serial monitor outputs "Recording data." and it stops the entire code. Any tips?

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/25#issuecomment-347079764, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4P-FcxFBkMPLIUfcxQ6fV9aZ0zqWks5s6kMlgaJpZM4QrA7c.

[ChuckBell]

Hi,

Response inline.

On 11/26/17 11:17 PM, jythomaskim wrote:

Hi, thanks for your quick response. I'm using mySQL Version 5.7.19. I'm not sure I understand when you say check the routing is correct from with a laptop. But what I did do is that I used the same Wifi points with the laptop and tried to connect to the mySQL DB, and it worked, for all of the Wifi points. It just doesn't seem to work on the arduino (except for one, for now). With the Wifi that works, inserting to the table and everything works. But with the other Wifi points, the serial monitor displays:

|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 the rest.

Sounds like networking...

Also, adding the credentials directly to the sketch didn't solve the problem. When you refer to adjusting the delay in the connector code, do you mean to simply change the delay(3000) to a longer time period? I tried that with no success.

No, in MySQL_Connection.cpp:

// Retry up to MAX_CONNECT_ATTEMPTS times 1 second apart. do { delay(100); connected = client->connect(server, port); i++; } while (i < MAX_CONNECT_ATTEMPTS && !connected);

Change 100 to 500.

Also, change MAX_CONNECT_ATTEMPTS from 3 to 10 or so. That will wait a really, really long time.

It does seem like it might have something to do with the timeout and the connection time... Im not sure if this has anything to do with the problem but the Wifi IP that works (connecting to the DB server) has an IP address that is similar to the IP of the server (meaning the IPs are same.same.diff.diff), and the ones that are taking too long to connect and result in a connection error have IPs that are all different to that of the server. Do you think this might cause a delay in the connection?

Yes, that denotes a different subnet. That's where the routing comes into play. If the routing on your network is setup correctly, it shouldn't be a problem. If not, there may be a long delay or no connection at all. Try the delay trick first.

Dr. Bell

[jyaquinas]

I modified the MySQL_Connection.cpp file like you suggested it, and still leads to a failed connection; it just now takes longer to output the error, like you had said. So I've tried creating a new mysql database and reconfigured the entire thing, in case the settings were not set properly. (I opened ports from the firewall setting, created users with all privileges on any IP,%, also tried uncommenting bind-address, as well as using bind-address=* and bind-address=0.0.0.0, all three leading to the same result mentioned below)

But again, I can connect to the DB server from another computer using MySQL workbench, and I confirmed it by checking the client connections list, which appears there as connected under a different IP (from the local host or the host IP). So I checked and confirmed that I could modify/query databases/tables remotely using Workbench. The problem arises when I try connecting with the arduino MKR1000, which uses the same public IP (not the private IP assigned to the arduino since that will vary from machine to machine under the same WiFi router), and the same DB username/pass.

The only time I can successfully connect to the DB server is when I am using the WiFi that is under the same LAN (so the same router provides internet to both the DB hosting server and the WiFi router). Any other Wifi/IP leads to failed connections.. I still can't seem to pin point the source of error.

[jyaquinas]

UPDATE:

I've been playing around with the code and found where the error is coming from. It seems like the MKR1000 is having trouble connecting to the client(TCP,Port) when it is connected to other Wifi points (other than the one that shares the same router as the server that I'm trying to connect to):

[in MySQL_Connection.cpp]
    connected = client->connect(server, port);

So I've tried to replicate the problem with this simple client connection sketch:

#include <SPI.h>
#include <WiFi101.h>

char ssid[] = "***";          //  your network SSID (name) 

int status = WL_IDLE_STATUS;
IPAddress server(*.*.*.*);

WiFiClient client;

void setup() {
  delay(2000);
  Serial.begin(9600);
  Serial.println("Attempting to connect to WPA network...");
  Serial.print("SSID: ");
  Serial.println(ssid);

  status = WiFi.begin(ssid);
  if ( status != WL_CONNECTED) { 
    Serial.println("Couldn't get a wifi connection");
    // don't do anything else:
    while(true);
  } 
  else {
    Serial.println("Connected to wifi");
    Serial.println("\nStarting connection...");
    // if you get a connection, report back via serial:
    if (client.connect(server, ****)) {
      Serial.println("connected");

      client.println();
    }
  }
}

void loop() {

}

So.. like I mentioned, it successfully connects to the TCP/port under that one Wifi point that has the same router/IP domain as the server. All other IPs/Wifi points fail to connect.. Any suggestions on how to fix this?

Thanks.

Thomas

[jyaquinas]

I found out that it was my school network that was blocking external IP's. I had to ask them to unblock them in order to be able to connect to the server. So.. Issue solved. Thanks a lot for the help! Connection seems to be slower, as well as executing queries. So actually delaying the mysql_connection.cpp part helped ensuring that the MKR1000 would successfully connect to the server. Any tips on improving the connection speed?