Re: [sqlite] In what case will the online backup api hang for acquiring the Btree lock of the destination on-disk file?

[Yihong Zhan]

Thanks for everybody’s help.

Today I did more experiment and finally narrowed down the cause (but not the 
root cause) of the problem.

Under the following situation the in-memory source database and the NFS disk 
destination database will meet deadlock across 2 threads:

  *   Thread 1 (T1) creates an in-memory database with a dir-path-like database 
name, such as “/NFS/some_dir/database” (/NFS/some_dir/ exists)
 *   std::string m_tmpFileName = 
"file:/NFS/some_dir/database?mode=memory&=shared”;
 *   int rv = sqlite3_open_v2(m_tmpFileName.c_str(), _sqlObj, 
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_SHAREDCACHE | 
SQLITE_OPEN_URI, NULL);
  *   Thread 2 (T2) creates a NFS disk database with exactly the same 
dir-path-like database name - “/NFS/some_dir/database”
 *   std::string m_fileNameOnNFS = "/NFS/some_dir/database”;
 *   int rc = sqlite3_open(m_fileNameOnNFS.c_str(), _sqlBackupDestObj);

This is the event sequence in my scenario when deadlock is reproduced:

  1.  T1 is writing to the in-memory db. Meanwhile, T2 is trying to back up the 
in-memory db to NFS db but fails due to in-memory db is busy (sqlite3_back_step 
returns SQLITE_BUSY).
  2.  T1 finishes the writing.
  3.  T2 retries the back-up. It acquires the in-memory db’s mutex and Btree’s 
mutex. Then acquires the NFS db’s mutex. But finally fails to acquire the NFS 
db’s Btree’s lock somehow (probably due to the 2 databases share the same name)
  4.  T1 tries to write to in-memory db again. However, it fails to acquire the 
mutex since T2 has acquired it in step #3. Now, both threads cannot go ahead.

It looks sqlite does not handle the locking well for this case where both 
databases use the same dir-path-like database name. I have tried that if the 
name does not include any slashes (“/“) there would be no problem.

So I think it probably be a bug in sqlite library?

Thanks

From: Quan Yong Zhai >
Date: Tuesday, July 19, 2016 at 23:56
To: Yihong Zhan >, 
"sqlite-users@mailinglists.sqlite.org"
 
>
Subject: RE: [sqlite] In what case will the online backup api hang for 
acquiring the Btree lock of the destination on-disk file?

https://www.sqlite.org/faq.html#q5

“But use caution: this locking mechanism might not work correctly if the 
database file is kept on an NFS filesystem. This is because fcntl() file 
locking is broken on many NFS implementations. You should avoid putting SQLite 
database files on NFS if multiple processes might try to access the file at the 
same time.”


Sent from 
Mail
 for Windows 10

From: Yihong Zhan
Sent: 2016年7月19日 19:44
To: 
sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] In what case will the online backup api hang for acquiring 
the Btree lock of the destination on-disk file?

Hi sqlite experts,

I am currently incorporating the sqlite online backup API (version 3.8.0.2) 
into our product. I meet a tricky problem, where the sqlite3_backup_step hangs 
at acquiring the BTree’s lock of the destination file.

Specifically, I am backing up in-memory database to NFS disk file. The 
in-memory database is manipulated in the main thread (T1) while the back-up 
runs in another thread (T2). There are only 2 threads in the process and there 
is no other process existing in our product environment.

