Restarting problem!

[pedrotvo]

Hello, everyone!

So, I've been working on a project with the same configuration as this guy -> https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/59#issue-346506980

Made the changes @ChuckBell mentioned on the libraries and everything worked fine.

But, what happens is that everytime I start the board, the first request I send to the database works perfectly, but when the sketch send a second request, everything restart and the board take some (way more) time to connect to the database.

Here it is the source code of my project, with some comments for better understanding:

(This is my first project with arduino, so it might have some brute coding at some parts, but as my cat says "if it fit, it sit")

#include "SPI.h"
#include "Ethernet_W5500.h"
#include "sha1.h"
#include "mysql.h"
#include "LiquidCrystal.h"
#include <Keypad.h>

/*
  Do not use pins 4, 10, 50, 51 e 52
  cuz they're reserved to the ethernet shield
*/

//keyboard
const byte ROWS = 4;
const byte COLS = 3;
char keys[ROWS][COLS] = {
  {'1', '2', '3'},
  {'4', '5', '6'},
  {'7', '8', '9'},
  {'*', '0', '#'}
};

//keyboard pins
byte rowPins[ROWS] = {33, 28, 29, 31};
byte colPins[COLS] = {32, 34, 30};

//new keyboard
Keypad keypad = Keypad( makeKeymap(keys), rowPins, colPins, ROWS, COLS );

//lcd pins
LiquidCrystal lcd(12, 11, 25, 24, 23, 22);

//MAC AD (real MAC)
byte mac_addr[] = {0x70, 0xB3, 0xD5, 0x0A, 0xC0, 0x1D};

//server ip address
IPAddress server_addr(192, 168, 1, 3);

//new connector with user and password
Connector my_conn;
char user[] = "arduino";
char password[] = "arduino123";

//query to be executed 
char AUX[] = "SELECT nome, senha FROM acesso_usuarios.usuarios WHERE senha = ";
char query[100];

char auxNome[51];
char auxSenha[17];

//various pins
const int relePin = 9;
const int pinG = 26;
const int pinR = 27;

void reset_suav(){
  asm volatile("jmp 0");
}

//connect to the db
Connector Conecta() {

  Connector con;
  con.disconnect();
  Ethernet.begin(mac_addr);
  while (!Serial);
  delay(1000);
  Serial.println("Conectando...");
  if (con.mysql_connect(server_addr, 3306, user, password)) {
    Serial.println("Sucesso!");
  } else {
    Serial.println("Falha na conexão.");
  }

  return con;
}

//receiver for keypad inputs
  char key = 0;

  int i= 0;

void setup() {
  //turning on serial monitor
  Serial.begin(9600);

  Serial.println("entrou no setup");

  //pins i/o
  pinMode(pinG, OUTPUT); //green led
  pinMode(pinR, OUTPUT); //red led
  pinMode(relePin, OUTPUT);
  pinMode(10, OUTPUT);

  //"hey, ES, we're not using SD cards"
  digitalWrite(10, HIGH);

  //connect to the db
  my_conn = Conecta();

  //start lcd
  lcd.begin(16, 2);
  lcd.setCursor(0, 0);
}

void loop() {
  if(my_conn.is_connected()){
    lcd.print("Digite a senha:"); //"enter password"
    lcd.setCursor(0, 1);
    int auxi = 0;

    // password to be looked on db
    char senha[17];

    char auxSenha1[17];
    int auxKpd = 0;
    senha[0] = '\0';
    auxSenha1[0] = '\0';

    while (auxi == 0) {

      //receiving data from keyboard
      key = keypad.getKey();

      if (key != NO_KEY && key != '*' && key != '#') {
        lcd.print(key);
        auxKpd++;
        auxSenha1[i++] = key;
      }   

      if (key == '#') {        
        senha[0] = '\0';
        auxSenha1[0] = '\0';
        auxKpd = 0;
        lcd.clear();
        digitalWrite(pinR, HIGH);
        delay(1000);
        lcd.print("Cancelado");
        delay(1000);
        digitalWrite(pinG, LOW);
        lcd.clear();
        auxi = 1;
      }

      if (key == '*') {
       Serial.println(senha);
       Serial.println(auxSenha1);
       senha[0]='\0'; 
       for (int t = 0; t < auxKpd; t++){
          senha[t] = auxSenha1[t];
          Serial.println(senha);
        }
        senha[auxKpd] = '\0';
        Serial.println(senha);

        lcd.clear();
        lcd.print("Buscando senha");
        lcd.setCursor(0, 1);
        lcd.print("...");
        delay(1000);

        //busca no banco + libera ou não o pino do relé + acende led verde ou vermelho

        //main query + password inserted 
        strcat(query, AUX);
        strcat(query, senha);

        //send query to db
        my_conn.cmd_query(query);

        column_names *cols = my_conn.get_columns();
        for (int f = 0; f < cols->num_fields; f++) {
          Serial.print(cols->fields[f]->name);
          if (f < cols->num_fields - 1)
            Serial.print(',');
        }

        Serial.println();

        row_values *row = NULL;
        while (row = my_conn.get_next_row()) {
          for (int f = 0; f < cols->num_fields; f++) {
            Serial.print(row->values[f]);
            if (f < cols->num_fields - 1)
              Serial.print(',');
          }

          //catching received values
          strcpy(auxNome, row->values[0]);
          strcpy(auxSenha, row->values[1]);
          my_conn.free_row_buffer();
        }
        my_conn.free_columns_buffer();

        // if the inserted password is the same as the received from the query's result set
        if (strcmp(auxSenha, senha) == 0) {

          //activate relay and turn on the green led
          lcd.clear();
          lcd.print("Bem vindo(a):");
          lcd.setCursor(0, 1);
          lcd.print(auxNome);

          digitalWrite(relePin, HIGH);
          digitalWrite(pinG, HIGH);
          delay(1000);
          digitalWrite(relePin, LOW);
          digitalWrite(pinG, LOW);
          reset_suav();
        } else {

          // in case they're not the same or password not found
          // turn on red led and write msg on lcd

          digitalWrite(pinR, HIGH);
          lcd.clear();
          lcd.print("Usuario nao");
          lcd.setCursor(0, 1);
          lcd.print("encontrado.");
          delay(1000);
          digitalWrite(pinR, LOW);
          reset_suav();
        }
      }

    }

    auxKpd = 0;
    senha[0] = '\0';
    auxNome[0] = '\0';
    auxSenha[0] = '\0';
    auxSenha1[0] = '\0';
    i = 0;
    lcd.clear();
  }else{
    my_conn.disconnect();
    my_conn = Conecta();
  }
}

