Sqlite drop column7/24/2023 ![]() ![]() When the database is in journal_mode=DELETE, the Step the statement once only, then finalize the statement.Ī separate process holds a read transaction open on the database fileĭuring all tests.Prepare statement "INSERT INTO t1(a) VALUES(?) RETURNING rowid ".Open a database connection to the database "x1.db".My C-language test program to reproduce this behavior is shown below. These changes will apply as of 3.42.1 and 3.43.ĮDIT: Larry has convinced me that having finalize() throw is just plain bad, so the above solution will be reworked so that finalize() no longer throws, but reset() will continue to. The INSERT RETURNING case described in this thread will now trigger an exception in DB.exec() instead of ignoring the potential SQLITE_LOCKED reported via sqlite3_finalize()/reset(), and similar constructs implemented in client code will trigger an exception via either finalize() or reset() or may actively check the result code of Stmt.finalize() for an error. The application or library needs to either run sqlite3_step() until it completes or else check the return value from sqlite3_finalize() or sqlite3_reset() to see if the INSERT really did complete. One or the other of these codes is assuming that if the first call to sqlite3_step() on a DML statement does not return SQLITE_BUSY then the DML statement must have succeeded. So I think the bug here is in either the application, or in the methods that the application is calling. Per Richard's explanation further down in this thread: Prior to this change, finalize() ignored non-0 result codes per the long-standing wisdom that "destructors do not throw" and reset() ignored the result due to an oversight on my part. In the current trunk, both () and Stmt.reset() will now throw if the underlying sqlite3_finalize/sqlite3_reset() returns non-0. (Edited to reflect changes made after this post.) Also, I see the WASM OO1 API will skip extra stepping when output isn't needed so I'm wondering whether that needs attention.įYI, investigating the equivalent in detail for the JS API is on my TODO. The reason I'm asking is I want to make sure I don't need a similar fix in my own code that calls the SQLite API. How did you know it was a bug and not an SQLite bug? ![]() There is also has some strange behaviour where ExecuteNonQuery is returning -1 when inserting a record using the RETURNING clause instead of 1.Īlso, the EF Core team thought this was an SQLite bug. I guess the RETURNING clause inside ExecuteNonQuery is not a particularly valid use case for that API but I can see people making the mistake. I was just having a play around with the 1.0.117 version and this bug also occurs using ExecuteNonQuery. I can see you've only added the fix to ExecuteScalar. progress, keep stepping until done unless forbidden from Therefore, if there is a write transaction in to be started and that always require more than one step BUGFIX: There are SQL statements that cause a write transaction This new comment you added seems to describe the cause of the bug. I was going through the code changes you made. String filePath = Path.GetTempFileName() Note: original use case of RETURNING was for an auto incremented ID column but this repros the bug without that so I removed to make the example simpler. I've found that wrapping the insert in a transaction resolves this issue, but that shouldn't be necessary for a single statement insert. In addition the inserts using the RETURNING clause took far less time to run, so it seems like there's some locking which is being skipped due the RETURNING clause and that's causing data loss. You can see that for the inserts using the RETURNING clause we ended up with some records missing from the DB. INSERT INTO 'LogEvents' ('ID', 'Message') The output looks like this: SQL Statement: It does all that once using inserts without the RETURNING clause, and then again with the clause. ![]() At the same time it does each insert it queries the database in a different Task.Īt the end it counts how many records are in the DB. This code inserts 250 records 1 by 1 in individual Tasks. I originally found this using EF Core and created a GitHub issue here but have replicated below using so this appears to be an SQLite bug. This bug seems to only happen if PRAGMA journal_mode=DELETE. I'm experiencing a data loss issue when inserting single records using the RETURNING clause in a multi threaded scenario. ![]()
0 Comments
Leave a Reply. |