Nano 33 IOT hangs on mysql execute, then somehow restarts itself to only to the same thing again a few hours later

Using Arduino Programming Questions
1

I am using a Nano 33 iot to save sensor data, get setpoints, and get a current timestamp from a MySQL db. I am using this mysql_connector library

The program functions perfectly for hours, but will eventually hang on one of the executes (it can hang on any one of them). It will hang here for hours on end; eventually it will unstick itself & resume operation. Repeat everything a few hours later.

This makes me think it is not a memory issue but something wrong with the execute. What can I do to make it not hang here?

code below

//Common Libraries
#include <SPI.h>
#include <WiFiNINA.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include <avr/dtostrf.h>
#include "secret.h"
//Node Libraries
#include <DHT.h>

#define O_UPPER 9
#define O_LOWER 10


//Wifi
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED }; //Do I need this?
char ssid[] = SECRET_SSID;
char pass[] = SECRET_PASS;
int status = WL_IDLE_STATUS;
int keyIndex = 0;
WiFiClient client;


//MySQL
IPAddress server_addr(192, 168, 1, 133);
#define port 3306
char db[] = SECRET_MYSQL_DB;
char user[] = SECRET_MYSQL_USER;
char password[] = SECRET_MYSQL_PASS;
char query[250];
char tempStr[6], columnStr[150], dataStr[50];
char DATA_QUERY[] = "INSERT INTO canna1_data (%s) VALUES (%s);";
char SP_QUERY[] = "SELECT * FROM canna1_sp ORDER BY id DESC LIMIT 1;";
char TIME_QUERY[] = "SELECT * FROM canna1_data ORDER BY id DESC LIMIT 1;";
byte dbUpdateRate = 30, dbCheckRate = 5;
uint32_t dbCheckTimer;
uint32_t dbUpdateTimer;
MySQL_Connection conn((Client *)&client);


//Node Specific
bool daytime, lights, fan;
char lightsSP[5], fanSP[5];
byte dateTime = 0, sunrise = 7, sunset = 19;
int soilMoisture;
float temptemp;


DHT exhaust(6, DHT11);
DHT intake(7, DHT11);
DHT room(8, DHT11);


void setup() {
  Serial.begin(115200);
  while (status != WL_CONNECTED) {
    digitalWrite(LED_BUILTIN, HIGH);
    delay(100);
    status = WiFi.begin(ssid, pass);
  }
  digitalWrite(LED_BUILTIN, LOW);

  exhaust.begin();
  intake.begin();
  room.begin();

  pinMode(O_UPPER, OUTPUT);
  pinMode(O_LOWER, OUTPUT);
  digitalWrite(O_UPPER, LOW);
  digitalWrite(O_LOWER, LOW);

  while(!conn.connect(server_addr, port, user, password, db)) {
    digitalWrite(LED_BUILTIN, HIGH);
    delay(100);
  }
  dbUpdate();
  dbCheck();
  conn.close();

  dbCheckTimer = millis();
  dbUpdateTimer = millis();
}

void loop() {

  if (millis() - dbCheckTimer >= (dbCheckRate * 1000)) {
    if (conn.connect(server_addr, port, user, password, db)) {
      dbCheck();
      dbCheckTimer = millis();
      if (millis() - dbUpdateTimer >= (dbUpdateRate * 1000)) {
        Serial.println("Update");
        dbUpdate(); 
        dbUpdateTimer = millis();
      }
    }
    else Serial.println("Conn Failed.");
    conn.close();
  }
}

//------------------------------------MySql Functions-------------------------
void dbCheck() {
  MySQL_Cursor *cur = new MySQL_Cursor(&conn);
  row_values *row = NULL;

  cur->execute(TIME_QUERY);
  Serial.println("Fail Check 0.5");
  cur->get_columns();
  do {
    Serial.println("Fail Check 1");
    row = cur->get_next_row();
    Serial.println("Fail Check 2");
    if (row != NULL) {
      Serial.println("Fail Check 3");
      dateTime = (row->values[1][11] - 48) * 10 + (row->values[1][12] - 48);
    }
  } while (row != NULL);
  Serial.println("Fail Check 4");
  cur->execute(SP_QUERY);
  Serial.println("Fail Check 4.5");
  cur->get_columns();
  do {
    Serial.println("Fail Check 5");
    row = cur->get_next_row();
    Serial.println("Fail Check 6");
    if (row != NULL) {
      strcpy(lightsSP, row->values[2]);
      strcpy(fanSP, row->values[3]);
      sunrise = atol(row->values[12]);
      sunset = atol(row->values[13]);
      if (sunrise <= dateTime && dateTime < sunset) {
        if (sunrise < sunset) daytime = true;
        else daytime = false;
      }
      else {
        if (sunrise < sunset) daytime = false;
        else daytime = true;
      }
    }
  } while (row != NULL);
  Serial.println("Fail Check 7");
  if (strcmp(lightsSP, "NULL") != 0) {
    if (strcmp(lightsSP, "1") == 0) {
      digitalWrite(O_UPPER, HIGH);
      lights = 1;
    }
    else {
      digitalWrite(O_UPPER, LOW);
      lights = 0;
    }
  }
  else  {
    if (daytime) {
      digitalWrite(O_UPPER, HIGH);
      lights = 1;
    }
    else {
      digitalWrite(O_UPPER, LOW);
      lights = 0;
    }
  }
  delete cur;
}