int get_free_memory(){
  extern char __bss_end;
  extern char *__brkval;
  int free_memory;
  if((int)__brkval == 0)
    free_memory = ((int)&free_memory) - ((int)&__bss_end);
  else
    free_memory = ((int)&free_memory) - ((int)&__brkval);
  return free_memory;
}

[ChuckBell]

What size board are you using? Random lockup and reboots are most likely caused by running out of memory. In my experience, whenever to get < 500 bytes of data memory, the Arduino becomes unstable.

Also, ensure you are calling the close() on the connector objects to fully remove the memory. Simply disconnecting doesn't free memory.

[pedrotvo]

I'm using a Mega-like board. So, probably not a memory issue. But, I tried to insert the close() function on my_conn.close() and the IDE is telling me that there is no member called "close" on class Connector.

[...]
// if the inserted password is the same as the received from the query's result set
        if (strcmp(auxSenha, senha) == 0) {

          //activate relay and turn on the green led
          lcd.clear();
          lcd.print("Bem vindo(a):");
          lcd.setCursor(0, 1);
          lcd.print(auxNome);

          digitalWrite(relePin, HIGH);
          digitalWrite(pinG, HIGH);
          delay(1000);
          digitalWrite(relePin, LOW);
          digitalWrite(pinG, LOW);
          my_conn.close();  //here
          reset_suav();
        } else {

          // in case they're not the same or password not found
          // turn on red led and write msg on lcd

          digitalWrite(pinR, HIGH);
          lcd.clear();
          lcd.print("Usuario nao");
          lcd.setCursor(0, 1);
          lcd.print("encontrado.");
          delay(1000);
          digitalWrite(pinR, LOW);
          my_conn.close();  //here
          reset_suav();
        }
      }

    }

    auxKpd = 0;
    senha[0] = '\0';
    auxNome[0] = '\0';
    auxSenha[0] = '\0';
    auxSenha1[0] = '\0';
    i = 0;
    lcd.clear();
  }else{
    my_conn.close();  //here
    my_conn.disconnect();
    my_conn = Conecta();
  }
}
[...]

This is where I inserted the close() function.

[ChuckBell]

Ok. What version of the connector are you using? You can find this in the mysql_packet.h file. (e.g. #define MYSQL_VERSION_STR "1.1.1a")

Note: the latest version is 1.1.1a. If you're not using that version, you should be. That version supports the close() methods.

On 10/2/18 11:52 AM, Pedro Otávio wrote:

I'm using a Mega-like board. So, probably not a memory issue. But, I tried to insert the |close()| function on |my_conn.close()| and the IDE is telling me that there is no member called "close" on class Connector.

|[...] // if the inserted password is the same as the received from the query's result set if (strcmp(auxSenha, senha) == 0) { //activate relay and turn on the green led lcd.clear(); lcd.print("Bem vindo(a):"); lcd.setCursor(0, 1); lcd.print(auxNome); digitalWrite(relePin, HIGH); digitalWrite(pinG, HIGH); delay(1000); digitalWrite(relePin, LOW); digitalWrite(pinG, LOW); my_conn.close(); //here reset_suav(); } else { // in case they're not the same or password not found // turn on red led and write msg on lcd digitalWrite(pinR, HIGH); lcd.clear(); lcd.print("Usuario nao"); lcd.setCursor(0, 1); lcd.print("encontrado."); delay(1000); digitalWrite(pinR, LOW); my_conn.close(); //here reset_suav(); } } } auxKpd = 0; senha[0] = '\0'; auxNome[0] = '\0'; auxSenha[0] = '\0'; auxSenha1[0] = '\0'; i = 0; lcd.clear(); }else{ my_conn.close(); //here my_conn.disconnect(); my_conn = Conecta(); } } [...] |

This is where I inserted the close() function.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/65#issuecomment-426327227, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4DtCyj1b8WRxIM-ZTmwO8Fl1n7Hmks5ug4vTgaJpZM4W984d.

[pedrotvo]

Oh, in fact, I was using version 1.0.4ga from LaunchPad.

I've downloaded the latest version, read the manual and made the changes on functions and libraries.

On the first attempt, I was just closing connection and running the code again, reconnecting, and it did not work. So I wrote the code in order to close the connection and restart the arduino everytime it sent queries to MySQL and it WORKED!

Thank you so much, doc!