ESP8266 & MySQL_Connection: crash when creating cursor.

I’m having a serious problem with the MySQL_Connection.
I have two functions where I do basically the same thing: create a connection, create a cursor, write query to database, clean up. At one point it works exactly as expected, at the other point the exact same code causes a crash, and I just can’t find any possible reason for it.

When I have LOG_MYSQL defined, it crashes at line 183. If I do not have this defined, the whole thing runs fine, including the sendData() function. I’m using the same code to create a cursor there (line 125). It inserts the data in my database, no crashes.

It’s called in the main .ino like this:

#include <HydroMonitorMySQL.h>
HydroMonitorMySQL MySQL;

setup() {
  MySQL.begin();
}

The .h that goes with it:

/*
 * HydroMonitorMySQL
 *
 */

#ifndef HYDROMONITORMYSQL_H
#define HYDROMONITORMYSQL_H

#include <WiFiUdp.h>
#include <ESP8266WebServer.h>
#include <Time.h>
#include <HydroMonitorCore.h>

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

class HydroMonitorMySQL
{
  public:

    struct Settings {
      char mySQLHostname[100];
      char mySQLUsername[32];
      char mySQLPassword[32];
    };

    HydroMonitorMySQL();
    void begin();

    void sendData(HydroMonitorCore::SensorData);
    String settingsHtml(void);
    void updateSettings(String[], String[], uint8_t);
    void checkCredentials(void);
  bool loginValid;
  void writeTrace(char*);
  void writeDebug(char*);
  void writeTesting(char*);
  void writeInfo(char*);
  
  private:
    char* username;
    char* password;
  void checkCredentials (char*, char*, char*);
  void writeLog(char*);
    
    Settings settings;
    HydroMonitorCore core;
};
#endif

And finally the error message and decoded stack trace:

Exception 9: LoadStoreAlignmentCause: Load or store to an unaligned address
Decoding 14 results
0x4020c994: MySQL_Connection::connected() at /home/wouter/Arduino/libraries/MySQL_Connector_Arduino-master/src/MySQL_Cursor.cpp line 275
:  (inlined by) MySQL_Cursor::MySQL_Cursor(MySQL_Connection*) at /home/wouter/Arduino/libraries/MySQL_Connector_Arduino-master/src/MySQL_Cursor.cpp line 47
0x40213254: operator new(unsigned int) at /home/wouter/.arduino15/packages/esp8266/hardware/esp8266/2.3.0/cores/esp8266/abi.cpp line 84
0x402054f1: HydroMonitorMySQL::writeLog(char*) at /home/wouter/Arduino/libraries/HydroMonitor/src/HydroMonitorMySQL.cpp line 184
0x4010068c: free at /home/wouter/.arduino15/packages/esp8266/hardware/esp8266/2.3.0/cores/esp8266/umm_malloc/umm_malloc.c line 1733
0x4020c92e: MySQL_Connection::connect(IPAddress, int, char*, char*) at /home/wouter/Arduino/libraries/MySQL_Connector_Arduino-master/src/MySQL_Connection.cpp line 84
0x40213344: esp_yield at /home/wouter/.arduino15/packages/esp8266/hardware/esp8266/2.3.0/cores/esp8266/core_esp8266_main.cpp line 56
0x402055d1: HydroMonitorMySQL::begin() at /home/wouter/Arduino/libraries/HydroMonitor/src/HydroMonitorMySQL.cpp line 30
0x402037b0: setup at /home/wouter/Arduino/hydromonitor-core/hydromonitor-core.ino line 283
0x40213558: configModeCallback(WiFiManager*) at /home/wouter/Arduino/hydromonitor-core/hydromonitor-core.ino line 514
0x40202880: blinkTimerCallback(void*) at /home/wouter/Arduino/hydromonitor-core/hydromonitor-core.ino line 945
0x40202e74: _M_manager at /home/wouter/Arduino/hydromonitor-core/hydromonitor-core.ino line 945
0x40202d78: _M_invoke at /home/wouter/Arduino/hydromonitor-core/hydromonitor-core.ino line 945
0x40213388: loop_wrapper at /home/wouter/.arduino15/packages/esp8266/hardware/esp8266/2.3.0/cores/esp8266/core_esp8266_main.cpp line 56
0x40100718: cont_norm at /home/wouter/.arduino15/packages/esp8266/hardware/esp8266/2.3.0/cores/esp8266/cont.S line 109

This is the .cpp file:

#include <HydroMonitorMySQL.h>

/*
* Take care of database connectivity (expects networking to be enabled).
*/

/*
* The constructor.
*/
HydroMonitorMySQL::HydroMonitorMySQL() {
}

