sqlite3 @3.10.2_0 Cannot open database via symlink

[hanche]

The summary says it all. To reproduce:

; mkdir x y
; sqlite3 x/foo.sqlite 'create table foo(x);'
; sqlite3 x/foo.sqlite 'select count(*) from foo;'
0
; ln -s ../x/foo.sqlite y
; cmp x/foo.sqlite y/foo.sqlite
; sqlite3 y/foo.sqlite 'select count(*) from foo;'
Error: unable to open database "y/foo.sqlite": unable to open database file

However, this works:

; cd y
; sqlite3 foo.sqlite 'select count(*) from foo;'
0

It appears from ​http://sqlite.org/changes.html that some changes have recently been introduced related to symlinked database files. Presumably, that must have been tested upstream, so it's more likely an OS X problem, I think. (I am on OS X 10.11.2.)

[neverpanic (Clemens Lang)]

Adding mschamschula, who may be interested in this.

[neverpanic (Clemens Lang)]

Yes, this seems to be an upstream bug. Relative symlinks seem to be expanded relative to the current working directory, not to the path of the database given on the command line. See:

$ mkdir x y
$ sqlite3 x/foo.sqlite 'create table foo(x);'
$ ln -s ../x/foo.sqlite y
$ sudo dtruss -f sqlite3 y/foo.sqlite 'select count(*) from foo;'
[...]
15929/0xc741:  stat64("/private/var/folders/6t/nzy5y3kn1f5fg1xhxvgg4gr00000gn/T\0", 0x7FFF579EEC00, 0x7FFF579EE800)		 = 0 0
15929/0xc741:  stat64("/private/var/folders/6t/nzy5y3kn1f5fg1xhxvgg4gr00000gn/T/../x/foo.sqlite\0", 0x7FFF579EE060, 0x7FFF579EE800)		 = -1 Err#2
15929/0xc741:  open("/private/var/folders/6t/nzy5y3kn1f5fg1xhxvgg4gr00000gn/T/../x/foo.sqlite\0", 0x1000202, 0x1A4)		 = -1 Err#2
15929/0xc741:  open("/private/var/folders/6t/nzy5y3kn1f5fg1xhxvgg4gr00000gn/T/../x/foo.sqlite\0", 0x1000000, 0x1A4)		 = -1 Err#2
15929/0xc741:  write_nocancel(0x2, "Error: unable to open database \"y/foo.sqlite\": unable to open database file\n\0", 0x4C)		 = 76 0

[hanche]

Okay, thanks for figuring that out. (I should have managed that myself.) Do you want me to report it upstream, or do you want to do it?

[neverpanic (Clemens Lang)]

It would be helpful if you could do it. Our current official maintainer of sqlite3 isn't very active and mschamschula who has been de-facto maintaining it in the last few months hasn't replied yet (though he'd probably do it). Please keep us posted.

[hanche]

I'll get on their mailing list and report the bug that way.

I looked at the upstream repository, and see that the bug is introduced with this checkin:

uuid:         c7c8105099c0412ac6c605f98987092c10bde57c 2015-10-31 17:58:33 UTC
parent:       9f19420b0a79dff65fc3a9d548f4b3fc4955f9f9 2015-10-30 20:54:25 UTC
merged-into:  6d5ce3ede4c7038c19a77268a5a7b9d5650933c2 2015-11-02 15:08:56 UTC
tags:         follow-symlinks
comment:      On unix, if a file is opened via a symlink, create, read and
              write journal and wal files based on the name of the actual db
              file, not the symlink. (user: dan)

The trouble is in the function unixFullPathname in src/os_unix.c. It seems clear that it did not occur to the write of that patch that someone might refer to a symlink outside of the current directory. Ho hum.

(I only mention this here for the record. I'll repeat it in my upstream bug report, of course. And I'll report back here.)

[hanche]

This problem is now fixed in trunk upstream. I am not sure what their release schedule is like, but I expect it should show up in the next release. I'll make a noise here if I notice when it happens.

[Schamschula (Marius Schamschula)]

Thanks for reporting this problem upstream.

I usually see if sqlite3 is updated (via livecheck) the following morning and post a ticket soon thereafter.

[ryandesign (Ryan Carsten Schmidt)]

sqlite3 was updated to 3.11.0; see #50641. Did that fix the problem?

[hanche]

Yes, that fixed it. Not surprising to me, as I also tested the fix upstream earlier. But I did check the macports version all the same, and it works like it should now.