Re: [sqlite] Issue with SQLite3 for WinRT ARM

[Christian Le Gall]

Sorry about that.

Here is the project:
http://dl.dropbox.com/u/4076650/Direct3DApp_ProblemExample.zip

> This typically indicates a bug in your program, such as threading
> issues, objects used after being freed, or just general memory
> corruption.

Typically I would agree with you but in the case above I have replicated
the issue in a stripped down example project so there should be no rogue
memory allocation or threading happening. It even happens if you comment
out all unnecessary calls so all you're left with is an empty Metro
application and it still happens on ARM. I've sent this to Microsoft
developer support who tell me it is most likely a problem with SQLite.

Regards,

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

Re: [sqlite] How much disk space is required to checkpoint?

[Jay A. Kreibich]

On Wed, Sep 26, 2012 at 02:59:12PM +0100, Simon Slavin scratched on the wall:

> Allow me to present an alternative point of view.
> 
> If filling up your filespace is causing you to crash, it's easier to
> understand what's going on if you can see the files which are filling
> it up.  Running out of disk space when you can't see huge files makes
> it difficult to debug.  With an unlinked file I would find it hard to 
> figure out what was filling my hard disk causing me to crash.

  That's a valid point, and is one of the reasons why /tmp is
  traditionally its own file system... if you accidentally fill it, you
  might lock an application or two, but you shouldn't cause issues
  with the whole OS.

  Of course if the application dies because it ran out of space, that's
  its own fault for making assumptions about storage.  Having the file
  unlinked (or not) doesn't change those issues, other than leaving
  behind evidence of the cause.  An application properly checking 
  and reporting error codes doesn't need evidence.

  Regardless, you're saying it is easier and more logical to hunt
  through a whole file tree looking for a large file, rather than just
  checking to see how full the disks are.  While I get where you're
  coming from, I think it is a pretty weak point next to the general
  advantages open-and-unlink offers.


  There also seems to be a running assumption that disk space should,
  in some direct way, relate to the sum total of all the file sizes on
  a volume.  That's not a very safe assumption with Unix file systems.
  While the use non-symbolic links is somewhat rare these days, they
  do exist.  You can have the same file show up multiple places in the
  file system as a full-on native file (not a link or alias), but it
  only shows up on the disk once (that's why the call is unlink() and
  not delete()).
  
  Similarly, the "size" of a file and how much disk space it takes up
  are not always the same.   Most Unix file systems support sparse
  files, so you can have a files that report a "size" significantly
  larger than their actual disk usage.

  In fact, we used to test backup and archive software that way.  Open
  a file, write a byte, advanced the file counter several hundred
  gigabytes, write another byte.  The file shows up as huge, but it is
  only taking up a few kilobytes on disk.  The smart backup systems
  understand this, the dumb ones burn through a lot of tape recording
  zeros.  Same with tar and similar utilities, and don't get me started
  with crappy home-grown quota systems.  You can cause a lot of headaches
  for your sysadmin that way.  It's also fun to freak out the newbies 
  by putting a 2 TB file onto their 500 GB disk.

> Avoiding filename clashes can be done by creating files with random
> or time-related elements to their names.  It's less of a problem.

  As I already pointed out, there are many, many reasons for the
  "open-and-unlink" pattern that go beyond file name collision.  In
  fact, that's a very minor reason, since an application should be
  using one of the C standard calls like tmpnam() to generate unique
  file names in a known, accepted way.

> > How would a file that clogs up /tmp be preferrable to some unnamed data
> > that will be automatically removed from the file system by the fsck
> > after the crash?
> 
> Unix deletes the contents of /tmp at boot time.

  Some, but not all.  I've run across systems that don't bother to
  delete /tmp.  Not that it matters... many Unix systems have a high
  enough uptime that anything done on reboot happens pretty rare, and
  really shouldn't be considered standard maintenance.

> That's why it's special. 

  That, and it has specific permissions that allow anyone to create
  files.  Traditionally, it is also its own file system, although
  that's somewhat rare these days.

