Skip to content
This repository has been archived by the owner on Apr 26, 2024. It is now read-only.

SQLite: too many SQL variables (federation) #7174

Closed
zougloub opened this issue Mar 30, 2020 · 12 comments
Closed

SQLite: too many SQL variables (federation) #7174

zougloub opened this issue Mar 30, 2020 · 12 comments
Labels
z-bug (Deprecated Label) z-p3 (Deprecated Label)

Comments

@zougloub
Copy link

Description

Can't log in after upgrade to v1.12.0

During login, server logs say:

2020-03-30 10:23:48,083 - synapse.http.server - 110 - ERROR - GET-1160- Failed handle request via 'SyncRestServlet': <XForwardedForRequest at 0x7fa1783d0470 method='GET' uri='/_matrix/client/r0/sync?filter=4&timeout=0&_cacheBuster=1585578246256' clientproto='HTTP/1.0' site=8008>
...
File "/home/matrix/.synapse/lib/python3.6/site-packages/synapse/storage/data_stores/main/event_federation.py", line 185, in _get_auth_chain_difference_txn
     txn.execute(sql % (clause,), args)
   File "/home/matrix/.synapse/lib/python3.6/site-packages/synapse/storage/database.py", line 199, in execute
     self._do_execute(self.txn.execute, sql, *args)
   File "/home/matrix/.synapse/lib/python3.6/site-packages/synapse/storage/database.py", line 225, in _do_execute
     return func(sql, *args)
 sqlite3.OperationalError: too many SQL variables

(may be another problem)

Version information

  • Version: 1.12.0
  • Install method: pip
  • Platform: python 3.6.9 / linux / SQLite3 DB
@richvdh
Copy link
Member

richvdh commented Mar 30, 2020