/*
* Start the network services.
*/
void HydroMonitorMySQL::begin() {

 if (MYSQL_EEPROM > 0)
   EEPROM.get(MYSQL_EEPROM, settings);
 
 // Default settings, to be applied if the first byte is 255, which indicates the EEPROM
 // has never been written.
 if ((int)settings.mySQLHostname[0] == 255) {
   strlcpy(settings.mySQLHostname, MYSQL_HOSTNAME, 100);
   strlcpy(settings.mySQLUsername, MYSQL_USERNAME, 32);
   strlcpy(settings.mySQLPassword, MYSQL_PASSWORD, 32);
 }
 checkCredentials();
 writeTesting("HydroMonitorMySQL: configured MySQL connection.");
 if (loginValid) 
   writeTesting("HydroMonitorMySQL: stored login credentials valid.");
 else 
   writeTesting("HydroMonitorMySQL: stored login credentials invalid.");
 return;
}

/**
* Send the latest sensor data to the database.
*/
void HydroMonitorMySQL::sendData(HydroMonitorCore::SensorData sensorData) {

 WiFiClient client;
 MySQL_Connection conn((Client *)&client);
 IPAddress server_ip;
 WiFi.hostByName(settings.mySQLHostname, server_ip);
 if (conn.connect(server_ip, 3306, settings.mySQLUsername, settings.mySQLPassword) == false) 
   return; // Don't try to continue if we can't connect to the server successfully.
   
 // Prepare the data string to be sent to the server.
 char query[290] = "";
 strcat(query, "INSERT INTO ch_");
 strcat(query, MYSQL_USERNAME);
 strcat(query, ".");
 strcat(query, MYSQL_DATA);
 strcat(query, " ");
 char fields[110] = "(";
 char values[90] = " VALUES (";
 char val[10];
 
#ifdef USE_EC_SENSOR
   strcat(fields, "EC, ");
   strcat(values, dtostrf(sensorData.EC, 4, 2, val));
   strcat(values, (", "));
#endif
#ifdef USE_BRIGHTNESS_SENSOR
   strcat(fields, "brightness, ");
   sprintf(val, "%d", sensorData.brightness);
   strcat(values, val);
   strcat(values, ", ");
#endif
#ifdef USE_WATERTEMPERATURE_SENSOR
   strcat(fields, "watertemp, ");
   strcat(values, dtostrf(sensorData.waterTemp, 4, 2, val));
   strcat(values, ", ");
#endif
#ifdef USE_WATERLEVEL_SENSOR
   strcat(fields, "waterlevel, ");
   strcat(values, dtostrf(sensorData.waterLevel, 4, 2, val));
   strcat(values, ", ");
#endif
#ifdef USE_PRESSURE_SENSOR
   strcat(fields, "pressure, ");
   strcat(values, dtostrf(sensorData.pressure, 4, 2, val));
   strcat(values, ", ");
#endif
#ifdef USE_TEMPERATURE_SENSOR
   strcat(fields, "airtemp, ");
   strcat(values, dtostrf(sensorData.temperature, 4, 2, val));
   strcat(values, ", ");
#endif
#ifdef USE_HUMIDITY_SENSOR
   strcat(fields, "humidity, ");
   strcat(values, dtostrf(sensorData.humidity, 4, 2, val));
   strcat(values, ", ");
#endif
#ifdef USE_PH_SENSOR
   strcat(fields, "ph, ");
   strcat(values, dtostrf(sensorData.pH, 4, 2, val));
   strcat(values, ", ");
#endif
#ifdef USE_DO_SENSOR
   strcat(fields, "DO, ");
   strcat(values, dtostrf(sensorData.DO, 4, 2, val));
   strcat(values, ", ");
#endif
#ifdef USE_ORP_SENSOR
   strcat(fields, "ORP, ");
   strcat(values, dtostrf(sensorData.ORP, 4, 2, val));
   strcat(values, ", ");
#endif
#ifdef USE_GROWLIGHT
   strcat(fields, "growlight, ");
   if (sensorData.growlight) strcat(values, "1, ");
   else strcat(values, "0, ");
#endif
 
 strncat(query, fields, strlen(fields)-2);
 strcat(query, ")");
 strncat(query, values, strlen(values)-2);
 strcat(query, ");");

 writeDebug("HydroMonitorMySQL: Sending out data.");
 writeDebug(query);
 
 MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);  // This one works pefectly.
 cur_mem->execute(query);
 delete cur_mem; 

 conn.close();
 client.stop();
 return;
}

/*
* Check a set of login credentials to a MySQL database.
*
* Returns true if valid; false if invalid.
*/ 
void HydroMonitorMySQL::checkCredentials() {
 checkCredentials(settings.mySQLHostname, settings.mySQLUsername, settings.mySQLPassword);
 return;
}

void HydroMonitorMySQL::checkCredentials(char* host, char* un, char* pw) {
 IPAddress server_ip;
 WiFi.hostByName(host, server_ip);
 WiFiClient client;
 MySQL_Connection conn((Client *)&client);
 loginValid = conn.connect(server_ip, 3306, un, pw);
 conn.close();
 client.stop();
 return;
}


