SQLite Forum

How to free max heap size on execution of sqlite query using sqlite3_exec?
Login

How to free max heap size on execution of sqlite query using sqlite3_exec?

(1.1) By Mangal Prajapati (V16be1p11) on 2021-11-23 04:28:14 edited from 1.0 [link] [source]

I have seen that when I execute sqlite query each time max heap size continuously increasing I want to free it on my device.

How to free it after execution? Because I know that once ram done it's work it is not need to occupy heap memory. I need technical solution for this problem.

Just for your information I am writing query for get and set 2104 records.

(2) By Gunter Hick (gunter_hick) on 2021-11-23 08:13:19 in reply to 1.1 [link] [source]

Which "device", which operating system, which release of sqlite, which schema, which query?

Can you replicate the problem using sqlite shell?

There are basically two possibilities:

a) There is an actual memory leak (indicated by heap memory increasing linearly each time you re-run the query), in which case you need to show that this is happening in sqlite and not in your own code.

b) What you are observing is heap memory increasing to a point where queries can be run by re-using memory space already acquired.

(3.1) By Mangal Prajapati (V16be1p11) on 2021-11-23 15:42:34 edited from 3.0 in reply to 2 [link] [source]

I am using verifone device c680. With verix os. I am using select and insert query.

This is happening for 2104 records.

heap max increases for each time when different query get executed

I want to know why heap max memory does not get release once sqlite3_exec executed.

Is there any function for release it?

(4) By Gunter Hick (gunter_hick) on 2021-11-23 15:47:15 in reply to 3.0 [link] [source]

Sorry but that opens up more questions than it answers.

Did you build SQLite for verix OS yourself?

Have you checked to see if each execution of the query adds about the same amount to heap memory?

Have you used valgrind or whatever tool there is available on verix OS to check for memory leaks?

Have you tried reproducing the error on a less obscure OS?

Can you provide a minimal example (schema and queries in plain SQL) that illustrates the problem?

(6) By Mangal Prajapati (V16be1p11) on 2021-11-23 16:16:31 in reply to 4 [link] [source]

  1. No

  2. Depending on number of records it increases

  3. Verix provided function to check utilize heap that shows heap continuously increases

  4. No

  5. I must have to check this thing in my running application on same device

(7) By Larry Brasfield (larrybr) on 2021-11-23 16:46:24 in reply to 6 [link] [source]

To Gunter's question, "Have you checked to see if each execution of the query adds about the same amount to heap memory?", answer was:

Depending on number of records it increases

In other words, "No, allocated memory does not appear to grow approximately linearly as a query is repeated."

You have not described a memory "leak". Nor have you shown in what manner memory allocation increases over time. Hence, nobody can distinguish between something abnormal or objectionable and expectable, ordinary allocation growth toward a semi-stable state.

To Gunter's question, "Can you provide a minimal example (schema and queries in plain SQL) that illustrates the problem?", answer was:

I must have to check this thing in my running application on same device

Until you provide enough information for somebody else to observe your (not quite) reported problem, the assumption by others will be that what you see is a feature of your application rather than a deficiency of the SQLite library.

You may find this doc on SQLite memory allocation useful, particularly the configuration and debug tips.

(8) By Mangal Prajapati (V16be1p11) on 2021-11-23 17:14:18 in reply to 7 [link] [source]

How you can tell this is memory leak?

I am simply run sqlite query using sqlite3_exec() in c.

I hope you get understand the example std::stringstream sql;