> In contrast, using unlink() can cause some
> chaos including filespace hogs turning up in lost+found -- the sort
> of thing that might cause problems that a mundane user might never
> understand.

  No, it can't.  In fact, if "problems for the mundane user" is your
  primary concern, open-and-unlink is a much better pattern in almost
  every way.

  Understand that if a process does an open-and-unlink and the
  process terminates FOR ANY REASON, including a crash, then the OS
  will close all the file descriptors, which will trigger an automatic
  deletion of the file.  The process cannot exit without cleaning up.

  Compare this to a traditional file in temp, which is just going to get
  left there, taking up space until the next reboot.  Most users-- even
  savvy users-- don't manually clean up their /tmp directory between
  reboots.  If this is a desktop with a multi-month uptime, and more
  and more cruft is getting left behind, it is conceivable that /tmp
  may fill up.

  If an application manages to fill up /tmp, the correct thing to do is
  notice that the write operations aren't working, report the error and
  exit. 

Re: [sqlite] Sending SQLite3 .timeout command in Command-Line Mode

[John]

I used the following to upgrade SQLite 3 on 10.6.8. I now have version
3.6.12 installed in /usr/bin/ and version 3.7.14 installed in
/usr/local/bin/. Was this the best way to install it?

mkdir ~/tempFolder
cd ~/tempFolder
curl https://www.sqlite.org/sqlite-autoconf-3071400.tar.gz | tar xvz
cd sqlite-autoconf-3071400
autoconf
./configure --prefix=/usr/local
make
sudo make install


As long as I call the latest version, the command below works perfectly.
set xxx to do shell script "/usr/local/bin/sqlite3 -cmd \".timeout 2\"  " &
databasePath & " \"select * from " & table1 & ";\""

Thank you for your help.
John


On Thu, Sep 27, 2012 at 10:49 AM, Black, Michael (IS) <
michael.bla...@ngc.com> wrote:

> The other thing you should do is check the exit status of sqlite3.  if not
> 0 then an error occurred.
>
> Plus parse the output to see if you get any errors -- in specific handle
> the errors you know about and show errors that need a handler.  So for BUSY
> and LOCKED you may loop for a while retrying it.
>
> Again..don't know how applescript does this.
>
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> Northrop Grumman Information Systems
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Win CE support for WAL

[Caleb A. Austin]

I have looked over the forums, but have not seen a recent answer to this
question, so I will ask it again.

 

Is WAL mode supported if SQLite is used on Windows CE(WEC7)?

 

Also are there any users that are using SQLite on WEC7 that would share
there experiences?

 

Thanks

 

Caleb Austin


##
CONFIDENTIALITY NOTICE: This email and any files transmitted with it are 
confidential and intended
solely for the use of the individual or entity to whom they are addressed. It 
may contain confidential,
privileged, and/or proprietary information. Any review, dissemination, 
distribution, copying, printing,
or other use of this email by persons or entities other than the addressee and 
his/her authorized agent
is prohibited. 

If you have received this email in error please notify the originator of the 
message and delete the
material from your computer.
##
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sending SQLite3 .timeout command in Command-Line Mode

[Jay A. Kreibich]

On Thu, Sep 27, 2012 at 10:15:14AM -0400, John scratched on the wall:
> As you can tell, I don't have much experience with sql. I was going in the
> timeout direction because simply resending the command several seconds
> after the locked error occurred seemed to return the correct value. My plan
> is to implement Michael's suggestion and if the error continues to occur,
> place a rollback in an error handler and move on from there. Is that
> reasonable or am I still missing something?

  That sounds fine.  The main point I was trying to make is that there
  are some (rare) situations when a timeout value will not solve every
  problem, even if the server has very light concurrency needs.  There
  are situations when the handler will still return a SQLITE_BUSY error,
  and you're only choice is to rollback and start over.  The timeout
  should catch and handle the vast, vast majority of SQLITE_BUSY errors,
  however.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Switching journal mode from WAL to DELETE results in SQLITE_IOERR rather than SQLITE_READONLY (bug?)

