'sqlite3.OperationalError: near "WHERE"' on sqlite 3.7

[Bazis007]

Hi, I have problem, on first run:

(env) [root@chat synapse]# synctl start
Starting ...
This server is configured to use 'matrix.org' as its trusted key server via the
'trusted_key_servers' config option. 'matrix.org' is a good choice for a key
server since it is long-lived, stable and trusted. However, some admins may
wish to use another server for this purpose.

To suppress this warning and continue using 'matrix.org', admins should set
'suppress_key_server_warning' to 'true' in homeserver.yaml.
--------------------------------------------------------------------------------
2019-10-24 13:40:10,455 - twisted - 171 - INFO - None - Redirected stdout/stderr to logs
2019-10-24 13:40:10,455 - root - 236 - WARNING - None - ***** STARTING SERVER *****
2019-10-24 13:40:10,455 - root - 237 - WARNING - None - Server /var/www/synapse/env/lib/python3.6/site-packages/synapse/app/homeserver.py version 1.4.1
2019-10-24 13:40:10,456 - root - 238 - INFO - None - Server hostname: matrix.omnichat.tech
2019-10-24 13:40:10,457 - synapse.app.homeserver - 359 - INFO - None - Preparing database: sqlite3...
2019-10-24 13:40:16,312 - synapse.storage.prepare_database - 249 - INFO - None - Upgrading schema to v55
2019-10-24 13:40:16,313 - synapse.storage.prepare_database - 287 - INFO - None - Applying schema 55/access_token_expiry.sql
2019-10-24 13:40:16,314 - synapse.storage.prepare_database - 287 - INFO - None - Applying schema 55/track_threepid_validations.sql
2019-10-24 13:40:16,316 - synapse.storage.prepare_database - 287 - INFO - None - Applying schema 55/users_alter_deactivated.sql
2019-10-24 13:40:16,316 - synapse.storage.prepare_database - 249 - INFO - None - Upgrading schema to v56
2019-10-24 13:40:16,317 - synapse.storage.prepare_database - 287 - INFO - None - Applying schema 56/add_spans_to_device_lists.sql
2019-10-24 13:40:16,318 - synapse.storage.prepare_database - 287 - INFO - None - Applying schema 56/current_state_events_membership.sql
2019-10-24 13:40:16,318 - synapse.storage.prepare_database - 287 - INFO - None - Applying schema 56/current_state_events_membership_mk2.sql
2019-10-24 13:40:16,319 - synapse.storage.prepare_database - 287 - INFO - None - Applying schema 56/destinations_failure_ts.sql
2019-10-24 13:40:16,320 - synapse.storage.prepare_database - 287 - INFO - None - Applying schema 56/devices_last_seen.sql
2019-10-24 13:40:16,322 - synapse.storage.prepare_database - 287 - INFO - None - Applying schema 56/fix_room_keys_index.sql
2019-10-24 13:40:16,323 - synapse.storage.prepare_database - 287 - INFO - None - Applying schema 56/redaction_censor.sql
2019-10-24 13:40:16,324 - twisted - 171 - ERROR -  - Traceback (most recent call last):
2019-10-24 13:40:16,325 - twisted - 171 - ERROR -  -   File "/usr/lib64/python3.6/runpy.py", line 193, in _run_module_as_main
2019-10-24 13:40:16,326 - twisted - 171 - ERROR -  -     "__main__", mod_spec)
2019-10-24 13:40:16,326 - twisted - 171 - ERROR -  -   File "/usr/lib64/python3.6/runpy.py", line 85, in _run_code
2019-10-24 13:40:16,326 - twisted - 171 - ERROR -  -     exec(code, run_globals)
2019-10-24 13:40:16,326 - twisted - 171 - ERROR -  -   File "/var/www/synapse/env/lib/python3.6/site-packages/synapse/app/homeserver.py", line 659, in <module>
2019-10-24 13:40:16,327 - twisted - 171 - ERROR -  -     main()
2019-10-24 13:40:16,327 - twisted - 171 - ERROR -  -   File "/var/www/synapse/env/lib/python3.6/site-packages/synapse/app/homeserver.py", line 654, in main
2019-10-24 13:40:16,328 - twisted - 171 - ERROR -  -     hs = setup(sys.argv[1:])
2019-10-24 13:40:16,328 - twisted - 171 - ERROR -  -   File "/var/www/synapse/env/lib/python3.6/site-packages/synapse/app/homeserver.py", line 363, in setup
2019-10-24 13:40:16,329 - twisted - 171 - ERROR -  -     prepare_database(db_conn, database_engine, config=config)
2019-10-24 13:40:16,329 - twisted - 171 - ERROR -  -   File "/var/www/synapse/env/lib/python3.6/site-packages/synapse/storage/prepare_database.py", line 74, in prepare_database
2019-10-24 13:40:16,329 - twisted - 171 - ERROR -  -     _setup_new_database(cur, database_engine)
2019-10-24 13:40:16,330 - twisted - 171 - ERROR -  -   File "/var/www/synapse/env/lib/python3.6/site-packages/synapse/storage/prepare_database.py", line 171, in _setup_new_database
2019-10-24 13:40:16,330 - twisted - 171 - ERROR -  -     is_empty=True,
2019-10-24 13:40:16,330 - twisted - 171 - ERROR -  -   File "/var/www/synapse/env/lib/python3.6/site-packages/synapse/storage/prepare_database.py", line 288, in _upgrade_existing_database
2019-10-24 13:40:16,331 - twisted - 171 - ERROR -  -     executescript(cur, absolute_path)
2019-10-24 13:40:16,331 - twisted - 171 - ERROR -  -   File "/var/www/synapse/env/lib/python3.6/site-packages/synapse/storage/prepare_database.py", line 427, in executescript
2019-10-24 13:40:16,332 - twisted - 171 - ERROR -  -     txn.execute(statement)
2019-10-24 13:40:16,332 - twisted - 171 - ERROR -  - sqlite3.OperationalError: near "WHERE": syntax error

