element-hq / element-hq/dendrite

Problems when hitting max parameter count in SQLite

Open
#1,223 3 comments 0 reactions 0 assignees View on GitHub
C-SQLite T-Defect
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.