Running the unit tests results in sqlite3.OperationalError: object name reserved for internal use: event_search_content

[MadLittleMods]

When trying to run the unit tests, I see sqlite3.OperationalError: object name reserved for internal use: event_search_content which fails a bunch of the tests.

$ python -m twisted.trial tests
...
sqlite3.OperationalError: object name reserved for internal use: event_search_content

Ran 1412 tests in 57.728s

FAILED (skips=20, errors=1069, successes=323)

Looking at synapse/storage/databases/main/schema/full_schemas/54/full.sql.sqlite#L70, it has the following code which is the only reference to event_search_content.

CREATE TABLE IF NOT EXISTS 'event_search_content'(docid INTEGER PRIMARY KEY, 'c0event_id', 'c1room_id', 'c2sender', 'c3key', 'c4value');

---

Trying this syntax out directly in the postgres command line (not sqlite):

$ psql synapse

# The current code
synapse=# CREATE TABLE IF NOT EXISTS 'event_search_content'(docid INTEGER PRIMARY KEY, 'c0event_id', 'c1room_id', 'c2sender', 'c3key', 'c4value');
ERROR:  syntax error at or near "'event_search_content'"
LINE 1: CREATE TABLE IF NOT EXISTS 'event_search_content'(docid INTE...
                                   ^

# Replacing the table name single quotes to double quotes
synapse=# CREATE TABLE IF NOT EXISTS "event_search_content" (docid INTEGER PRIMARY KEY, 'c0event_id', 'c1room_id', 'c2sender', 'c3key', 'c4value');
ERROR:  syntax error at or near "'c0event_id'"
LINE 1: ...event_search_content" (docid INTEGER PRIMARY KEY, 'c0event_i...
                                                             ^
                                                             
# Replacing all single with double quotes
synapse=# CREATE TABLE IF NOT EXISTS "event_search_content"(docid INTEGER PRIMARY KEY, "c0event_id", "c1room_id", "c2sender", "c3key", "c4value");
ERROR:  syntax error at or near ","
LINE 1: ...h_content"(docid INTEGER PRIMARY KEY, "c0event_id", "c1room_...
                                                             ^

And the last piece seems to be missing the types for the columns, https://stackoverflow.com/a/14950544/796832

---

This is from a fresh clone and install on a new machine.

• macOS 11.1
• Python 3.8.6
• SQLite version 3.32.3 2020-06-18 14:16:19
• postgres (PostgreSQL) 13.1

[MadLittleMods]

I am unsure whether this leans towards developer problem or support request which is better handled in #synapse:matrix.org

[clokep]

Note that since the SQL in question is in full.sql.sqlite it will only run on SQLite. I don't think the results of testing on postgres matter much here.

Looking online a bit it seems that sqlite3_* namespace is reserved, but not sure why it would happen with event_search_content. I seem to have a much older version of sqlite (and I'm on an older version of macOS), although I thought that Python uses a separately internally packaged version of sqlite.

Can you run Python in your virtualenv and do the following:

>>> import sqlite3
>>> sqlite3.sqlite_version
'3.28.0'

[clokep]

I am unsure whether this leans towards developer problem or support request which is better handled in #synapse:matrix.org

I think #synapse-dev:matrix.org is the correct channel. I've invited you. 😄

[MadLittleMods]

Thanks for the correct shout on #synapse-dev:matrix.org! I see it as a badge on the top of the readme now. Can we link it in the Development section of the readme and in the topic of the #synapse:matrix.org room? I'll create a PR 🚅 -> #9002

---

The sqlite3.sqlite_version matches my sqlite3 binary version 3.32.3.

$ python
Python 3.8.6 (default, Dec 29 2020, 03:21:26)
[Clang 12.0.0 (clang-1200.0.32.28)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.sqlite_version
'3.32.3'

$ sqlite3 --version
3.32.3 2020-06-18 14:16:19 02c344aceaea0d177dd42e62c8541e3cab4a26c757ba33b3a31a43ccc7d4aapl

[MadLittleMods]

If I remove these tables, the tests seem to run well 🤷. Need to look at those lines closer.

diff --git a/synapse/storage/databases/main/schema/full_schemas/54/full.sql.sqlite b/synapse/storage/databases/main/schema/full_schemas/54/full.sql.sqlite
index a0411ede7..308124e53 100644
--- a/synapse/storage/databases/main/schema/full_schemas/54/full.sql.sqlite
+++ b/synapse/storage/databases/main/schema/full_schemas/54/full.sql.sqlite
@@ -67,11 +67,6 @@ CREATE TABLE IF NOT EXISTS "user_threepids" ( user_id TEXT NOT NULL, medium TEXT
 CREATE INDEX user_threepids_user_id ON user_threepids(user_id);
 CREATE VIRTUAL TABLE event_search USING fts4 ( event_id, room_id, sender, key, value )
 /* event_search(event_id,room_id,sender,"key",value) */;
-CREATE TABLE IF NOT EXISTS 'event_search_content'(docid INTEGER PRIMARY KEY, 'c0event_id', 'c1room_id', 'c2sender', 'c3key', 'c4value');
-CREATE TABLE IF NOT EXISTS 'event_search_segments'(blockid INTEGER PRIMARY KEY, block BLOB);
-CREATE TABLE IF NOT EXISTS 'event_search_segdir'(level INTEGER,idx INTEGER,start_block INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY KEY(level, idx));
-CREATE TABLE IF NOT EXISTS 'event_search_docsize'(docid INTEGER PRIMARY KEY, size BLOB);
-CREATE TABLE IF NOT EXISTS 'event_search_stat'(id INTEGER PRIMARY KEY, value BLOB);
 CREATE TABLE guest_access( event_id TEXT NOT NULL, room_id TEXT NOT NULL, guest_access TEXT NOT NULL, UNIQUE (event_id) );
 CREATE TABLE history_visibility( event_id TEXT NOT NULL, room_id TEXT NOT NULL, history_visibility TEXT NOT NULL, UNIQUE (event_id) );
 CREATE TABLE room_tags( user_id TEXT NOT NULL, room_id TEXT NOT NULL, tag     TEXT NOT NULL, content TEXT NOT NULL, CONSTRAINT room_tag_uniqueness UNIQUE (user_id, room_id, tag) );
@@ -149,11 +144,6 @@ CREATE INDEX device_lists_outbound_last_success_idx ON device_lists_outbound_las
 CREATE TABLE user_directory_stream_pos ( Lock CHAR(1) NOT NULL DEFAULT 'X' UNIQUE, stream_id BIGINT, CHECK (Lock='X') );
 CREATE VIRTUAL TABLE user_directory_search USING fts4 ( user_id, value )
 /* user_directory_search(user_id,value) */;
-CREATE TABLE IF NOT EXISTS 'user_directory_search_content'(docid INTEGER PRIMARY KEY, 'c0user_id', 'c1value');
-CREATE TABLE IF NOT EXISTS 'user_directory_search_segments'(blockid INTEGER PRIMARY KEY, block BLOB);
-CREATE TABLE IF NOT EXISTS 'user_directory_search_segdir'(level INTEGER,idx INTEGER,start_block INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY KEY(level, idx));
-CREATE TABLE IF NOT EXISTS 'user_directory_search_docsize'(docid INTEGER PRIMARY KEY, size BLOB);
-CREATE TABLE IF NOT EXISTS 'user_directory_search_stat'(id INTEGER PRIMARY KEY, value BLOB);
 CREATE TABLE blocked_rooms ( room_id TEXT NOT NULL, user_id TEXT NOT NULL );
 CREATE UNIQUE INDEX blocked_rooms_idx ON blocked_rooms(room_id);
 CREATE TABLE IF NOT EXISTS "local_media_repository_url_cache"( url TEXT, response_code INTEGER, etag TEXT, expires_ts BIGINT, og TEXT, media_id TEXT, download_ts BIGINT );

$ python -m twisted.trial tests
...
Ran 1412 tests in 704.890s

PASSED (skips=20, successes=1392)