SQLite: Clicking a table immediately after opening DB hangs on "Executing…"

[nano9g]

What happened?

Opening a SQLite database and clicking any table in the left sidebar will display "Executing…" forever. Stopping the operation, selecting another table, then clicking back displays the data immediately.

Steps to reproduce

1. Open a SQLite database
2. Click any table
3. Note that "Executing…" is displayed forever in the toolbar
4. Click the ⊗ next to Executing…
5. Select a different table and observe that rows are displayed immediately
6. Click back to the original table and its rows load as well

Expected behavior

Tables should load normally.

Database type

No response

TablePro version

0.66.0

macOS version & chip

macOS 15.8 / Apple Silicon

Screenshots / Logs

Let me know if you need any more information. I've tested this will multiple SQLite databases and observed the same behavior. The number of rows in the table that won't load initially also does not seem to matter; an empty table triggers the issue just like one with a high number of rows. Thanks!

[datlechin]

Thanks for the detailed report, the steps made this much easier to chase.

I traced it as far as I can from here. The "Executing…" label and the ⊗ beside it are driven by one window-level flag, and there is exactly one way for them to stay up: the flag was raised when the query started and nothing lowered it. A fix for that is on the way: the indicator will read the execution registry directly instead of a separately stored flag, so it cannot outlive the work it describes, and Stop, ⌘. and the disconnect warning will follow the same value.

What I could not reproduce is the stall itself. I built 0.66.0 and current main and drove both against SQLite: a restored window with no tabs, a fresh connection from the welcome list, opening a .sqlite file into a running app, cold launching with the file, a 400-table database, and clicking a table with a blank SQL editor tab open. Every first click finished in 68 to 140 ms.

So I would like to know whether your query actually ran and never came back, or ran and had its result discarded. TablePro traces the whole path already. If you can capture it, that answers it in one line.

In Terminal, before you open the database:

log stream --level debug --predicate 'subsystem == "com.TablePro" AND category == "TableLoad"'

Then open the database, click a table, wait until "Executing…" is clearly stuck, and paste what the terminal printed. Each navigation prints one numbered block. The last stage in the block is the answer:

• ends at executeStarted with no driverFetchBegin after it: the query never reached SQLite
• ends at driverFetchBegin with no driverFetchEnd: SQLite has it and is not returning
• reaches driverFetchEnd but then prints staleResultDropped: the rows arrived and were thrown away

Two other things that would help narrow it:

1. Where do the database files live? A network share, an external disk, iCloud Drive or Dropbox behaves differently from a local folder on the first read.
2. Does it still happen if you click a second table straight away, without pressing ⊗ first?

[datlechin]

Thanks for the detail in this one, particularly that an empty table stalls the same way and that everything works after cancelling. Both of those ruled out whole families of cause.

I could not reproduce it here yet, so this stays open. What I did rule out, by measurement rather than reading:

• The connect itself. sqlite3_open is lazy and takes 0.1ms; the file is not touched until the first statement runs.
• TablePro's own second connection. It opens a pooled connection for table metadata alongside the browse query, which was the leading theory. Two handles on one file do not block each other's reads in rollback-journal mode, and never in WAL.
• Anything to do with row count, which matches what you saw with an empty table.
• A new UI test opens the sample SQLite database, clicks a table and asserts the rows arrive and "Executing…" clears. It passes, so the plain first-click path is not enough on its own to trigger this.

One thing the investigation did find and #2355 fixes: a SQLite query that stops early, because the database is locked or the volume errors, was reported as a successful empty result. If your database were locked by another process you would have seen a long stall and then an empty table rather than an error, which is at least adjacent to what you describe. That is now reported.

To get further I need one of these:

1. Does it still happen on 0.67.0? Several things on this path changed since 0.66.0.
2. Is the database file on an external drive, a network share, iCloud Drive or a synced folder, and is anything else (an app, a sqlite3 shell, a running service) holding it open at the time?
3. Are there -wal or -journal files next to it when it happens?
4. If it is reproducible for you, a sample of the hung app is the most useful thing of all: while "Executing…" is stuck, open Activity Monitor, select TablePro, and use the gear menu's Sample Process, then attach the result here. That shows exactly what it is waiting on.

Nothing in that list needs your data, only the shape of the setup.

[datlechin]

Follow-up: I closed off the last thing that could have explained why this reproduces for you and not here, and it does not explain it.

The suspicion was that our own tests never exercised what you are doing. Opening the sample database from the Help menu opens a table for you, so every test built on it clicks a table on a connection that has already connected, fetched metadata and run a query. Your first click is the first query that connection ever runs, which is the one moment the app fetches a table's metadata alongside the rows.

