System.Data.SQLite

Login
This project makes use of Eagle, provided by Mistachkin Systems.
Eagle: Secure Software Automation
Ticket Hash: 69cfdf79fa11f506602146b0fd192c9a546ad167
Title: Pooling not working after upgrading to 1.0.109.x
Status: Closed Type: Incident
Severity: Important Priority: Blocker
Subsystem: Connection Resolution: Works_As_Designed
Last Modified: 2018-12-04 21:06:56
Version Found In: 1.0.109
User Comments:
anonymous added on 2018-10-23 15:35:43: (text/x-fossil-plain)
Hi,

After upgrading System.Data.SQLite.Core NuGet package from 1.0.108 to 1.0.109.1 or 1.0.109.2, pooling isn't working as before because SQLiteConnection.PoolCount property isn't returning the expected values after closing connections: it's always 0.

I have several tests that assert that property's value under different use cases which are failing after upgrading to either version 1.0.109.x.

Databases used are either :memory: or a 0 byte local file.
Connections are created using a connection string got from a SQLiteConnectionStringBuilder created this way:

const int MaxPoolSize = 64;
var maxPoolSize = "Max Pool Size=" + MaxPoolSize;
var _connectionStringBuilder = new SQLiteConnectionStringBuilder(maxPoolSize)
                                       {
                                           FailIfMissing = true,
                                           Pooling = true,
                                           ToFullPath = false,
                                       };

Thanks in advance.

mistachkin added on 2018-10-23 22:23:54: (text/x-fossil-plain)
Are you using the PoolCount property on a connection that was already closed?

anonymous added on 2018-11-07 16:34:38: (text/x-fossil-plain)
Sorry for not checking this before.

The PoolCount property is checked on an opened connection (indeed, the very first one opened in my test) after closing the other 63 connections.

mistachkin added on 2018-11-08 01:29:46: (text/x-fossil-plain)
I have a feeling this issue may be difficult to reproduce without more details.

Is it possible for you to provide any more details on how this issue arose?

Are you using your own custom ISQLiteConnectionPool implementation or the
default one?

anonymous added on 2018-11-12 11:01:09: (text/x-fossil-plain)
I'm using the default ISQLiteConnectionPool implementation, but reproducing this issue with a barebones test code I've pinpointed the root cause: setting SQLiteConnectionStringBuilder.FailIfMissing to true causes SQLiteConnection.PoolCount not to return its expected value.

Here's my code, which using System.Data.SQLite.Core 1.0.109.2 shows a final SQLiteConnection.PoolCount of 0 instead of 63 when using System.Data.SQLite.Core 1.0.108:

using System;
using System.Data.SQLite;
using System.Linq;

namespace Test109
{
    class Program
    {
        private const int MaxPoolSize = 64;
        private const int Preserve = 1;
        private static SQLiteConnectionStringBuilder _sqLiteConnectionStringBuilder;

        static void Main(string[] args)
        {
            Console.WriteLine($"Using SQLite {SQLiteConnection.SQLiteVersion}");

            _sqLiteConnectionStringBuilder = new SQLiteConnectionStringBuilder($"Max Pool Size={MaxPoolSize}")
            {
                DataSource = ":memory:",
                FailIfMissing = true, // This setting causes SQLiteConnection.PoolCount not to return its expected value.
                Pooling = true,
                ToFullPath = false,
            };

            var connections = Enumerable.Range(1, MaxPoolSize).Select(_ => OpenSqLiteConnection()).ToArray();
            foreach (var connection in connections.Skip(Preserve))
            {
                CloseSqLiteConnection(connection);
            }

            CheckSqLiteConnection(connections[0]);
        }

        private static SQLiteConnection OpenSqLiteConnection()
        {
            var connection = new SQLiteConnection(_sqLiteConnectionStringBuilder.ToString());
            connection.Open();

            CheckSqLiteConnection(connection);
            return connection;
        }

        private static void CloseSqLiteConnection(SQLiteConnection connection)
        {
            connection.Close();
            CheckSqLiteConnection(connection);
        }

        private static void CheckSqLiteConnection(SQLiteConnection connection)
        {
            Console.WriteLine($"Connection {connection.State}: {connection.PoolCount} in pool");
        }
    }
}

mistachkin added on 2018-11-13 23:27:40: (text/x-fossil-plain)
As it turns out, this is correct based on the current design.  At present, the
flags used to open the database must be "Default" ("ReadWrite | Create") for
the connection to be eligible for the connection pool.  Perhaps this restriction
could be relaxed; however, in that case, I would imagine there would need to be
more than one pool (i.e. one for each "compatible combination" of flags).

anonymous added on 2018-11-14 16:43:39: (text/x-fossil-plain)
So, if I understand you correctly, in this case there's no pooling.
Thank you for your clarification.