[Sebastian Krysmanski]

Ok, then: is this the expected behavior?

On Fri, Sep 28, 2012 at 3:29 PM, Richard Hipp  wrote:

> On Fri, Sep 28, 2012 at 9:23 AM, Sebastian Krysmanski
> wrote:
>
> > Yes, I know that. It's just that the error code reported by
> > "sqlite3_step()" is inconsistent.
> >
>
> I "bug" means that the wrong answer is computed.  I don't think getting a
> slightly different error code for an illegal operation when performed on
> different operating systems qualifies as a "bug".
>
>
> >
> > On Fri, Sep 28, 2012 at 3:21 PM, Richard Hipp  wrote:
> >
> > > On Fri, Sep 28, 2012 at 9:09 AM, Sebastian Krysmanski
> > > wrote:
> > >
> > > > Hi,
> > > >
> > > > I'm working on a C# SQLite wrapper and have created some unit tests.
> > One
> > > of
> > > > them checks the behavior when changing the permanent journal mode of
> a
> > > > database (from DELETE to WAL and the other way around) while the
> > database
> > > > is in read-only mode. Here's what I do basically:
> > > >
> > > > 1. Open new database in read-write mode.
> > > > 2. Set "source" journal mode (WAL or DELETE)
> > > > 3. Close connection
> > > > 4. Open connection to this database in read-only mode
> > > > 5. Try to change to the "destination" journal mode (again WAL or
> > DELETE).
> > > > Except this to return SQLITE_READONLY.
> > > >
> > > > Now, on Windows "sqlite3_step()" returns "SQLITE_READONLY" in both
> > > > directions but on Linux (or on Android, to be more precise) I get
> > > > "SQLITE_READONLY" when changing from DELETE to WAL but I get
> > > "SQLITE_IOERR"
> > > > ("disk I/O error") when changing from WAL to DELETE.
> > > >
> > > > I'm using SQLite 3.7.14.
> > > >
> > > > Is this a bug?
> > > >
> > >
> > > The DELETE/WAL distinction is recorded in the database file itself.  So
> > if
> > > the database is read-only, you cannot alter the file in order to change
> > it
> > > between DELETE and WAL.
> > >
> > >
> > > >
> > > > Best regards
> > > > Sebastian
> > > > ___
> > > > sqlite-users mailing list
> > > > sqlite-users@sqlite.org
> > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > > >
> > >
> > >
> > >
> > > --
> > > D. Richard Hipp
> > > d...@sqlite.org
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Switching journal mode from WAL to DELETE results in SQLITE_IOERR rather than SQLITE_READONLY (bug?)

[Richard Hipp]

On Fri, Sep 28, 2012 at 9:23 AM, Sebastian Krysmanski
wrote:

> Yes, I know that. It's just that the error code reported by
> "sqlite3_step()" is inconsistent.
>

I "bug" means that the wrong answer is computed.  I don't think getting a
slightly different error code for an illegal operation when performed on
different operating systems qualifies as a "bug".


>
> On Fri, Sep 28, 2012 at 3:21 PM, Richard Hipp  wrote:
>
> > On Fri, Sep 28, 2012 at 9:09 AM, Sebastian Krysmanski
> > wrote:
> >
> > > Hi,
> > >
> > > I'm working on a C# SQLite wrapper and have created some unit tests.
> One
> > of
> > > them checks the behavior when changing the permanent journal mode of a
> > > database (from DELETE to WAL and the other way around) while the
> database
> > > is in read-only mode. Here's what I do basically:
> > >
> > > 1. Open new database in read-write mode.
> > > 2. Set "source" journal mode (WAL or DELETE)
> > > 3. Close connection
> > > 4. Open connection to this database in read-only mode
> > > 5. Try to change to the "destination" journal mode (again WAL or
> DELETE).
> > > Except this to return SQLITE_READONLY.
> > >
> > > Now, on Windows "sqlite3_step()" returns "SQLITE_READONLY" in both
> > > directions but on Linux (or on Android, to be more precise) I get
> > > "SQLITE_READONLY" when changing from DELETE to WAL but I get
> > "SQLITE_IOERR"
> > > ("disk I/O error") when changing from WAL to DELETE.
> > >
> > > I'm using SQLite 3.7.14.
> > >
> > > Is this a bug?
> > >
> >
> > The DELETE/WAL distinction is recorded in the database file itself.  So
> if
> > the database is read-only, you cannot alter the file in order to change
> it
> > between DELETE and WAL.
> >
> >
> > >
> > > Best regards
> > > Sebastian
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Switching journal mode from WAL to DELETE results in SQLITE_IOERR rather than SQLITE_READONLY (bug?)

