SQLite3 DatabaseError: malformed database schema

Question

Trying to run sydent on Debian Jessie using SQLite version 3.18.0, but receiving an error.

(sydent)gooseberry@servername:/opt/sydent# python -m sydent.sydent
INFO:sydent.db.sqlitedb:Using DB file sydent.db
WARNING:sydent.http.httpcommon:No HTTPS private key / cert found: not starting replication server or doing replication pushes
INFO:sydent.http.httpserver:Starting Client API HTTP server on port 8090
INFO:twisted:Site starting on 8090
INFO:twisted:Starting factory <twisted.web.server.Site instance at 0x7fda3b6c2950>
Unhandled error in Deferred:
CRITICAL:twisted:Unhandled error in Deferred:
CRITICAL:twisted:
Traceback (most recent call last):
File "/opt/sydent/local/lib/python2.7/site-packages/twisted/internet/defer.py", line 150, in maybeDeferred
result = f(*args, **kw)
File "/opt/sydent/local/lib/python2.7/site-packages/sydent/replication/pusher.py", line 76, in scheduledPush
peers = self.peerStore.getAllPeers()
File "/opt/sydent/local/lib/python2.7/site-packages/sydent/db/peers.py", line 52, in getAllPeers
res = cur.execute("select p.name, p.port, p.lastSentVersion, pk.alg, pk.key from peers p, peer_pubkeys pk "
DatabaseError: malformed database schema (medium_lower_address) - near "(": syntax error
^CINFO:twisted:Received SIGINT, shutting down.

below is the output from select * from sqlite_master;

table|invite_tokens|invite_tokens|2|CREATE TABLE invite_tokens (
id integer primary key,
medium varchar(16) not null,
address varchar(256) not null,
room_id varchar(256) not null,
sender varchar(256) not null,
token varchar(256) not null,
received_ts bigint, -- When the invite was received by us from the homeserver
sent_ts bigint -- When the token was sent by us to the user
)
index|invite_token_medium_address|invite_tokens|3|CREATE INDEX invite_token_medium_address on invite_tokens(medium, address)
index|invite_token_token|invite_tokens|4|CREATE INDEX invite_token_token on invite_tokens(token)
table|ephemeral_public_keys|ephemeral_public_keys|5|CREATE TABLE ephemeral_public_keys(
id integer primary key,
public_key varchar(256) not null,
verify_count bigint default 0,
persistence_ts bigint
)
index|ephemeral_public_keys_index|ephemeral_public_keys|6|CREATE UNIQUE INDEX ephemeral_public_keys_index on ephemeral_public_keys(public_key)
table|peers|peers|7|CREATE TABLE peers (id integer primary key, name varchar(255) not null, port integer default null, lastSentVersion integer, lastPokeSucceededAt integer, active integer not null default 0)
index|name|peers|8|CREATE UNIQUE INDEX name on peers(name)
table|peer_pubkeys|peer_pubkeys|9|CREATE TABLE peer_pubkeys (id integer primary key, peername varchar(255) not null, alg varchar(16) not null, key text not null, foreign key (peername) references peers (peername))
index|peername_alg|peer_pubkeys|10|CREATE UNIQUE INDEX peername_alg on peer_pubkeys(peername, alg)
table|threepid_validation_sessions|threepid_validation_sessions|11|CREATE TABLE threepid_validation_sessions (id integer primary key, medium varchar(16) not null, address varchar(256) not null, clientSecret varchar(32) not null, validated int default 0, mtime bigint not null)
table|threepid_token_auths|threepid_token_auths|12|CREATE TABLE threepid_token_auths (id integer primary key, validationSession integer not null, token varchar(32) not null, sendAttemptNumber integer not null, foreign key (validationSession) references threepid_validations(id))
table|local_threepid_associations|local_threepid_associations|13|CREATE TABLE local_threepid_associations (id integer primary key, medium varchar(16) not null, address varchar(256) not null, mxid varchar(256) not null, ts integer not null, notBefore bigint not null, notAfter bigint not null)
index|medium_address|local_threepid_associations|14|CREATE UNIQUE INDEX medium_address on local_threepid_associations(medium, address)
table|global_threepid_associations|global_threepid_associations|15|CREATE TABLE global_threepid_associations (id integer primary key, medium varchar(16) not null, address varchar(256) not null, mxid varchar(256) not null, ts integer not null, notBefore bigint not null, notAfter integer not null, originServer varchar(255) not null, originId integer not null, sgAssoc text not null)
index|medium_lower_address|global_threepid_associations|16|CREATE INDEX medium_lower_address on global_threepid_associations (medium, lower(address))
index|originServer_originId|global_threepid_associations|17|CREATE UNIQUE INDEX originServer_originId on global_threepid_associations (originServer, originId)

How do I resolve this error? I can't see any issues with the schema.

Answer 1

Expression indexes were added in version 3.9.0. Apparently, sysdent uses an older SQLite version.