1
\$\begingroup\$

I hate hate posting to ask for help. I can usually just figure this stuff out. I'm in over my head.

--How I arrived at my current conclusions (only read if you want my problem solving steps--

I discovered an application on which I was working had memory leaks. I endeavored to find them. I engaged in a process of trial-and-error removal of calls and functions until I eventually found a set of processes which appeared to be the culprit. It was a simple function which queried a sqlite database using an .ExecutableNonQuery(). More trial-and-error ensued to see if I could reproduce the error in a new function. I could. I tried to strip that function down to its absolute bare minimum and polished it to continue be more in line with best practices (as far as I can tell).

The problem persisted. Confused, I started trying to break the code altogether. I started taking out certain key components of the query to see if I could eliminate the memory leak. To be sure that the problem wasn't hidden somewhere else in the application, I built a new Unity Project with only one game object (no camera). It has one component on it (a C# script titled: Script; it's below). Nothing else. There are no other objects, scripts, functions, calls, references or anything in this project. The leak persists.

Here's the most minimal code I generated that works in full:

using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using Mono.Data.Sqlite;
using System.Data;

public class Script : MonoBehaviour {

    string dbconnstring;

    void Start () {
        dbconnstring = "URI=file:" + Application.dataPath + "/civicismdb.sqlite";
    }

    void Update () {
        dbconnect();
    }

    void dbconnect()
    {
            using (IDbConnection conn = new SqliteConnection(dbconnstring))
            {
                using (IDbCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = @"select 1";
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
            }
        }
}

This is the most minimal query I could possibly have. Two things stop the memory leak. If I comment out:

cmd.ExecuteNonQuery();

Or I adjust the query to be empty:

cmd.CommandText = @"";

In the latter case, I suspect .ExecuteNonQuery isn't actually running, but I'm unsure. Moreover, I've created a cmd.CommandText and assigned it the query value at this line:

cmd.CommandText = cmd1.CommandText;

Same result. The memory leak persists, of course, because that wouldn't really make much sense as a fix. Whatever, due diligence...

I tried to create my connections and commands once and simply reference them, but 1) that really isn't optimal and 2) it doesn't work anyway. The leak persists.

I tried to ensure that my cmd and conn were disposed of. Again, that 1) didn't fix the problem, and 2) doesn't make much sense as a fix because the using statement should ensure that they are disposed of anyway. But, I tried. To no avail.

Though explicitly calling GC.Collect() is a bad idea, I tried that as well given that it might be creating more connections/commands than it could dispose of given the update function calling the DB. No dice.

Also, I know querying the DB every update is a bad idea. I'm doing that for illustrative purposes. It makes the leaks clearer. In reality, I'm querying it often as called by a coroutine.

Now, I know that coroutines could cause their own memory leaks, but when running this with a coroutine, the leak stops if I comment out the .ExecuteNonQuery().

--My conclusion--

ExecuteNonQuery is leaking. Somewhere/somehow under the hood.

--My questions --

Am I missing anything?

Is this a known issue and I failed to find it in my googling?

Do you see a solution?

Thank you for reading. This has been a long and frustrating experience and my posting here is my conceding defeat.


EDIT -

I'm learning that I think SQlite does memoryleak a little bit at each invoke but for most applications this is just not relevant because the database is queried so infrequently. I'm also learning that sqlite does a ton to provide options to developers to manage memory more efficiently. See here for example:

https://www.sqlite.org/malloc.html

So, this isn't really a Unity problem. It's a user-error when using SQlite problem.

As far as I can tell, what I need to be doing is using unmanaged code to manage the memory myself. I'm only just learning how to do this and am struggling mightily to figure out how specifically to change configuration options, manage the heap, finalize connections, etc. any insights are very much appreciated.

At the moment, I'm trying to figure out how to call the sqlite3_config option so that I can the configuration to MemSys5 instead of using malloc. No idea if that will actually solve the problem.

\$\endgroup\$
  • \$\begingroup\$ First, you really shouldn't have to query a DB more than once every session or once every game level imho, so, even if there are leaks, this really shouldn't matter. Secondly, yeah there could be some leaks going on under the hood, you should report this on a Unity bug report and make them aware of the situation. Third, I usually avoid calling DB's in Unity since that's not really safe. Can't you serve a page for this exact purpose and call the DB in there? (I know that will cost extra for you, but it would avoid the DB calls entirely and just connect to a plain text html page) \$\endgroup\$ – John Hamilton Nov 24 '17 at 6:18
  • \$\begingroup\$ There is a reason to query often in this case. This isn't a 'level' based game. I cannot accomplish what I'm trying to do in html. Hence this design and this problem. \$\endgroup\$ – mrliioadin Nov 24 '17 at 6:50
  • \$\begingroup\$ Did you report this as a bug to Unity like I suggested? You're talking about a bare-bones structure and a problem that seems like it's in the engine itself. I could provide alternatives if you added why you're using this method, the reason is important and "there's a reason for this" is not really enough. Also, I did not suggest doing it in just HTML, I suggested serving a web page which connects to a DB and presents the results in HTML. You'd need to use PHP to connect to the DB and then present the results. Unless you're checking passwords or something (this is why the reason is important). \$\endgroup\$ – John Hamilton Nov 24 '17 at 7:12
  • \$\begingroup\$ I will report it in unity, yes. I can't get into a ton of detail about why exactly multiple calls are necessary, but querying a database was found to be the most effective and efficient way to accomplish the scale of data transfers and updates necessary for the project to work. At least, until I ran into this problem. And, unless I'm missing something obvious (which is possible), a php html setup would require a substantial redesign of the system. I'd have to migrate the entire database over to MySQL. I'm hesitant to go near that route. No assurance it would avoid this problem. \$\endgroup\$ – mrliioadin Nov 24 '17 at 7:18
  • \$\begingroup\$ One thing that's a possibility is the command itself. ExecuteNonQuery is supposed to be used with INSERT UPDATE and DELETE statements, according to the .NET references. There may be weird behavior under the hood by using a select (which is a query) with a non-query execution. Is this just your example, or is the real code doing a select with ExecuteNonQuery as well? \$\endgroup\$ – Stephan Nov 27 '17 at 15:34
1
\$\begingroup\$

Alright, I think I finally have this sorted. when wrapping these in using statements, the idea is that the garbage collector (GC) will collect all the discarded commands and connections no longer in use. something about this implementation is preventing the GC from doing that. I don't know what or why. In theory this implementation should have been stable. It isn't. So here's the plan instead:

Generally it is silly to create a pool of connections to be cycled through because I think sqlite already pools connections for you. But, again... something in this design is stopping it from pooling properly. Either that or the individual commands themselves are inflating. Either way, I'm building a pool of connections, readers, and commands and building a function to cycle through them to be constantly reused dynamically. I'll also build in a query cue so that no two queries can query at the same time.

Basically, build a query+command+reader combo, send it to the cue. Cue grabs the next one off the stack and runs it. Returns the result or null. That appears to be robust to inflation as long as I'm not closing the collections or commands. The readers can be closed without inflation.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.