/*
* Write log message to the database.
*/
void HydroMonitorMySQL::writeLog(char *msg) {

#if defined(LOG_SERIAL) && defined(SERIAL)
 Serial.println(msg);
#endif

#ifdef LOG_MYSQL
 if (loginValid) {

   WiFiClient client;
   MySQL_Connection conn((Client *)&client);
   IPAddress server_ip;
   WiFi.hostByName(settings.mySQLHostname, server_ip);
   if (conn.connect(server_ip, 3306, settings.mySQLUsername, settings.mySQLPassword) == false) 
     return; // Don't try to continue if we can't connect to the server successfully.

   // Prepare the command string to be sent to the server.
   char query[34 + strlen(msg) + strlen(MYSQL_LOG)];
   strcat(query, "INSERT INTO ");
   strcat(query, MYSQL_LOG);
   strcat(query, " (message) VALUES (");
   strncat(query, msg, strlen(msg));
   strcat(query, ");");
   Serial.println("Creating cursor.");
   MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);  // Crash happens at this point!
   Serial.println("Got cursor, executing query.");
   cur_mem->execute(query);
   delete cur_mem; 
   conn.close();
   client.stop();
 }
#endif
 return;
}

void HydroMonitorMySQL::writeTrace(char *msg) {
 if (LOGLEVEL >= LOG_TRACE)
   writeLog(msg);
}

void HydroMonitorMySQL::writeDebug(char *msg) {
 if (LOGLEVEL >= LOG_DEBUG)
   writeLog(msg);
}

void HydroMonitorMySQL::writeTesting(char *msg) {
 if (LOGLEVEL >= LOG_TESTING)
   writeLog(msg);
}

void HydroMonitorMySQL::writeInfo(char *msg) {
 if (LOGLEVEL >= LOG_INFO)
   writeLog(msg);
}

/*
* The settings as HTML.
*/
String HydroMonitorMySQL::settingsHtml() {
 String html;
 html = F("\
     <tr>\n\
       <th colspan=\"2\">Networking settings.</th>\n\
     </tr><tr>\n\
       <td>MySQL host name:</td>\n\
       <td><input type=\"text\" name=\"network_mysql_hostname\" value=\"");
 html += String(settings.mySQLHostname);
 html += F("\"></td>\n\
     </tr><tr>\n\
       <td>MySQL username:</td>\n\
       <td><input type=\"text\" name=\"network_mysql_username\" value=\"");
 html += String(settings.mySQLUsername);
 html += F("\"></td>\n\
     </tr><tr>\n\
       <td>MySQL password:</td>\n\
       <td><input type=\"text\" name=\"network_mysql_password\" value=\"");
 html += String(settings.mySQLPassword);
 html += F("\"></td>\n\
     </tr><tr>\n\
       <td></td>\n");
 if (loginValid) html += F("<td><span style=\"color:green\">Login valid.</span></td>");
 else html += F("<td><span style=\"color:red\">Login invalid.</span></td>");
 html += F("\
     </tr>");
 return html;
}

/*
* Update the settings.
*/
void HydroMonitorMySQL::updateSettings(String keys[], String values[], uint8_t nArgs) {
 char hostname[100];
 char username[32];
 char password[32];
 for (uint8_t i=0; i<nArgs; i++) {
   if (keys[i] == "network_mysql_hostname") {
     if (keys[i].length() < 100) values[i].toCharArray(hostname, 100);
   }
   if (keys[i] == "network_mysql_username") {
     if (keys[i].length() < 32) values[i].toCharArray(username, 32);
   }
   if (keys[i] == "network_mysql_password") {
     if (keys[i].length() < 32) values[i].toCharArray(password, 32);
   }
 }

 // If nothing changed, just keep the original settings as is.  
 if (settings.mySQLHostname == hostname 
     && settings.mySQLUsername == username 
     && settings.mySQLPassword == password)
     return;

 // If any settings changed, check whether the login is valid. Only change the settings if the new
 // credentials are correct.
 checkCredentials(hostname, username, password);
 if (loginValid) {
   strlcpy(settings.mySQLHostname, hostname, 100);
   strlcpy(settings.mySQLUsername, username, 32); 
   strlcpy(settings.mySQLPassword, password, 32);
 }
 EEPROM.put(MYSQL_EEPROM, settings);
 EEPROM.commit();
 return;
}

Which Arduino are you running this on? Where does the log file get created?

Running on an ESP8266, the 12E module on my own design PCB.

The error is as said is a decoded stack trace; simply from the serial monitor.

For added interest: now I moved the code that handles the query into a separate function, it suddenly works fine?! I really don't get it.

Hmm! PaulS If you read the post it says which processor he is using DUH!

wvmarle:
For added interest: now I moved the code that handles the query into a separate function, it suddenly works fine?! I really don't get it.

No idea about MySql connector, no idea about ESP. But this symptom can often be an indication of memory problems. Try without the use of String (capital S).

I know - but with almost 40 kB of available RAM and some 650-700 kB remaining flash that shouldn't be a problem.

I'm looking into slowly getting rid of String, and getting my fixed strings out of RAM and into PROGMEM as I have over 40 kB worth of global variables in total... and hope to add lots of html/CSS. The String is just so convenient.