[Sebastian Krysmanski]

Yes, I know that. It's just that the error code reported by
"sqlite3_step()" is inconsistent.

On Fri, Sep 28, 2012 at 3:21 PM, Richard Hipp  wrote:

> On Fri, Sep 28, 2012 at 9:09 AM, Sebastian Krysmanski
> wrote:
>
> > Hi,
> >
> > I'm working on a C# SQLite wrapper and have created some unit tests. One
> of
> > them checks the behavior when changing the permanent journal mode of a
> > database (from DELETE to WAL and the other way around) while the database
> > is in read-only mode. Here's what I do basically:
> >
> > 1. Open new database in read-write mode.
> > 2. Set "source" journal mode (WAL or DELETE)
> > 3. Close connection
> > 4. Open connection to this database in read-only mode
> > 5. Try to change to the "destination" journal mode (again WAL or DELETE).
> > Except this to return SQLITE_READONLY.
> >
> > Now, on Windows "sqlite3_step()" returns "SQLITE_READONLY" in both
> > directions but on Linux (or on Android, to be more precise) I get
> > "SQLITE_READONLY" when changing from DELETE to WAL but I get
> "SQLITE_IOERR"
> > ("disk I/O error") when changing from WAL to DELETE.
> >
> > I'm using SQLite 3.7.14.
> >
> > Is this a bug?
> >
>
> The DELETE/WAL distinction is recorded in the database file itself.  So if
> the database is read-only, you cannot alter the file in order to change it
> between DELETE and WAL.
>
>
> >
> > Best regards
> > Sebastian
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Switching journal mode from WAL to DELETE results in SQLITE_IOERR rather than SQLITE_READONLY (bug?)

[Richard Hipp]

On Fri, Sep 28, 2012 at 9:09 AM, Sebastian Krysmanski
wrote:

> Hi,
>
> I'm working on a C# SQLite wrapper and have created some unit tests. One of
> them checks the behavior when changing the permanent journal mode of a
> database (from DELETE to WAL and the other way around) while the database
> is in read-only mode. Here's what I do basically:
>
> 1. Open new database in read-write mode.
> 2. Set "source" journal mode (WAL or DELETE)
> 3. Close connection
> 4. Open connection to this database in read-only mode
> 5. Try to change to the "destination" journal mode (again WAL or DELETE).
> Except this to return SQLITE_READONLY.
>
> Now, on Windows "sqlite3_step()" returns "SQLITE_READONLY" in both
> directions but on Linux (or on Android, to be more precise) I get
> "SQLITE_READONLY" when changing from DELETE to WAL but I get "SQLITE_IOERR"
> ("disk I/O error") when changing from WAL to DELETE.
>
> I'm using SQLite 3.7.14.
>
> Is this a bug?
>

The DELETE/WAL distinction is recorded in the database file itself.  So if
the database is read-only, you cannot alter the file in order to change it
between DELETE and WAL.


>
> Best regards
> Sebastian
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Issue with SQLite3 for WinRT ARM

[Richard Hipp]

On Fri, Sep 28, 2012 at 9:06 AM, Clemens Ladisch  wrote:

> Christian Le Gall wrote:
> > I have included an example project
>
> ... and the mailing list server has stripped it.
> Please put it somewhere on the web.
>

The SQLite developers got the project files last night, through a
back-channel.  We are working the problem now.

