Thank you as well for offering some tips. I have run the
integrity_check and the reindex. The reindex got rid of one of the 2
errors, but the overall problem still persists:
pragma integrity_check;
*** in database main ***
On tree page 11 cell 0: Rowid 424 out of order
wrong # of entries in index sqlite_autoindex_messages_priority_1
**2 rows returned**
reindex messages_priority
**Command returned 0**
pragma integrity_check;
*** in database main ***
On tree page 11 cell 0: Rowid 424 out of order
**1 rows returned**
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**
select id,created from messages_priority where id = 424
**No Rows**
select id,created from messages_priority where id = 423
id created
423 2/12/2017 8:07:09 PM
**1 rows returned**
On 2/13/2017 11:50 AM, David Raymond wrote:
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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
