NodeMCU crashes when table has too many rows

[MikeAUT]

Hi all, After altering the examples, i got my NodeMCU fetching data from my SQL Database. But one thing is strange. At the current setup, the NodeMCU is only getting proper informations without crashing when the table holds max 6 rows of data. As soon i add one row, I get

Memory error.
Bad mojo. EOF found reading column header.

in the console and the NodeMCU restarts.

I limit the result in the query with 5. So why does it even matter when the table grows?

My Sketch:

#include <ESP8266WiFi.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>

// MySQL
IPAddress server_addr(192, 168, 1, 5);
char user[] = "xxxxxxxxxx";
char password[] = "xxxxxxxxx";
char default_db[] = "arduino";

// Queries
char selectQuery[] = "SELECT * FROM calendar ORDER BY date, id LIMIT 5;";

char ssid[] = "xxxxxxx";
char pass[] = "xxxxxxxxx";
byte mac[6];
unsigned int localPort = 8888;

WiFiClient client;
MySQL_Connection conn(&client);
MySQL_Cursor* cursor;

#include <MikeysTimer.h>
PulseTimer pulseCheckWiFi;
PulseTimer pulse1secPulse;
// -----------

void setup() {
  Serial.begin(115200);
  WiFi.mode(WIFI_STA);
  connectWiFi();
  printWiFiStatus();

  // Connect MySQL
  if (conn.connect(server_addr, 3306, user, password, default_db)) {
    delay(1000);
  }
  else
    Serial.println("Connection failed.");
}

void loop() {
  //if (pulseCheckWiFi.pulse) {
    //checkWiFi();
  //}

  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  cur_mem->execute(selectQuery);
  // Fetch the columns and print them
  column_names *cols = cur_mem->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();
  // Read the rows and print them
  row_values *row = NULL;
  do {
    row = cur_mem->get_next_row();
    if (row != NULL) {
      for (int f = 0; f < cols->num_fields; f++) {
        Serial.print(row->values[f]);
        if (f < cols->num_fields - 1) {
          Serial.print(',');
        }
      }
      Serial.println();
    }
  } while (row != NULL);
  // Deleting the cursor also frees up memory used
  delete cur_mem;

  delay(10000);
}

void connectWiFi() {
  // attempt to connect to Wifi network:
  WiFi.begin(ssid, pass);
  Serial.println("connecting");
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }
  Serial.println("WiFi Connected");
}

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:
  WiFi.macAddress(mac);
  Serial.print("MAC: ");
  Serial.print(mac[5], HEX);
  Serial.print(":");
  Serial.print(mac[4], HEX);
  Serial.print(":");
  Serial.print(mac[3], HEX);
  Serial.print(":");
  Serial.print(mac[2], HEX);
  Serial.print(":");
  Serial.print(mac[1], HEX);
  Serial.print(":");
  Serial.println(mac[0], HEX);
  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");
}

void checkWiFi() {
  if (WiFi.status() != WL_CONNECTED) {
    Serial.println("Connection lost. Reconnecting!");
    WiFi.begin(ssid, pass);
  }
}

This is my table

+--------+-----------------------+------+-----+----------------------+--------------------------------+
| Field  | Type                  | Null | Key | Default              | Extra                          |
+--------+-----------------------+------+-----+----------------------+--------------------------------+
| id     | smallint(10) unsigned | NO   | PRI | NULL                 | auto_increment                 |
| date   | timestamp(6)          | NO   |     | CURRENT_TIMESTAMP(6) | on update CURRENT_TIMESTAMP(6) |
| text   | char(50)              | NO   |     | Standard Text        |                                |
| allday | bit(1)                | YES  |     | b'1'                 |                                |
| icon   | tinyint(4)            | YES  |     | NULL                 |                                |
+--------+-----------------------+------+-----+----------------------+--------------------------------+

I don't think that this query is too much for the ESP or am I wrong?

[ChuckBell]

Hi. What version of MySQL are you using? This error normally manifests when there is one or more packet left to read from the server. If I know which version you’re using, I may be able to suggest a modification that may work.

On Apr 22, 2020, at 2:37 PM, MikeAUT notifications@github.com wrote:

Hi all, After altering the examples, i got my NodeMCU fetching data from my SQL Database. But one thing is strange. At the current setup, the NodeMCU is only getting proper informations without crashing when the table holds max 6 rows of data. As soon i add one row, I get

Memory error. Bad mojo. EOF found reading column header. in the console and the NodeMCU restarts.

I limit the result in the query with 5. So why does it even matter when the table grows?