can you paste the complete stacktrace (ie, the bit you've omitted with ...)?

@zougloub
Copy link
Author 

Mar 30 11:27:13 zougloub.eu bash[23420]: 2020-03-30 11:27:13,505 - synapse.http.server - 110 - ERROR - GET-5365- Failed handle request via 'SyncRestServlet': <XForwardedForRequest at 0x7fa170da9358 method='GET' uri='/_matrix/client/r0/sync?filter=4&timeout=0&_cacheBuster=1585582051693' clientproto='HTTP/1.0' site=8008>
Mar 30 11:27:13 zougloub.eu bash[23420]: Traceback (most recent call last):
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
Mar 30 11:27:13 zougloub.eu bash[23420]:     result = result.throwExceptionIntoGenerator(g)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/twisted/python/failure.py", line 512, in throwExceptionIntoGenerator
Mar 30 11:27:13 zougloub.eu bash[23420]:     return g.throw(self.type, self.value, self.tb)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/synapse/handlers/sync.py", line 312, in _wait_for_sync_for_user
Mar 30 11:27:13 zougloub.eu bash[23420]:     sync_config, since_token, full_state=full_state
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/synapse/handlers/sync.py", line 343, in current_sync_for_user
Mar 30 11:27:13 zougloub.eu bash[23420]:     return await self.generate_sync_result(sync_config, since_token, full_state)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/synapse/handlers/sync.py", line 1000, in generate_sync_result
Mar 30 11:27:13 zougloub.eu bash[23420]:     sync_result_builder, account_data_by_room
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/synapse/handlers/sync.py", line 1414, in _generate_sync_entry_for_rooms
Mar 30 11:27:13 zougloub.eu bash[23420]:     await concurrently_execute(handle_room_entries, room_entries, 10)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
Mar 30 11:27:13 zougloub.eu bash[23420]:     result = result.throwExceptionIntoGenerator(g)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/twisted/python/failure.py", line 512, in throwExceptionIntoGenerator
Mar 30 11:27:13 zougloub.eu bash[23420]:     return g.throw(self.type, self.value, self.tb)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/synapse/util/async_helpers.py", line 159, in _concurrently_execute_inner
Mar 30 11:27:13 zougloub.eu bash[23420]:     await maybe_awaitable(func(next(it)))
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/synapse/handlers/sync.py", line 1798, in _generate_room_entry
Mar 30 11:27:13 zougloub.eu bash[23420]:     newly_joined_room=newly_joined,
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/synapse/handlers/sync.py", line 510, in _load_filtered_recents
Mar 30 11:27:13 zougloub.eu bash[23420]:     room_id
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
Mar 30 11:27:13 zougloub.eu bash[23420]:     result = result.throwExceptionIntoGenerator(g)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/twisted/python/failure.py", line 512, in throwExceptionIntoGenerator
Mar 30 11:27:13 zougloub.eu bash[23420]:     return g.throw(self.type, self.value, self.tb)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/synapse/state/__init__.py", line 173, in get_current_state_ids
Mar 30 11:27:13 zougloub.eu bash[23420]:     ret = yield self.resolve_state_groups_for_events(room_id, latest_event_ids)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
Mar 30 11:27:13 zougloub.eu bash[23420]:     result = result.throwExceptionIntoGenerator(g)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/twisted/python/failure.py", line 512, in throwExceptionIntoGenerator
Mar 30 11:27:13 zougloub.eu bash[23420]:     return g.throw(self.type, self.value, self.tb)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/synapse/util/metrics.py", line 82, in measured_func
Mar 30 11:27:13 zougloub.eu bash[23420]:     r = yield func(self, *args, **kwargs)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
Mar 30 11:27:13 zougloub.eu bash[23420]:     result = result.throwExceptionIntoGenerator(g)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/twisted/python/failure.py", line 512, in throwExceptionIntoGenerator
Mar 30 11:27:13 zougloub.eu bash[23420]:     return g.throw(self.type, self.value, self.tb)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/synapse/state/__init__.py", line 404, in resolve_state_groups_for_events
Mar 30 11:27:13 zougloub.eu bash[23420]:     state_res_store=StateResolutionStore(self.store),
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
Mar 30 11:27:13 zougloub.eu bash[23420]:     result = result.throwExceptionIntoGenerator(g)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/twisted/python/failure.py", line 512, in throwExceptionIntoGenerator
Mar 30 11:27:13 zougloub.eu bash[23420]:     return g.throw(self.type, self.value, self.tb)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/synapse/state/__init__.py", line 527, in resolve_state_groups
Mar 30 11:27:13 zougloub.eu bash[23420]:     state_res_store=state_res_store,
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
Mar 30 11:27:13 zougloub.eu bash[23420]:     result = result.throwExceptionIntoGenerator(g)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/twisted/python/failure.py", line 512, in throwExceptionIntoGenerator
Mar 30 11:27:13 zougloub.eu bash[23420]:     return g.throw(self.type, self.value, self.tb)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/synapse/state/v2.py", line 86, in resolve_events_with_store
Mar 30 11:27:13 zougloub.eu bash[23420]:     auth_diff = yield _get_auth_chain_difference(state_sets, event_map, state_res_store)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
Mar 30 11:27:13 zougloub.eu bash[23420]:     result = result.throwExceptionIntoGenerator(g)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/twisted/python/failure.py", line 512, in throwExceptionIntoGenerator
Mar 30 11:27:13 zougloub.eu bash[23420]:     return g.throw(self.type, self.value, self.tb)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/synapse/state/v2.py", line 232, in _get_auth_chain_difference
Mar 30 11:27:13 zougloub.eu bash[23420]:     [set(state_set.values()) for state_set in state_sets]
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
Mar 30 11:27:13 zougloub.eu bash[23420]:     result = result.throwExceptionIntoGenerator(g)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/twisted/python/failure.py", line 512, in throwExceptionIntoGenerator
Mar 30 11:27:13 zougloub.eu bash[23420]:     return g.throw(self.type, self.value, self.tb)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/synapse/storage/database.py", line 524, in runInteraction
Mar 30 11:27:13 zougloub.eu bash[23420]:     **kwargs
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
Mar 30 11:27:13 zougloub.eu bash[23420]:     result = result.throwExceptionIntoGenerator(g)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/twisted/python/failure.py", line 512, in throwExceptionIntoGenerator
Mar 30 11:27:13 zougloub.eu bash[23420]:     return g.throw(self.type, self.value, self.tb)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/synapse/storage/database.py", line 574, in runWithConnection
Mar 30 11:27:13 zougloub.eu bash[23420]:     self._db_pool.runWithConnection(inner_func, *args, **kwargs)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/twisted/python/threadpool.py", line 250, in inContext
Mar 30 11:27:13 zougloub.eu bash[23420]:     result = inContext.theWork()
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/twisted/python/threadpool.py", line 266, in <lambda>
Mar 30 11:27:13 zougloub.eu bash[23420]:     inContext.theWork = lambda: context.call(ctx, func, *args, **kw)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/twisted/python/context.py", line 122, in callWithContext
Mar 30 11:27:13 zougloub.eu bash[23420]:     return self.currentContext().callWithContext(ctx, func, *args, **kw)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/twisted/python/context.py", line 85, in callWithContext
Mar 30 11:27:13 zougloub.eu bash[23420]:     return func(*args,**kw)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/twisted/enterprise/adbapi.py", line 306, in _runWithConnection
Mar 30 11:27:13 zougloub.eu bash[23420]:     compat.reraise(excValue, excTraceback)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/twisted/python/compat.py", line 464, in reraise
Mar 30 11:27:13 zougloub.eu bash[23420]:     raise exception.with_traceback(traceback)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/twisted/enterprise/adbapi.py", line 297, in _runWithConnection
Mar 30 11:27:13 zougloub.eu bash[23420]:     result = func(conn, *args, **kw)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/synapse/storage/database.py", line 571, in inner_func
Mar 30 11:27:13 zougloub.eu bash[23420]:     return func(conn, *args, **kwargs)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/synapse/storage/database.py", line 407, in new_transaction
Mar 30 11:27:13 zougloub.eu bash[23420]:     r = func(cursor, *args, **kwargs)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/synapse/storage/data_stores/main/event_federation.py", line 185, in _get_auth_chain_difference_txn
Mar 30 11:27:13 zougloub.eu bash[23420]:     txn.execute(sql % (clause,), args)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/synapse/storage/database.py", line 199, in execute
Mar 30 11:27:13 zougloub.eu bash[23420]:     self._do_execute(self.txn.execute, sql, *args)
Mar 30 11:27:13 zougloub.eu bash[23420]:   File "/home/matrix/.synapse/lib/python3.6/site-packages/synapse/storage/database.py", line 225, in _do_execute
Mar 30 11:27:13 zougloub.eu bash[23420]:     return func(sql, *args)
Mar 30 11:27:13 zougloub.eu bash[23420]: sqlite3.OperationalError: too many SQL variables

@zougloub
Copy link
Author

That _get_auth_chain_difference_txn does a query with too many parameters...

Mar 30 12:34:13 zougloub.eu bash[25373]:             SELECT depth, event_id FROM events
Mar 30 12:34:13 zougloub.eu bash[25373]:             WHERE event_id IN (?,?,?,?,?,?

... I guess it could be split in different queries.

@zougloub
Copy link
Author

Will try to work around with an sqlite compiled with -DSQLITE_MAX_VARIABLE_NUMBER...

@richvdh
Copy link
Member

richvdh commented Mar 30, 2020

if you're hitting that limit, you probably need to switch to postgres anyway...

@zougloub
Copy link
Author

No it's OK, SQLite3 is a proper database.

@richvdh
Copy link
Member

richvdh commented Mar 30, 2020

proper database or not, emprically it is an order of magnitude slower than postgres and synapse only supports it for test purposes. See also #2917.

@zougloub
Copy link
Author

I didn't know it wasn't fully supported. In terms of load I'm OK, synapse usage is unnoticeable on my server. Is there a way to migrate SQLite data to PG?

@richvdh
Copy link
Member

richvdh commented Mar 30, 2020

https://github.com/matrix-org/synapse/blob/master/docs/postgres.md#porting-from-sqlite

@clokep
Copy link
Member

clokep commented Apr 2, 2020

It seems like the solution here is to upgrade to postgres, as @richvdh pointed out. I'm going to close this since it seems the specific error is resolved. As mentioned above, #2917 is a thing.

@clokep clokep closed this as completed Apr 2, 2020
@richvdh
Copy link
Member

richvdh commented Apr 3, 2020

@clokep I think that irrespective of the fact that people should be using postgres on production systems, we can and should fix whatever is causing this on sqlite. (It looks like we should break a single massive SELECT query up into separate queries; see https://www.sqlite.org/limits.html#max_variable_number).

@richvdh richvdh reopened this Apr 3, 2020
@richvdh richvdh added z-bug (Deprecated Label) z-p3 (Deprecated Label) labels Apr 3, 2020
@richvdh
Copy link
Member

richvdh commented Apr 20, 2020

I think this was fixed by #7274

@richvdh richvdh closed this as completed Apr 20, 2020
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees
No one assigned
Labels
z-bug (Deprecated Label) z-p3 (Deprecated Label)
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@clokep @zougloub @richvdh