So I drove a genuinely cold session by hand: a saved SQLite connection restored at launch with no tab open, then the first table opened from scratch. It loaded 347 rows in under a millisecond, with no stall. Two independent sets of scenarios, on 0.66.0 and on current main, all land between 68 and 140ms.

That points at something about your setup rather than the code path itself, which is why the questions in my previous comment are the whole ballgame now, especially whether the file sits on an external, network or synced volume and whether anything else has it open. A Sample Process of TablePro from Activity Monitor while "Executing…" is stuck would settle it outright: it names exactly what the app is waiting on, and it contains no data from your database.

Two things have changed in the meantime that are worth knowing:

• fix(plugin-sqlite): report a query that stops early instead of returning the rows it got #2355 fixes a separate defect found here: a SQLite query that stops early, because the database is locked or the volume errors, was reported as a successful empty result. If a lock were involved you would have seen a long wait and then an empty table rather than an error.
• Stop cannot end a wait on a locked SQLite database at all. Measured twice, independently: with a lock held, the waiting connection ran the full 60 second timeout and ignored the interrupt. Since cancelling recovers instantly for you, whatever is stuck is almost certainly the app's own state rather than SQLite, and that half is being fixed separately.

[datlechin]

Reopening: this closed automatically when #2362 merged, because I wrote Fixes #2342 on it. That was my mistake. Nobody decided this was solved, and the close was bookkeeping rather than a verdict.

Three real defects came out of this investigation and are now on main:

• fix(toolbar): derive the window's busy state from the execution registry #2362 stops the toolbar sitting on "Executing…" after a query has ended. Whether anything is running is now derived from the execution registry instead of a separate flag that had to be lowered by hand, so the indicator cannot outlive its work.
• fix(plugin-sqlite): report a query that stops early instead of returning the rows it got #2355 makes a SQLite query that stops early, because the database is locked or the volume errors, say so instead of returning a successful empty result.
• fix(plugin-sqlite): let Stop end a wait on a locked database #2363 lets Stop end a wait on a locked database in under a second, instead of ignoring the interrupt and waiting out the full 60 second timeout.

Any one of those could be what you hit. None of them was confirmed to be, because the stall itself never reproduced here: seven scenarios across 0.66.0 and current main, every first table click finishing between 68 and 140 ms.

I also want to correct something I said in my first reply. I wrote that there was exactly one way for "Executing…" to stay up. There are two. The other is a query that really is still running, waiting on a lock another process holds, where the indicator is telling the truth and Stop could not end it. That second shape is what #2355 and #2363 address.

So the useful thing now is confirmation rather than more theory. When a build carrying all three is available, if it still happens, the Sample Process output from Activity Monitor while "Executing…" is stuck is the single most useful thing you could attach: it names exactly what the app is waiting on and contains nothing from your database. The TableLoad capture from my earlier comment answers the same question from the app's side.

And if it has gone away, saying so is just as useful, and I will close this properly.

[datlechin]

Closing this against the three fixes now on main. All of them affect what you described, and each was measured rather than guessed:

• fix(toolbar): derive the window's busy state from the execution registry #2362 removed a stored copy of the window's "a query is running" state. It could only be lowered behind two ownership checks, so any path that ended a query without satisfying both left "Executing…" and its Stop control describing work that was already over. That matches your report most closely: pressing Stop cleared the stale state, which is why everything worked afterwards.
• fix(plugin-sqlite): let Stop end a wait on a locked database #2363 made Stop able to end a wait on a database another program has locked. It could not before: measured against SQLite 3.54.0, the interrupt was ignored and the connection waited the full 60 seconds.
• fix(plugin-sqlite): report a query that stops early instead of returning the rows it got #2355 stopped a query that ends early, on a lock or an I/O error, from being reported as a successful empty result. That case used to render as a table with no rows and no error.

Being straight with you about what this does not mean: none of the three was confirmed as your cause. The stall never reproduced here across nine scenarios between two of us, including a session driven cold, with the connection restored and no query having run, which is the closest match to your steps we could construct. So this is closed on the strength of the fixes rather than on a reproduction.

If it still happens on 0.68 or later, please reopen. The single most useful thing then is a Sample Process of TablePro taken while "Executing…" is stuck: Activity Monitor, select TablePro, gear menu, Sample Process. It names exactly what the app is waiting on and contains nothing from your database. Whether the file sits on an external, network or synced volume, and whether anything else has it open, would also narrow it quickly.

Thanks for the detail in the original report. That an empty table stalled identically, and that cancelling fixed it permanently, is what ruled out query time and pointed at held state.

[nano9g]

I'll retest once 0.68 is released and let you know. Thanks!

[datlechin]

@nano9g v0.67.1 with bug fixes is releasing, please wait