System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: 886387ad40d6fd753ee40e97b9e37d47e306ce2a
Title: PRAGMA statements and ExecuteNonQuery
Status: Closed Type: Incident
Severity: Important Priority: Medium
Subsystem: Data_Reader Resolution: Not_A_Bug
Last Modified: 2015-10-16 17:10:31
Version Found In: 1.0.98
User Comments:
anonymous added on 2015-10-15 16:37:12:
After upgrading from 1.0.96.0 to 1.0.98.0, some PRAGMA statements issued with DbCommand.ExecuteNonQuery() no longer executes and seem to be stuck on SQLiteDataReader.NextResult().

Pausing the execution in debug mode with Visual Studio shows the thread stuck at the following point:
System.Data.SQLite.dll!System.Data.SQLite.SQLiteDataReader.NextResult() Ligne 1440

Some problematic PRAGMA statements: locking_mode, legacy_file_format, page_size

Changing the PRAGMA execution from DbCommand.ExecuteNonQuery() to DbCommand.ExecuteScalar() seems to be executing correctly. Using ExecuteScalar seems the correct way to issue such statements, but I would have expected a "Potentially Incompatible Change" in the release notes. Of course, feel free to point it out if I missed it.

mistachkin added on 2015-10-15 17:16:24:
Do you have an example SQL query and/or C# code that causes this?

mistachkin added on 2015-10-15 19:21:27:
Sorry, in the above comment I meant "SQL schema" instead of "SQL query".

mistachkin added on 2015-10-15 22:08:27:
I've added a preliminary test case (check-in [ae33a7a7f3f64ea2]); however, I'm
unable to reproduce the behavior described by this ticket so far.  Here is a link
to the test case itself:

[/artifact?ci=tkt-886387ad40&filename=Tests/tkt-886387ad40.eagle]

anonymous added on 2015-10-16 08:55:22:
I could not reproduce it on a simple test case this morning, but I think I found the culprit which was in my code and the consequence of another documented change: 
https://system.data.sqlite.org/index.html/info/74542e702e

In conformance with SELECT statements returning -1, the behavior of all statements which are not INSERT, UPDATE or DELETE now return -1 instead of 0. So this is also the case for PRAGMA statements.
https://msdn.microsoft.com/fr-fr/library/system.data.sqlclient.sqlcommand.executenonquery(v=vs.110).aspx

In my case, I had some kind of (buggy) "replay query on busy/lock/failure" which was not working anymore with this change since I was expecting a return code of 0 for pragma statements. Getting -1 stuck me in an infinite loop which explains the debugging step I was getting on NextStep: it wasn't stuck, but spinning on it.

Sorry for the disturbance! I'm not editing the ticket resolution because I'm not sure of which one to choose (Rejected / Not_A_Bug).

mistachkin added on 2015-10-16 17:10:31:
Thanks for the update, closing ticket...