SQL ";.save" command does not seem to work as advertised

[dardhal]

lnav version lnav 0.14.0-a2e2380-dirty

Describe the bug As per #1525 a new SQL feature was introduced, so a ".save" command would create a SQLite3 database file in the running host containing the "tables that were created whilst working with the log data".

However, running the very same commands found in the "test/test_sql.sh" file does not work as expected (it creates the .db file but it has no data).

To Reproduce

• Open up a log file of "syslog_log" type, to perfectly match the example in the test file (although this should be irrelevant)
• Confirm the format-specific database has data:

;SELECT COUNT(*) FROM syslog_log
✔ SQL Result: 18336

• Create a user table running the same command as in "lnav" SQL test script, and confirm is has data:

;CREATE TABLE syslog_copy AS SELECT * FROM syslog_log
;SELECT COUNT(*) FROM syslog_copy
✔ SQL Result: 18336

• Now run the "save" command as in the test file:

;.save syslog_copy.db

• And see what the output is (file exists but it is an empty SQLite DB file):

$ ls -l syslog_copy.db 
-rw-r-----. 1 user group 4096 Nov 20 10:19 syslog_copy.db

$ echo ".dump" | sqlite3 syslog_copy.db 
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;

The debug file includes the following detail for the saving of the user DB:

2025-11-20T10:19:24.193977+01:00 I t0 [182007::user_input-103] view_helpers.cc:1730 changing mode from SQL to PAGING
2025-11-20T10:19:24.194013+01:00 I t0 [182007::user_input-103::execute_sql-6] command_executor.cc:357 Executing SQL: .save syslog_copy.db
2025-11-20T10:19:24.194064+01:00 I t2 [182007::backup_user_db-0] sql_commands.cc:208 starting backup of user DB: syslog_copy.db
2025-11-20T10:19:24.194828+01:00 I t2 [182007::backup_user_db-0] sql_commands.cc:289 backup complete

Looking into the debug file for the time when the "syslog_copy" table was created I could not find anything in particular.

In the ".schema" view, I can see the two "lnav" databases:

ATTACH DATABASE '' AS 'main';
ATTACH DATABASE '' AS 'lnav_db'

