MySQL and Memory Management

Projects Programming
1

I've built a small project that uses an Arduino Mega2560, a Freetronics 2004 shield, an Arduino Ethernet Shield 2, and a Weatherproof Ultrasonic Sensor w/ Separate Probe (RB-Dfr-720) to monitor a 2,600 gallon water tank, determine the water level, and then update a MySQL DB table with the data it gathered. This thing will run 24x7x365 and I need it to be stable. I live off grid and the tank, which serves my small house, is 1,300 feet away, so I don't want to be hiking there all time to check on things or reset the set up.

I've been working thru various issues with this project (LCD conflicting with ethernet, sensor stability issues (now using NewPing), my learning curve, etc) but I think I've now got all devices working and am in the code refactoring phase for stability and efficiency.

Here's the deal... I've set the system up in my house and just let it run for hours/days to check on stability. But, I get random hangs where the system LCD displays "Reading Sensor" (my display logic) and just stalls there. I assumed it was still stability issues with the sensor, even though NewPing is supposed to address that issue. Anyway, in trouble shooting I added a slew of other LCD display writes to indicate where I am in the code, and have found that the sensor seems to hang fairly consistently on a new debug display statement I added "Writing to DB". I'm now looking at the code and am wondering if my program is overflowing memory and creating more or less random fails.

I'd assumed that the Arduino IDE wraps my code in a loader that did some memory management, but obviously that's not the case. So after that long preamble here's the question...can anyone help me understand if lots of LCD display statements and/or my MySQL cursor create and insert logic is blowing thru the Flash memory.

Here's the MySQL insert logic. I assumed that my Delete Cursor statement was clearing Flash but maybe it's not. And I also understand that strings take up a fair amount of SRAM and maybe because of all my LCD writes I'm blowing that out. Any memory management tips or tutorials you can refer me to would be great. Or, if you see a stupid problem in my DB write code hit me with your feedback. I have thick skin so will take all responses as helpful. Thanks...

    if (priorgallons != gallons) {
      lcd.setCursor( 0, 0 );
      lcd.print("Writing To DB   ");
      lcd.setCursor( 0, 1 );
      lcd.print("             ");
      char INSERT_SQL[] = "INSERT INTO welldb.well_tank (inches, gallons) VALUES (%d, %d);";
      char query[255];
      sprintf(query, INSERT_SQL, waterheight, gallons);
      // Initiate the query class instance
      MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
      // Execute the query
      cur_mem->execute(query);
      // Delete the cursor to free up memory
      delete cur_mem;
      priorgallons = gallons;
      lcd.setCursor( 0, 0 );
      lcd.print("DB Write Done   ");
      lcd.setCursor( 0, 1 );
      lcd.print("             ");
2

This line should be called once and only once in your program. I would expect that calling it in loop() or a subroutine will eventually overflow ram memory.

MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);

3

Interesting...

That is the way Dr. Bell (author of MySQL Library) shows it in his examples. I've reached out to him as well and will follow up if that is creating an issue.

I also added the MemoryFree library to the sketch and don't see any memory leaks, SDRAM usage stays exactly stable while the sketch is running...

Appreciate the feedback though, and will check into the statement...

4

What is SDRAM?

The “new” keyword allocates memory and creates a new object in ram. The Mega has 8kb of ram. Any leak is going crash things pretty quickly.

5

Some of you guys need to both understand how the SQL librararies work, AND read the OPs code. It is perfectly normal to create a request object, perform the query, then delete the object. That is EXACTLY what the OPs code snippet does. There IS no memory leak indicated, and after the delete, the heap will look EXACTLY like it did before the new, so there also is NO fragmentation.

Regards,
Ray L.

6

WattsThat: SDRAM was a typo, should have been SRAM...

RayLivingston: Agree, my understanding is that SRAM is where strings and other such variables get created and stored. I didn’t think that creating the DB object and then deleting it in the loop would be an issue, and using the MemoryFree library confirmed that.

Both: Thanks for looking at this and taking the time to reply. Truly appreciated...

7

  1. Fragmentation and memory leaks are not the same thing.

  2. The problem (IF it is in that snippet ) is with the call to execute() which looks like it is blocking and will lock up if there is a problem (for example with the comm's to the PC.

A far better way is to send the data to the PC over serial and use an app on the PC to write it to your DB.

Mark

8

I think i mentioned that the Arduino set up is well over 1,300 feet from the PC running the DB. Network connectivity, I believe, is the only practical solution. Also, if the network solution is a poor option to get data out and serial comms are the best solution then it doesn’t seem like there’s much point in having ethernet or Wi-Fi libraries at all...maybe I’m missing your point...

9

I can't tell if you are using WIFI or e-net but it looks to me like you are getTing problems with your comms and that the execute method CAN NOT COPE WITH ERRORS.

Mark