void dbUpdate() {
  MySQL_Cursor *cur = new MySQL_Cursor(&conn);

  strcpy(columnStr, "lights,fan");

  dataStr[0] = lights + 48;
  dataStr[1] = ',';
  dataStr[2] = fan + 48;
  dataStr[3] = '\0';

  temptemp = intake.readTemperature();
  if (!isnan(temptemp)) {
    dtostrf(temptemp, 4, 2, tempStr);
    strcat(columnStr, ",intakeT");
    strcat(dataStr, ",");
    strcat(dataStr, tempStr);
  }

  temptemp = intake.readHumidity();
  if (!isnan(temptemp)) {
    dtostrf(temptemp, 4, 2, tempStr);
    strcat(columnStr, ",intakeH");
    strcat(dataStr, ",");
    strcat(dataStr, tempStr);
  }

  temptemp = room.readTemperature();
  if (!isnan(temptemp)) {
    dtostrf(temptemp, 4, 2, tempStr);
    strcat(columnStr, ",roomT");
    strcat(dataStr, ",");
    strcat(dataStr, tempStr);
  }

  temptemp = room.readHumidity();
  if (!isnan(temptemp)) {
    dtostrf(temptemp, 4, 2, tempStr);
    strcat(columnStr, ",roomH");
    strcat(dataStr, ",");
    strcat(dataStr, tempStr);
  }

  temptemp = exhaust.readTemperature();
  if (!isnan(temptemp)) {
    dtostrf(temptemp, 4, 2, tempStr);
    strcat(columnStr, ",exhaustT");
    strcat(dataStr, ",");
    strcat(dataStr, tempStr);
  }

  temptemp = exhaust.readHumidity();
  if (!isnan(temptemp)) {
    dtostrf(temptemp, 4, 2, tempStr);
    strcat(columnStr, ",exhaustH");
    strcat(dataStr, ",");
    strcat(dataStr, tempStr);
  }
  Serial.println("Fail Check 9");
  sprintf(query, DATA_QUERY, columnStr, dataStr);
  cur->execute(query);

  memset(columnStr, 0, sizeof(columnStr));
  memset(dataStr, 0, sizeof(dataStr));
  delete cur;
  Serial.println("Fail Check 10");
}
2

Before opening the MySQL connection, you should check for at valid WiFi connection. If WiFi has dropped out, you must reconnect before using MySQL.

1 Like
3

but shouldn't it not matter because I check for a mysql connection first? I've never had it fail at the mysql connection.

4

After some more checks, I can confirm Wifi is not a problem.

I made a small code block that would check for wifi connectivity before every execute, and it did not ping any problems. In addition, I have proof the program can fail on ay of the executes, even the first one immediately after making the connection.

Something is going wrong in the execute function.

5

Are you sure that the N33 does not reboot when it "unsticks" itself?

Looking into the source code for the MySQL library, there is a lot of allocating and releasing memory going on. I do not know how the memory management on the Nano33 works, but the periodic hiccups may suggest that it either runs out of memory or there is some sort of garbage collection going on.

I know to little about the Nano33 to help you any further, but you could try to modify the library to have a static buffer which is large enough to handle the requests you require from it or you could file a bug for the library on github.

6

Is there any way for me to reboot the Arduino automatically?

Because I am honestly not confident enough to change much in the library, and even if I do end up doing so I would like something that could work in the mean time.

7

You could enable the watchdog timer and create an endless loop. AFAIR the Nano 33 uses Mbed-OS, so a call to either "mbed_reset()" or "NVIC_SystemReset()" should work, but look it up.

8

This topic was automatically closed 120 days after the last reply. New replies are no longer allowed.