My Sketch:

include

include

include

// MySQL IPAddress server_addr(192, 168, 1, 5); char user[] = "xxxxxxxxxx"; char password[] = "xxxxxxxxx"; char default_db[] = "arduino";

// Queries char selectQuery[] = "SELECT * FROM calendar ORDER BY date, id LIMIT 5;";

char ssid[] = "xxxxxxx"; char pass[] = "xxxxxxxxx"; byte mac[6]; unsigned int localPort = 8888;

WiFiClient client; MySQL_Connection conn(&client); MySQL_Cursor* cursor;

include

PulseTimer pulseCheckWiFi; PulseTimer pulse1secPulse; // -----------

void setup() { Serial.begin(115200); WiFi.mode(WIFI_STA); connectWiFi(); printWiFiStatus();

// Connect MySQL if (conn.connect(server_addr, 3306, user, password, default_db)) { delay(1000); } else Serial.println("Connection failed."); }

void loop() { //if (pulseCheckWiFi.pulse) { //checkWiFi(); //}

MySQL_Cursor cur_mem = new MySQL_Cursor(&conn); cur_mem->execute(selectQuery); // Fetch the columns and print them column_names cols = cur_mem->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(); // Read the rows and print them row_values *row = NULL; do { row = cur_mem->get_next_row(); if (row != NULL) { for (int f = 0; f < cols->num_fields; f++) { Serial.print(row->values[f]); if (f < cols->num_fields - 1) { Serial.print(','); } } Serial.println(); } } while (row != NULL); // Deleting the cursor also frees up memory used delete cur_mem;

delay(10000); }

void connectWiFi() { // attempt to connect to Wifi network: WiFi.begin(ssid, pass); Serial.println("connecting"); while (WiFi.status() != WL_CONNECTED) { delay(500); Serial.print("."); } Serial.println("WiFi Connected"); }

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: WiFi.macAddress(mac); Serial.print("MAC: "); Serial.print(mac[5], HEX); Serial.print(":"); Serial.print(mac[4], HEX); Serial.print(":"); Serial.print(mac[3], HEX); Serial.print(":"); Serial.print(mac[2], HEX); Serial.print(":"); Serial.print(mac[1], HEX); Serial.print(":"); Serial.println(mac[0], HEX); 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"); }

void checkWiFi() { if (WiFi.status() != WL_CONNECTED) { Serial.println("Connection lost. Reconnecting!"); WiFi.begin(ssid, pass); } } This is my table

+--------+-----------------------+------+-----+----------------------+--------------------------------+ | Field | Type | Null | Key | Default | Extra | +--------+-----------------------+------+-----+----------------------+--------------------------------+ | id | smallint(10) unsigned | NO | PRI | NULL | auto_increment | | date | timestamp(6) | NO | | CURRENT_TIMESTAMP(6) | on update CURRENT_TIMESTAMP(6) | | text | char(50) | NO | | Standard Text | | | allday | bit(1) | YES | | b'1' | | | icon | tinyint(4) | YES | | NULL | | +--------+-----------------------+------+-----+----------------------+--------------------------------+

I don't think that this query is too much for the ESP or am I wrong?

— 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/131, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYC32D2BPWTGCMH7W2LRN42PNANCNFSM4MONAYDA.

[MikeAUT]

Hi Chuck, As I see now, I'm running a pretty old version of MySQL: 5.7.24 on Ubuntu Server 16.04 LTS

[ChuckBell]

Ok. I will test a mockup on my system and see if I can find anything that may help. Please I’ve me a few days. ;)

On Apr 25, 2020, at 2:19 AM, MikeAUT notifications@github.com wrote:

Hi Chuck, As I see now, I'm running a pretty old version of MySQL: 5.7.24

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/131#issuecomment-619329024, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYE7Z2TE3RVUPEIDS3TROJ6GDANCNFSM4MONAYDA.

[MikeAUT]

Thank you very much, but I don't think this is necessary anymore. I made many changes in my sketch as well in my database. No error occured for a long time now. I really don't know whats different now. Even with 500+ rows, no error occurs. ;)

[ChuckBell]

Serendipity! :)

On Apr 25, 2020, at 9:28 AM, MikeAUT notifications@github.com wrote:

Thank you very much, but I don't think this is necessary anymore. I made many changes in my sketch as well in my database. No error occured for a long time now. I really don't know whats different now. Even with 500+ rows, no error occurs. ;)

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/131#issuecomment-619379808, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYHBJ6SVIFGYKW5ATY3ROLQPXANCNFSM4MONAYDA.