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

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

Closed
Bazis007 opened this issue Oct 24, 2019 · 12 comments
Closed

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

Bazis007 opened this issue Oct 24, 2019 · 12 comments
Assignees

Comments

@Bazis007
Copy link

Bazis007 commented Oct 24, 2019

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
Copy link

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
Copy link

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
Copy link
Author

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
Copy link
Author

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

@richvdh
Copy link
Member

richvdh commented Oct 28, 2019

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
Copy link
Contributor

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
Copy link

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

@richvdh
Copy link
Member

richvdh commented Oct 29, 2019

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.

@richvdh richvdh reopened this Oct 29, 2019
@aaronraimist
Copy link
Contributor

and a note in UPGRADE.rst

@volter
Copy link

volter commented Nov 5, 2019

This is terrible.

@neilisfragile neilisfragile added A-Docs things relating to the documentation z-p2 (Deprecated Label) labels Nov 6, 2019
@richvdh richvdh changed the title version 1.4.1 SQL error on first start. fix support for sqlite 3.7 Nov 7, 2019
@richvdh richvdh removed A-Docs things relating to the documentation z-p2 (Deprecated Label) labels Nov 7, 2019
@richvdh richvdh changed the title fix support for sqlite 3.7 'sqlite3.OperationalError: near "WHERE"' on sqlite 3.7 Dec 4, 2019
@richvdh richvdh closed this as completed Dec 11, 2019
@richvdh
Copy link
Member

richvdh commented Dec 11, 2019

fixed in #6499

@volter
Copy link

volter commented Dec 12, 2019

Thank you so much for that!

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
None yet
Projects
None yet
Development

No branches or pull requests

7 participants