Re: [sqlite] Change in behavior between 1.0.79.0 and1.0.83.0 in System.Data.SQLite

[Mason, Philip]

I'm somewhat stymied by this situation with System.Data.SQLite 1.0.84.0 and EF5 
as well.   I'm trying to delete a database file to force my application to 
recreate the database.   I can't seem to be able to do this, even though I 
should be in a state where there are no active connections to the file.

Joe mentioned that there seemed to be orphaned SQLiteCommand objects that 
appear to be keeping the connection open, is this correct?   What part or 
function of the Entity Framework or System.Data.SQLite are generating these?

If SQLite sits around waiting for objects to be disposed, when will the 
connection drop?   Other providers, such as SQL Server + SQL Server Compact do 
not appear to exhibit this problem - or at least mask it by allowing a "drop 
database" command to reinitialize the database despite other connections.

Is there any way to workaround this problem?   Is there a workable SQLite 
equivalent to "drop database"?

Or is there a way to get a version of 1.0.81.0 that plays nice with .NET 4.5?  
(I don't mind building from sources and doing some backporting to make this 
happen  just need to know how to get fossil to do my bidding (i.e. fetch a 
particular historic version of the tree) ...)

- - - Philip Mason
Nuance Communications
Burlington, MA



From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Michael Russell [michael.russ...@mtifilm.com]
Sent: Wednesday, January 23, 2013 12:33 PM
To: General Discussion of SQLite Database
Subject: 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
___
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

[Michael Russell]

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 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

[Joe Mistachkin]

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

Re: [sqlite] Change in behavior between 1.0.79.0 and1.0.83.0 in System.Data.SQLite

[Michael Russell]

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 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

[Joe Mistachkin]

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

Re: [sqlite] Change in behavior between 1.0.79.0 and1.0.83.0 in System.Data.SQLite

[Michael Russell]

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 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

[Joe Mistachkin]

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

Re: [sqlite] Change in behavior between 1.0.79.0 and1.0.83.0 in System.Data.SQLite

[Michael Russell]

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 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 and1.0.83.0 in System.Data.SQLite

[Joe Mistachkin]

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

Re: [sqlite] Change in behavior between 1.0.79.0 and1.0.83.0 in System.Data.SQLite

[Larry Brasfield]

Joe Mistachkin wrote:

Does the Entity Framework make use of data adapters?  If so, I just checked
in a fix to dispose the internal commands managed by the SQLiteDataAdapter
class (i.e. because the base class DbDataAdapter apparently does not
explicitly dispose them).


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.



Could you please try the latest trunk code and let us know if that clears
the issue you are seeing?


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.

Cheers,
--
Larry Brasfield

___
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

[Joe Mistachkin]

Does the Entity Framework make use of data adapters?  If so, I just checked
in a fix to dispose the internal commands managed by the SQLiteDataAdapter
class (i.e. because the base class DbDataAdapter apparently does not
explicitly
dispose them).

Could you please try the latest trunk code and let us know if that clears
the
issue you are seeing?

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users