This site will have limited functionality while we undergo maintenance to improve your experience. If an article doesn't solve your issue and you want to ask a question, we have our support community waiting to help you at @FirefoxSupport on Twitter and/r/firefox on Reddit.

Search Support

Avoid support scams. We will never ask you to call or text a phone number or share personal information. Please report suspicious activity using the “Report Abuse” option.

Learn More

Probleme with 'places.sqlite' -> 'moz_places' Can't retrieve the "real" $LAST_VISITED_URL ?

more options

the last url in moz_places in places.sqlite are not the same as in my history tab I posted here

Here is my `last-url.sh`

   #!/bin/bash
   
   rm  -rf /tmp/db.sqlite 
   cp ~/.mozilla/firefox/*.default-esr/places.sqlite /tmp/db.sqlite ;
   LAST_URL=`sqlite3 /tmp/db.sqlite "select url from moz_places order by last_visit_date desc limit 1;"`;
   echo $LAST_URL


My script keeps giving me wrong answer. For exemple, a stackoverflow.com url instead of the youtube.com one even if i reload the page.

I have only one `xxxxxx.default-esr` profile.

Screenshot examples: the "google test" is a brand new visited url, and the others were refreshed pages

the last url in moz_places in places.sqlite are not the same as in my history tab I posted [https://unix.stackexchange.com/questions/771771/places-sqlite-buggy-seems-to-beware-anormally-how-to-get-last-visited-url here] Here is my `last-url.sh` #!/bin/bash rm -rf /tmp/db.sqlite cp ~/.mozilla/firefox/*.default-esr/places.sqlite /tmp/db.sqlite ; LAST_URL=`sqlite3 /tmp/db.sqlite "select url from moz_places order by last_visit_date desc limit 1;"`; echo $LAST_URL My script keeps giving me wrong answer. For exemple, a stackoverflow.com url instead of the youtube.com one even if i reload the page. I have only one `xxxxxx.default-esr` profile. Screenshot examples: the "google test" is a brand new visited url, and the others were refreshed pages
Attached screenshots

Modified by dehya

All Replies (7)

more options

I tested through the site https://inloop.github.io/sqlite-viewer/ with places.sqlite and this query:

SELECT url, title, last_visit_date, datetime(last_visit_date/1000000, 'unixepoch') AS LatestVisit FROM 'moz_places' ORDER BY last_visit_date DESC

The result seemed correct when I had just started up Firefox.

One thing I noticed is if I visit a site, then load the database into the query tool and run the query again, it isn't there. Same after following a link in that page. Same after following a link in that page. It's not until I follow a third link that all four of the pages appeared in moz_places. This appears to be some kind of optimization (SQLite uses journaling files) that causes weird results during your session. But when you exit out of Firefox, presumably it cleans up and does all the updates before quitting, so the results should be accurate by then.

more options

Closing firefox don't make the change, I'am really lost on this one. Thanks for help. Now i ask myself how firefox create the history tab ? as it reflect the real history of browsing, including refreshes.

Using what file and what sql request on which file  (is it even use places.sqlite? Now i doubt)


PS: I liked that one : datetime(last_visit_date/1000000, 'unixepoch')

Modified by dehya

more options

Some history may be cached in memory, but the Library window shows a lot of entries, so presumably it is going to disk for that information. The SQLite engine is constructing that from the places.sqlite file and from its journaling files, so it has a more complete view than you do simply operating on the one file.

more options

Ok thanks a lot. There is a places.wal file Write-Ahead Logging: this is what you talk about when saying journaling files ? So can a tool like wal2json help me to achieve my goal.

Modified by dehya

more options

Thanks. When you say journaling file, you talk about places.sqlite.wal ? I saw a tool wal2json on github (here). Not trivial to install so should i give it more try ?

I did a mistake previously. Closing firefox populate places.sqlite properly. I don't know what i did before. sorry Anyway closing firefox to get that data is not an option, so i will keep searching.

Maybe there is a way to tell firefox to populate the db And i saw a wal2json on github . Maybe all the info i need are on this places.sqlite.wal

Modified by dehya

more options

Sorry , closing firefox make the request coherent ( I made a mistake). So it populate the db at that time. When you say journaling file, you talk about places.sqlite.wal ? Can a tool like wal2json give me my real time info? Maybe you see another way to programmatically get the $LAST_URL Thanks for the help.

Modified by dehya

more options

Firefox writes -wal and -shm files.

I don't know about programmatic access to SQLite databases, but I'm puzzled why sqlite3 doesn't handle it automatically.

I kind of glazed over and didn't finish https://www.sqlite.org/wal.html