The stack trace I got in the T2 when the hanging happens is as following:
#0  0x003de740d654 in __lll_lock_wait () from /lib64/libpthread.so.0
#1  0x003de7408f4a in _L_lock_1034 () from /lib64/libpthread.so.0
#2  0x003de7408e0c in pthread_mutex_lock () from /lib64/libpthread.so.0
#3  0x2acc20f6 in pthreadMutexEnter (p=0x23084300) at sqlite3.c:18299
#4  0x2acc1f31 in sqlite3_mutex_enter (p=0x23084300) at sqlite3.c:17812
#5  0x2acd9e4f in lockBtreeMutex (p=0x2aaad8001040) at sqlite3.c:49323
#6  0x2acd9f49 in sqlite3BtreeEnter (p=0x2aaad8001040) at 
sqlite3.c:49410
#7  0x2acdd970 in sqlite3BtreeBeginTrans (p=0x2aaad8001040, wrflag=2) 
at sqlite3.c:52240
#8  0x2ace71b7 in sqlite3_backup_step (p=0x2aaad8000fc0, nPage=50) at 
sqlite3.c:58451
#9  0x2aaace885b37 in ResDB::backup (this=0x2314c3f0, numBlocks=10, 

Re: [sqlite] Database corruption question

[Tim Streater]

On 20 Jul 2016 at 20:29, Richard Hipp  wrote: 

> On 7/19/16, Doug Nebeker  wrote:
>>  there is only one process using the database file (though multiple
>> threads, but they each have their own database handle).
>
> That threading mode (https://www.sqlite.org/threadsafe.html) are you
> using?  Are you sure that you are using the threading mode that you
> think you are using?  If you have not done so already, have you tried
> setting the threading mode to SERIALIZED to see if that clears your
> problem?

Is there a way to determine which threading mode is in use? I'm running the 
SQLite built-in to an IDE (Xojo) which uses SQLite 3.9.2. I couldn't see a 
pragma to use.

--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL to SQLite

[R.A. Nagy]

If you are into Java, then I have a source code generator that should do
the trick:

https://sourceforge.net/projects/sqlmate/?source=directory

All one need do is to use the main.java included in the archive to generate
the DAO, then create two connection strings - one for each database file.

Using JDBC one could then connect to both files, then copy one set of data
using the generated code between the two database Connection()s.

Easy-peasy!  =)


On Wed, Jul 20, 2016 at 4:48 PM, Robby Helperin 
wrote:

> Thanks R.A.Nagy.
>
> Would you be able to provide a quick example of what you mean?
>
> What I ended up doing was iterating through the rows and columns of the SQL
> database and creating a string that would later be used as an SQLite Insert
> command.  Seemed like that was the long way around.
>
> If there's a way to construct a command that in one step takes from one SQL
> database and writes to a SQLite database, I'd like to see an example of
> that
> syntax.
>
> Thanks.
>
> -Original Message-
> From: sqlite-users-boun...@mailinglists.sqlite.org
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R.A.
> Nagy
> Sent: Wednesday, July 20, 2016 7:25 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] SQL to SQLite
>
> There are several ways to work with other databases. From the SQLite, we
> can
> always attach another file so as to work with > 1 database file at a time.
> From a programmatic point of view, one simply uses yet another database
> connection to do the same thing.
>
>
>
>
>
>
> On Tue, Jul 19, 2016 at 7:21 PM, Robby Helperin 
> wrote:
>
> > Thanks for your response.
> >
> > Programming language is definitely the way I want to go, and in fact I
> > programmed a workaround, but I assume I took the long way around and
> > that there's a more standard way to do it.
> >
> > Any SQLite string is going to refer to just one database, so you can't
> > write an INSERT command that will take from one database and write to
> > another, or can you?  How would this normally done?
> >
> > -Original Message-
> > From: sqlite-users-boun...@mailinglists.sqlite.org
> > [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R.A.
> > Nagy
> > Sent: Tuesday, July 19, 2016 3:32 PM
> > To: SQLite mailing list
> > Subject: Re: [sqlite] SQL to SQLite
> >
> > Surely the best way to routinely & autocratically copy a set of data
> > from one database to another SQL technology would be to use a
> > programming language - like Java, C/C++, C#, or Python for example -
> > so as to copy data between two different database connections.
> >
> > The only other way would be to create a textual .dump or CSV (etc)
> > export file, then munge the data for a clear-text importation via any
> > data migration tools available for the foreign SQL 'tech.
> >
> > Here is an explanation of how to do the later for SQLite:
> > https://www.youtube.com/watch?v=bVq57NBOaLs
> >
> >
> >
> >
> >
> > On Tue, Jul 19, 2016 at 6:01 PM,  wrote:
> >
> > > What's the best way to Insert or Update records from a connected SQL
> > > database to the connected SQLite database?
> > >
> > >
> > >
> > > I don't mean just once, but to do every so often.
> > >
> > >
> > >
> > > Thanks.
> > >
> > > ___
> > > 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
> >
> ___
> 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

Re: [sqlite] SQL to SQLite

[Robby Helperin]

Thanks R.A.Nagy.

Would you be able to provide a quick example of what you mean?

What I ended up doing was iterating through the rows and columns of the SQL
database and creating a string that would later be used as an SQLite Insert
command.  Seemed like that was the long way around.

If there's a way to construct a command that in one step takes from one SQL
database and writes to a SQLite database, I'd like to see an example of that
syntax.

Thanks.

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R.A. Nagy
Sent: Wednesday, July 20, 2016 7:25 AM
To: SQLite mailing list
Subject: Re: [sqlite] SQL to SQLite

There are several ways to work with other databases. From the SQLite, we can
always attach another file so as to work with > 1 database file at a time.
>From a programmatic point of view, one simply uses yet another database
connection to do the same thing.






On Tue, Jul 19, 2016 at 7:21 PM, Robby Helperin 
wrote:

> Thanks for your response.
>
> Programming language is definitely the way I want to go, and in fact I 
> programmed a workaround, but I assume I took the long way around and 
> that there's a more standard way to do it.
>
> Any SQLite string is going to refer to just one database, so you can't 
> write an INSERT command that will take from one database and write to 
> another, or can you?  How would this normally done?
>
> -Original Message-
> From: sqlite-users-boun...@mailinglists.sqlite.org
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R.A.
> Nagy
> Sent: Tuesday, July 19, 2016 3:32 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] SQL to SQLite
>
> Surely the best way to routinely & autocratically copy a set of data 
> from one database to another SQL technology would be to use a 
> programming language - like Java, C/C++, C#, or Python for example - 
> so as to copy data between two different database connections.
>
> The only other way would be to create a textual .dump or CSV (etc) 
> export file, then munge the data for a clear-text importation via any 
> data migration tools available for the foreign SQL 'tech.
>
> Here is an explanation of how to do the later for SQLite:
> https://www.youtube.com/watch?v=bVq57NBOaLs
>
>
>
>
>
> On Tue, Jul 19, 2016 at 6:01 PM,  wrote:
>
> > What's the best way to Insert or Update records from a connected SQL 
> > database to the connected SQLite database?
> >
> >
> >
> > I don't mean just once, but to do every so often.
> >
> >
> >
> > Thanks.
> >
> > ___
> > 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
>
___
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

Re: [sqlite] Database corruption question

[Richard Hipp]

On 7/19/16, Doug Nebeker  wrote:
>  there is only one process using the database file (though multiple
> threads, but they each have their own database handle).

