mySQL connection get stuck while reconnect (no WiFi)

I'm using your library to send Data from my Arduino to a mySQL database using WiFi. All works fine, but sometime it lost the connection to the Wifi. I have two reconection algorithem. First my code checks for Wifi and afterwords it checkts the mySQL-Server connection. It seams my problem is, if the Wifi connection get lost betwen both algorithem, the mySQL reconect detect the los of connection and trys to reconect. But witout the Wifi it will never be posible. So it get stucked. Shouldent there be a timeout or a maximum amount of trys in the conn.connect() ?

_Connected to WiFi SSID: Lab IP Address: 192.168.100.170 Signal strength (RSSI):-58 dBm Attempting to connect to mySQL: ...trying... Connected to server version 8.0.31 INSERT INTO raum_1.maschine_1 (Status) VALUES (1) INSERT INTO raum_1.maschine_1 (Status) VALUES (1) ... INSERT INTO raum_1.maschine_1 (Status) VALUES (1) INSERT INTO raum_1.maschine1 (Status) VALUES (1) Error: 36 = Server shutdown in progress. Attempting to connect to mySQL: ...trying...

At this point ...trying... it get stuck!

If I manual stop the mySQL Server it shows the folowing an reconect if I start the Server again. _... INSERT INTO raum_1.maschine_1 (Status) VALUES (1) INSERT INTO raum_1.maschine_1 (Status) VALUES (1) Error: 36 = Server shutdown in progress. Attempting to connect to mySQL: ...trying... ...got: 0 retrying... ...trying... ...got: 0 retrying... ...trying... ERROR: Timeout waiting for client. [NN] WiFiClient::write: NO_SOCKET_AVAIL ERROR: Timeout waiting for client. Error: -1 = ...trying... Connected to server version 8.0.31 INSERT INTO raum_1.maschine_1 (Status) VALUES (1) INSERT INTO raum_1.maschine1 (Status) VALUES (1)

This is what the corresponding area of my program looks like:

...

void loop(){
  startTime = micros();

  ...

  if ((micros() - last_update) > update_intervall){
    if (WiFi.status() != WL_CONNECTED){
      WiFi_connect();
    }
    if (!conn.connected()){
      mySQL_reconnect();
    }
    last_update = micros();
  }

  if ((micros() - last_send) > send_intervall){
    MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
    sprintf(query, INSERT_SQL, state);
    bool result = cur_mem->execute(query);
    Serial.println(query);
    delete cur_mem;

    last_send = micros();
  }
}

void WiFi_connect() {
  Serial.println(F("WiFi_Connect..."));
  while (WiFi.status() != WL_CONNECTED)
  {
    Serial.print(F("Attempting to connect to SSID: "));
    Serial.println(ssid);
    WiFi.begin(ssid, pass);
    delay(1000);
  }
  Serial.println(F("Connected to WiFi"));
  printWiFiStatus();
}

void mySQL_reconnect() {
  Serial.println("Attempting to connect to mySQL:");
  while (!conn.connected()) {
    conn.connect(server_addr, 3306, user, password);
  }
}

I've tried embedding my WiFi-reconnect in the while loop of mySQL_reconnect(), but it doesn't get processed at all. It seems to me that the program hangs up directly with the first conn.connect() !?

Hi. First, let’s consider why an Arduino would hang or reboot. Most of the causes are a lack of memory either from a memory leak or devices that simply require more memory than is available.

For cases where this cannot be solved or it is not convenient to solve, you can use a watchdog timer to reboot the Arduino if it goes zombie. See the example in the Wiki on how to do this.

Now, for the code you have presented, I see some issues. I would recommend you move the Wiki connect to the setup() function and do not put the MySQL connect in its own function. Leave it in the loop(). See the various example sketches and follow that pattern. Also, see the connect/reconnect example and follow that. That should help.

Next, check the memory on your Arduino (you can see the available memory when you compile). If it is below 512 bytes available, your sketch will be unstable. Work to reduce the footprint of your code or use an Arduino with more memory.

Hope this helps.

On Nov 16, 2022, at 5:55 AM, DennisCor @.***> wrote:

I'm using your library to send Data from my Arduino to a mySQL database using WiFi. All works fine, but sometime it lost the connection to the Wifi. I have two reconection algorithem. First my code checks for Wifi and afterwords it checkts the mySQL-Server connection. It seams my problem is, if the Wifi connection get lost betwen both algorithem, the mySQL reconect detect the los of connection and trys to reconect. But witout the Wifi it will never be posible. So it get stucked. Shouldent there be a timeout or a maximum amount of trys in the conn.connect() ?

Connected to WiFi SSID: Lab IP Address: 192.168.100.170 Signal strength (RSSI):-58 dBm Attempting to connect to mySQL: ...trying... Connected to server version 8.0.31 INSERT INTO raum_1.maschine_1 (Status) VALUES (1) INSERT INTO raum_1.maschine_1 (Status) VALUES (1) ... INSERT INTO raum_1.maschine_1 (Status) VALUES (1) INSERT INTO raum_1.maschine_1 (Status) VALUES (1) Error: 36 = Server shutdown in progress. Attempting to connect to mySQL: ...trying...

At this point ...trying... it get stuck!

If I manual stop the mySQL Server it shows the folowing an reconect if I start the Server again. ... INSERT INTO raum_1.maschine_1 (Status) VALUES (1) INSERT INTO raum_1.maschine_1 (Status) VALUES (1) Error: 36 = Server shutdown in progress. Attempting to connect to mySQL: ...trying... ...got: 0 retrying... ...trying... ...got: 0 retrying... ...trying... ERROR: Timeout waiting for client. [NN] WiFiClient::write: NO_SOCKET_AVAIL ERROR: Timeout waiting for client. Error: -1 = ...trying... Connected to server version 8.0.31 INSERT INTO raum_1.maschine_1 (Status) VALUES (1) INSERT INTO raum_1.maschine_1 (Status) VALUES (1)

This is what the corresponding area of my program looks like:

...

void loop(){ startTime = micros();

...

if ((micros() - last_update) > update_intervall){ if (WiFi.status() != WL_CONNECTED){ WiFi_connect(); } if (!conn.connected()){ mySQL_reconnect(); } last_update = micros(); }

if ((micros() - last_send) > send_intervall){ MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn); sprintf(query, INSERT_SQL, state); bool result = cur_mem->execute(query); Serial.println(query); delete cur_mem;
last_send = micros();
} }

void WiFi_connect() { Serial.println(F("WiFi_Connect...")); while (WiFi.status() != WL_CONNECTED) { Serial.print(F("Attempting to connect to SSID: ")); Serial.println(ssid); WiFi.begin(ssid, pass); delay(1000); } Serial.println(F("Connected to WiFi")); printWiFiStatus(); }

void mySQL_reconnect() { Serial.println("Attempting to connect to mySQL:"); while (!conn.connected()) { conn.connect(server_addr, 3306, user, password); } } I've tried embedding my WiFi-reconnect in the while loop of mySQL_reconnect(), but it doesn't get processed at all. It seems to me that the program hangs up directly with the first conn.connect() !?

— Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/197, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYE6XU7HBL7PFIMQV2TWIS4RHANCNFSM6AAAAAASCCX5QQ. You are receiving this because you are subscribed to this thread.

ChuckBell / MySQL_Connector_Arduino

mySQL connection get stuck while reconnect (no WiFi) #197