I vaguely recall a while ago someone finding a small bug when "primary key unique" was used. Since those are redundant you should get rid of the "unique" in the id field creation as "primary key" takes care of that. (It "shouldn't" make a difference, but don't tempt fate)
Other things: run a pragma integrity_check; to look for problems. And a reindex; to try fixing any found index problems, as it sounds to me like an index got messed up somewhere along the line. If it was using the bad index for ordering, but displaying the id from the actual table that would explain showing the record out of order. And if it used that index to look for the record to delete then it wouldn't find 424, just whatever else was in there for the id. Apparently deleting something else there might have nudged it to correctness? I'm not sure why deleting another record would fix the index though, weird. Apart from the "primary key unique" thing I don't have a clue how it would get into that state though, so can't help you there. -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Jeff B. Sent: Monday, February 13, 2017 12:32 PM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Partially "hidden" row, very bizarre state Hi Everyone, Sorry for the strange subject, but the situation I'm facing is so odd that I wasn't even sure how to summarize it. First, a little context: I'm using v1.0.104 of System.Data.SQLite.Core in my C#/.NET 4.5 project. I have a table that is created as follows: CREATE TABLE messages_priority ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, type TEXT, json TEXT, options TEXT, created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL) The purpose of my SQLite db is to locally queue messages until they can be sent to a web API, at which time they are deleted. My single consumer thread basically does a "SELECT * FROM messages_priority ORDER BY id LIMIT 1" and then if the send to the web is successful it deletes that id. This has all been working fine for months until I had one client hit a problem yesterday. They kept sending the same message over and over to the web and would never move on to anything else. Once I dug in further this is what I found: (Note that I use a password on the database and I have not been able to find any db viewers for Windows which support the type of encryption that Windows SQLite uses, so I had to write my own simple query tool, the output of which is shown below) select id,created from messages_priority order by id limit 5 id created 424 2/12/2017 8:07:10 PM 423 2/12/2017 8:07:09 PM 425 2/12/2017 8:07:11 PM 426 2/12/2017 8:07:12 PM 427 2/12/2017 8:07:13 PM **5 rows returned** // So here we see the first problem. 424 is listed before 423 even though I'm specifying an order by id. select id,created from messages_priority where id = 424 **No Rows** // Now we see the next problem... even though 424 comes back in the first query, when I try to specify it in a WHERE clause it is not found. select id,created from messages_priority where id = 423 id created 423 2/12/2017 8:07:09 PM **1 rows returned** // 423, however, returns just fine that way... delete from messages_priority where id = 424 **Command returned 0** // delete doesn't throw an exception or anything, but indicates nothing was deleted select id,created from messages_priority order by id limit 1 id created 424 2/12/2017 8:07:10 PM **1 rows returned** // still shows up in a select with no WHERE delete from messages_priority where id = 423 **Command returned 1** // attempt to delete 423 is successful select id,created from messages_priority where id = 424 id created 424 2/12/2017 8:07:10 PM **1 rows returned** // now I can finally query by the id! delete from messages_priority where id = 424 **Command returned 1** // and delete it! So somehow, records 423 and 424 seemed to have gotten "out of order" in the database, and until 423 was deleted, nothing could directly find 424. Because 424 could never be deleted my consumer kept getting it over and over and could never move on. That makes sense at least... But I can't imagine how I can work around this in code once the database has gotten into this state. Does this seem like something that could be caused by improper inserts or is this possibly a bug in SQLite or the C# wrapper? I don't do anything to change the defaults in terms of concurrency or isolation settings. My understanding from the docs was that this should be thread safe without any extra locking on my end. Each thread that uses the db has its own connection var and re-uses that over and over. The producer and consumer are separate threads but there is only one of each. This is my code for inserting the rows: // use a transaction so we are sure to get the correct row id back using (var transaction = sqlConnection.BeginTransaction()) { try { var sql = String.Format("INSERT INTO messages_priority (type, json, options) VALUES (@p0, @p1, @p2)"); using (var command = new SQLiteCommand(sql, sqlConnection)) { command.Parameters.Add(new SQLiteParameter("@p0", type)); command.Parameters.Add(new SQLiteParameter("@p1", json)); command.Parameters.Add(new SQLiteParameter("@p2", options)); command.ExecuteNonQuery(); } var rowId = sqlConnection.LastInsertRowId; transaction.Commit(); return rowId; } catch { // technically shouldn't be anything to roll back as we are only doing one thing and if that threw // an exception then nothing happened... transaction.Rollback(); throw; } } I'm pretty much stumped here, any thoughts would be greatly appreciated! Thanks, Jeff _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