That threading mode (https://www.sqlite.org/threadsafe.html) are you
using?  Are you sure that you are using the threading mode that you
think you are using?  If you have not done so already, have you tried
setting the threading mode to SERIALIZED to see if that clears your
problem?

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL to SQLite

[dmp]

> Message: 2
> Date: Tue, 19 Jul 2016 15:01:55 -0700
> From: 
> To: 
> Subject: [sqlite] SQL to SQLite
> Message-ID: <004d01d1e209$2a356360$7ea02a20$@spotlightmusic.com>
> Content-Type: text/plain; charset="us-ascii"
>
> What's the best way to Insert or Update records from a connected SQL
> database to the connected SQLite database?
>
> I don't mean just once, but to do every so often.
> Thanks.

> Thanks for your response.

> Programming language is definitely the way I want to go, and in fact I
> programmed a workaround, but I assume I took the long way around and that
> there's a more standard way to do it.

Hello,

   If the source database is different, or SQLite, than the destination
database, SQLite, can be done and I have been working on a bridge in Java
that will perform the transfer. It is functional, but needs further work
and testing.

Essentially:

Create an ArrayBlockingQueue, start a LoadThread and a InsertPrepareThread.
Define the ArrayBlockingQueue as objects of a relation row element. Have
the load thread fill the blocking queue then the insert prepare thread
consume the table row elements from the queue.

A single SQL query can be used to define the SQLite table then fill it
with the source database data. A type definitions conversion needs to
take place to correctly transfer db --> db data types.

I have defined these type info conversions for various database and
it is available as part of my MyJSQLView project. At this time the
db --> db code is a plugin for MyJSQLView and is not been released to
the public.

https://github.com/danap/myjsqlview/blob/master/src/com/dandymadeproductions/myjsqlview/datasource/TypesInfoCache.java

Dana Proctor
MyJSQLView Project Manager

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL to SQLite

[R.A. Nagy]

There are several ways to work with other databases. From the SQLite, we
can always attach another file so as to work with > 1 database file at a
time. From a programmatic point of view, one simply uses yet another
database connection to do the same thing.






On Tue, Jul 19, 2016 at 7:21 PM, Robby Helperin 
wrote:

> Thanks for your response.
>
> Programming language is definitely the way I want to go, and in fact I
> programmed a workaround, but I assume I took the long way around and that
> there's a more standard way to do it.
>
> Any SQLite string is going to refer to just one database, so you can't
> write
> an INSERT command that will take from one database and write to another, or
> can you?  How would this normally done?
>
> -Original Message-
> From: sqlite-users-boun...@mailinglists.sqlite.org
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R.A.
> Nagy
> Sent: Tuesday, July 19, 2016 3:32 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] SQL to SQLite
>
> Surely the best way to routinely & autocratically copy a set of data from
> one database to another SQL technology would be to use a programming
> language - like Java, C/C++, C#, or Python for example - so as to copy data
> between two different database connections.
>
> The only other way would be to create a textual .dump or CSV (etc) export
> file, then munge the data for a clear-text importation via any data
> migration tools available for the foreign SQL 'tech.
>
> Here is an explanation of how to do the later for SQLite:
> https://www.youtube.com/watch?v=bVq57NBOaLs
>
>
>
>
>
> On Tue, Jul 19, 2016 at 6:01 PM,  wrote:
>
> > What's the best way to Insert or Update records from a connected SQL
> > database to the connected SQLite database?
> >
> >
> >
> > I don't mean just once, but to do every so often.
> >
> >
> >
> > Thanks.
> >
> > ___
> > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL to SQLite

[Simon Slavin]

On 20 Jul 2016, at 12:21am, Robby Helperin  wrote:

> Any SQLite string is going to refer to just one database, so you can't write
> an INSERT command that will take from one database and write to another, or
> can you?  How would this normally done?

See the ATTACH command:



Open one database file and ATTACH another.  Once command can refer to tables in 
both database files.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database corruption question

[Simon Slavin]

On 19 Jul 2016, at 5:43pm, Doug Nebeker  wrote:

> Would it be possible for a file scanning process (anti-virus, backup, etc) to 
> grab hold of a database file at just the right moment, momentarily blocking a 
> write or delete, and causing corruption?

It might prevent the program from accessing the file in a way that the program 
interprets as corruption.  It should not actually corrupt the file on disk.  In 
that if you later reopen the file and run

PRAGMA integrity_check

it should not find a problem.

Does that command find a problem with your file ?  Once you have noticed the 
problem are you restoring to an uncorrupt version before continuing use of the 
file ?

> There is a greater chance that this is my bug or environmental than being in 
> SQLite, so I'm looking for anything I can do to decrease these occurrences.

