While upgrading a set of diagnosis applications on Linux to go-sqlite3 v1.14.15 that are in turn using sqlite 3.39.3, several unit tests of my applications started to fail. After raising and discussing the issue first with the go-sqlite3 project (https://github.com/mattn/go-sqlite3/issues/1096) it looks as if a recent change in sqlite 3.39.3 causes my unit tests and in turn my applications to fail.

The release notes for 3.39.3 mention for item no 5: "The unix os interface resolves all symbolic links in database filenames to create a canonical name for the database before the file is opened. If the SQLITE_OPEN_NOFOLLOW flag is used with sqlite3_open_v2() or similar, the open will fail if any element of the path is a symbolic link."

In my case, this new API behavior will always fail, as my diagnosis applications need to access sqlite3 database files via /proc/123456/root/... While the root elements in procfs appear to be symbolic links they don't behave as such and cannot be correctly readlink'ed (or similar).

The release notes appear to indicate that there is no way to disable this breaking change. Or is there some option to disable this problematic new and backwards-incompatible behavioral API change?

SQLite needs to know the canonical filename of the database file. Without that information, it cannot know the name of the rollback journal or WAL file, and without knowing the names of those auxiliary files, the database might go corrupt if a transaction is interrupted (for example by SIGKILL) and then afterwards the database is opened by another process.

Being new to sqlite3 I don't understand why this wasn't necessary until recently? I'm reading the db only and before the Sqlite3 API never threw a tantrum.

Being new to sqlite3 I don't understand why this wasn't necessary until recently? I'm reading the db only and before the Sqlite3 API never threw a tantrum.

To paraphrase Warren out of context: it worked fine the old way... until it didn't (which was recently).

While I can see that following and resolving symbolic links especially makes sense for the final element, the filename of the database, I still fail to understand how resolving the other path elements is essential for correct operation.

In my system configuration the variable part is in the path to the database, but not the database name itself. Wouldn't this allow sqlite to correctly find the other files mentioned before and deal with them properly?

I'm afraid that the quotation joke is lost to me, more so as you dont give context. Would you care to explain so I have a chance to understand, more so as I explicitly mentionedthat I'm new here so that people understand I don't yet share the same history of jokes?

I'm afraid that the quotation joke is lost to me, more so as you dont give context.

It's actually not a joke. As you say, "it's always worked fine." Until, that is, someone recently traced corruption back to the old behavior. The fix for that obscure case of corruption was to do what arguably should have always been the behavior: fully resolve the paths.

The fact that /proc pseudo-symlinks do not resolve as such is a bug in the /proc filesystem driver. sqlite relies on consistent, standards-compliant behavior from the filesystem and /proc obviously fauls to provide that in this case.

someone recently traced corruption back to the old behavior

Could some details be shared about this please?

Could some details be shared about this please?

If i was at liberty to, i would have already ;). "Citation needed" is one of my mottos, but i don't have a publicly-citable source for that one :/.

Just say it more precise to me: ((expletive removed by admin - please keep this forum family-friendly))

Prove the Linux kernel devs they're wrong.

The fact that /proc pseudo-symlinks do not resolve as such is a bug in the /proc filesystem driver. sqlite relies on consistent, standards-compliant behavior from the filesystem and /proc obviously fauls to provide that in this case.

This is an overly simplistic position. For processes running in a different mount namespace, there is no guaranteed way to provide a canonical path those files.

It's not that /proc is misbehaving, it's that /proc/12345/root/.../ can provide access to files which are literally not mapped anywhere else in the current process namespace.

(just about any singularity or docker container will do for demonstration purposes here)

As Dorothy put it, I don't think we're in POSIX anymore ;)

Given that the OP says he only reads the DB, i.e. there should be no need for a JOURNAL or WAL file? Could the symlink resolution be turned OFF in that case?

The OP might need to explicitly tell SQLite his connection is read-only, to basically opt-out of symlink resolution. WDYT?

Thanks

Understood. But this is also guaranteed on ensuring to check the final database name (basename, I think) to be a link and then resolve that. There's no way that other path elements influence the ability to correctly or incorrectly deduce the basename for the journal and WAL files ... or is there?

IMHO the current implementation is overachieving it, trashing perfectly valid use cases. So far, no-one has been denied that my use case should not work.

Consider the following scenario, which takes place in the context of three separate "terminals" T1, T2, T3:

T1$ cd /tmp
T1$ mkdir alpha omega
T1$ ln -s alpha active
T2$ sqlite3 /tmp/active/db
T2 sqlite> begin;
T2 sqlite> create table alpha(a);
T3$ sqlite3 /tmp/active/db
T3 sqlite> begin;
T3 sqlite> select * from sqlite_master;
T1$ ln -snf omega active

At this point the sqlite connections in T2 and T3 both have the same "logical" path /tmp/active/db, and they both have an open file descriptor on the same physical file (with canonical path /tmp/alpha/db). However if the logical path is used to locate the journal file we're in a pickle, as T2 has an active journal at /tmp/alpha/db-journal which will not be noticed by connections looking for /tmp/active/db-journal.

Testing this with sqlite 3.7.17 demonstrates that T2 becomes quite confused:

T3 sqlite> end transaction;
T2 sqlite> commit;
T2 Error: disk I/O error

Probably I could have omitted T3 from this example to make it simpler...

I feel like your reasoning is probably correct in the case of stable symlinks. An alternative solution which doesn't require resolving all symlinks up front may be to keep a file descriptor open on the containing directory, and use openat() to open the database file and journals

Even without symlinks you can face issues renaming the intermediate directories.

T1

$ mkdir foo
$ sqlite3 foo/foo.sqlite
sqlite> select * from sqlite_master;

T2

$ mv foo bar

T1

sqlite> create table foo(fooid);
Error: disk I/O error

As you mentioned, the solution is to use openat (at least on Unix), which resolves both problems.