sql<<"select IFNULL(intTicketTypeID,0),IFNULL(strTicketNo,''),IFNULL(strSubTicketNo,0)" ",IFNULL(intFromStationID,0),IFNULL(intToStationID,0),IFNULL(intFullTicketsAmt,0)" ",IFNULL(dteTicketDateTime,''),IFNULL(intUserID,0),IFNULL(strCardNumber,''),IFNULL(strCardUID,'')" ",IFNULL(intOldBalance,0),IFNULL(intNewBalance,0),IFNULL(intMachineID,0),IFNULL(intRouteID,0)" ",IFNULL(intTripID,0),IFNULL(intShiftID,0),IFNULL(intVehicleID,0),IFNULL(intFullTicketsAmt,0)" ",IFNULL(intFullTickets,0),IFNULL(intHalfTicketsAmt,0),IFNULL(intHalfTickets,0),IFNULL(dteServiceDate,'')" ",IFNULL(intTotalDiscountGiven,0),IFNULL(intHHTicketCategoryID,0),IFNULL(intLessAmount,0)" ",IFNULL(strIDProofNo,''),IFNULL(intVehicleTypeID,0),IFNULL(intRouteTypeID,0),IFNULL(strWayBillNo,'')" ",IFNULL(intNoofTimesPrinted,0),IFNULL(intTotalGSTCharges,0),IFNULL(intSGSTCharge,0),IFNULL(intCGSTCharge,0)" ",IFNULL(intSGSTAdjustmentAmt,0),IFNULL(intCGSTAdjustmentAmt,0),IFNULL(intServiceTypeID,0),IFNULL(intTollAmount,0)" ",IFNULL(INTCARDCATEGORYID,0),IFNULL(BSUCCESSTXNFORCARD,0),IFNULL(STRPAYMENTREFNO,0),IFNULL(STRRESPONSECODE,0)" " from tblTicketTransactions where bUploaded = 0 and strWayBillNo ='"<<strWaybillNo<<"' and strTicketNo='"<<strTicketNo<<"'and intTripID='"<<strTripID<<"';";

std::string usr_query = sql.str(); char* errMsg=0; DBGF_TRACE("InsertTransaction::GenerateTransactionDataForSDCard::preparePendingTxnString : :usr_query=%s",usr_query); strRowDataBufferFULL.assign(""); result = sqlite3_exec(app::dbmanager::SqliteManager::GetDBInstance()->handle, usr_query.c_str(), &GenerateTransactionDataCallBack, 0, &errMsg); if(result == SQLITE_OK) { app::dbmanager::SqliteManager::GetDBInstance()->closeDatabase(); DBGF_TRACE("InsertTransaction::GenerateTransactionDataForSDCard::GetBufferConcate :=%s",strRowDataBufferFULL); return strRowDataBufferFULL; } else { app::dbmanager::SqliteManager::GetDBInstance()->closeDatabase(); AlertVC.displayError("Error",sqlite3_errmsg(app::dbmanager::SqliteManager::GetDBInstance()->handle)); return "1"; } } }

boomer::transaction::TransactionResult SDCardBackUpData::GenerateTransactionDataCallBack(void* NotUsed, int argc, char** argv, char** azColName) { char CheckSum[4]=""; strRowDataBufferFULL.append(std::string(argv[0])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[1])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[2])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[3])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[4])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[5])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[6])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[7])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[8])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[9])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[10])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[11])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(strTicketMachineNo); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[13])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[14])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[15])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[16])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[17])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[18])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[19])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[20])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[21])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[22])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[23])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[24])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[25])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[26])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[27])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[28])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[29])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[30])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[31])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[32])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[33])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[34])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[35])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[36])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[37])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[38])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[39])); strRowDataBufferFULL.append("#"); strRowDataBufferFULL.append(std::string(argv[40])); strRowDataBufferFULL.append("#");

strRowDataBufferFULL.append(std::string(argv[0]));//checksum strRowDataBufferFULL.append("#"); //Check_Sum_Generate(strlen(strRowDataBuffer.c_str()),strRowDataBuffer.c_str(),CheckSum); strRowDataBufferFULL.append("^");

//DBGF_TRACE("strRowDataBuffer :=%s",strRowDataBufferFULL); return 0;

}

(9) By Richard Hipp (drh) on 2021-11-23 17:51:09 in reply to 8 [link] [source]

I hope you get understand the example

I understand from the example that you probably have an SQL Injection Vulnerability that is way more important to fix than your alleged memory leak.

I think you need to fix your SQL injections first. These will be the most important class of bugs in your application. Once you have cleaned up your injections, then and only then should you start looking into memory usage concerns.

(10) By Larry Brasfield (larrybr) on 2021-11-23 18:11:31 in reply to 8 [link] [source]

