Problems when hitting max parameter count in SQLite

[matrixbot]

This issue was originally created by @HenrikSolver at matrix-org/dendrite#1223.

With monolith server built from commit 7b86238. 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 matrix-org/dendrite#1223 (comment).

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 matrix-org/dendrite#1223 (comment).

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 matrix-org/dendrite#1223 (comment).

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.