Are you using any PRAGMAs after you open the file or special modes in your open 
command ?

Do you check the result codes returned by all commands run on the file to make 
sure they're SQLITE_OK ?  The command which notices that the file is corrupt is 
run sometime /after/ the command which corrupts it.  It's probably not the 
command which causes the corruption.

When you make changes to the file do you use _exec() or 
_prepare,_step,_finalize ?

Is the database short enough to allow you to run 'PRAGMA integrity_check' 
occasionally (when your app starts or quits ?  At 3am every morning ?) and 
check the result ?

Do not forget that your mention of 'environmental' includes the possibility of 
a faulty hard disk.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] get field precision

[Igor Tandetnik]

On 7/20/2016 6:40 AM, Leonardo Massei wrote:

I've a simple question: I need to get the precision (or scale) of a
table's field


SQLite doesn't have a concept of field precision or scale, so there's 
nothing to get. See also: http://www.sqlite.org/datatype3.html

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] get field precision

[Leonardo Massei]

Hello,
I've a simple question: I need to get the precision (or scale) of a 
table's field; there is
a sqlite core function ? Is it possible make an SQL command to get the 
precision ?


Best Regards
Leonard

LEGAL DISCLAIMER:
The contents of this email and any transmitted files are confidential and 
intended solely for the use of the individual or entity to whom they are 
addressed. We hereby exclude any warranty and any liability as to the quality 
or accuracy of the contents of this email and any attached transmitted files. 
If you are not the intended recipient, be advised that you have received this 
email in error and that any use, dissemination, forwarding, printing or copying 
of this email is strictly prohibited. If you have received this email in error 
please contact the sender and delete the material from any computer.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Typographical error in sqlite.org/howtocorrupt.html

[Eubank, Tom]

I believe that there is a typographical error on the following page:

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

*7.4 Database pages leak from the free page list*

When content is deleted from an SQLite database, pages that are no
longer used are added to a free list and are reused to hold content
added *but* subsequent inserts.

Change 'added *but* subsequent inserts.'
to 'added *by* subsequent inserts.'

Peace,
Tom Eubank

---
Explanation, only if needed:
 * It seems most likely that this paragraph intends to mean:
  "pages in the free list are reused ... by subsequent inserts."
 * The sentence as written seems awkward and incomplete.
 * If some other reading is intended, such reading is not obvious.
 * If 'but' is being used as a conjunction, then something else is
   missing from the conjunctive clause.

This 'Explanation' was added after reading How to Report Bugs Effectively
,
in case my suggested change is not obvious.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL to SQLite

[Robby Helperin]

Thanks for your response.

Programming language is definitely the way I want to go, and in fact I
programmed a workaround, but I assume I took the long way around and that
there's a more standard way to do it.

Any SQLite string is going to refer to just one database, so you can't write
an INSERT command that will take from one database and write to another, or
can you?  How would this normally done?

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R.A. Nagy
Sent: Tuesday, July 19, 2016 3:32 PM
To: SQLite mailing list
Subject: Re: [sqlite] SQL to SQLite

Surely the best way to routinely & autocratically copy a set of data from
one database to another SQL technology would be to use a programming
language - like Java, C/C++, C#, or Python for example - so as to copy data
between two different database connections.

The only other way would be to create a textual .dump or CSV (etc) export
file, then munge the data for a clear-text importation via any data
migration tools available for the foreign SQL 'tech.

Here is an explanation of how to do the later for SQLite:
https://www.youtube.com/watch?v=bVq57NBOaLs





On Tue, Jul 19, 2016 at 6:01 PM,  wrote:

> What's the best way to Insert or Update records from a connected SQL 
> database to the connected SQLite database?
>
>
>
> I don't mean just once, but to do every so often.
>
>
>
> Thanks.
>
> ___
> 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

[sqlite] Database corruption question

[Doug Nebeker]

