SQLite 3.2.5 and Mac OSX network folders

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

SQLite 3.2.5 and Mac OSX network folders

Steve Palmer
I'm currently investigating a problem with my application, SQLite  
3.2.5 and a database located on a Mac OSX Server network share that  
does not seem to repro with the SQLite 3.1.3 that ships with Mac OSX  
4.1. Specifically if I place a SQLite database file on a folder on a  
remote network share and attempt to access it using the sqlite3  
utility, I get different results.

With the Mac OSX 4.1 version of sqlite3 (in /usr/bin):

gawain:/usr/bin steve$ ./sqlite3 /Volumes/WORKSPACE\;MACHINE/messages.db
SQLite version 3.1.3
Enter ".help" for instructions
sqlite> select * from info;
12|2005-10-24 10:03:34 -0700
sqlite>

With the version of sqlite3 built from the 3.2.5 source code:

gawain:~/Source/sqlite-3.2.5/bld steve$ ./sqlite3 /Volumes/WORKSPACE
\;MACHINE/messages.db
SQLite version 3.2.5
Enter ".help" for instructions
sqlite> select * from info;
SQL error: database is locked
sqlite>

I note the comment at http://www.sqlite.org/lockingv3.html that Mac  
OSX locking mechanics are not to be relied on and presumably this  
means that SQLite cannot be used to access a database located on a  
network on Mac OSX but Apple appear to have resolved this with the  
version they shipped with Mac OSX 4.1. However this appears to be an  
earlier version (3.1.3) so why does the later version not work? Is  
there a flag that I need to build the source with to enable this?

Thanks!
- Steve

Reply | Threaded
Open this post in threaded view
|

Re: SQLite 3.2.5 and Mac OSX network folders

D. Richard Hipp
Steve Palmer <[hidden email]> wrote:
> I'm currently investigating a problem with my application, SQLite  
> 3.2.5 and a database located on a Mac OSX Server network share that  
> does not seem to repro with the SQLite 3.1.3 that ships with Mac OSX  
> 4.1. Specifically if I place a SQLite database file on a folder on a  
> remote network share and attempt to access it using the sqlite3  
> utility, I get different results.
>

Apple added special hacks to their release of SQLite 3.1.3 that
allow it to work on remote filesystems with broken file locking.
For various reasons, those hacks have not been incorporated into
the SQLite core, yet.

--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: SQLite 3.2.5 and Mac OSX network folders

Steve Palmer
What are those reasons and is there any expectation that they can be
made available as patches for folks who build SQLite privately? I
cannot use the libsqlite3.dylib that comes with Mac OSX 10.4 since my
application needs to run on 10.3.9 too and there is no equivalent
static version that I can find.

- Steve


On Oct 25, 2005, at 4:57am, [hidden email] wrote:

> Steve Palmer <[hidden email]> wrote:
>
>> I'm currently investigating a problem with my application, SQLite
>> 3.2.5 and a database located on a Mac OSX Server network share that
>> does not seem to repro with the SQLite 3.1.3 that ships with Mac OSX
>> 4.1. Specifically if I place a SQLite database file on a folder on a
>> remote network share and attempt to access it using the sqlite3
>> utility, I get different results.
>>
>>
>
> Apple added special hacks to their release of SQLite 3.1.3 that
> allow it to work on remote filesystems with broken file locking.
> For various reasons, those hacks have not been incorporated into
> the SQLite core, yet.
>
> --
> D. Richard Hipp <[hidden email]>
>
>

Reply | Threaded
Open this post in threaded view
|

Re: SQLite 3.2.5 and Mac OSX network folders

Aaron Burghardt
In reply to this post by Steve Palmer
You can take a look at what Apple has done for OS X here:

http://www.opensource.apple.com/darwinsource/10.4/SQLite-28/

Don't be mislead by the project name--it is SQLite 3.  I haven't  
studied it closely, but it should allow you to build exactly what  
Apple shipped.  The Makefile has references to locking callbacks, so  
this might be relevant:

# add -DSQLITE_DEBUG=1 to enable lock tracing and other debugging  
features
# you also need to set sqlite3_os_trace to 1 in SQLite3/src/os_common.h
Extra_CC_Flags += -DASSERT_VIA_CALLBACK=1 -DENABLE_LOCKING_CALLBACKS=1

HTH,

Aaron

On Oct 25, 2005, at 12:45 PM, Steve Palmer wrote:

> What are those reasons and is there any expectation that they can be
> made available as patches for folks who build SQLite privately? I
> cannot use the libsqlite3.dylib that comes with Mac OSX 10.4 since my
> application needs to run on 10.3.9 too and there is no equivalent
> static version that I can find.
>
> - Steve
>
>
> On Oct 25, 2005, at 4:57am, [hidden email] wrote:
>
>> Steve Palmer <[hidden email]> wrote:
>>
>>> I'm currently investigating a problem with my application, SQLite
>>> 3.2.5 and a database located on a Mac OSX Server network share that
>>> does not seem to repro with the SQLite 3.1.3 that ships with Mac OSX
>>> 4.1. Specifically if I place a SQLite database file on a folder on a
>>> remote network share and attempt to access it using the sqlite3
>>> utility, I get different results.
>>>
>>>
>>
>> Apple added special hacks to their release of SQLite 3.1.3 that
>> allow it to work on remote filesystems with broken file locking.
>> For various reasons, those hacks have not been incorporated into
>> the SQLite core, yet.
>>
>> --
>> D. Richard Hipp <[hidden email]>
>>
>>
>


Reply | Threaded
Open this post in threaded view
|

Re: SQLite 3.2.5 and Mac OSX network folders

vidushi tandon
hi all
       can you please tell me is their any sqlite
source code available for the java

rgds
vidushi tandon

--- Aaron Burghardt <[hidden email]> wrote:

> You can take a look at what Apple has done for OS X
> here:
>
>
http://www.opensource.apple.com/darwinsource/10.4/SQLite-28/

>
> Don't be mislead by the project name--it is SQLite
> 3.  I haven't  
> studied it closely, but it should allow you to build
> exactly what  
> Apple shipped.  The Makefile has references to
> locking callbacks, so  
> this might be relevant:
>
> # add -DSQLITE_DEBUG=1 to enable lock tracing and
> other debugging  
> features
> # you also need to set sqlite3_os_trace to 1 in
> SQLite3/src/os_common.h
> Extra_CC_Flags += -DASSERT_VIA_CALLBACK=1
> -DENABLE_LOCKING_CALLBACKS=1
>
> HTH,
>
> Aaron
>
> On Oct 25, 2005, at 12:45 PM, Steve Palmer wrote:
>
> > What are those reasons and is there any
> expectation that they can be
> > made available as patches for folks who build
> SQLite privately? I
> > cannot use the libsqlite3.dylib that comes with
> Mac OSX 10.4 since my
> > application needs to run on 10.3.9 too and there
> is no equivalent
> > static version that I can find.
> >
> > - Steve
> >
> >
> > On Oct 25, 2005, at 4:57am, [hidden email] wrote:
> >
> >> Steve Palmer <[hidden email]> wrote:
> >>
> >>> I'm currently investigating a problem with my
> application, SQLite
> >>> 3.2.5 and a database located on a Mac OSX Server
> network share that
> >>> does not seem to repro with the SQLite 3.1.3
> that ships with Mac OSX
> >>> 4.1. Specifically if I place a SQLite database
> file on a folder on a
> >>> remote network share and attempt to access it
> using the sqlite3
> >>> utility, I get different results.
> >>>
> >>>
> >>
> >> Apple added special hacks to their release of
> SQLite 3.1.3 that
> >> allow it to work on remote filesystems with
> broken file locking.
> >> For various reasons, those hacks have not been
> incorporated into
> >> the SQLite core, yet.
> >>
> >> --
> >> D. Richard Hipp <[hidden email]>
> >>
> >>
> >
>
>
>



               
__________________________________
Start your day with Yahoo! - Make it your home page!
http://www.yahoo.com/r/hs
Reply | Threaded
Open this post in threaded view
|

Re: SQLite 3.2.5 and Mac OSX network folders

Steve Palmer
In reply to this post by Aaron Burghardt
Has anybody tried building that though? If you download the files  
from http://www.sqlite.org/cvstrac/tktview?tn=1240 and run make from  
the root of the expanded folder then it will go through the process  
of configuring and building, but I can't find the output files  
anywhere. A search under the folder shows nothing seems to have been  
created.

Interestingly, if you build from the SQLite3 folder, it will generate  
the object files and place the libraries in a hidden .libs folder but  
those all omit the new locking mechanism and hence the final results  
don't work for me. Unfortunately I can't quite figure out what is  
different.

Any ideas?

- Steve


On Oct 27, 2005, at 6:09am, Aaron Burghardt wrote:

> You can take a look at what Apple has done for OS X here:
>
> http://www.opensource.apple.com/darwinsource/10.4/SQLite-28/
>
> Don't be mislead by the project name--it is SQLite 3.  I haven't  
> studied it closely, but it should allow you to build exactly what  
> Apple shipped.  The Makefile has references to locking callbacks,  
> so this might be relevant:
>
> # add -DSQLITE_DEBUG=1 to enable lock tracing and other debugging  
> features
> # you also need to set sqlite3_os_trace to 1 in SQLite3/src/
> os_common.h
> Extra_CC_Flags += -DASSERT_VIA_CALLBACK=1 -DENABLE_LOCKING_CALLBACKS=1
>
> HTH,
>
> Aaron
>
> On Oct 25, 2005, at 12:45 PM, Steve Palmer wrote:
>
>
>> What are those reasons and is there any expectation that they can be
>> made available as patches for folks who build SQLite privately? I
>> cannot use the libsqlite3.dylib that comes with Mac OSX 10.4 since my
>> application needs to run on 10.3.9 too and there is no equivalent
>> static version that I can find.
>>
>> - Steve
>>
>>
>> On Oct 25, 2005, at 4:57am, [hidden email] wrote:
>>
>>
>>> Steve Palmer <[hidden email]> wrote:
>>>
>>>
>>>> I'm currently investigating a problem with my application, SQLite
>>>> 3.2.5 and a database located on a Mac OSX Server network share that
>>>> does not seem to repro with the SQLite 3.1.3 that ships with Mac  
>>>> OSX
>>>> 4.1. Specifically if I place a SQLite database file on a folder  
>>>> on a
>>>> remote network share and attempt to access it using the sqlite3
>>>> utility, I get different results.
>>>>
>>>>
>>>>
>>>
>>> Apple added special hacks to their release of SQLite 3.1.3 that
>>> allow it to work on remote filesystems with broken file locking.
>>> For various reasons, those hacks have not been incorporated into
>>> the SQLite core, yet.
>>>
>>> --
>>> D. Richard Hipp <[hidden email]>
>>>
>>>
>>>
>>
>>
>
>
>

Reply | Threaded
Open this post in threaded view
|

Re: SQLite 3.2.5 and Mac OSX network folders

Aaron Burghardt
The Makefile has this line:

        include $(MAKEFILEPATH)/CoreOS/ReleaseControl/GNUSource.make

so you will need the Darwin project CoreOSMakefiles-30. Working  
backwards, GNUSource.make includes Common.make, which defines OBJROOT  
to be /tmp/$(ProjectName)/Build. With this, I found the built  
products in:

        /tmp/SQLite3/Build/.libs/

The makefiles appear to be from a Project Builder makefile-based  
project (does Xcode still support these?). Any, I was able to build  
it outside of Xcode and it may work to create a project if you want.  
(I didn't actually install the built product, so there may be issues  
with that step.)

Here's another suggestion for you. Obviously you are going to be  
interested in merging Apple's changes in with the latest SQLite  
source. Subversion (and probably other SCMs) has some ability to  
track a branch relative to a vendor distribution (i.e., the standard  
SQLite distribution), which would help combine Apple's changes and  
the changes from 3.1.3 to 3.2.5. I've never used that feature of  
Subversion, so I can't offer much more at the moment.

Keep us posted, I'm curious to know how this goes for you.

Aaron

On Oct 28, 2005, at 11:36 AM, Steve Palmer wrote:

