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)

[richvdh]

hrm. These tables (event_search_content etc) are special tables that should get created automatically as a side-effect of creating the virtual event_search table, which I think is why it is complaining about the name being reserved. But then, the IF NOT EXISTS part should mean that the whole thing is ignored.

Arguably these tables shouldn't be listed in the full schema, but before we remove them I'd like us to understand why the IF NOT EXISTS part has stopped working.

[MadLittleMods]

Good info @richvdh!

The CREATE VIRTUAL TABLE event_search USING fts4 ( event_id, room_id, sender, key, value ) line is using fts4 (full text search 4) https://www.sqlite.org/fts3.html

For each FTS virtual table in a database, three to five real (non-virtual) tables are created to store the underlying data. These real tables are called "shadow tables". The real tables are named "%_content", "%_segdir", "%_segments", "%_stat", and "%_docsize", where "%" is replaced by the name of the FTS virtual table.

https://www.sqlite.org/fts3.html#shadow_tables

---

Arguably these tables shouldn't be listed in the full schema, but before we remove them I'd like us to understand why the IF NOT EXISTS part has stopped working.

Anything I can test on my local machine?

It looks like it works as expected(skips the table) when trying it out manually:

$ sqlite3
SQLite version 3.32.3 2020-06-18 14:16:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE VIRTUAL TABLE event_search USING fts4 ( event_id, room_id, sender, key, value );
sqlite> CREATE TABLE IF NOT EXISTS 'event_search_content'(docid INTEGER PRIMARY KEY, 'c0event_id', 'c1room_id', 'c2sender', 'c3key', 'c4value');
sqlite> CREATE TABLE 'event_search_content'(docid INTEGER PRIMARY KEY, 'c0event_id', 'c1room_id', 'c2sender', 'c3key', 'c4value');
Error: table 'event_search_content' already exists

---

I'm unable to find anyone else from Google running into this problem except for SQLITE_* tables like SQLITE_SEQUENCE, sqlite_master, sqlite_stat1

[MadLittleMods]

For reference, do the twisted unit tests work for you when it dumps the full schema into the database?

[richvdh]

yeah, I've never had this problem with the unit tests.

[richvdh]

I'm still a bit mystified by this. I wonder if there is anything odd about the compile-time flags used to build your sqlite.

Please could you run:

/path/to/venv/bin/python3 -c "import sqlite3; print(sqlite3.connect(':memory:').execute('PRAGMA compile_options').fetchall())"

[richvdh]

ok, new theory. somehow the defensive bit is being set on the sqlite connection (see https://sqlite.org/c3ref/c_dbconfig_defensive.html). When that is set I see the same behaviour:

sqlite> CREATE VIRTUAL TABLE event_search USING fts4 ( event_id, room_id, sender, key, value );
sqlite> CREATE TABLE IF NOT EXISTS 'event_search_content'(docid INTEGER PRIMARY KEY, 'c0event_id', 'c1room_id', 'c2sender', 'c3key', 'c4value');
sqlite> .dbconfig defensive on
          defensive on
sqlite> CREATE TABLE IF NOT EXISTS 'event_search_content'(docid INTEGER PRIMARY KEY, 'c0event_id', 'c1room_id', 'c2sender', 'c3key', 'c4value');
Error: object name reserved for internal use: event_search_content

[MadLittleMods]

Please could you run:

/path/to/venv/bin/python3 -c "import sqlite3; print(sqlite3.connect(':memory:').execute('PRAGMA compile_options').fetchall())"

# Activate the venv
source ./env/bin/activate

python -c "import sqlite3; print(sqlite3.connect(':memory:').execute('PRAGMA compile_options').fetchall())"
[('BUG_COMPATIBLE_20160819',), ('COMPILER=clang-12.0.0',), ('DEFAULT_CACHE_SIZE=2000',), ('DEFAULT_CKPTFULLFSYNC',), ('DEFAULT_JOURNAL_SIZE_LIMIT=32768',), ('DEFAULT_PAGE_SIZE=4096',), ('DEFAULT_SYNCHRONOUS=2',), ('DEFAULT_WAL_SYNCHRONOUS=1',), ('ENABLE_API_ARMOR',), ('ENABLE_COLUMN_METADATA',), ('ENABLE_DBSTAT_VTAB',), ('ENABLE_FTS3',), ('ENABLE_FTS3_PARENTHESIS',), ('ENABLE_FTS3_TOKENIZER',), ('ENABLE_FTS4',), ('ENABLE_FTS5',), ('ENABLE_JSON1',), ('ENABLE_LOCKING_STYLE=1',), ('ENABLE_PREUPDATE_HOOK',), ('ENABLE_RTREE',), ('ENABLE_SESSION',), ('ENABLE_SNAPSHOT',), ('ENABLE_SQLLOG',), ('ENABLE_STMT_SCANSTATUS',), ('ENABLE_UNKNOWN_SQL_FUNCTION',), ('ENABLE_UPDATE_DELETE_LIMIT',), ('HAS_CODEC_RESTRICTED',), ('HAVE_ISNAN',), ('MAX_LENGTH=2147483645',), ('MAX_MMAP_SIZE=1073741824',), ('MAX_VARIABLE_NUMBER=500000',), ('OMIT_AUTORESET',), ('OMIT_LOAD_EXTENSION',), ('STMTJRNL_SPILL=131072',), ('THREADSAFE=2',), ('USE_URI',)]

[richvdh]

possibly the defensive bit is set by default. It's clear that this stuff is changing in sqlite - the relevant change seems to be https://www.sqlite.org/src/info/bae76a5c40703871. I don't understand how to translate that into sqlite version numbers, but it's clear that it post-dates 3.28.0.

so... after all that, #9003 probably is the right solution.

[MadLittleMods]

Fantastic find @richvdh!

The docs you linked align so well as well with what I'm seeing:

SQLITE_DBCONFIG_DEFENSIVE
[...]
The disabled features include but are not limited to the following:
[...]

• Direct writes to shadow tables.

https://sqlite.org/c3ref/c_dbconfig_defensive.html

---

Thanks for linking the direct change!

Not sure about it being exactly 3.28.0 since @clokep is using that version and is unable to reproduce but maybe soon after 🤷‍♀️.

---

For my own reference, this post is talking about previous versions of SQLite but talks about SQLITE_DBCONFIG_DEFENSIVE and preventing an RCE: https://news.ycombinator.com/item?id=1868630. It still mentions that the config is off by default because of breaking backwards compatibility but maybe they went with it eventually.