Been using SQLite for a long time and a huge fan.  We occasionally see database 
corruption on a local NTFS Windows drive and I've been trying to figure it out.

I finally have some logs from the SQLITE_CONFIG_LOG callback that may be of 
help:

(11) database corruption at line 78267 of [3d862f207e]
statement aborts at 29: [DELETE FROM StatData WHERE StatID IN (SELECT StatID 
FROM Statistic WHERE OwnerType IN (4) AND 
OwningComputer='f7ab745b-1aa7-4159-bbf7-b0b4d1262804') AND Date < 1350715577;]

This is for version 3.11, and the line is for a OP_NotExists case.
There was no power outage or other OS problem, this was not a restored database 
but had been in use for at least 12 hours.  It was not using WAL, and there is 
only one process using the database file (though multiple threads, but they 
each have their own database handle).

I've been through https://www.sqlite.org/howtocorrupt.html a number of times 
over the years :)

Would it be possible for a file scanning process (anti-virus, backup, etc) to 
grab hold of a database file at just the right moment, momentarily blocking a 
write or delete, and causing corruption?

There is a greater chance that this is my bug or environmental than being in 
SQLite, so I'm looking for anything I can do to decrease these occurrences.

Thanks for any input.

Doug
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In what case will the online backup api hang for acquiring the Btree lock of the destination on-disk file?

[Simon Slavin]

On 20 Jul 2016, at 9:03am, Yihong Zhan  wrote:

> *   std::string m_tmpFileName = 
> "file:/NFS/some_dir/database?mode=memory&=shared”;
> *   int rv = sqlite3_open_v2(m_tmpFileName.c_str(), _sqlObj, 
> SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_SHAREDCACHE | 
> SQLITE_OPEN_URI, NULL);

Try it without the option:

*   std::string m_tmpFileName = "file:/NFS/some_dir/database?mode=memory”;
*   int rv = sqlite3_open_v2(m_tmpFileName.c_str(), _sqlObj, 
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_URI, NULL);

Also remove these options from all other connections and file specifications in 
other programs running at the same time.

Are you using any PRAGMA commands ?

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In what case will the online backup api hang for acquiring the Btree lock of the destination on-disk file?

[Yihong Zhan]

Thanks Quan Yhong. Today I did more experiment and finally narrowed down the 
cause (but not the root cause) of the problem.

Under the following situation the in-memory source database and the NFS disk 
destination database will meet deadlock across 2 threads:

  *   Thread 1 (T1) creates an in-memory database with a dir-path-like database 
name, such as “/NFS/some_dir/database” (/NFS/some_dir/ exists)
 *   std::string m_tmpFileName = 
"file:/NFS/some_dir/database?mode=memory&=shared”;
 *   int rv = sqlite3_open_v2(m_tmpFileName.c_str(), _sqlObj, 
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_SHAREDCACHE | 
SQLITE_OPEN_URI, NULL);
  *   Thread 2 (T2) creates a NFS disk database with exactly the same 
dir-path-like database name - “/NFS/some_dir/database”
 *   std::string m_fileNameOnNFS = "/NFS/some_dir/database”;
 *   int rc = sqlite3_open(m_fileNameOnNFS.c_str(), _sqlBackupDestObj);

This is the event sequence in my scenario when deadlock is reproduced:

  1.  T1 is writing to the in-memory db. Meanwhile, T2 is trying to back up the 
in-memory db to NFS db but fails due to in-memory db is busy (sqlite3_back_step 
returns SQLITE_BUSY).
  2.  T1 finishes the writing.
  3.  T2 retries the back-up. It acquires the in-memory db’s mutex and Btree’s 
mutex. Then acquires the NFS db’s mutex. But finally fails to acquire the NFS 
db’s Btree’s lock somehow (probably due to the 2 databases share the same name)
  4.  T1 tries to write to in-memory db again. However, it fails to acquire the 