Our best theory so far is that this is a compiler bug.  But we haven't
proven that yet.  As I said, we are still working the problem...


>
> > The error I am getting is generally "critical error detected c000374"
> which
> > my research tells me is a corrupted heap.
>
> This typically indicates a bug in your program, such as threading
> issues, objects used after being freed, or just general memory
> corruption.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] Switching journal mode from WAL to DELETE results in SQLITE_IOERR rather than SQLITE_READONLY (bug?)

[Sebastian Krysmanski]

Hi,

I'm working on a C# SQLite wrapper and have created some unit tests. One of
them checks the behavior when changing the permanent journal mode of a
database (from DELETE to WAL and the other way around) while the database
is in read-only mode. Here's what I do basically:

1. Open new database in read-write mode.
2. Set "source" journal mode (WAL or DELETE)
3. Close connection
4. Open connection to this database in read-only mode
5. Try to change to the "destination" journal mode (again WAL or DELETE).
Except this to return SQLITE_READONLY.

Now, on Windows "sqlite3_step()" returns "SQLITE_READONLY" in both
directions but on Linux (or on Android, to be more precise) I get
"SQLITE_READONLY" when changing from DELETE to WAL but I get "SQLITE_IOERR"
("disk I/O error") when changing from WAL to DELETE.

I'm using SQLite 3.7.14.

Is this a bug?

Best regards
Sebastian
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Issue with SQLite3 for WinRT ARM

[Clemens Ladisch]

Christian Le Gall wrote:
> I have included an example project

... and the mailing list server has stripped it.
Please put it somewhere on the web.

> The error I am getting is generally "critical error detected c000374" which
> my research tells me is a corrupted heap.

This typically indicates a bug in your program, such as threading
issues, objects used after being freed, or just general memory
corruption.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Issue with SQLite3 for WinRT ARM

[Christian Le Gall]

Hey,

We need to use SQLite3 for a game we are building on the WinRT platform,
the same codebase has worked for a long time but doesn't work when
compiling for the WinRT ARM platform. I have tried using the SQLite for
Windows Runtime Visual Studio extension (visx) as well as the latest source
amalgamation directly but experience the same issue with both.

I have included an example project that demonstrates the issue when
compiling for ARM (it works targeting x86). If you compile as is you should
experience the error I've been getting. In the Direct3DApp2.cpp there are
some strings with SQL statements in the SetWindow method. There are some
lines commented, if you uncomment them and comment the block below it will
work. A simple change in order of execution and it works. Surely there is
more going on behind the scenes here than I am aware of.

The error I am getting is generally "critical error detected c000374" which
my research tells me is a corrupted heap.

Has anyone else experienced issues on WinRT ARM platform? Is there anything
I can do or a newer version I can get?

Any help appreciated.

Note: The project is for Visual Studio 2012 and to replicate the issue you
need to be building to an ARM device.

Regards,

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

Re: [sqlite] ATTACH DATABASE question

[Wolfgang Enzinger]

Am Thu, 27 Sep 2012 20:31:36 -0700 schrieb
john_prov...@yahoo.com:

> I’m confused about how to attach a database file to an existing 
> database. I open my main database ok, then to attach the second
> database, I try to execute the following SQL:
>
> ATTACH DATABASE ‘c:\mydata\my_attach_database.db’ AS ‘attached_db’

Use simple single quotes around the filename, and don't quote the alias
name at all:

ATTACH DATABASE 'c:\mydata\my_attach_database.db' AS attached_db;

HTH,
Wolfgang

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

Re: [sqlite] interesting deadlock.

[Dan Kennedy]

On 09/28/2012 03:32 AM, Jonathan Engle wrote:

I've been picking away at this for the last few days and have it
narrowed down fairly well.

It looks like if I turn off shared cache, it works fine (same
application code).

If I run with SQL_DEBUG enabled, the first issue I run into in an
assertion in sqlite3BtreeEnter: assert(
sqlite3_mutex_held(p->db->mutex) ); The call stack from it is