[mymindstorm]

Are you using CentOS 7? I had to work around this by installing sqlite 3.8.

https://kojipkgs.fedoraproject.org//packages/sqlite/3.8.11/1.fc21/x86_64/sqlite-3.8.11-1.fc21.x86_64.rpm

[NANASHI0X74]

works for me on CentOS with the above rpm package, I also tried to compile sqlite3.30 from the official site but for whatever reason, that doesn't work either. 3.8 works.

[Bazis007]

Problem with query:
CREATE INDEX redactions_have_censored ON redactions(event_id) WHERE not have_censored;

I am commnent it, and was abble to install.
Path: synapse/synapse/storage/schema/delta/56/redaction_censor.sql

[Bazis007]

My SQLite version is 3.7.17 2013-05-20 00:56:22 118a3b35693b134d56ebd780123b7fd6f1497668

[richvdh]

so it looks like #5934 broke compatibility with sqlite 3.7.

At this point sqlite 3.7 is over 6 years old, and there have been a lot of improvements to sqlite since.

In any case, the sqlite support in synapse isn't really intended for production use: you should be using postgres for anything other than a test installation.

[neilisfragile]

We even have a shiny new migration script about to land in 1.5.0.

So either way, upgrade sqlite will fix, but I'd really recommend that you migrate to postgres.

[NANASHI0X74]

I'd suggest making postgres the default then :)
thanks very much for the support 👍

[richvdh]

So either way, upgrade sqlite will fix, but I'd really recommend that you migrate to postgres.

sadly you won't be able to run the migration script without having run the migrations, which means that you'll first need to upgrade sqlite.

Neil: if we're dropping support for sqlite 3.7, we need to update https://github.com/matrix-org/synapse/blob/master/INSTALL.md#centosfedora and possibly add something to https://github.com/matrix-org/synapse/blob/master/INSTALL.md#installing-from-source.

[aaronraimist]

and a note in UPGRADE.rst

[volter]

This is terrible.

[richvdh]

fixed in #6499

[volter]

Thank you so much for that!