Partially "hidden" row, very bizarre state

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|

Partially "hidden" row, very bizarre state

Jeff B.
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Partially "hidden" row, very bizarre state

David Raymond
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:[hidden email]] On Behalf Of Jeff B.
Sent: Monday, February 13, 2017 12:32 PM
To: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Partially "hidden" row, very bizarre state

R Smith
In reply to this post by Jeff B.
Could you run and post the output of your tool to the following two
queries please:

pragma integrity_check;

select '<'||id||'>',created from messages_priority order by id limit ;


Thanks,
Ryan


On 2017/02/13 7:32 PM, Jeff B. wrote:
> 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....//

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Partially "hidden" row, very bizarre state

R Smith
Correction: That's "limit 5;" of course on the second query...

On 2017/02/13 7:58 PM, R Smith wrote:

> Could you run and post the output of your tool to the following two
> queries please:
>
> pragma integrity_check;
>
> select '<'||id||'>',created from messages_priority order by id limit ;
>
>
> Thanks,
> Ryan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Partially "hidden" row, very bizarre state

Jeff Bromberger
In reply to this post by Jeff B.
First off, thanks for the help!

Here is the output:

pragma integrity_check;
**Command returned -1**

select '<'||id||'>',created from messages_priority order by id limit 5;
     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**


For anything other than "select" I am calling ExecuteNonQuery() and
printing its return value (the -1 in this case).  Is there something
additional I need to get to you for that integrity_check command?

Thanks,
Jeff

On 2/13/2017 11:58 AM, R Smith wrote:

> Could you run and post the output of your tool to the following two
> queries please:
>
> pragma integrity_check;
>
> select '<'||id||'>',created from messages_priority order by id limit ;
>
>
> Thanks,
> Ryan
>
>
> On 2017/02/13 7:32 PM, Jeff B. wrote:
>> 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....//
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Partially "hidden" row, very bizarre state

Jeff B.
In reply to this post by Jeff B.
First off, thanks for the help!

Here is the output:

pragma integrity_check;
**Command returned -1**

select '<'||id||'>',created from messages_priority order by id limit 5;
     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**


For anything other than "select" I am calling ExecuteNonQuery() and
printing its return value (the -1 in this case).  Is there something
additional I need to get to you for that integrity_check command?

Thanks,
Jeff


On 2/13/2017 11:58 AM, R Smith wrote:

> Could you run and post the output of your tool to the following two
> queries please:
>
> pragma integrity_check;
>
> select '<'||id||'>',created from messages_priority order by id limit ;
>
>
> Thanks,
> Ryan
>
>
> On 2017/02/13 7:32 PM, Jeff B. wrote:
>> 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....//
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Partially "hidden" row, very bizarre state

Jeff B.
In reply to this post by Jeff B.
Should have read up on integrity_check before posting the results... I
re-worked my tool and here is the output:


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**



On 2/13/2017 11:58 AM, R Smith wrote:

> Could you run and post the output of your tool to the following two
> queries please:
>
> pragma integrity_check;
>
> select '<'||id||'>',created from messages_priority order by id limit ;
>
>
> Thanks,
> Ryan
>
>
> On 2017/02/13 7:32 PM, Jeff B. wrote:
>> 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....//
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Partially "hidden" row, very bizarre state

Simon Slavin-3

On 13 Feb 2017, at 6:55pm, Jeff B. <[hidden email]> wrote:

> Should have read up on integrity_check before posting the results... I re-worked my tool and here is the output:
>
>
> 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**

Just like they said, your index is corrupt.  Unfortunately it’s an automatic index so you can’t easily remake it.  And you have a password-protected database and maybe you can’t use the shell tool on it.

Make a backup copy of the database file, just in case.  Then try the VACUUM command.  Then to the integrity_check thing again.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Partially "hidden" row, very bizarre state

