Bug 5058 - SqliteConnection, SqliteCommand and SqliteReader can result in Error "Unable to open the database file"
Summary: SqliteConnection, SqliteCommand and SqliteReader can result in Error "Unable ...
Status: RESOLVED FIXED
Alias: None
Product: iOS
Classification: Xamarin
Component: Xamarin.iOS.dll ()
Version: 5.3.x
Hardware: All All
: --- normal
Target Milestone: Untriaged
Assignee: Bugzilla
URL:
Depends on:
Blocks:
 
Reported: 2012-05-14 05:31 UTC by Rolf
Modified: 2012-05-16 16:34 UTC (History)
3 users (show)

Tags:
Is this bug a regression?: ---
Last known good build:


Attachments
Example code 1: which works (1.49 KB, text/plain)
2012-05-14 05:31 UTC, Rolf
Details
Code which fails (1.23 KB, text/plain)
2012-05-14 05:32 UTC, Rolf
Details


Notice (2018-05-24): bugzilla.xamarin.com is now in read-only mode.

Please join us on Visual Studio Developer Community and in the Xamarin and Mono organizations on GitHub to continue tracking issues. Bugzilla will remain available for reference in read-only mode. We will continue to work on open Bugzilla bugs, copy them to the new locations as needed for follow-up, and add the new items under Related Links.

Our sincere thanks to everyone who has contributed on this bug tracker over the years. Thanks also for your understanding as we make these adjustments and improvements for the future.


Please create a new report on Developer Community or GitHub with your current version information, steps to reproduce, and relevant error messages or log files if you are hitting an issue that looks similar to this resolved bug and you do not yet see a matching new report.

Related Links:
Status:
RESOLVED FIXED

Description Rolf 2012-05-14 05:31:31 UTC
Created attachment 1878 [details]
Example code 1: which works

During our development we found saw that the SqliteDataReader does not properly handle the CommandBehavior.CloseConnection.

For example when running the first attachment ('Example1.txt') code you will be able to open all the connections you desire.

However, when we use a design that limits other programmers the need to create connections/commands, who are developing data layer adapters, like in the second attachment ('Example2.txt'), we get the message 'Unable to open the database file' in the simulator after 242 connections. On a device its around the same and furthermore any other action will crash the application despite handling the exception thrown.

Now while the design can be argued if it the design of return a DataReader should be done in the first place, however the design is on only used with towards internal code for database data access. 

Futhermore when using 'Example1' and supplying the CommandBehavior.CloseConnection to the reader the same message appears. Despite closing the reader first, then disposing the command and then the connection.


After some digging through the code the first thing that was found was that the SqliteDataReader does not handle the CommandBehavior.CloseConnection properly because of at least the following reason:

The connection is closed first instead of the command, normal order would be reader/command(dispose)/connection(close/dispose).
However in the dispose with CommandBehavior.CloseConnection the following happens: reader/connection, the command which should be disposed because the connection is no longer there is not disposed.
This is because the bool variable in SqliteDataReader '_disposeCommand' is never set with CommandBehavior.CloseConnection, this is because the command which holds the reader, 'SqliteCommand', actually sets variable when Dispose() is called, which again will never happen. 
This means that while the connection is closed the command will keep alive which seems to be required for the pool.

(Note: After reviewing my message I saw that in the attachments you first have to make a database named testDb.db with a table named Test with 1 row in it, my apologies for not having added that);

To bypass this for such a design one must create a DbReader wrapper which will handle the correct way and the CloseConnection must never be supplied to the reader, the wrapper should handle this.
Comment 1 Rolf 2012-05-14 05:32:07 UTC
Created attachment 1879 [details]
Code which fails
Comment 2 Rolf 2012-05-14 06:07:20 UTC
Addition: It seems like that a disposing of a connection does not dispose of any commands being handled properly for example the following structure fails on Mono when same actions are used for the Sqlite variant, while the Sql readers of .NET handle this properly.

The following code creates a connection and performs 50 simple select queries on it.

public void Test()
{
  try
  {
    while (!done)
    {
      int x = 0;
      using (var con = CreateOpenConnection()) //Open a SqlConnection
      {
        Console.WriteLine(Environment.NewLine + Environment.NewLine + "Connection: " + (++x));
        for(int i = 0; i < 50; i++)
        {
          using (var reader = GetReader("SELECT * FROM TBLTEST", con))
          {
            Console.Write((i + 1) + " ");
            reader.Read();
          }
        }
      }
    }
  }catch(Exception e)
  {
    Console.WriteLine("Error Connection after[" + counter + "]: " + e.Message);
  }
}
private DbConnection CreateOpenConnection()
{
  var con = new SqlConnection("Data Source=X;Initial Catalog=DB;");
  con.Open();
  return con;
}

public DbDataReader GetReader(string sql, DbConnection connection = null)
{
  bool noConnectionSupplied;
  if (noConnectionSupplied = (connection == null))
    connection = CreateOpenConnection();
  DbCommand command = null;
  try
  {
    command = connection.CreateCommand();
    command.CommandType = CommandType.Text;
    command.CommandText = sql;
    return command.ExecuteReader(noConnectionSupplied ? CommandBehavior.CloseConnection : CommandBehavior.Default);
  }
  catch (Exception e)
  {
    if (command != null)
      command.Dispose();
    if (connection != null)
      connection.Close();
    throw e;
  }
}
Comment 3 Rolf Bjarne Kvinge [MSFT] 2012-05-16 16:34:47 UTC
Fixed in master and 5.2-series. Unit tests are included.

The next releases with this fix well be 5.2.13 and 5.3.4.

monotouch master: eecc39babe71438157e20bf0957342147d039d6d
monotouch 5.2-series: d84465f0e5c322475ca6bb59f9f00dd25b1a9b1e

Thanks for tracking down the issue, it helped a lot when fixing it.