> Has anybody tried building that though? If you download the files  
> from http://www.sqlite.org/cvstrac/tktview?tn=1240 and run make  
> from the root of the expanded folder then it will go through the  
> process of configuring and building, but I can't find the output  
> files anywhere. A search under the folder shows nothing seems to  
> have been created.
>
> Interestingly, if you build from the SQLite3 folder, it will  
> generate the object files and place the libraries in a hidden .libs  
> folder but those all omit the new locking mechanism and hence the  
> final results don't work for me. Unfortunately I can't quite figure  
> out what is different.
>
> Any ideas?
>
> - Steve
>
>
> On Oct 27, 2005, at 6:09am, Aaron Burghardt wrote:
>
>> You can take a look at what Apple has done for OS X here:
>>
>> http://www.opensource.apple.com/darwinsource/10.4/SQLite-28/
>>
>> Don't be mislead by the project name--it is SQLite 3.  I haven't  
>> studied it closely, but it should allow you to build exactly what  
>> Apple shipped.  The Makefile has references to locking callbacks,  
>> so this might be relevant:
>>
>> # add -DSQLITE_DEBUG=1 to enable lock tracing and other debugging  
>> features
>> # you also need to set sqlite3_os_trace to 1 in SQLite3/src/
>> os_common.h
>> Extra_CC_Flags += -DASSERT_VIA_CALLBACK=1 -
>> DENABLE_LOCKING_CALLBACKS=1
>>
>> HTH,
>>
>> Aaron
>>
>> On Oct 25, 2005, at 12:45 PM, Steve Palmer wrote:
>>
>>
>>> What are those reasons and is there any expectation that they can be
>>> made available as patches for folks who build SQLite privately? I
>>> cannot use the libsqlite3.dylib that comes with Mac OSX 10.4  
>>> since my
>>> application needs to run on 10.3.9 too and there is no equivalent
>>> static version that I can find.
>>>
>>> - Steve
>>>
>>>
>>> On Oct 25, 2005, at 4:57am, [hidden email] wrote:
>>>
>>>
>>>> Steve Palmer <[hidden email]> wrote:
>>>>
>>>>
>>>>> I'm currently investigating a problem with my application, SQLite
>>>>> 3.2.5 and a database located on a Mac OSX Server network share  
>>>>> that
>>>>> does not seem to repro with the SQLite 3.1.3 that ships with  
>>>>> Mac OSX
>>>>> 4.1. Specifically if I place a SQLite database file on a folder  
>>>>> on a
>>>>> remote network share and attempt to access it using the sqlite3
>>>>> utility, I get different results.
>>>>>
>>>>>
>>>>>
>>>>
>>>> Apple added special hacks to their release of SQLite 3.1.3 that
>>>> allow it to work on remote filesystems with broken file locking.
>>>> For various reasons, those hacks have not been incorporated into
>>>> the SQLite core, yet.
>>>>
>>>> --
>>>> D. Richard Hipp <[hidden email]>
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>>
>

Reply | Threaded
Open this post in threaded view
|

Re: SQLite 3.2.5 and Mac OSX network folders

Steve Palmer
Thanks! That helps tremendously.

As regards merging the changes, I've been in touch with drh over this  
and I understand he is already looking into this patch. So in the  
interest of not duplicating effort, I'm not going to make any attempt  
to submit a merge of these changes back into the most recent core  
code. Instead I plan to review and test the 3.1.3 based changes in my  
own project and if they past muster, I'll use that version. Otherwise  
I'll wait for an official core release that incorporates these or  
similar changes.

Right now based on my limited testing I have some concerns over the  
performance of the locking. The implementation may well not be as  
optimal as it can be or possibly it is optimal but my code isn't  
optimised for network access. I don't know yet.

- Steve


On Oct 28, 2005, at 10:13pm, Aaron Burghardt wrote:

> The Makefile has this line:
>
>     include $(MAKEFILEPATH)/CoreOS/ReleaseControl/GNUSource.make
>
> so you will need the Darwin project CoreOSMakefiles-30. Working  
> backwards, GNUSource.make includes Common.make, which defines  
> OBJROOT to be /tmp/$(ProjectName)/Build. With this, I found the  
> built products in:
>
>     /tmp/SQLite3/Build/.libs/
>
> The makefiles appear to be from a Project Builder makefile-based  
> project (does Xcode still support these?). Any, I was able to build  
> it outside of Xcode and it may work to create a project if you  
> want. (I didn't actually install the built product, so there may be  
> issues with that step.)
>
> Here's another suggestion for you. Obviously you are going to be  
> interested in merging Apple's changes in with the latest SQLite  
> source. Subversion (and probably other SCMs) has some ability to  
> track a branch relative to a vendor distribution (i.e., the  
> standard SQLite distribution), which would help combine Apple's  
> changes and the changes from 3.1.3 to 3.2.5. I've never used that  
> feature of Subversion, so I can't offer much more at the moment.
>
> Keep us posted, I'm curious to know how this goes for you.
>
> Aaron
>
> On Oct 28, 2005, at 11:36 AM, Steve Palmer wrote:
>
>
>> Has anybody tried building that though? If you download the files  
>> from http://www.sqlite.org/cvstrac/tktview?tn=1240 and run make  
>> from the root of the expanded folder then it will go through the  
>> process of configuring and building, but I can't find the output  
>> files anywhere. A search under the folder shows nothing seems to  
>> have been created.
>>
>> Interestingly, if you build from the SQLite3 folder, it will  
>> generate the object files and place the libraries in a  
>> hidden .libs folder but those all omit the new locking mechanism  
>> and hence the final results don't work for me. Unfortunately I  
>> can't quite figure out what is different.
>>
>> Any ideas?
>>
>> - Steve
>>
>>
>> On Oct 27, 2005, at 6:09am, Aaron Burghardt wrote:
>>
>>
>>> You can take a look at what Apple has done for OS X here:
>>>
>>> http://www.opensource.apple.com/darwinsource/10.4/SQLite-28/
>>>
>>> Don't be mislead by the project name--it is SQLite 3.  I haven't  
>>> studied it closely, but it should allow you to build exactly what  
>>> Apple shipped.  The Makefile has references to locking callbacks,  
>>> so this might be relevant:
>>>
>>> # add -DSQLITE_DEBUG=1 to enable lock tracing and other debugging  
>>> features
>>> # you also need to set sqlite3_os_trace to 1 in SQLite3/src/
>>> os_common.h
>>> Extra_CC_Flags += -DASSERT_VIA_CALLBACK=1 -
>>> DENABLE_LOCKING_CALLBACKS=1
>>>
>>> HTH,
>>>
>>> Aaron
>>>
>>> On Oct 25, 2005, at 12:45 PM, Steve Palmer wrote:
>>>
>>>
>>>
>>>> What are those reasons and is there any expectation that they  
>>>> can be
>>>> made available as patches for folks who build SQLite privately? I
>>>> cannot use the libsqlite3.dylib that comes with Mac OSX 10.4  
>>>> since my
>>>> application needs to run on 10.3.9 too and there is no equivalent
>>>> static version that I can find.
>>>>
>>>> - Steve
>>>>
>>>>
>>>> On Oct 25, 2005, at 4:57am, [hidden email] wrote:
>>>>
>>>>
>>>>
>>>>> Steve Palmer <[hidden email]> wrote:
>>>>>
>>>>>
>>>>>
>>>>>> I'm currently investigating a problem with my application, SQLite
>>>>>> 3.2.5 and a database located on a Mac OSX Server network share  
>>>>>> that
>>>>>> does not seem to repro with the SQLite 3.1.3 that ships with  
>>>>>> Mac OSX
>>>>>> 4.1. Specifically if I place a SQLite database file on a  
>>>>>> folder on a
>>>>>> remote network share and attempt to access it using the sqlite3
>>>>>> utility, I get different results.
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>> Apple added special hacks to their release of SQLite 3.1.3 that
>>>>> allow it to work on remote filesystems with broken file locking.
>>>>> For various reasons, those hacks have not been incorporated into
>>>>> the SQLite core, yet.
>>>>>
>>>>> --
>>>>> D. Richard Hipp <[hidden email]>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>
>>
>
>

Reply | Threaded
Open this post in threaded view
|

Re: SQLite 3.2.5 and Mac OSX network folders

Bill Bumgarner
In reply to this post by D. Richard Hipp
> Apple added special hacks to their release of SQLite 3.1.3 that
> allow it to work on remote filesystems with broken file locking.
> For various reasons, those hacks have not been incorporated into
> the SQLite core, yet.

A bit more detail;   Apple shipped with 3.1.3 with locking support  
for various filesystems.  The implementation attempts to be defensive  
when locking on non-local filesystems.   In particular, it makes the  
assumption that NFS servers will sometimes completely lie about their  
locking capabilities.  Thus, it falls back to .lock files and treats  
every lock request as an exclusive lock.  Efficient?  Nope.   Safe?  
Yup -- well -- as safe as any DB operation on a network can be.

Version 3.1.5 and beyond of SQLite contained changes to the great  
fsync() vs. FULL_FSYNC conundrum such that integrating 3.1.5 and  
beyond into Apple's source was not entirely trivial.   That is in the  
works and we will post the changes back to DRH as soon as possible.  
Apple has no interest in forking SQLite or maintaining some weird  
derivative (the source is already available, anyway).

> You can take a look at what Apple has done for OS X here:
>
> http://www.opensource.apple.com/darwinsource/10.4/SQLite-28/
>
> Don't be mislead by the project name--it is SQLite 3.  I haven't  
> studied it closely, but it should allow you to build exactly what  
> Apple shipped.  The Makefile has references to locking callbacks,  
> so this might be relevant:

Yeah -- the project name reflects an internal serial number.  It  
confuses us, too.  Sorry.

> Right now based on my limited testing I have some concerns over the  
> performance of the locking. The implementation may well not be as  
> optimal as it can be or possibly it is optimal but my code isn't  
> optimised for network access. I don't know yet.

That is not surprising.  With NFS, the only safe assumption is that  
locking is utterly broken.  If your application will be run in an  
environment where you can guarantee that locking works on NFS -- that  
the NFS server is not broken -- then change the "what type of locking  
system to use" code to return the same locking mechanism as the local  
filesystem.

b.bum