Jeff B.
In reply to this post by Jeff B.
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:[hidden email]] On Behalf Of Jeff B.
> Sent: Monday, February 13, 2017 12:32 PM
> To: [hidden email]
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Partially "hidden" row, very bizarre state

Jeff B.
In reply to this post by Jeff B.
The vacuum followed by a reindex seems to have fixed the db (see
below).  But are there any other theories as to how we got to this
point?  Could I have possibly caused this or do I just need to build in
code to detect problems like this and rebuild on the fly?  I'm new to
SQLite so I don't have a good feel for what is overkill vs what is best
practices.

Thanks!

Jeff


vacuum
**Command returned 0**

pragma integrity_check
wrong # of entries in index sqlite_autoindex_messages_priority_1
**1 rows returned**

reindex messages_priority
**Command returned 0**

pragma integrity_check
ok
**1 rows returned**

select id,created from messages_priority order by id limit 5
id    created
423    2/12/2017 8:07:09 PM
424    2/12/2017 8:07:10 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
id    created
424    2/12/2017 8:07:10 PM
**1 rows returned**


On 2/13/2017 12:59 PM, Simon Slavin wrote:

> On 13 Feb 2017, at 6:55pm, Jeff B. <[hidden email]> wrote:
>
>> Should have read up on integrity_check before posting the results... I re-worked my tool and here is the output:
>>
>>
>> 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**
> Just like they said, your index is corrupt.  Unfortunately it’s an automatic index so you can’t easily remake it.  And you have a password-protected database and maybe you can’t use the shell tool on it.
>
> Make a backup copy of the database file, just in case.  Then try the VACUUM command.  Then to the integrity_check thing again.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Partially "hidden" row, very bizarre state

R Smith
In reply to this post by Jeff B.

On 2017/02/13 8:55 PM, Jeff B. wrote:

> Should have read up on integrity_check before posting the results... I
> re-worked my tool and here is the output:
>
>
> 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**

Well, that sheds the necessary light. The index have somehow become
corrupted - not a bad corruption mind you, just an out-of-order node and
an easy fix by simply running:
REINDEX sqlite_autoindex_messages_priority_1;
  or just:
REINDEX;
you can also drop the automatic index and it should be recreated, or
create your own index that does the job.

