Memory leak when calling sqlite3_exec()

(1) By mngz (kwontmdwo) on 2020-04-14 00:58:41 [link] [source]

Hi, I'm Sean.

I am currently trying to use sqlite3 in a Linux embedded product, but the library function seems to leak memory.

To test the performance of the DB, I ran an insert query in the for statement and continued calling sqlite3_exec(), and as a result of monitoring with the top command, I confirmed that the free memory size was gradually decreasing.

The sqlite homepage uses sqlite3_malloc() for errmsg, which is the fifth argument inside sqlite3_exec(), so I also confirmed that sqlite3_free() should be used, but errmsg is NULL because it was processed normally.

I know sqlite3_exec() consists of sqlite3_prepare_v2(), sqlite3_step() and sqlite3_finalize(). When I replaced it with other functions instead of sqlite3_exec(), it seemed to be a memory leak in sqlite3_step().

The sqlite3 version I am using now is 3.22.0. What should I do?

(2.1) By Simon Slavin (slavin) on 2020-04-14 05:14:45 edited from 2.0 in reply to 1 [link] [source]

Are you writing C code and calling the SQLite3 C API or are you using another language and library ?

While diagnosing the problem, please test with the three function version: sqlite3_prepare_v2(), sqlite3_step(), sqlite3_finalize().

Are you checking the result codes returned by sqlite3_prepare_v2() and sqlite3_finalize() to make sure they're SQLITE_OK ?

If you call sqlite3_step() many times for the same prepared statement, returning a different row each time, does it leak more memory each time, or just the first time you call it for each prepared statement ?

Does sqlite3_finalize() release the memory ?  If so, try using sqlite3_reset() instead of sqlite3_finalize().  Does sqlite3_reset() also release the memory leaked by sqlite3_step() ?

(5.1) Originally by mngz (kwontmdwo) with edits by Richard Hipp (drh) on 2020-04-14 11:01:45 from 5.0 in reply to 2.1 [link] [source]

I just wrote C code and did not use other language and library.
It was implemented to call each once sqlite3_prepare_v2(), sqlite3_step(), sqlite3_finalize().
I didn't check return value of sqlite3_prepare_v2(), sqlite3_finalize(), but sqlite3_step() return SQLITE3_DONE. I used insert state.

I saw it did not occur memory leak:

sqlite3_prepare_v2()
// sqlite3_step()
sqlite3_finalize()

But it occurs memory leak:

sqlite3_prepare_v2()
sqlite3_step()
sqlite3_finalize()

(3) By Keith Medcalf (kmedcalf) on 2020-04-14 05:30:08 in reply to 1 [link] [source]

Can you show the code you used, was it something like this:

stmt = sqlite3_prepare_v2('insert into table values (?)')
for i = 1 to n
   rc = sqlite3_bind_int(stmt,1,i)
   if (rc == SQLITE_OK)
       rc == sqlite3_exec(stmt)
       if (rc == SQLITE_DONE)
          next
print "error", rc
sqlite3_finalize(stmt)

Was the value of n 3 or 4, or more than a million?

(6) By mngz (kwontmdwo) on 2020-04-14 09:25:18 in reply to 3 [link] [source]

Here is my code:

static int my_sqlite3_exec(DbHelper *dbHelper, char* sql)
{
	DbHelper_LOGV(dbHelper, ">> %d. %s", dbHelper->trCnt, sql);

	int ret = sqlite3_exec(dbHelper->db, sql, NULL, NULL, &dbHelper->errMsg);
	if (ret == SQLITE_OK || ret == SQLITE_DONE)
		DbHelper_LOGD(dbHelper, "<< %d. OK", dbHelper->trCnt);
	else
		DbHelper_LOGE(dbHelper, "<< %d. %d(%s)", dbHelper->trCnt, ret, dbHelper->errMsg);

	if (dbHelper->errMsg)
	{
		sqlite3_free(dbHelper->errMsg);
		dbHelper->errMsg = NULL;
	}

	dbHelper->trCnt++;

	return ret;
}

(8) By Keith Medcalf (kmedcalf) on 2020-04-14 19:17:13 in reply to 6 [link] [source]

Sorry, does not compile or run.

(4) By anonymous on 2020-04-14 07:39:55 in reply to 1 [link] [source]

and as a result of monitoring with the top command, I confirmed that the free memory size was gradually decreasing

Which column did you look at, free or avail? Have you tried compiling your application with -fsanitize=address or running it under Valgrind? Those tools would detect leaks more reliably, and pinpoint their origins, too.

(7) By mngz (kwontmdwo) on 2020-04-14 09:27:17 in reply to 4 [link] [source]

I am cleaning the memory as a way to temporarily clear the cache.

system("echo 3 > /proc/sys/vm/drop_caches");
system("sync");

(9) By anonymous on 2020-04-15 07:44:40 in reply to 7 [source]

This doesn't answer the question. Note that "free memory available for applications to start without swapping" is a bit more complicated than "used memory - slab objects - pagecache" and can only be estimated, not measured in modern Linux. (See also: overcommit, why it was introduced, and the problems it causes.) Also note that even if the leak exists, we still have to trace it to a specific allocation that returns the pointer that's later abandoned and left to the OS to reclaim after the application terminates, hence the use of tools specifically designed to find memory errors (including leaks).

Besides, the leak might be elsewhere: some other process (or a kernel driver, why not?) running on your embedded Linux might be allocating memory and not freeing it - your method of measurement doesn't discern these situations. The standard C library allocator is a complicated thing; it requests big memory chunks by running mmap(NULL, length, PROT_READ+PROT_WRITE, MAP_PRIVATE+MAP_ANONYMOUS, -1, 0), then distributes pieces of them to your application when the latter calls malloc. Even if you free() all pieces you have allocated, the allocator is not guaranteed to munmap() the whole chunk back to the operating system; instead, it might keep the chunk for later allocations. Your method of measurement would call this a leak, too. (See also: limit amount of memory used by SQLite; zero-malloc allocator.)