sqlite3BackupUpdate backupOnePage sqlite3BtreeGetReserve(p->pSrc)
sqlite3BtreeEnter

Look up the stack, it looks like sqlite3BackupUpdate locks the mutex
on the destination database but not the source.


Say you have an active backup operation (one created by backup_init()
but not yet completed) using source database handle db1. In
non-shared-cache mode. The backup is half-way done - 50% of the source
database pages have been copied to the destination.

If the source db is written by another process at this point, or using
a database handle other than db1, the backup operation has to start over
from the beginning on the next call to sqlite3_backup_step().

However, if the app writes to the source database using handle
db1, SQLite will automatically update the backup database as well. So
that the backup operation doesn't have to restart. That's the call to
sqlite3BackupUpdate() above. As you say, the code assumes that the
mutex on the source database handle (i.e. db1) is already held.

Turns out that this assumption is only true in non-shared-cache mode.
Because of the way the code is structured, in shared-cache mode, this
call to sqlite3BackupUpdate() will be made even if the source database
is updated using a second database handle - db2. But the backup code
still calls routines that assume the db1 mutex is held... Bug.

In the deadlock scenario, all the threads are blocked in
lockBtreeMutex(). This routine is supposed to prevent deadlock
by ensuring that mutexes are only obtained in a globally defined
order. But that could malfunction in unpredictable ways if two threads
were running the lockBtreeMutex() code on behalf of the same database
connection simultaneously. The mutex on the database handle is
supposed to prevent that from happening, but since the bug above
allows lockBtreeMutex() to be called without actually holding
the mutex it easily might.

I think the fix will likely be to have shared-cache mode work like
non-shared-cache mode - force the backup to start over if the source
database is written via a second database handle (i.e. db2).

Dan.






Tried as a test adding locking the source db, bad results. Altered
the definition of asserts to make them not fatal, got a ton of
assertions then deadlocking again.

Haven't tried to make a sample program yet, but the gist of it would
be to have one (or more threads) doing lots of small transactions
updating the database while simultaneously having another thread
continuously making a backup of the db (unrealistic scenario, just
makes the race easier to see).

It may or may not matter whether or not encryption is used, or more
importantly whether SQLITE_HAS_CODEC is defined, since the portion of
code that's asserting is only there when SQLITE_HAS_CODEC is
defined.

At this point, I guess I'll just run without enabling shared cache,
which seems to work just fine (a little better with regards to
backups actually) and just hope this gets fixed in a future release.

Jon



It looks like it's unhappy that the mutex for the source database in
the

On Aug 25, 2012, at 1:33 PM, Jonathan Engle wrote:


No, the deadlock is deeper than that, it's stuck trying to lock
mutexes.  My current theory is that the thread trying to update the
page in the backup destination database is what's causing trouble.

I also forgot to mention, each thread is using a different
connection object and that it's using shared cache mode.

Jon On Aug 25, 2012, at 12:57 PM, Patrik Nilsson wrote:


Do you test for the backup errors, i.e. SQLITE_BUSY and
SQLITE_LOCKED?

Do you test for step errors, i.e.  SQLITE_BUSY?

If you get the busy error, you can wait a while and try again or
start over.

/Patrik

On 08/24/2012 05:46 PM, Jonathan Engle wrote:

Ran into this recently, it's happened on one machine running a
beta test of our software.  This is a multi-threaded
application, and I've run into a sequence of steps that
deadlocks hard that as far as I can tell from the documentation
shouldn't. This is using SQLite 3.7.13 with SEE. The source
database is using WAL mode, all transactions are done as
IMMEDIATE, synchronous mode is set to 0, and it is encrypted.
The destination database for the backup is not encrypted, and
is default (non-WAL, full synchronous) modes.


There are multiple threads active:

- one performing a write - two performing reads - one closing a
connection - one is in the middle of a backup operation

Here are the call stacks for the threads:


Writing thread:

sqlite3_step sqlite3VdbeExec sqlite3VdbeHalt
sqlite3BtreeCommitPhaseOne sqlite3PagerCommitPhaseOne
pagerWalFrames