System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: 89d3a159f14dd09991beef51ae3b0464dca37d94
Title: Issue with Lost Updates with Password protected database with Connection Pooling ON.
Status: Closed Type: Code_Defect
Severity: Important Priority: Medium
Subsystem: Connection_Pool Resolution: Fixed
Last Modified: 2015-08-13 18:30:52
Version Found In: 1.0.93
User Comments:
anonymous added on 2015-08-07 05:26:35:
I have a Sq-lite database which I am accessing with Entity Framework 6.1. 
Now, I am facing a Really weird issue that i want your help with. 
With the Sq-lite Database [It is a Database first model.] when into the Password protected mode & the Connection Pooling is turned ON into the Connection String; the Data is somehow getting Randomly lost; i.e. 

Problem 1:

For an E.g.: From Entity framework i hitting a simple query like : "Select * from Table where id = 11" 
Now the result is Null as given by EF, when i go to check the Database explorer since this cant be null at all as per the System flow, the Record is there into the Table.

Problem 2:

Besides this e.g. I have also faced issues like the Updates which are done on the database have been lost i.e. for e.g. : I add a Record into the Database, it gets that record (it shows up into my Trace logs.) but when after some few seconds when the Data has been churned as per Business Logic and goes to Update the data for that Record; It is not available into the Database.

After some Trial and Error tests; I found when i Turned OFF the Connection pooling this worked well in that case. I want to know What is the reason for the same? 

Summary of Usage:
Entity Framework 6.1
System.Data.Sqlite  - Version 1.0.93 (Also have tried with Updated package but still works weird.)
.NET 4.5 
Platform: Windows

mistachkin added on 2015-08-07 22:49:52:
Is this a new issue?  I don't think using connection pooling is supported when
the database is encrypted.

mistachkin added on 2015-08-08 01:29:35:
Fixed on trunk via check-in [661e4882371a7e6a].  Encrypted connections are no
longer allowed in the connection pool.

anonymous added on 2015-08-08 07:14:42:
Thank you for such a Rapid reply. Besides that, Could you please help me with the Reason which it could have been occurring due to ?

mistachkin added on 2015-08-10 17:20:46:
It's possible that some component (e.g. Entity Framework) was trying to open the
database without the password, I suppose.

I'm not really sure.  The real problem here is that the connection pool was never
designed to support keeping track of encrypted connections (i.e. it does not know
whether they are encrypted or not).

anonymous added on 2015-08-13 05:11:43:
How can that possibly create a problem ? Since the Connection string is same for all the Connections hence its being pooled. And in the Connection string itself the Password is mentioned. Besides that the same thing wouldn't work with any other provider if that is the case. Hence this doesnt seem to be an Exact reason for the same. Could you please help me with the same ? 

I would be glad to contribute anywhere it will be slightest of Help ?

mistachkin added on 2015-08-13 18:30:52:
I'm not 100% sure; however, since the most likely cause is some .NET Framework component (e.g. Entity Framework) opening another connection to the database
without the password, I think this is the safest fix for now.