element-hq / element-hq/dendrite
Problems when hitting max parameter count in SQLite
- Dominant language
- Go
- Stars
- 967
- Forks
- 102
- PR merge metrics
- No merged PRs in 30d
Description
*This issue was originally created by [**@HenrikSolver**](https://github.com/HenrikSolver) at .*
With monolith server built from commit 7b862384a779f067f07ffeb2151856f89d372732. Running on Ubuntu server 18.04.
After creating account and joining a couple of rooms (#dendrite:matrix.org, #dendrite-dev:matrix.org and #go-lang:matrix.org), clear the client's cache or log out from the home server. When logging in again the initial sync fails.
The log says:
`ERRO[2020-07-24T20:33:21.201032227Z] [github.com/matrix-org/dendrite@/syncapi/sync/requestpool.go:74] OnIncomingSyncRequest
rp.currentSyncForUser failed device_id=XmAikzqI error="too many SQL variables" limit=20 req.id=GyfDiWaa0tAw req.method=GET req.path=/_matrix/client/r0/sync since=s0_0 timeout=0s user_id=""`
The source for this error is the function
```Go
func (s *currentRoomStateStatements) SelectEventsWithEventIDs(
ctx context.Context, txn *sql.Tx, eventIDs []string,
) ([]types.StreamEvent, error) {}
```
in syncapi/storage/sqlite3/current_room_state_table.go
The reason is that the query is expanded to something like.
`SELECT added_at, headered_event_json, 0 AS session_id, false AS exclude_from_sync, '' AS transaction_id FROM syncapi_current_room_state WHERE event_id IN ($1, $2, $3, $4, $5, $6, $7, ........ )`
With a large number of variables (in my case 1022) and the default limit of number of variables in SQLite is 999, (bullet number 9 in [SQLite limits](https://www.sqlite.org/limits.html) ).
Contributor guide
Research direction
Start in syncapi/storage/sqlite3/current_room_state_table.go at currentRoomStateStatements.SelectEventsWithEventIDs and reproduce the initial sync with enough event IDs to exceed SQLite’s 999-variable limit. Inspect the generated IN query and verify that initial sync succeeds when more than 999 IDs are requested.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sqlite
- Domain
- backend, databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 55/100