1831950 - Cannot select rows from the DuckDuckGo bookmarks database on macOS

Status: NEW

(Firefox :: Migration, defect, P3)

Description

[Mike Conley (:mconley) (:⚙️)]

STR:

1. Install the DuckDuckGo browser on macOS using the Apple App Store
2. Run the browser and create some bookmarks
3. Notice that there will be an SQLite database in the file system at the following path:

~/Library/Containers/com.duckduckgo.mobile.ios/Data/Library/Application Support/Bookmarks.sqlite

(com.duckduckgo.mobile.ios appears to be replaced with "DuckDuckGo" in the Finder, but the actual file system path to the SQLite database is as above)
4. Use DB Browser for SQLite to verify that there is a table in that database called ZBOOKMARKENTITY, and that there are some rows in it.
5. Run Firefox, and use SQLite.sys.mjs to attempt to read the rows from that table. I used this:

const { Sqlite } = ChromeUtils.importESModule("resource://gre/modules/Sqlite.sys.mjs");
let bookmarksPath = FileUtils.getDir("ULibDir", [
  "Containers",
  "com.duckduckgo.mobile.ios",
  "Data",
  "Library",
  "Application Support",
  "Bookmarks.sqlite",
], false);

let db = await Sqlite.openConnection({
  path: bookmarksPath.path,
  readOnly: true,
  ignoreLockingMode: true,
});

let rows = await db.execute("SELECT * FROM ZBOOKMARKENTITY");
console.log(rows);

ER:

If there are rows in the SQLite database in step 4, Sqlite.sys.mjs should return them to us in step 5.

AR:

The rows array has length 0.

Any idea what's going on here, mak?

Comment 1

[Marco Bonardo [:mak]]

Thank you for the steps to reproduce, very useful.
Afaict the problem is that they don't merge the WAL journal on shutdown, so the bookmarks you have created are still in the journal -wal file and has not been merged into the main database.
Either they do that because they want instant shutdown, thus they are not closing their Sqlite connection, or they are explicitly leaving the journal on disk, but then for reliability they should set a journal size limit (we are doing that in bug 1820478). To me it looks like a bug on their side.
Those bookmarks will likely be merged into the main database once their journal reaches their "checkpoint" size, whatever it is.

The problem at hand is ignoreLockingMode. That option makes so we open the database as if it would be on a read-only device. That means any auxiliary file, included the journal, will be ignored and the .sqlite file is opened in isolation. If you try to open without that option, that is what DB Browser for Sqlite does, you'll get your rows, because it will also look at the journal.
Apart from modifying Sqlite to ignore non-existance of the journal, but use it if it exists (not sure if it's possible, we'd have to contact the Sqlite team if we want that), the other option would be to try querying without the ignoreLockingMode, and if that fails or returns zero entries, then retry with it. Otherwise, accept that most recent bookmarks may not be there.

Comment 2

[Marco Bonardo [:mak]]

Overall, I don't think we can do anything on the Storage side, this can be handled on the client side with a fallback strategy, like I explained.
So moving back to Migration.

Comment 3

[Molly Howell (she/her) (mostly inactive)]

I have to call this one S2 on the grounds that there isn't really any reliable workaround; bookmark import from DDG effectively just cannot be done with this bug in place.