Eventual connection breaking

Dr. Bell First of all, thank you very much for giving us this library. It has been very useful to my projects. I even dared to modify it a little bit: I overloaded connect function to be able to send data through a FQDN instead an ip address. I don't know if it is good for others and also don't know how to share it. If you think it can be good I can attach the modifications.

My question is about broken connectivity: One of my ESP32s is located in a place where it can loose network connection sometimes. Everything runs ok if network connection is working when ESP starts. The code (I took from your examples) takes care for that, and only a small delay happens if no network connection is available, but everything else runs smoothly. The problem comes when no connection is available at the start. ESP freezes waiting a connection to the database and gets stuck. I've tried to "debug" through serial messages and identified the line where it gets stuck: connected = client->connect(server, port); //In MySQL_Connection::connect(IPAddress server, int port, char user, char password) It doesn't matter the delay (I increased, decreased and even took it out from my code and yours). I know maybe this is not an issue in your code, but you could perhaps guide me to the right path and I should be able to identify the problem and, eventually, solve it.

My device is a Heltec wireless stick ESP32 and the code is this:

IPAddress server_addr(192,168,0,222); // IP of the MySQL server here
int sqlPort = 3306;
char user[] = "user";              // MySQL user login username
char passwd[] = "password";        // MySQL user login password

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

In setup:

 if (conn.connect(server_addr, sqlPort, user, passwd)) { //The line of my code where it stucks
    delay(1000);
  }
  else{
    conn.close();
    Serial.println("No DB connection");
  }

In Loop:

if(conn.connected()){
...
}else{
        //If connection fails, close it and try to reconnect
        conn.close();
        if (conn.connect(server_addr, sqlPort, user, passwd)){
          delay(500);
          numFallos++;
          if(numFallos >=MAX_FAILED_CONNECTS){
            delay(1000);
            ESP.restart();
          }
        }
      }

Apologies for the kicks to english language, I'm not native as you can see

Hi,

Yes, poor network quality is the bane of all IOT devices. You can send me your modifications if you'd like. A future version is planned with that modification so it will be good to see how you solved it. ;)

As for what to do about no network at startup, the only thing you can do is try to setup some form of watchdog to detect when/if the network is down on startup. Maybe a counter or some such to cause it to go to sleep. Google for examples on this.

As for what to do about broken connections, I think the approach you're using is the most reliable. The only thing I could add is make a longer delay when the connection fails to give the network time to reconnect.

That said, you may also consider instead of restarting, try reconnecting to the network then try connecting to MySQL. This may help solve cases where you've got poor connection to two or more routers. That is, if you reconnect to a different router, you get a different IP, which MySQL may not expect and thus may break any open connections.

Dr. Bell

On 5/30/19 6:19 AM, Miguel Ángel Contreras wrote:

Dr. Bell First of all, thank you very much for giving us this library. It has been very useful to my projects. I even dared to modify it a little bit: I overloaded connect function to be able to send data through a FQDN instead an ip address. I don't know if it is good for others and also don't know how to share it. If you think it can be good I can attach the modifications.

My question is about broken connectivity: One of my ESP32s is located in a place where it can loose network connection sometimes. Everything runs ok if network connection is working when ESP starts. The code (I took from your examples) takes care for that, and only a small delay happens if no network connection is available, but everything else runs smoothly. The problem comes when no connection is available at the start. ESP freezes waiting a connection to the database and gets stuck. I've tried to "debug" through serial messages and identified the line where it gets stuck: connected = client->connect(server, port); //In MySQL_Connection::connect(IPAddress server, int port, char user, char password) It doesn't matter the delay (I increased, decreased and even took it out from my code and yours). I know maybe this is not an issue in your code, but you could perhaps guide me to the right path and I should be able to identify the problem and, eventually, solve it.

My device is a Heltec wireless stick ESP32 and the code is this: WiFiClient client; MySQL_Connection conn((Client *)&client); In setup: if (conn.connect(server_addr, sqlPort, user, passwd)) { //The line of my code where it stucks delay(1000); } else{ conn.close(); Serial.println("No DB connection"); } In Loop: if(conn.connected()){ ... }else{ //If connection fails, close it and try to reconnect conn.close(); if (conn.connect(server_addr, sqlPort, user, passwd)){ delay(500); numFallos++; if(numFallos >=MAX_FAILED_CONNECTS){ delay(1000); ESP.restart(); } } }

Apologies for the kicks to english language, I'm not native as you can see

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/104?email_source=notifications&email_token=AB6SHYFII56RFZ4S25RIDV3PX6S2RA5CNFSM4HRCNQA2YY3PNVWWK3TUL52HS4DFUVEXG43VMWVGG33NNVSW45C7NFSM4GWWBHOA, or mute the thread https://github.com/notifications/unsubscribe-auth/AB6SHYDS3YV3GVMHMOUQMUDPX6S2RANCNFSM4HRCNQAQ.

Thank you very much. I'll try that approach, and try to reconnect instead of restart as you said. My changes in the code are: In MySQL_Connection.h: boolean connect(char *server, int port, char *user, char *password); //added In MySQL_Connector.cpp:

//added
boolean MySQL_Connection::connect(char *server, int port, char *user,
                                  char *password)
{
  int connected = 0;
  int i = -1;

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

  if (connected) {
    read_packet();
    parse_handshake_packet();
    send_authentication_packet(user, password);
    read_packet();
    if (check_ok_packet() != 0) {
      parse_error_packet();
      return false;
    }
    show_error(CONNECTED);
    Serial.println(server_version);
    free(server_version); // don't need it anymore
    return true;
  }
  return false;
}

Maybe it is not the rightest or smartest way, but I'm not used to program and it works for me. I hope it will be useful.

Thanks a lot

ChuckBell / MySQL_Connector_Arduino

Eventual connection breaking #104