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/[email protected]/bin
implementation = CPython
version_info = 3.11.2.final.0
virtualenv = 20.17.1
include-system-site-packages = false
base-prefix = /opt/homebrew/opt/[email protected]/Frameworks/Python.framework/Versions/3.11
base-exec-prefix = /opt/homebrew/opt/[email protected]/Frameworks/Python.framework/Versions/3.11
base-executable = /opt/homebrew/opt/[email protected]/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.

[simonw]

This seems to work:

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
PRAGMA writable_schema = 1;
UPDATE sqlite_master SET sql = 'CREATE TABLE [foos] (id integer primary key)';
PRAGMA writable_schema = 0;
""")

It succeeds on my Python 3.11 and raises the following exception on my broken Python 3.9:

sqlite3.OperationalError: table sqlite_master may not be modified

Removing the PRAGMA writable_schema = 1; causes the same exception to be raised on both Pythons.

[simonw]

I also tested this against the current ubuntu:latest Docker image (on an M2 Mac), in Python 3.10 and 3.11:

docker run -it ubuntu:latest /bin/bash

Then in the container:

apt-get update
apt-get install python3
python3
# pasted in the above recipe
apt install software-properties-common
add-apt-repository ppa:deadsnakes/ppa
apt install python3.11
python3.11 
# pasted it in again

In both cases the Python code did not raise an exception, which suggests that on Ubuntu those two Python versions do not have the defensive mode set.

[simonw]

Also checked the official Datasette Docker image - I had to run that in Codespaces because it doesn't currently work on my M2 Mac:

codespace@codespaces-112c61:/workspaces/sqlite-utils$ docker pull datasetteproject/datasette
Using default tag: latest
...
codespace@codespaces-112c61:/workspaces/sqlite-utils$ docker run -it datasetteproject/datasette /
bin/bash
root@75ba34f501ec:/# python
Python 3.11.0 (main, Dec  6 2022, 13:31:55) [GCC 10.2.1 20210110] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
.executescript("""
PRAGMA writable_schema = 1;
UPDATE sqlite_master SET sql = 'CREATE TABLE [foos] (id integer primary key)';
PRAGMA writable_schema = 0;
""")>>> db = sqlite3.connect(":memory:")
>>> db.executescript("""
... PRAGMA writable_schema = 1;
... UPDATE sqlite_master SET sql = 'CREATE TABLE [foos] (id integer primary key)';
... PRAGMA writable_schema = 0;
... """)
<sqlite3.Cursor object at 0x7fd9b0561140>
>>>

So that confirms that the official image also has a Python with a SQLite that's not in defensive mode.

[simonw]

Ideally a workaround for this right now would be to install pysqlite3 in the same virtual environment:

sqlite-utils install pysqlite3-binary

But pysqlite3-binary doesn't yet ship a wheel for macOS so this probably won't work for most people.

The "easiest" fix at the moment is to use Python from Homebrew - so brew install sqlite-utils for example won't suffer from this problem. Not a great solution for people who aren't using Homebrew though!

[simonw]

https://sqlite-utils.datasette.io/en/stable/changelog.html#v3-33 - upgrading to sqlite-utils>=3.33 and then installing both sqlean.py and sqlite-dump in the same virtual environment as sqlite-utils should fix this issue.

[simonw]

I just tested this in a brand new virtual environment using the macOS Python 3:

pipenv shell --python /Applications/Xcode.app/Contents/Developer/usr/bin/python3

Then in that virtual environment I ran:

pip install sqlite-utils
# Confirm the right one is on the path:
which sqlite-utils

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

This threw the same error reported above. Then I did this:

rm meteorites.db
pip install sqlean.py

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

And that second time it worked correctly.