ESP32 erratic with mySQL, wifiManager, VS1053, ADS1115 and MAX30102

Hi.
I am hoping that someone might be able to help me figure out a strange issue I’m having with an ESP32 (Sparkfun Thing Plus) used with the Arduino IDE.

Here is the project:

I am trying to connect to a mySQL database over WiFi to transmit sensor measurements into the database.
I am also using a WiFi manager to facilitate getting onto the WiFi network.

For sensors I have a Max30102 along with two channels of an ADS1115 ADC, both over i2c. I am also using a VS1053 over SPI and one of the onboard analog pins of the Sparkfun Thing Plus to use a potentiometer as a volume knob.

Here are the issues:

    • When I upload just the example code for the Max30102, the sensor works fine. When I implement that code into the larger sketch, it is unable to initialize the sensor. (yes, the two i2c devices have separate addresses, but they are both using different libraries so perhaps there is a conflict there somewhere.)
    • The WiFi manager works, and once connected, the Thing Plus begins to try to insert data into the mySQL database. Upon the first iteration, it is able to access the mySQL database with the specified IP address and credentials. Then I get this in the serial monitor:
18:46:12.944 -> After waiting 0.00 secs more in setup(), connection result is connected. Local IP: 192.168.100.232
18:46:13.553 -> VS1053 found
18:46:14.378 -> Requesting URL: /weatherwarlock.mp3
18:46:14.378 -> Initializing Max30102...
18:46:14.378 -> MAX30102 was not found. Please check wiring/power/solder jumper at MH-ET LIVE MAX30102 board. 
18:46:14.378 -> Single-ended readings from AIN0 with >3.0V comparator
18:46:14.378 -> ADC Range: +/- 6.144V (1 bit = 3mV/ADS1015, 0.1875mV/ADS1115)
18:46:14.378 -> Comparator Threshold: 1000 (3.000V)
18:46:14.378 -> [WM] freeing allocated params!
18:46:14.378 -> HWe have reached line 752 and will attempt to connect to mySQL server...
18:46:14.413 -> [SQL] Connecting to Server: 192.168.100.18 , Port =  3306
18:46:14.594 -> [SQL] Connect OK. Try reading packets
18:46:15.209 -> [SQL] Try parsing packets
18:46:15.209 -> [SQL] Try send_authentication packets
18:46:15.508 -> [SQL] Connected. Server Version = 8.0.22-0ubuntu0.20.04.3
18:46:16.024 -> INSERT INTO measures (IP, EDA_Unfiltered, EDA_Filtered, Heart_IR, Heart_Red, Temperature, Heart_Valid, pw_a, pw_b, EntryTime) VALUES ('t','-23328','-23312','0.000000','0.000000','0.000000','0','0.000000','0.000000','NOW()')
18:46:16.305 -> Complex Insert error
18:46:16.305 -> [SQL] Disconnected
18:46:16.305 -> Reached the end of the loop, waiting 10mS and then looping!
18:46:16.340 -> We have reached line 752 and will attempt to connect to mySQL server...
18:46:16.340 -> [SQL] Connecting to Server: 39.44.39.45 , Port =  3306
18:46:34.782 -> [SQL] Can't connect. Retry # 1
18:46:53.295 -> [SQL] Can't connect. Retry # 2
18:47:11.811 -> [SQL] Can't connect. Retry # 3
18:47:30.325 -> [SQL] Can't connect. Retry # 4
18:47:48.855 -> [SQL] Can't connect. Retry # 5
18:47:48.855 -> 
18:47:48.855 -> Connect failed. Trying again on next iteration.

It appears to change the server IP address to something else, and this number isn’t random, it is the same incorrect IP address every time. This IP address is nowhere in my code. I cannot find any conflicting variable names. I’m totally stumped.

The code that I am using is attached. I tried to embed it but apparently it goes beyond the 9000 character limit of this forum - its basically a combination of a bunch of different example codes from the respective libraries I have been using.

Any help would be greatly appreciated. Thanks!

measuresV0.9.1_Simple.ino (30.4 KB)

defines.h (13.1 KB)

I did not know there was a ESP32 library that allowed the ESP32 over the Internet to directly access a SQL server.

Without looking at your code, have you configured the SQL server to allow remote access?

Your INSERT_DATA is 185 chars in length. This is just a format for sprintf(). You use it when writing into 'query' which is only 128 chars in length. That may be your problem.

Also, it is quite difficult to follow your code with all the #if/#else statements. If you are using an ESP32, you can figure out which includes are true/false and just streamline your code. Especially when posting for help

The main culprit of the erratic behavior is, as @blh64 said, the sprintf() overwrites the too small buffer and destroys the adjacent memory locations.

There are several other improvements you can do, such as

  1. Moving the data read and query insertion into functions
  2. Using staggered type of timer to read and insert sensor data, just whenever necessary, not too frequently in the loop()
  3. Using DoubleResetDetect feature to avoid Config Portal everytime you start. Check ConfigOnDoubleReset

The following is just a snippet of code, taking from yours, to demo what you can do

// Must use bigger buffer
char query[256];

...

void readSensorData()
{
  /////////////////ADS1115////////////////////
  int16_t adc0;
  int16_t adc1;

  // Comparator will only de-assert after a read
  edaFilt = ads0.getLastConversionResults();
  edaUnfilt = ads1.getLastConversionResults();
...
    particleSensor.nextSample(); //We're finished with this sample so move to next sample
    //Serial.println(SpO2);
  }
#endif 
}

void insertSensorData()
{
  /////mySQL///////////////
  Serial.println("connectNonBlocking...");
  //if (conn.connect(server_addr, server_port, user, mySQLpassword)) {
  if (conn.connectNonBlocking(server_addr, server_port, user, mySQLpassword) != RESULT_FAIL) {
    delay(500);
    runInsert();
    conn.close();                     // close the connection
  } else {
    Serial.println("\nConnect failed. Trying again on next iteration.");
  }
}

void check_status() 
{
  static ulong checkstatus_timeout  = 0;
  static ulong checkwifi_timeout    = 0;
  static ulong readdata_timeout     = 30000UL;
  
  static ulong current_millis;

#define WIFICHECK_INTERVAL    1000UL
#define HEARTBEAT_INTERVAL    10000UL

#define DATAREAD_INTERVAL     30000UL

  current_millis = millis();

  // Check WiFi every WIFICHECK_INTERVAL (1) seconds.
  if ((current_millis > checkwifi_timeout) || (checkwifi_timeout == 0)) {
    check_WiFi();
    checkwifi_timeout = current_millis + WIFICHECK_INTERVAL;
  }

  // Print hearbeat every HEARTBEAT_INTERVAL (10) seconds.
  if ((current_millis > checkstatus_timeout) || (checkstatus_timeout == 0)) {
    heartBeatPrint();
    checkstatus_timeout = current_millis + HEARTBEAT_INTERVAL;
  }

  // Read Data every DATAREAD_INTERVAL (30) seconds.
  if ((current_millis > readdata_timeout) ) {
    readSensorData();
    insertSensorData();
    readdata_timeout = current_millis + DATAREAD_INTERVAL;
  }
}

void loop() 
{
  ////////////WiFi Manager////////////////////
  check_status(); 
}