Re: [sqlite] Change in behavior between 1.0.79.0 and1.0.83.0 in System.Data.SQLite
Ok. So how does this get fixed? -- Michael Russell MTI Film, LLC michael.russ...@mtifilm.com http://www.mtifilm.com/ Providence, RI 02906 USA +1 (401) 831-1315 On Thu, Jan 10, 2013 at 8:49 PM, Joe Mistachkin <sql...@mistachkin.com>wrote: > > Michael Russell wrote: > > > > 1) The break seems to have happened between 1.0.81.0 and 1.0.82.0. > > > > There was a change in System.Data.SQLite disposal behavior in that > timeframe, namely using the new sqlite3_close_v2() API; however, > everything [now] conforms to the IDisposable interface semantics > and established best practices for native resource cleanup. > > The root issue here is that all disposable managed objects (in this > case CriticalHandle derived classes) must be properly disposed by > whatever uses them. Failing to do so with System.Data.SQLite used > to be a source of potential access violations, etc; now, it just > keeps the database connection handle active until everything has > been properly disposed. > > > > > 2) Do Entity Framework team members read this list? > > > > I don't know. > > -- > Joe Mistachkin > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Change in behavior between 1.0.79.0 and1.0.83.0 in System.Data.SQLite
Thanks Joe. A couple of follow-up items: 1) The break seems to have happened between 1.0.81.0 and 1.0.82.0. 2) Do Entity Framework team members read this list? -- Michael Russell MTI Film, LLC michael.russ...@mtifilm.com http://www.mtifilm.com/ Providence, RI 02906 USA +1 (401) 831-1315 On Thu, Jan 10, 2013 at 12:38 PM, Joe Mistachkin <sql...@mistachkin.com>wrote: > > Michael Russell wrote: > > > > It's hard for me to tell that, but what I did was to use your > > "testlinq.exe" program as follows: > >0) Did "build.bat ReleaseNativeOnly x64" to generate > SQLite.Interop.dll > >1) Load SQLite.NET.2010.sln into VS2010. > >2) Set build to "Debug / Mixed Platforms" > >3) Add Projects "System.Data.SQLite.2010" & > > "System.Data.SQLite.Linq.2010" to "testlinq.2010" project. > >4) Set "testlinq.2010" as StartUp Project > >5) Add this code to "testlinq.2010's Program.cs" right at the top of > > "OldTests()" > > > > I am able to replicate your results here. > > The root cause in this case is that the Entity Framework indirectly creates > a > SQLiteCommand object and then subsequently fails to dispose it. > Furthermore, > it does not appear to expose these internally created commands, nor a way > to > explicitly dispose of them, leaving no means for an outside caller to > cleanup. > This seems quite strange since almost all IDbCommand implementations "in > the > wild" would likely require native resources of one kind or another. Also, > even the DbCommand base class provided by the .NET Framework itself > implements > IDisposable (i.e. the class used as the base class for SQLiteCommand). > > Perhaps somebody on the Entity Framework team could shed some extra light > on > this issue? > > -- > Joe Mistachkin > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Change in behavior between 1.0.79.0 and1.0.83.0 in System.Data.SQLite
Hello Joe,
It's hard for me to tell that, but what I did was to use your
"testlinq.exe" program as follows:
0) Did "build.bat ReleaseNativeOnly x64" to generate SQLite.Interop.dll
1) Load SQLite.NET.2010.sln into VS2010.
2) Set build to "Debug / Mixed Platforms"
3) Add Projects "System.Data.SQLite.2010" &
"System.Data.SQLite.Linq.2010" to "testlinq.2010" project.
4) Set "testlinq.2010" as StartUp Project
5) Add this code to "testlinq.2010's Program.cs" right at the top of
"OldTests()"
var tmpDb = "northwindEF-tmp.db";
File.Copy("northwindEF.db", tmpDb, true);
var connectionString =
string.Format(@"metadata=res://*/NorthwindModel2010.csdl|res://*/NorthwindModel2010.ssdl|res://*/NorthwindModel2010.msl;provider=System.Data.SQLite;provider
connection string='data source=.\{0}'", tmpDb);
using (northwindEFEntities db = new
northwindEFEntities(connectionString))
{
string scity = "London";
Customers c = db.Customers.FirstOrDefault(cd =>
cd.City == scity);
Console.WriteLine(c.CompanyName);
}
File.Delete(tmpDb);
6) Set "testlinq.2010" as StartUp Project
7) Build "testlinq.2010"
8) Copy SQLite.Interop.DLL to bin\2010\Debug\bin
9) Run "testlinq.2010" - get Exception at "File.Delete()" line.
I hope this helps.
--
Michael Russell
MTI Film, LLC
michael.russ...@mtifilm.com
http://www.mtifilm.com/
Providence, RI 02906 USA
+1 (401) 831-1315
On Wed, Jan 9, 2013 at 8:13 PM, Joe Mistachkin <sql...@mistachkin.com>wrote:
>
> Michael Russell wrote:
> >
> > I did do a check-out after you made the above suggestion to me. I did
> the
> > fossil checkout / build, but I still have the same problem.
> >
>
> Is there any way you can determine which System.Data.SQLite objects are
> being
> held open by the Entity Framework?
>
> --
> Joe Mistachkin
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Change in behavior between 1.0.79.0 and1.0.83.0 in System.Data.SQLite
I did do a check-out after you made the above suggestion to me. I did the fossil checkout / build, but I still have the same problem. -- Michael Russell MTI Film, LLC michael.russ...@mtifilm.com http://www.mtifilm.com/ Providence, RI 02906 USA +1 (401) 831-1315 On Wed, Jan 9, 2013 at 2:54 PM, Joe Mistachkin <sql...@mistachkin.com>wrote: > > Larry Brasfield wrote: > > > > I don't know about data adapter usage, but I am seeing the same behavior > > with Entity Framework version 5. I have scrupulously disposed of > > everything my code has gotten. I have also overridden the > > DbContext.Dispose(bool disposing) method and can see that the base > > (DbContext) is in fact disposing the connection it has. > > > > Maybe there is some internal object that the Entity Framework is holding > onto? I'm not sure how it works internally. > > > > > I do not see such a change anywhere in recent check-ins listed at > > http://www.sqlite.org/src/timeline?r=trunk > > . Is there a build somewhere? I would be happy to try it. > > > > The check-in I mentioned is here: > > https://system.data.sqlite.org/index.html/info/42d873a6d8 > > I'm doing the release build for 1.0.84.0 right now; however, it will not > be ready until later in the day. > > -- > Joe Mistachkin > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Change in behavior between 1.0.79.0 and 1.0.83.0inSystem.Data.SQLite
Everything that can be disposed of should be getting done automatically by
the using:
using (var testEntities = new TestEntities(connectionString))
{
Assert.AreEqual(1, testEntities.Users.Count(), "Must
be one user");
var firstOrDefault = testEntities.Users.FirstOrDefault();
if (firstOrDefault != null)
{
Assert.AreEqual("1234", firstOrDefault.Id, "ID mismatch");
}
}
but I still cannot delete the file after the using.
--
Michael Russell
MTI Film, LLC
michael.russ...@mtifilm.com
http://www.mtifilm.com/
Providence, RI 02906 USA
+1 (401) 831-1315
On Wed, Jan 9, 2013 at 12:06 PM, Joe Mistachkin <sql...@mistachkin.com>wrote:
>
> Michael Russell wrote:
> >
> > One further follow-up. I tested all the other versions in between. The
> > break happened between 1.0.81.0 and 1.0.82.0
> >
>
> The switch to using sqlite3_close_v2 happened in that time frame; however,
> the root cause of the issue you are seeing is that one or more
> System.Data.SQLite objects have not been disposed and it's holding the
> database connection and it's associated file(s) open.
>
> --
> Joe Mistachkin
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Change in behavior between 1.0.79.0 and 1.0.83.0 inSystem.Data.SQLite
One further follow-up. I tested all the other versions in between. The break happened between 1.0.81.0 and 1.0.82.0 -- Michael Russell MTI Film, LLC michael.russ...@mtifilm.com http://www.mtifilm.com/ Providence, RI 02906 USA +1 (401) 831-1315 On Wed, Jan 9, 2013 at 10:47 AM, Michael Russell < michael.russ...@mtifilm.com> wrote: > I have narrowed this down and to just the System.Data.SQLite.DLL. I have > a test project in VS2010 with .NET 4.0. If I only change the > System.Data.SQLite.DLL from 1.0.79.0 to 1.0.83.0 I get the failure that the > file is open. I am not changing any other components or DLL's. > > > -- > Michael Russell > MTI Film, LLC > michael.russ...@mtifilm.com > http://www.mtifilm.com/ > Providence, RI 02906 USA > +1 (401) 831-1315 > > > On Tue, Jan 8, 2013 at 2:12 PM, Joe Mistachkin <sql...@mistachkin.com>wrote: > >> >> Michael Russell wrote: >> > >> > The File.Delete() fails with the same issue as above >> (System.IO.Exception: >> > The process cannot access the file...because it is being used by another >> > process.) >> > >> >> My guess would be that the Entity Framework is somehow holding the >> underlying >> connection open. I'm not sure if there is a way to force it to close the >> connection and/or dispose of the other System.Data.SQLite resources it's >> using. >> >> -- >> Joe Mistachkin >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Change in behavior between 1.0.79.0 and 1.0.83.0 inSystem.Data.SQLite
I have narrowed this down and to just the System.Data.SQLite.DLL. I have a test project in VS2010 with .NET 4.0. If I only change the System.Data.SQLite.DLL from 1.0.79.0 to 1.0.83.0 I get the failure that the file is open. I am not changing any other components or DLL's. -- Michael Russell MTI Film, LLC michael.russ...@mtifilm.com http://www.mtifilm.com/ Providence, RI 02906 USA +1 (401) 831-1315 On Tue, Jan 8, 2013 at 2:12 PM, Joe Mistachkin <sql...@mistachkin.com>wrote: > > Michael Russell wrote: > > > > The File.Delete() fails with the same issue as above > (System.IO.Exception: > > The process cannot access the file...because it is being used by another > > process.) > > > > My guess would be that the Entity Framework is somehow holding the > underlying > connection open. I'm not sure if there is a way to force it to close the > connection and/or dispose of the other System.Data.SQLite resources it's > using. > > -- > Joe Mistachkin > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Change in behavior between 1.0.79.0 and 1.0.83.0 in System.Data.SQLite
Thanks very much. That solved that problem. I'm seeing a similar issue
when using an EntityFramework open:
using (var testEntities = new
SystemDataSQLiteTest.TestEntities(connectionString))
{
var users = testEntities.Users.ToArray();
Assert.IsTrue(users.Any());
}
File.Delete(TmpDatabase);
The File.Delete() fails with the same issue as above (System.IO.Exception:
The process cannot access the file...because it is being used by another
process.)
Thanks for any help with this.
--
Michael Russell
MTI Film, LLC
michael.russ...@mtifilm.com
http://www.mtifilm.com/
Providence, RI 02906 USA
+1 (401) 831-1315
On Fri, Jan 4, 2013 at 3:07 PM, Joe Mistachkin <sql...@mistachkin.com>wrote:
>
> Michael Russell wrote:
> >
> > We were previously using 1.0.79.0, but in an attempt to move to VS2012 /
> > .NET 4.5 I started doing some testing with 1.0.83.0. I found that many
> of
> > our UnitTests now fail when they try to delete the temporary test
> database
> > file they created. This happens with the SQLiteConnection() call. I can
> > provide a complete VS project, but here's a code snippet that has the
> > crucial parts:
> >
>
> The change was to keep the underlying native connection open until all
> other
> native objects that refer to it have been disposed.
>
> >
> > var sqlCommand = new SQLiteCommand(connection)
> > {
> > CommandText = "SELECT * from User where Username='administrator'"
> > };
> >
>
> If you make sure and dispose of all SQLiteCommand and SQLiteDataReader
> objects
> associated with a particular connection, you should not see any issue.
>
> --
> Joe Mistachkin
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Change in behavior between 1.0.79.0 and 1.0.83.0 in System.Data.SQLite
Hello all,
We were previously using 1.0.79.0, but in an attempt to move to VS2012 /
.NET 4.5 I started doing some testing with 1.0.83.0. I found that many of
our UnitTests now fail when they try to delete the temporary test database
file they created. This happens with the SQLiteConnection() call. I can
provide a complete VS project, but here's a code snippet that has the
crucial parts:
const string TmpDatabase = @"c:\tmp\Test_SQLConnection.db";
var connectionString = string.Format("Data
Source={0};Pooling=false;FailIfMissing=true;", TmpDatabase);
using (var connection = new SQLiteConnection(connectionString))
{
connection.Open();
var sqlCommand = new SQLiteCommand(connection)
{
CommandText = "SELECT * from User where
Username='administrator'"
};
using (var reader = sqlCommand.ExecuteReader())
{
if (!reader.Read())
{
throw new InvalidDataException("Unable to read
data");
}
Assert.AreEqual(reader["FirstName"], "admin", "Database
Mismatch - expected 'admin'");
}
}
File.Delete(TmpDatabase);
Simply changing the System.Data.SQLite.DLL from the 1.0.79.0 version to the
1.0.83.0 version causes the "File.Delete()" to fail with:
System.IO.IOException: The process cannot access the file
'c:\tmp\Test_SQLConnection.db' because it is being used by another process.
I would appreciate any info on this change. Thanks very much.
--
Michael Russell
MTI Film, LLC
michael.russ...@mtifilm.com
http://www.mtifilm.com/
Providence, RI 02906 USA
+1 (401) 831-1315
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
