WebGL: Queries appear to invalidate the database or delete records?

[NecroticNanite]

Still investigating this, but adding it here now.

Using 1.2.2, I'm hitting an issue where my queries stop working.

Executing Query: SELECT 
                        product_skus.id as sku_id,
                        product_skus.price_usd as price_usd,
                        objects.display_name as object_name
                    FROM product_skus
                        
                    LEFT JOIN objects on product_skus.root_object = objects.id
                    
                    WHERE product_skus.id = 49
Executed query SkuTooltipQuery and retrieved 1 records.
...
Executing Query: SELECT 
                        product_skus.id as sku_id,
                        product_skus.price_usd as price_usd,
                        objects.display_name as object_name
                    FROM product_skus
                        
                    LEFT JOIN objects on product_skus.root_object = objects.id
                    
                    WHERE product_skus.id = 49
Executed query SkuTooltipQuery and retrieved 0 records.

Executing the same query - the first result is correct, returning a single record. The second query returns zero records. This is happening for all queries. It's almost like each query is deleting records or invalidating the state of the database.

Query:

public struct SkuTooltipQuery : IQuery<SkuTooltipQuery.SQL_TooltipResponse>
{
    [Preserve]
    public class SQL_TooltipResponse
    {
        [PrimaryKey, AutoIncrement, Preserve]
        public int id { get; set; }
        [Preserve]
        public string object_name { get; set; }
        [Preserve]
        public float price_usd { get; set; }
    }
    
    private int m_skuId;
    public SkuTooltipQuery(int skuId)
    {
        m_skuId = skuId;
    }
    
    public List<SQL_TooltipResponse> Execute(SQLiteConnection database)
    {
        string query =
            $@"SELECT 
                    product_skus.id as sku_id,
                    product_skus.price_usd as price_usd,
                    objects.display_name as object_name
                FROM product_skus
                    
                LEFT JOIN objects on product_skus.root_object = objects.id
                    
                WHERE product_skus.id = {m_skuId}";
        return database.Query<SQL_TooltipResponse>(query);
    }
}

Execution code:

public List<T> ExecuteQuery<T>(IQuery<T> query)
{
    var result = query.Execute(m_connection);
    if (m_connection.Trace)
    {
        Debug.Log($"Executed query {query.GetType().Name} and retrieved {result.Count} records.");
    }
    return result;
}

Database load code:

string url = SQLPathResolver.ResolvePath();
UnityWebRequest request = UnityWebRequest.Get(url);
UnityWebRequest response = await request.SendWebRequest();

var db = new SQLiteConnection("localdb", SQLiteOpenFlags.ReadOnly);
db.Deserialize(response.downloadHandler.data, null, SQLite3.DeserializeFlags.ReadOnly);

m_connection = db;

I load the database once at boot.

Will be testing previous versions (before the Unity WebGL changes) and testing re-loading the database before each query.

[NecroticNanite]

1.2.1 (with codeOptimization turned off) presents the same issue.

[NecroticNanite]

Additional context I forgot, in case it ends up being relevant: Unity 6000.0.26

[NecroticNanite]

Also 1.1.2, so assuming that this is unrelated to changes made recently and is either an issue with my configuration or a long-standing issue that hasn't been caught. It works in Editor.

[NecroticNanite]

One possible theory: The web request being garbage collected would clear the byte array from memory. If SQLite isn't copying it in deserialize? I'm trying a 'recreate database before queries' approach right now, I'll try a 'Retain a reference to the byte array' approach after.

Seems like that might be the case:
https://www.sqlite.org/c3ref/deserialize.html
"Applications must not modify the buffer P or invalidate it before the database connection D is closed."

[NecroticNanite]

Ok, you can ignore this!
It was indeed the array being GC'd.

[gilzoide]

Hey @NecroticNanite, thanks for the report.

The web request being garbage collected would clear the byte array from memory, if SQLite isn't copying it in deserialize?

You already found it, but yeah, that's correct. And you got lucky (or maybe unlucky?) that the memory was cleared instead of filled up with garbage. I say unlucky because if there was garbage data in memory SQLite could maybe return some other kind of error, like SQLITE_NOTADB, and it would be easier to figure that something was wrong with the database memory.

We should have a user-friendly / C#-friendly alternative for deserialized databases that copy data into SQLite, though. Either a flag to Deserialize or a new method. If this flag is true, we allocate a buffer with sqlite3_malloc, copy the data, and always pass DeserializeFlags.FreeOnClose, so that SQLite owns the memory and automatically frees it when the database connection is closed.
I'll create a new issue on that, thanks for the help!