However regarding the user table, the create command does not indicate the DB it should be created into (don't know if it should):

CREATE TABLE syslog_copy(
  log_line INT,
  log_time NUM,
  log_level TEXT,
...

[tstack]

Hmm, those same commands are working as expected for me. Can you attach the full debug log?

However regarding the user table, the create command does not indicate the DB it should be created into (don't know if it should):

I believe it creates the table in the main DB by default. That being said, I should update the SCHEMA view to add lnav_db. for the virtual table CREATE statements.

[dardhal]

Attaching the log file from a pristine "lnav" session and no $HOME/.lnav/ directory for clarity:

debug-sql.log

[tstack]

Yeah, I don't see anything unusual in the log either. Can you run the following to make sure the table is created in the main DB:

;SELECT * FROM sqlite_schema

It should show one result row.

[dardhal]

Yes, that's right, exactly one result:

✔ SQL Result: type=table; name=syslog_copy; tbl_name=syslog_copy; rootpage=2; sql=CREATE TABLE syslog_copy(   log_line INT,   log_time NUM,   log_level TEXT,   log_hostname TEXT,   log_msgid TEXT,   log_pid TEXT,   log_pri INT,   log_procname TEXT,   log_struct TEXT,   log_syslog_tag TEXT,   syslog_version INT,   log_part TEXT,   log_idle_msecs INT,   log_mark NUM,   log_comment TEXT,   log_tags TEXT,   log_annotations TEXT,   log_filters TEXT )  

[dardhal]

OK, so took the time to investigate this problem using opencode.ai (using the free Grok Code Fast 1 model), to give the "agentic technology a try". Note my C++ knowledge is a 30 year ago experience creating nothing more complex than a "hello world", hence let the "coding assistant" do the heavy lifting, and just sharing its findings...because it "fixed" the code for me, when somehow, it worked for the author. All cautions advised with the following information, which I am just sharing in a hope to help the author get this fixed (or at least narrowed down) , saving him some precious time.

Please allow me to reiterate that this is in no way anything other than probably a blunt attempt at helping with something the author could not reproduce, so I did try myself with the tools I have at hand (given my knowledge is insufficient for the troubleshooting to be made in a more traditional fashion). If the author believes this is not a good approach for him or for the project, I will refrain from following the same path in the future.

Problem: The .save command was creating empty SQLite database files despite user-created tables existing in memory.

Root Cause: The backup_user_db() function was creating a new in-memory database connection instead of using the existing database connection that contained the user data.

Original Broken Code: (src/sql_commands.cc)

// Created NEW empty database connection
auto_sqlite3 db;
sqlite3_open("file:user_db?mode=memory&cache=shared", db.out());

// Tried to attach lnav_db to empty database
auto stmt = prepare_stmt(db, LNAV_ATTACH_DB);

// Backed up the EMPTY database
auto* backup = sqlite3_backup_init(out_db.in(), "main", db.in(), "main");

Why This Failed:

• lnav_data.ld_db already contains the user-created tables
• Creating a new "file:user_db?mode=memory&cache=shared" connection creates a fresh, empty database
• The empty database was backed up, resulting in an empty output file

Applied Fix:

// Use EXISTING database connection with user data
auto* backup = sqlite3_backup_init(out_db.in(), "main", lnav_data.ld_db.in(), "main");

Again, this came out from the coding agent after a few iterations between it and me, and the hard fact is, the same testing I made before (which just created empty .db for ";.save"), now with the correction it works (vs 0.14.0-alpha1 which does not, using the same identical input file and test commands)!!!

Another thing that this exercise allowed me to notice is tables created from ":create-logline-table" (and probably those being created as ":create-search-table") are still not exported to the DB, but in this case it seems this was an intentional decision (or just an oversight) as the resulting tables are put into the lnav_db schema, and not in the "main" schema, which is the one being saved (for user created tables) when doing ";.save".

The IA enabled code assistant put it this way:

Root Cause:

• :create-logline-table creates virtual tables in the lnav_db schema: CREATE VIRTUAL TABLE lnav_db.{name} ...
• Current backup only saves the main database schema
• lnav_db schema (containing logline tables) is not backed up

Evidence:

• Virtual tables are registered in lnav_db database via log_vtab_manager::register_vtab()
• Debug logs show lnav_db as an attached database
• Backup only targets "main" database schema

Impact: User-created logline table definitions are lost on save/restore.

** Recommendations **

1. Preferred: Extend Backup to Include All Databases

   • Modify backup_user_db() to backup both main and lnav_db schemas
   • Ensures complete user data preservation

2. Alternative: Change Logline Table Storage

   • Modify :create-logline-table to create tables in main schema instead of lnav_db
   • Requires testing to ensure no conflicts with system tables

3. Documentation Approach:

   • Update .save help text to clarify scope: "Saves user-created tables from main database"
   • Document that logline tables need recreation after restore

[dardhal]

Adding the diff that the tool created. Double checked that:

• A pristine "lnav" tree cloned from github, and compiled, fails to populate the .db file when doing ";.save" (neither user table nor :create-logline-table)
• Applied the patch below on top of the above code, compiled, repeated exactly the same test commands and ";.save" did save the user created table but not the :create-logline-table

diff --git a/src/sql_commands.cc b/src/sql_commands.cc
index 0703f063..808813be 100644
--- a/src/sql_commands.cc
+++ b/src/sql_commands.cc
@@ -205,46 +205,23 @@ backup_user_db(const std::string& filename)
 {
     static auto op = lnav_operation{__FUNCTION__};
     auto op_guard = lnav_opid_guard::internal(op);
-    log_info("starting backup of user DB: %s", filename.c_str());
+    log_info("starting backup of database: %s", filename.c_str());
     auto bguard = backup_progress_t::begin();
 
     auto_sqlite3 out_db;
     if (sqlite3_open(filename.c_str(), out_db.out()) != SQLITE_OK) {
         auto um = lnav::console::user_message::error(
-                      attr_line_t("unable to open output DB: ")
+                      attr_line_t("unable to open output database: ")
                           .append(lnav::roles::file(filename)))
                       .with_reason(sqlite3_errmsg(out_db.in()));
         bguard.push_msg(um);
         return;
     }
 
-    auto_sqlite3 db;
-    if (sqlite3_open("file:user_db?mode=memory&cache=shared", db.out())
-        != SQLITE_OK)
-    {
-        auto um = lnav::console::user_message::error(
-                      "unable to open lnav's user DB")
-                      .with_reason(sqlite3_errmsg(db.in()));
-        bguard.push_msg(um);
-        return;
-    }
-
-    {
-        auto stmt = prepare_stmt(db, LNAV_ATTACH_DB).unwrap();
-        auto exec_res = stmt.execute();
-        if (exec_res.isErr()) {
-            auto um
-                = lnav::console::user_message::error("unable to attach lnav_db")
-                      .with_reason(sqlite3_errmsg(db.in()));
-            bguard.push_msg(um);
-            return;
-        }
-    }
-
-    auto* backup = sqlite3_backup_init(out_db.in(), "main", db.in(), "main");
+    auto* backup = sqlite3_backup_init(out_db.in(), "main", lnav_data.ld_db.in(), "main");
     if (backup == nullptr) {
-        auto um = lnav::console::user_message::error("unable to backup user DB")
-                      .with_reason(sqlite3_errmsg(db.in()));
+        auto um = lnav::console::user_message::error("unable to backup database")
+                      .with_reason(sqlite3_errmsg(lnav_data.ld_db.in()));
         bguard.push_msg(um);
         return;
     }
@@ -268,8 +245,8 @@ backup_user_db(const std::string& filename)
                 break;
             default: {
                 auto um = lnav::console::user_message::error(
-                              "unable to backup user DB")
-                              .with_reason(sqlite3_errmsg(db.in()));
+                              "unable to backup database")
+                              .with_reason(sqlite3_errmsg(lnav_data.ld_db.in()));
                 bguard.push_msg(um);
                 sqlite3_backup_finish(backup);
                 return;
@@ -292,8 +269,8 @@ backup_user_db(const std::string& filename)
     }
     sqlite3_backup_finish(backup);
     log_info("backup complete: complete=%llu; total=%llu",
-             backup_progress_t::INSTANCE.bp_complete.load(),
-             backup_progress_t::INSTANCE.bp_total.load());
+              backup_progress_t::INSTANCE.bp_complete.load(),
+              backup_progress_t::INSTANCE.bp_total.load());
 }
 
 static Result<std::string, lnav::console::user_message>

I seem to understand the tool did things it was not asked for (somehow decided to edit some of the text in the logs, and at the end seemed to change the indentation), but I guess the code clearly shows the idea of the fix.

Hope it helps.

[tstack]

I think you're on the right track. I didn't remember the main database connection was being re-opened. I do think it needs to be reopened since this is happening in a separate thread and sharing the connection might be problematic. But, now that you point it out, I notice that the sqlite3_open() call is different from the main one. The open in the backup doesn't pass the extra flags:

https://github.com/tstack/lnav/blob/ca9a2d545673ea5af8a9f48c019f7de270515c14/src/lnav.cc#L2871-L2875

I wonder if we need to change the open in the backup to look like this:

sqlite3_open_v2(
            "file:user_db?mode=memory&cache=shared",
            db.out(),
            SQLITE_OPEN_URI | SQLITE_OPEN_READWRITE,
            nullptr)

Basically the same, except for not passing SQLITE_OPEN_CREATE. Maybe the SQLITE_OPEN_URI is not set by default for the regular sqlite3_open() call .. ? The cache=shared in the query string is supposed to tell SQLite to connect to the same in-memory DB. From your symptoms, it sounds like that is not happening.

[tstack]

I've pushed a change to use sqlite3_open_v2() with the SQLITE_OPEN_URI flag. It looks like that is needed since sqlite3_open() will only interpret the filename as a URI if a compile-time flag is set. Thanks for the investigation.

[dardhal]

Confirmed it is working with your changes.

Regarding the tables created from ":create-logline-table" and ":create-search-table", they are neither kept in the session (:export-session-to) nor saved when doing a ";.save", are those two intentional?

[tstack]

Regarding the tables created from ":create-logline-table" and ":create-search-table"

Those are vtables, so they won't work in a ;.save. I do agree that the search tables should be added to the exported session, so I've done that. I'd kinda like to get rid of the logline stuff, it's getting in the way more than it is useful.

[dardhal]

Confirmed now ":create-search-table" commands are exported in the session file.

And while I fail to understand the problems with the "logline stuff", it is handy (and much easier than :create-search-table) to focus on a give line format and do ":create-logline-table" than creating the search equivalent. Of course, most of the time the same may be achieved by other means, but I am just curious here.