The bigger question is, how did this happen?  Well, there is a myriad
possibilities from something as trivial as networked file access botch
(and a list of other possible corruption causes that you can read about
at https://sqlite.org/howtocorrupt.html) or something as insane as Ra
hating you and he had a cosmic ray hit your memory just right....

If this happened once in years, I wouldn't worry about it, but try to
work a "pragma integrity_check;" into your maintenance runs and take
action if it reports any funnies.
If this happens regularly, it indicates a system problem and you should
start logging activity and run (and log) integrity checks often till you
find the culprit.

Cheers,
Ryan
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Partially "hidden" row, very bizarre state

Richard Hipp-3
In reply to this post by Jeff B.
On 2/13/17, Jeff B. <[hidden email]> wrote:
> But are there any other theories as to how we got to this
> point?

Please see https://www.sqlite.org/howtocorrupt.html


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Partially "hidden" row, very bizarre state

Simon Slavin-3
In reply to this post by Jeff B.

On 13 Feb 2017, at 7:12pm, Jeff B. <[hidden email]> wrote:

> The vacuum followed by a reindex seems to have fixed the db (see below).  But are there any other theories as to how we got to this point?  Could I have possibly caused this or do I just need to build in code to detect problems like this and rebuild on the fly?  I'm new to SQLite so I don't have a good feel for what is overkill vs what is best practices.

The database was definitely corrupt.  It’s quite difficult to do that.  It might happen through using some of the PRAGMAs which tell SQLite not to bother doing locking/synching correctly.  It might happen through a power-cut at just the wrong moment.  It might happen because your hard disk is faulty and that was the first known sign of it failing.  It might happen through broken software overwriting part of the database. And, for any of those it might happen just once in the lifetime of your program.

But the stark fact is that many users never see this happen ever.

Proper defensive action, as you suggested, is to build the integrity_check thing into some monthly/yearly maintenance routine.  Or to do it weekly for a while, then monthly, then yearly, until you have confidence in your hardware.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Partially "hidden" row, very bizarre state

Jeff B.
In reply to this post by Jeff B.
Thanks again to everyone for the guidance.  I read through that list of
corruption candidates and most don't seem to apply.  Only one app
accesses the db file, it's on a local NTFS partition, there were no
power failures or hard reboots, no pragmas or non-default access options
were used.  So I guess that leaves hardware issues.  This software is
still in the testing state and has been run on about 10 different
machines for about 4 weeks total.  I guess it would be my luck to hit
something exceedingly rare with such a small sample size.  In any case,
I'll take this opportunity to add a bunch of additional code to check
for issues and try to fix them, or at the very least keep itself from
infinite looping if things go too wrong.

Jeff

On 2/13/2017 1:26 PM, Simon Slavin wrote:

> On 13 Feb 2017, at 7:12pm, Jeff B. <[hidden email]> wrote:
>
>> The vacuum followed by a reindex seems to have fixed the db (see below).  But are there any other theories as to how we got to this point?  Could I have possibly caused this or do I just need to build in code to detect problems like this and rebuild on the fly?  I'm new to SQLite so I don't have a good feel for what is overkill vs what is best practices.
> The database was definitely corrupt.  It’s quite difficult to do that.  It might happen through using some of the PRAGMAs which tell SQLite not to bother doing locking/synching correctly.  It might happen through a power-cut at just the wrong moment.  It might happen because your hard disk is faulty and that was the first known sign of it failing.  It might happen through broken software overwriting part of the database. And, for any of those it might happen just once in the lifetime of your program.
>
> But the stark fact is that many users never see this happen ever.
>
> Proper defensive action, as you suggested, is to build the integrity_check thing into some monthly/yearly maintenance routine.  Or to do it weekly for a while, then monthly, then yearly, until you have confidence in your hardware.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Partially "hidden" row, very bizarre state

Keith Medcalf
In reply to this post by Jeff B.

This is probably due to your bad table definition.

                 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)

is kind of nonsensical.  You are declaring some "data" column to be both a primary key, and autoincrement, and have a unique index at the same time.  You are probgably trying to declare an AUTOINCREMENT (meaning value inserted is always bigger than any even inserted before) on a rowid column.  This is pronounced thusly:

id integer primary key autoincrement

Only God (or DRH) knows what is done with the declaration "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE"

> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of Jeff B.
> Sent: Monday, 13 February, 2017 11:56
> To: SQLite mailing list
> Subject: Re: [sqlite] Partially "hidden" row, very bizarre state
>
> Should have read up on integrity_check before posting the results... I
> re-worked my tool and here is the output:
>
>
> 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**
>
>
>
> On 2/13/2017 11:58 AM, R Smith wrote:
> > Could you run and post the output of your tool to the following two
> > queries please:
> >
> > pragma integrity_check;
> >
> > select '<'||id||'>',created from messages_priority order by id limit ;
> >
> >
> > Thanks,
> > Ryan
> >
> >
> > On 2017/02/13 7:32 PM, Jeff B. wrote:
> >> 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....//
> >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Partially "hidden" row, very bizarre state

Stephen Chrzanowski
To further Keiths comment, I've never seen a database NOT treat a primary
key as not unique.  Primary Key automatically makes the field have a unique
constraint.

On Mon, Feb 13, 2017 at 8:14 PM, Keith Medcalf <[hidden email]> wrote:

>
> This is probably due to your bad table definition.
>
>                  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)
>
> is kind of nonsensical.  You are declaring some "data" column to be both a
> primary key, and autoincrement, and have a unique index at the same time.
> You are probgably trying to declare an AUTOINCREMENT (meaning value
> inserted is always bigger than any even inserted before) on a rowid
> column.  This is pronounced thusly:
>
> id integer primary key autoincrement
>
> Only God (or DRH) knows what is done with the declaration "id INTEGER NOT
> NULL PRIMARY KEY AUTOINCREMENT UNIQUE"
>
> > -----Original Message-----
> > From: sqlite-users [mailto:[hidden email]]
> > On Behalf Of Jeff B.
> > Sent: Monday, 13 February, 2017 11:56
> > To: SQLite mailing list
> > Subject: Re: [sqlite] Partially "hidden" row, very bizarre state
> >
> > Should have read up on integrity_check before posting the results... I
> > re-worked my tool and here is the output:
> >
> >
> > 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**
> >
> >
> >
> > On 2/13/2017 11:58 AM, R Smith wrote:
> > > Could you run and post the output of your tool to the following two
> > > queries please:
> > >
> > > pragma integrity_check;
> > >
> > > select '<'||id||'>',created from messages_priority order by id limit ;
> > >
> > >
> > > Thanks,
> > > Ryan
> > >
> > >
> > > On 2017/02/13 7:32 PM, Jeff B. wrote:
> > >> 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....//
> > >
> > > _______________________________________________
> > > sqlite-users mailing list
> > > [hidden email]
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Partially "hidden" row, very bizarre state

Jeff B.
In reply to this post by Keith Medcalf
Being a database newb I'll defer to you guys, and plan to remove the
unique keyword, but if the definition were "bad" shouldn't it fail to
create the table?  Not sure how it could be argued that accepting a
definition which allegedly leads to a problem < 0.01% of the time is an
acceptable sequence.

In any case, this thread seems to indicate that at one point primary key
+ unique resulted in a wasteful doubly indexed, but presumably still
functional, database.  But at the end it seems to have been confirmed
that it is optimized out as of 2008...

http://sqlite.1065341.n5.nabble.com/On-UNIQUE-and-PRIMARY-KEY-td41297.html


On 2/13/2017 7:28 PM, Stephen Chrzanowski wrote:

> To further Keiths comment, I've never seen a database NOT treat a primary
> key as not unique.  Primary Key automatically makes the field have a unique
> constraint.
>
> On Mon, Feb 13, 2017 at 8:14 PM, Keith Medcalf <[hidden email]> wrote:
>
>> This is probably due to your bad table definition.
>>
>>                   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)
>>
>> is kind of nonsensical.  You are declaring some "data" column to be both a
>> primary key, and autoincrement, and have a unique index at the same time.
>> You are probgably trying to declare an AUTOINCREMENT (meaning value
>> inserted is always bigger than any even inserted before) on a rowid
>> column.  This is pronounced thusly:
>>
>> id integer primary key autoincrement
>>
>> Only God (or DRH) knows what is done with the declaration "id INTEGER NOT
>> NULL PRIMARY KEY AUTOINCREMENT UNIQUE"
>>
>>> -----Original Message-----
>>> From: sqlite-users [mailto:[hidden email]]
>>> On Behalf Of Jeff B.
>>> Sent: Monday, 13 February, 2017 11:56
>>> To: SQLite mailing list
>>> Subject: Re: [sqlite] Partially "hidden" row, very bizarre state
>>>
>>> Should have read up on integrity_check before posting the results... I
>>> re-worked my tool and here is the output:
>>>
>>>
>>> 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**
>>>
>>>
>>>
>>> On 2/13/2017 11:58 AM, R Smith wrote:
>>>> Could you run and post the output of your tool to the following two
>>>> queries please:
>>>>
>>>> pragma integrity_check;
>>>>
>>>> select '<'||id||'>',created from messages_priority order by id limit ;
>>>>
>>>>
>>>> Thanks,
>>>> Ryan
>>>>
>>>>
>>>> On 2017/02/13 7:32 PM, Jeff B. wrote:
>>>>> 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....//
>>>> _______________________________________________
>>>> sqlite-users mailing list
>>>> [hidden email]
>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users