Offline Sync Issues & “Too Many SQL Variables” Error

[SDS1234]

Hi,
I’ve been experiencing issues with missing offline coding sessions, so I started testing the offline sync feature and noticed some unexpected behavior.

Background
According to the WakaTime FAQ, offline heartbeats should be stored locally and synced once the IDE is back online.
I’m running a self-hosted Wakapi instance at home, so when I’m coding outside, syncing isn’t always possible (VPN isn’t always available).

System information
Wakapi version: 2.15.0
OS: Debian (no Docker)
Database: SQLite

---

Steps to Reproduce & Analysis

Note

Please note that some log entries might not be in perfect chronological order, as I documented afterwards.

Test 1: Natural Sync

Opened the editor with pending offline heartbeats.
Checked the Wakapi dashboard for updates.

Wakapi log

Sep 06 12:16:42 homepage wakapi[2010]: {"time":"CENSORED","level":"INFO","msg":"[request]","status":200,"method":"GET","uri":"/api/users/current/statusbar/today","duration":5573646,"bytes":453,"addr":"CENSORED","user":"CENSORED"}
Sep 06 12:16:48 homepage wakapi[2010]: {"time":"CENSORED","level":"INFO","msg":"[request]","status":201,"method":"POST","uri":"/api/users/current/heartbeats.bulk","duration":59870587,"bytes":49,"addr":"CENSORED","user":"CENSORED"}
Sep 06 12:16:48 homepage wakapi[2010]: {"time":"CENSORED","level":"INFO","msg":"generating ephemeral durations for user up until now","user":"CENSORED","from":"0001-01-01T00:00:00Z"}
Sep 06 12:16:49 homepage wakapi[2010]: {"time":"CENSORED","level":"INFO","msg":"[request]","status":201,"method":"POST","uri":"/api/users/current/heartbeats.bulk","duration":84768071,"bytes":676,"addr":"CENSORED","user":"CENSORED"}
Sep 06 12:16:50 homepage wakapi[2010]: {"time":"CENSORED","level":"ERROR","msg":"failed to persist new ephemeral durations for user","user":"CENSORED","error":"SQL logic error: too many SQL variables (1)"}

Result:
Data seemed off; not all heartbeats appeared.

Test 2: Simulated Offline, Then Online

1. Stopped Wakapi
2. Coded for a while, monitored offline heartbeats with wakatime-cli --offline-count
3. Closed the editor
4. Restarted Wakapi
5. Reopened the editor
6. Monitoring journalctl

Wakapi log

Sep 06 13:26:47 homepage wakapi[2070]: {"time":"CENSORED","level":"INFO","msg":"[request]","status":200,"method":"GET","uri":"/api/users/current/statusbar/today","duration":4688540,"bytes":2169,"addr":"CENSORED","user":"CENSORED"}
Sep 06 13:29:32 homepage wakapi[2070]: {"time":"CENSORED","level":"INFO","msg":"[request]","status":200,"method":"GET","uri":"/api/users/current/statusbar/today","duration":9382000,"bytes":2169,"addr":"CENSORED","user":"CENSORED"}
Sep 06 13:29:41 homepage wakapi[2070]: {"time":"CENSORED","level":"INFO","msg":"[request]","status":201,"method":"POST","uri":"/api/users/current/heartbeats.bulk","duration":37094858,"bytes":49,"addr":"CENSORED","user":"CENSORED"}
Sep 06 13:29:41 homepage wakapi[2070]: {"time":"CENSORED","level":"INFO","msg":"generating ephemeral durations for user up until now","user":"CENSORED","from":"0001-01-01T00:00:00Z"}
Sep 06 13:29:42 homepage wakapi[2070]: {"time":"CENSORED","level":"INFO","msg":"[request]","status":201,"method":"POST","uri":"/api/users/current/heartbeats.bulk","duration":1037055871,"bytes":841,"addr":"CENSORED","user":"CENSORED"}
Sep 06 13:29:42 homepage wakapi[2070]: {"time":"CENSORED","level":"ERROR","msg":"failed to persist new ephemeral durations for user","user":"CENSORED","error":"SQL logic error: too many SQL variables (1)"}
Sep 06 13:29:42 homepage wakapi[2070]: {"time":"CENSORED","level":"INFO","msg":"[request]","status":201,"method":"POST","uri":"/api/users/current/heartbeats.bulk","duration":89089669,"bytes":511,"addr":"CENSORED","user":"CENSORED"}

Result:

• Only /api/users/current/statusbar/today was triggered at first.
• After typing, only this endpoint was hit.
• Eventually, after using another editor and typing there, /api/users/current/heartbeats.bulk was triggered.
• Found this error in Wakapi logs:
  "failed to persist new ephemeral durations for user","user":"CENSORED","error":"SQL logic error: too many SQL variables (1)"

Test 3: Manual Sync

1. Stopped Wakapi
2. Coded for a while, monitored offline heartbeats with wakatime-cli --offline-count
3. Closed the editor
4. Restarted Wakapi
5. Ran manualsync of offline heartbeats: wakatime-cli --sync-offline-activity 1000
6. Checked journalctl

Wakatime cli (Windows) manual sync

C:\Users\CENSORED\.wakatime>wakatime-cli-windows-amd64.exe --offline-count
11

C:\Users\CENSORED\.wakatime>wakatime-cli-windows-amd64.exe --sync-offline-activity 1000

C:\Users\CENSORED\.wakatime>wakatime-cli-windows-amd64.exe --offline-count
0

C:\Users\CENSORED\.wakatime>

Wakapi log

Sep 06 15:16:54 homepage wakapi[2120]: {"time":"CENSORED","level":"INFO","msg":"generating ephemeral durations for user up until now","user":"CENSORED","from":"0001-01-01T00:00:00Z"}
Sep 06 15:16:55 homepage wakapi[2120]: {"time":"CENSORED","level":"ERROR","msg":"failed to persist new ephemeral durations for user","user":"CENSORED","error":"SQL logic error: too many SQL variables (1)"}

Result:
/api/users/current/heartbeats.bulk was triggered immediately, but the same error appeared in the logs.

Conclusion

Sync triggering isn't working as expected. But even if it would work seamlessly, the error in wakapi logs still raises my eyebrows.
Reviewing the logs of the first "natural" sync attempt, the error might be the reason for missing heartbeats or missformated data.

One idea of mine is a shell script checking at startup if i'm in my home network (Wakapi reachable) and if so, trigger a manual sync. But still those errors worry me.

Note

I couldn't determine a continuous output behavior. Sometimes data appears in the dashboard, sometimes not, even when the error is present.

---

Questions for the Maintainers:

1. Is this a known issue with Wakapi and SQLite?
2. Why those errors occur, is this normal?
3. Could this error be causing the missing data, or is there something else I should check?

---

Thanks for your help!

[SDS1234]

Note

Written with support/review of AI. But analyzed, drafted and corrected manually by me :)

[muety]

Thanks for reporting 👍. I saw that error before in the context of SQLite when trying to insert too large batches of heartbeats at the same time (like many hundreds or so). But your offline cache was only 11 heartbeats big in the example and the error doesn't occur with heartbeats, but with durations. Actually looks like a bug to me.

Can you please do me a favor and send the output of the following SQL query?

PRAGMA table_info(durations);

[muety]

Turned this into an issue (because probably a bug), see #840.