How you can tell this is memory leak?

In just the code you posted, I see one likely memory leak. If errMsg is set by that sqlite3_exec() call, it is never freed by anything you show.

I am simply run sqlite query using sqlite3_exec() in c.

I hope you get understand the example std::stringstream sql;

I have written enough C++ to know that there is no need to extract a std::string from a std::stringstream when its only use is to get its content via c_str(). This cavalier approach to allocation makes me suspect those parts of your code that are not shown.

[big glop of code ...]

I'm not tempted to try to run that code fragment because I would have to create a lot of scaffolding around it; it's duplicative; it relies on a schema I am too lazy to infer; and I do not yet see any evidence or substantiated claim that an actual memory leak is occurring. What do sqlite3_memory_used() returns versus number of queries run look like? Does that function resemble a line with non-zero slope as the program keeps running?

(12) By Gunter Hick (gunter_hick) on 2021-11-24 07:08:59 in reply to 8 [link] [source]

Since you seem to have full c++ support, try compiling your application for a linux host. Then run it using valgrind to detect and display memory leaks. Once you have fixed those, see what it does on the target device.

I don't do c++, but from what you have shown your code does seem awfully verbose.

I also suggest you use NOT NULL DEFAULT clauses in your schema to avoid cluttering up your queries with IFNULL calls.

(13) By Mangal Prajapati (V16be1p11) on 2021-11-24 17:04:02 in reply to 12 [link] [source]

Just I want to know why heap size increase on query execution? If any body technically explain it will be better. Is there method to free it?

(14) By Larry Brasfield (larrybr) on 2021-11-24 17:18:00 in reply to 13 [link] [source]

Just I want to know why heap size increase on query execution? If any body technically explain it will be better. Is there method to free it?

See Ryan's post (#11) and read the doc he linked. Your questions are answered there, so there is no reason to write the same out here.

(15) By Ryan Smith (cuz) on 2021-11-24 18:17:04 in reply to 13 [source]

This thread has probably run its course, but in the interest of trying to be helpful:

Just I want to know why heap size increase on query execution?

Well, so do we. We have no idea. What we do know is:

  • 1. It is not SQLite, all of us use it daily and it does no memory leaking.
  • 2. That leaves your application, which we do not know and cannot guess at (and mostly we are not C++ programmers).
  • 3. Typically memory leaks of the sort is caused by the calling thread (your program) preparing statements, iterating them, but then not finalizing them. or
  • 4. receiving strings (in memory allocated by SQLite) from the SQLite APIs (such as Error messages) which you then do not free the memory of. Any memory structure passed to you from an SQLite API which is now no longer under the control of SQLite (and won't be passed back to SQLite to finalize) must be freed by your code.
  • 5. Error handling in code sometimes short-circuits the code execution and ends up by-passing the finalization of statements or other memory objects.

That's best we can do, somewhat educated guesses. Personally I am starting to think you would probably get better feedback from a C++ forum. Try to make the smallest C++ program that shows the leaky behaviour and ask them what is wrong with it (is what I would do).

If any body technically explain it will be better. Is there method to free it?

As Larry mentioned, this was already handled in another post (#11). We can do no better job than the documentation it shows, but if you do find something in the documentation that is not clear, feel free to quote it here, say what you understand it to mean, and we will gladly help correct any misunderstandings or add clarity.

(5) By Gunter Hick (gunter_hick) on 2021-11-23 15:51:28 in reply to 1.1 [link] [source]

Please do not edit your posts. It makes things much more complicated

(11) By Ryan Smith (cuz) on 2021-11-23 18:32:17 in reply to 1.1 [link] [source]

To answer the original question (since nobody else did) - what you are probably looking for is this:
sqlite3_db_release_memory() or sqlite3_release_memory - related to whether your version is compiled with SQLITE_ENABLE_MEMORY_MANAGEMENT.

That said, the warnings of the other posters are to be heeded - if your Heap grows and grows forever, especially when simply repeating the same query, you have a leak. This is most probably due to what Larry described, which should be fixed before attempting any other memory optimizations.