mutex since T2 has acquired it in step #3. Now, both threads cannot go ahead.

It looks sqlite does not handle the locking well for this case where both 
databases use the same dir-path-like database name. I have tried that if the 
name does not include any slashes (“/“) there would be no problem.

Thanks

From: Quan Yong Zhai >
Date: Tuesday, July 19, 2016 at 23:56
To: Yihong Zhan >, 
"sqlite-users@mailinglists.sqlite.org"
 
>
Subject: RE: [sqlite] In what case will the online backup api hang for 
acquiring the Btree lock of the destination on-disk file?

https://www.sqlite.org/faq.html#q5

“But use caution: this locking mechanism might not work correctly if the 
database file is kept on an NFS filesystem. This is because fcntl() file 
locking is broken on many NFS implementations. You should avoid putting SQLite 
database files on NFS if multiple processes might try to access the file at the 
same time.”


Sent from 
Mail
 for Windows 10

From: Yihong Zhan
Sent: 2016年7月19日 19:44
To: 
sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] In what case will the online backup api hang for acquiring 
the Btree lock of the destination on-disk file?

Hi sqlite experts,

I am currently incorporating the sqlite online backup API (version 3.8.0.2) 
into our product. I meet a tricky problem, where the sqlite3_backup_step hangs 
at acquiring the BTree’s lock of the destination file.

Specifically, I am backing up in-memory database to NFS disk file. The 
in-memory database is manipulated in the main thread (T1) while the back-up 
runs in another thread (T2). There are only 2 threads in the process and there 
is no other process existing in our product environment.

The stack trace I got in the T2 when the hanging happens is as following:
#0  0x003de740d654 in __lll_lock_wait () from /lib64/libpthread.so.0
#1  0x003de7408f4a in _L_lock_1034 () from /lib64/libpthread.so.0
#2  0x003de7408e0c in pthread_mutex_lock () from /lib64/libpthread.so.0
#3  0x2acc20f6 in pthreadMutexEnter (p=0x23084300) at sqlite3.c:18299
#4  0x2acc1f31 in sqlite3_mutex_enter (p=0x23084300) at sqlite3.c:17812
#5  0x2acd9e4f in lockBtreeMutex (p=0x2aaad8001040) at sqlite3.c:49323
#6  0x2acd9f49 in sqlite3BtreeEnter (p=0x2aaad8001040) at 
sqlite3.c:49410
#7  0x2acdd970 in sqlite3BtreeBeginTrans (p=0x2aaad8001040, wrflag=2) 
at sqlite3.c:52240
#8  0x2ace71b7 in sqlite3_backup_step (p=0x2aaad8000fc0, nPage=50) at 
sqlite3.c:58451
#9  0x2aaace885b37 in ResDB::backup (this=0x2314c3f0, numBlocks=10, 
numPagesPerBlock=50, blockIntervalInMillisec=50) at dbEngine.cpp:659
#10 

Re: [sqlite] SQL to SQLite

[Hick Gunter]

Converting from one SQLite database to another may also be accomplished by 
using the ".mode insert " output format of the SQLite shell and 
then SELECTING the rows according to the full target schema. This creates SQL 
of the form "insert into  values (); NOTE: No field 
list

Or you can SELECT 'insert into  () (',,');' 
from ; after setting .mode list and .separator "," and .out 

Maybe your source database supports equivalent features that allow "proof of 
concept" via scripts.

Writing your own "bridge program" to translate between yource and destination 
databases is bound to be significantly faster though.

-Ursprüngliche Nachricht-
Von: sqlite-users-boun...@mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von 
tm...@spotlightmusic.com
Gesendet: Mittwoch, 20. Juli 2016 00:02
An: sqlite-users@mailinglists.sqlite.org
Betreff: [sqlite] SQL to SQLite

What's the best way to Insert or Update records from a connected SQL database 
to the connected SQLite database?



I don't mean just once, but to do every so often.



Thanks.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users