database is empty but intact

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

database is empty but intact

Roman Fleysher
Dear SQLiters,

I am using command line sqlite3 with -vfs unix-none. This disables locking within SQLite. Instead, locking is provided externally by FLoM (distributed file lock manager). I asked questions in a thread "disable file locking mechanism over the network".

It is possible that FLoM has bugs and mismanaged locks. As a result, the database is now empty. PRAGMA integrity check shows the database is intact. I would expect it to be corrupt. The only DELETE operation in the queries was to delete a single row:

DELETE FROM jobs WHERE rowID = XXX;

Could this DELETE actually delete entire content of a table if lock is mismanaged? I am trying to figure out of this is a disk related issue. Or some other issue.



Roman
_______________________________________________
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: database is empty but intact

Simon Slavin-3
On 8 Nov 2019, at 12:03am, Roman Fleysher <[hidden email]> wrote:

> I am using command line sqlite3 with -vfs unix-none. This disables locking within SQLite. Instead, locking is provided externally by FLoM (distributed file lock manager). I asked questions in a thread "disable file locking mechanism over the network".

Is SQLite telling FLoM when it should place or remove a lock ?  If not, how does FLoM know ?

Do you have more than one process which might write to the database ?

Do all your processes which write to the database close their connections correctly ?  Do they check the result codes from their calls to see if SQlite successfully closed the database ?

> It is possible that FLoM has bugs and mismanaged locks. As a result, the database is now empty. PRAGMA integrity check shows the database is intact. I would expect it to be corrupt. The only DELETE operation in the queries was to delete a single row:
>
> DELETE FROM jobs WHERE rowID = XXX;
>
> Could this DELETE actually delete entire content of a table if lock is mismanaged?

It could, though it is unsual.  Have you run the analysis program on the file to find out more about it ?

<https://sqlite.org/sqlanalyze.html>

It could allow you to distinguish between a table with no rows in, and a table with lots of deleted rows in.
_______________________________________________
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: database is empty but intact

Roman Fleysher
There are multiple programs working in parallel. 750 of them from several compute nodes.

I use command line sqlite3, so,  sqlite itself it not a suspect.

FLoM is like a file lock:

flom argumentsToFLoMIncludingTimeout  --  sqlite3 "sqlStatement;"

I am using linux, I think this analyzer is windows only.

There several things that can fail: FLoM mismanaged locks is my primary suspect. But I would think database would be corrupt, not empty.

Maybe I should ask a different question: Would knowing answer to my question (is it possible for DB to become empty?) help me single out FLoM as the cause of the problem?

Roman

________________________________________
From: sqlite-users [[hidden email]] on behalf of Simon Slavin [[hidden email]]
Sent: Thursday, November 07, 2019 7:28 PM
To: SQLite mailing list
Subject: Re: [sqlite] database is empty but intact

On 8 Nov 2019, at 12:03am, Roman Fleysher <[hidden email]> wrote:

> I am using command line sqlite3 with -vfs unix-none. This disables locking within SQLite. Instead, locking is provided externally by FLoM (distributed file lock manager). I asked questions in a thread "disable file locking mechanism over the network".

Is SQLite telling FLoM when it should place or remove a lock ?  If not, how does FLoM know ?

Do you have more than one process which might write to the database ?

Do all your processes which write to the database close their connections correctly ?  Do they check the result codes from their calls to see if SQlite successfully closed the database ?

> It is possible that FLoM has bugs and mismanaged locks. As a result, the database is now empty. PRAGMA integrity check shows the database is intact. I would expect it to be corrupt. The only DELETE operation in the queries was to delete a single row:
>
> DELETE FROM jobs WHERE rowID = XXX;
>
> Could this DELETE actually delete entire content of a table if lock is mismanaged?

It could, though it is unsual.  Have you run the analysis program on the file to find out more about it ?

<https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsqlite.org%2Fsqlanalyze.html&amp;data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C885cd07bd2e8437eb7df08d763e29b88%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C637087697235291714&amp;sdata=Yq0C78yBQevXalcqKywwxOSV69EeRYLAG0755lCoMMY%3D&amp;reserved=0>

It could allow you to distinguish between a table with no rows in, and a table with lots of deleted rows in.
_______________________________________________
sqlite-users mailing list
[hidden email]
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&amp;data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C885cd07bd2e8437eb7df08d763e29b88%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C637087697235291714&amp;sdata=kCZumJpJuTsUpSw4wl2XncG7aATqbPFqDpaXXFc9ecs%3D&amp;reserved=0
_______________________________________________
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: database is empty but intact

Simon Slavin-3
On 8 Nov 2019, at 12:40am, Roman Fleysher <[hidden email]> wrote:

> I am using linux, I think this analyzer is windows only.

<https://sqlite.org/download.html>

"Precompiled Binaries for Linux"

> Maybe I should ask a different question: Would knowing answer to my question (is it possible for DB to become empty?) help me single out FLoM as the cause of the problem?

Could you try it for a while with locking enabled ?  See whether the problem reoccurs ?  I'm sure it'll be slower but apart from the above analyzer it's the ony diagnostic which occurs to me.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users