Problems when hitting max parameter count in SQLite

[matrixbot]

This issue was originally created by @HenrikSolver at https://github.com/matrix-org/dendrite/issues/1223.

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="<MATRIXID>"

The source for this error is the function

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 ).

[matrixbot]

This comment was originally posted by @neilalexander at https://github.com/matrix-org/dendrite/issues/1223#issuecomment-664362175.

The fix in #1224 is probably a temporary fix at best, we should look into where this might become a problem.

[matrixbot]

This comment was originally posted by @HenrikSolver at https://github.com/matrix-org/dendrite/issues/1223#issuecomment-664558133.

The fix in #1224 is probably a temporary fix at best, we should look into where this might become a problem.

I agree, the fact that the query needs more than 999 variables indicates that the database schema should to be adjusted. But I don't fully understand the relationship between the tables (yet?), so I can not suggest a proposal for that at the moment.

[matrixbot]

This comment was originally posted by @kegsay at https://github.com/matrix-org/dendrite/issues/1223#issuecomment-675393519.

Load testing the server will identify more places where we are scaling parameter count poorly. I'm less concerned about scaling in relation to the number of rooms a user is joined to (999 seems like a sensible cap) but I'm more concerned about scaling in relation to:

• state events
• number of users in room (or devices, keys, etc) which will be very high when users try to join large matrix.org rooms.