Extract columns cannot create foreign key relation: sqlite3.OperationalError: table sqlite_master may not be modified

[kristomi]

Thanks for what seems like a truly great suite of libraries. I wanted to try out Datasette, but never got more than half way through your YouTube video with the SF tree dataset. Whenever I try to extract a column, I get a sqlite3.OperationalError: table sqlite_master may not be modified error from Python. This snippet reproduces the error on my system, Python 3.9.1 and sqlite-utils 3.5 on an M1 Macbook Pro running in rosetta mode:

curl "https://data.nasa.gov/resource/y77d-th95.json" | \
    sqlite-utils insert meteorites.db meteorites - --pk=id
sqlite-utils extract meteorites.db meteorites  recclass

I have tried googling the problem, but all I've found is that this might be a problem with the sqlite3 database running in defensive mode, but I definitely can't know for sure. Does the problem seem familiar to you?

[mdrovdahl]

I just ran into what appears to be the same issue on a MacBook Pro, M1 Pro.

Environment:

markd@Marks-MacBook-Pro metabase % python --version                                                             
Python 3.8.9
markd@Marks-MacBook-Pro metabase % sqlite3 --version                                                            
3.37.0 2021-12-09 01:34:53 9ff244ce0739f8ee52a3e9671adb4ee54c83c640b02e3f9d185fd2f9a179aapl
markd@Marks-MacBook-Pro metabase % sqlite-utils --version                                                        
sqlite-utils, version 3.27
markd@Marks-MacBook-Pro metabase % sqlite3 gh.db 
SQLite version 3.37.0 2021-12-09 01:34:53
Enter ".help" for usage hints.
sqlite> .dbconfig
          defensive off
            dqs_ddl on
            dqs_dml on
        enable_fkey off
        enable_qpsg off
     enable_trigger on
        enable_view on
     fts3_tokenizer off
 legacy_alter_table on
 legacy_file_format off
     load_extension off
   no_ckpt_on_close off
     reset_database off
        trigger_eqp off
     trusted_schema on
    writable_schema off

Error

markd@Marks-MacBook-Pro metabase % github-to-sqlite repos gh.db a8cteam51                                        
Traceback (most recent call last):
  File "/Users/markd/Library/Python/3.8/bin/github-to-sqlite", line 8, in <module>
    sys.exit(cli())
  File "/Users/markd/Library/Python/3.8/lib/python/site-packages/click/core.py", line 1130, in __call__
    return self.main(*args, **kwargs)
  File "/Users/markd/Library/Python/3.8/lib/python/site-packages/click/core.py", line 1055, in main
    rv = self.invoke(ctx)
  File "/Users/markd/Library/Python/3.8/lib/python/site-packages/click/core.py", line 1657, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/Users/markd/Library/Python/3.8/lib/python/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/Users/markd/Library/Python/3.8/lib/python/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
  File "/Users/markd/Library/Python/3.8/lib/python/site-packages/github_to_sqlite/cli.py", line 268, in repos
    utils.ensure_db_shape(db)
  File "/Users/markd/Library/Python/3.8/lib/python/site-packages/github_to_sqlite/utils.py", line 688, in ensure_db_shape
    ensure_foreign_keys(db)
  File "/Users/markd/Library/Python/3.8/lib/python/site-packages/github_to_sqlite/utils.py", line 682, in ensure_foreign_keys
    db[table].add_foreign_key(column, table2, column2)
  File "/Users/markd/Library/Python/3.8/lib/python/site-packages/sqlite_utils/db.py", line 2004, in add_foreign_key
    self.db.add_foreign_keys([(self.name, column, other_table, other_column)])
  File "/Users/markd/Library/Python/3.8/lib/python/site-packages/sqlite_utils/db.py", line 1019, in add_foreign_keys
    cursor.execute(
sqlite3.OperationalError: table sqlite_master may not be modified

[mdrovdahl]

Note, I do not get this issue using my Intel MacBook Pro =/

Environment

markd@Marks-MBP metabase % python3 --version
Python 3.9.13
markd@Marks-MBP metabase % sqlite3 --version
3.37.0 2021-12-09 01:34:53 9ff244ce0739f8ee52a3e9671adb4ee54c83c640b02e3f9d185fd2f9a179aapl
markd@Marks-MBP metabase % sqlite-utils --version
sqlite-utils, version 3.27
markd@Marks-MBP metabase % sqlite3 github.db 
SQLite version 3.37.0 2021-12-09 01:34:53
Enter ".help" for usage hints.
sqlite> .dbconfig
          defensive off
            dqs_ddl on
            dqs_dml on
        enable_fkey off
        enable_qpsg off
     enable_trigger on
        enable_view on
     fts3_tokenizer off
 legacy_alter_table on
 legacy_file_format off
     load_extension off
   no_ckpt_on_close off
     reset_database off
        trigger_eqp off
     trusted_schema on
    writable_schema off

[mdrovdahl]

I was able to fight through this by capturing the SQL commands from the add_foreign_keys() function in sqlite-utils and then executing them manually via the sqlite3 client, first setting PRAGMA writable_schema on and then updating the sqlite_master table. Still no clue why they were failing when run in context...

[lfdebrux]

I am getting the same error when using github-to-sqlite (which uses sqlite-utils internally). I am also using an M1 MacBook Pro, with macOS Monterey 12.5, with Python 3.10.6 for arm64 installed using pyenv. I have sqlite-utils 3.28 installed.

[wpears]

This bug affects me as well. Env:

Python 3.8.12
sqlite-utils, version 3.28
sqlite3 3.32.3
MacOS Big Sur 11.6.7
Intel

Similar to @mdrovdahl, I was able to work around this bug by piping the SQL string constructed in add_foreign_keys to the sqlite3 command itself. Specifically, if you're trying to patch this yourself, replace lines 1026-1039 of db.py in your site packages with something similar to the following:

print("PRAGMA writable_schema = 1;")
for table_name, new_sql in table_sql.items():
    print("UPDATE sqlite_master SET sql = '{}' WHERE name = '{}';".format(
         new_sql, table_name)
    )
print("PRAGMA writable_schema = 0;")
print("VACUUM;")

Then from your terminal:
db-to-sqlite "<connection string>" your.db --all > output.sql && sqlite3 your.db < output.sql

If you want to run this with -p, you'll have to actually open a file in code to write to instead of redirecting the output.

[ryascott]

I had the problem this morning when running:

Python==3.9.6 sqlite3.sqlite_version==3.37.0 sqlite-utils==3.30

I upgraded to:
Python ==3.10.8 sqlite3.sqlite_version==3.37.2 sqlite-utils==3.30

and the error did not appear anymore.

Hope this helps
Ryan

[hydrosquall]

Hi @ryascott, thanks for sharing this! How did you upgrade your sqlite3 version? I'm running into this issue (also on an m1) with

Python ==3.10.7 sqlite3.sqlite_version==3.37.0 sqlite-utils==3.30

Unfortunately, 3.10.8 isn't listed in pyenv so I'm unable to install it.

For me, the trigger is trying to use the add-foreign-key command on its own:

sqlite-utils add-foreign-key library.db book_creators creator_id creators id

Some stackoverflow searching suggests that brew installing sqlite may fix it ( https://stackoverflow.com/questions/26345972/how-do-i-upgrade-the-sqlite-version-used-by-pythons-sqlite3-module-on-mac ), but I don't want to risk breaking the version of sqlite used by some other system, I'd only like to upgrade sqlite3 inside my current virtual environment.

[hydrosquall]

A related historical problem:

tekartik/sqflite#525 (comment)

I wonder if the version of Sqlite or Python for Intel chip have defensive mode disabled by default, whereas M1 chips versions have it enabled.

[hydrosquall]

My current workaround is to use this library from a python script instead of as a CLI tool.

This lets me set the foreign key constraint at table creation time, instead of trying to modify an existing table. docs

I found this stackoverflow helpful, as it explained that Sqlite doesn't support modifying existing tables directly.

[Thomascountz]

@wpears' workaround also worked for me, but also required me to manually set PRAGMA writable_schema.

PRAGMA writable_schema = 1;
UPDATE sqlite_master SET sql = 'CREATE TABLE [foos] (...)'
PRAGMA writable_schema = 0;

$ python --version
Python 3.11.2

$ sqlite3 --version
3.41.2 2023-03-22 11:56:21 0d1fc92f94cb6b76bffe3ec34d69cffde2924203304e8ffc4155597af0c191da

$ sqlite-utils --version
sqlite-utils, version 3.30

[simonw]

Investigating this one now.

The sqlite-utils test suite passes without errors on my Python 3.11.2 installation... but it fails with this error on a Python 3.9.6 installation.

In the broken version's virtual environment directory I ran this:

cat pyvenv.cfg 
home = /Applications/Xcode.app/Contents/Developer/usr/bin
implementation = CPython
version_info = 3.9.6.final.0
virtualenv = 20.17.1
include-system-site-packages = false
base-prefix = /Applications/Xcode.app/Contents/Developer/Library/Frameworks/Python3.framework/Versions/3.9
base-exec-prefix = /Applications/Xcode.app/Contents/Developer/Library/Frameworks/Python3.framework/Versions/3.9
base-executable = /Applications/Xcode.app/Contents/Developer/usr/bin/python3

So it looks like the Xcode python3 has "defensive" mode turned on for SQLite.

As far as I can tell there's no way to turn it OFF again in Python.

My virtual environment that DOES work has this:

home = /opt/homebrew/opt/python@3.11/bin
implementation = CPython
version_info = 3.11.2.final.0
virtualenv = 20.17.1
include-system-site-packages = false
base-prefix = /opt/homebrew/opt/python@3.11/Frameworks/Python.framework/Versions/3.11
base-exec-prefix = /opt/homebrew/opt/python@3.11/Frameworks/Python.framework/Versions/3.11
base-executable = /opt/homebrew/opt/python@3.11/bin/python3.11

So the Python 3.11 I installed through Homebrew doesn't exhibit this bug.

[simonw]

I'll ask on the SQLite forum if it's possible to toggle that mode on and off using regular SQL. My hunch is that it isn't.

In which case sqlite-utils should at least know how to catch this error and display a much more readable error message, maybe with a link to further documentation.

A utility function that can detect this mode would be really useful too. I'd probably have to do a test that tries to modify sqlite_master on a new in-memory database to catch if it's possible or not.