sqlite3.OperationalError: table sqlite_master may not be modified #60

Tinche · 2023-06-19T14:51:38Z

Howdy,

on llm 0.4.1, installed via pipx on 3.11, on a Mac. After running llm init-db, llm logs throws:

Traceback (most recent call last):
  File "/Users/tintvrtkovic/.local/bin/llm", line 8, in <module>
    sys.exit(cli())
             ^^^^^
  File "/Users/tintvrtkovic/.local/pipx/venvs/llm/lib/python3.11/site-packages/click/core.py", line 1130, in __call__
    return self.main(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/tintvrtkovic/.local/pipx/venvs/llm/lib/python3.11/site-packages/click/core.py", line 1055, in main
    rv = self.invoke(ctx)
         ^^^^^^^^^^^^^^^^
  File "/Users/tintvrtkovic/.local/pipx/venvs/llm/lib/python3.11/site-packages/click/core.py", line 1657, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/tintvrtkovic/.local/pipx/venvs/llm/lib/python3.11/site-packages/click/core.py", line 1657, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/tintvrtkovic/.local/pipx/venvs/llm/lib/python3.11/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/tintvrtkovic/.local/pipx/venvs/llm/lib/python3.11/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/tintvrtkovic/.local/pipx/venvs/llm/lib/python3.11/site-packages/llm/cli.py", line 273, in logs_list
    migrate(db)
  File "/Users/tintvrtkovic/.local/pipx/venvs/llm/lib/python3.11/site-packages/llm/migrations.py", line 13, in migrate
    fn(db)
  File "/Users/tintvrtkovic/.local/pipx/venvs/llm/lib/python3.11/site-packages/llm/migrations.py", line 60, in m003_chat_id_foreign_key
    db["log"].add_foreign_key("chat_id", "log", "id")
  File "/Users/tintvrtkovic/.local/pipx/venvs/llm/lib/python3.11/site-packages/sqlite_utils/db.py", line 2155, in add_foreign_key
    self.db.add_foreign_keys([(self.name, column, other_table, other_column)])
  File "/Users/tintvrtkovic/.local/pipx/venvs/llm/lib/python3.11/site-packages/sqlite_utils/db.py", line 1116, in add_foreign_keys
    cursor.execute(
sqlite3.OperationalError: table sqlite_master may not be modified

Let me know if I can help further!

The text was updated successfully, but these errors were encountered:

simonw · 2023-06-23T14:40:52Z

Urgh, this issue has cropped up before here:

Extract columns cannot create foreign key relation: sqlite3.OperationalError: table sqlite_master may not be modified sqlite-utils#235

It's caused by a locked down version of Python and sqlite3 on macOS.

~~There's a workaround: you can run this:~~

llm install pysqlite3-binary

Actually no, that won't work because pysqlite3-binary doesn't have a wheel for macOS at the moment.

simonw · 2023-06-23T14:41:57Z

@Tinche do you get the same problem if you install using Homebrew?

brew install simonw/llm/llm

That should come with a copy of Python that doesn't have this problem.

simonw · 2023-06-23T15:32:51Z

I was able to replicate this on my own Mac like so. First I figured out the path to the macOS python3 executable:

type -a python3

python3 is /opt/homebrew/Caskroom/miniconda/base/bin/python3
python3 is /opt/homebrew/bin/python3
python3 is /usr/bin/python3

Then I installed llm into that version of Python's environment:

/usr/bin/python3 -m pip install llm

Then ran init-db with that command against a temporary database file:

LLM_LOG_PATH=/tmp/tmp.db /usr/bin/python3 -m llm init-db

Then tried running a prompt:

LLM_LOG_PATH=/tmp/tmp.db /usr/bin/python3 -m llm 'say cheese'

Truncated output:

/Users/simon/Library/Python/3.9/lib/python/site-packages/urllib3/__init__.py:34: NotOpenSSLWarning: urllib3 v2.0 only supports OpenSSL 1.1.1+, currently the 'ssl' module is compiled with 'LibreSSL 2.8.3'. See: https://github.com/urllib3/urllib3/issues/3020
  warnings.warn(
Cheese! 📷 🧀
Traceback (most recent call last):
...
  File "/Users/simon/Library/Python/3.9/lib/python/site-packages/llm/cli.py", line 170, in prompt
    log(no_log, system, prompt, "".join(response), model, chat_id, debug, start)
  File "/Users/simon/Library/Python/3.9/lib/python/site-packages/llm/cli.py", line 415, in log
    migrate(db)
  File "/Users/simon/Library/Python/3.9/lib/python/site-packages/llm/migrations.py", line 13, in migrate
    fn(db)
  File "/Users/simon/Library/Python/3.9/lib/python/site-packages/llm/migrations.py", line 60, in m003_chat_id_foreign_key
    db["log"].add_foreign_key("chat_id", "log", "id")
  File "/Users/simon/Library/Python/3.9/lib/python/site-packages/sqlite_utils/db.py", line 2155, in add_foreign_key
    self.db.add_foreign_keys([(self.name, column, other_table, other_column)])
  File "/Users/simon/Library/Python/3.9/lib/python/site-packages/sqlite_utils/db.py", line 1116, in add_foreign_keys
    cursor.execute(
sqlite3.OperationalError: table sqlite_master may not be modified

Refs simonw/llm#60

simonw · 2023-06-23T16:38:44Z

Here's a workaround that seems to work right now, using an experimental branch of sqlite-utils which can load sqlean:

llm install sqlean.py
llm uninstall sqlite-utils
llm install https://github.com/simonw/sqlite-utils/archive/refs/heads/sqlean.zip

On my machine that fixes the issue, as far as I can tell.

Tinche · 2023-06-24T20:08:41Z

Hm, the brew solution didn't work for me:

==> Installing llm from simonw/llm
==> Installing dependencies for simonw/llm/llm: ca-certificates, openssl@1.1, sqlite, xz and python3
==> Installing simonw/llm/llm dependency: ca-certificates
==> Pouring ca-certificates--2023-05-30.ventura.bottle.tar.gz
==> Regenerating CA certificate bundle from keychain, this may take a while...
🍺  /usr/local/Cellar/ca-certificates/2023-05-30: 3 files, 216.2KB
==> Installing simonw/llm/llm dependency: openssl@1.1
==> Pouring openssl@1.1--1.1.1u.ventura.bottle.tar.gz
🍺  /usr/local/Cellar/openssl@1.1/1.1.1u: 8,101 files, 18.5MB
==> Installing simonw/llm/llm dependency: sqlite
==> Pouring sqlite--3.42.0.ventura.bottle.tar.gz
🍺  /usr/local/Cellar/sqlite/3.42.0: 11 files, 4.5MB
==> Installing simonw/llm/llm dependency: xz
==> Pouring xz--5.4.3.ventura.bottle.tar.gz
🍺  /usr/local/Cellar/xz/5.4.3: 162 files, 2.5MB
==> Installing simonw/llm/llm dependency: python3
==> Pouring python@3.11--3.11.4.ventura.bottle.1.tar.gz
==> /usr/local/Cellar/python@3.11/3.11.4/bin/python3.11 -m ensurepip
Last 15 lines from /Users/tintvrtkovic/Library/Logs/Homebrew/python@3.11/post_install.01.python3.11:
             ^^^^^^^^^^^^
  File "/usr/local/Cellar/python@3.11/3.11.4/Frameworks/Python.framework/Versions/3.11/lib/python3.11/importlib/resources/_adapters.py", line 149, in _reader
    return self.spec.loader.get_resource_reader(self.spec.name)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<frozen importlib._bootstrap_external>", line 605, in _check_name_wrapper
  File "<frozen importlib._bootstrap_external>", line 1138, in get_resource_reader
  File "/usr/local/Cellar/python@3.11/3.11.4/Frameworks/Python.framework/Versions/3.11/lib/python3.11/importlib/readers.py", line 8, in <module>
    from .resources.readers import (
  File "/usr/local/Cellar/python@3.11/3.11.4/Frameworks/Python.framework/Versions/3.11/lib/python3.11/importlib/resources/readers.py", line 4, in <module>
    import zipfile
  File "/usr/local/Cellar/python@3.11/3.11.4/Frameworks/Python.framework/Versions/3.11/lib/python3.11/zipfile.py", line 14, in <module>
    import struct
  File "/usr/local/Cellar/python@3.11/3.11.4/Frameworks/Python.framework/Versions/3.11/lib/python3.11/struct.py", line 13, in <module>
    from _struct import *
ImportError: dlopen(/usr/local/lib/python3.11/lib-dynload/_struct.cpython-311-darwin.so, 0x0002): symbol not found in flat namespace '__PyFloat_Pack2'
Warning: The post-install step did not complete successfully
You can try again using:
  brew postinstall python@3.11
==> Summary
🍺  /usr/local/Cellar/python@3.11/3.11.4: 3,147 files, 57.9MB
==> Installing simonw/llm/llm
==> python3 -m venv --system-site-packages /usr/local/Cellar/llm/0.4.1/libexec
Last 15 lines from /Users/tintvrtkovic/Library/Logs/Homebrew/llm/01.python3:
2023-06-24 20:00:17 +0000

python3
-m
venv
--system-site-packages
/usr/local/Cellar/llm/0.4.1/libexec

Error: fork_exec() takes exactly 21 arguments (23 given)

If reporting this issue please do so at (not Homebrew/brew or Homebrew/homebrew-core):
  https://github.com/simonw/homebrew-llm/issues

The Python that was being used by pipx was installed via asdf, not sure if that matters.

Back to pipx, the llm install steps don't run for me:

❯ llm install sqlean.py
Usage: llm prompt [OPTIONS] [PROMPT]
Try 'llm prompt --help' for help.

Error: Got unexpected extra argument (sqlean.py)

The latest public version of llm doesn't seem to have an install subcommand? Maybe you meant pip?

I tried:

❯ pipx inject llm sqlean.py
  injected package sqlean-py into venv llm
done! ✨ 🌟 ✨

☸ dev1 (dev1) in ~ took 3s
❯ pipx uninject llm sqlite-utils
sqlite-utils is not in the llm venv. Skipping.

☸ dev1 (dev1) in ~
❯ pipx inject llm https://github.com/simonw/sqlite-utils/archive/refs/heads/sqlean.zip
  injected package sqlite-utils into venv llm
done! ✨ 🌟 ✨

But no luck, still the same issue.

Sorry for all this trouble 🙈

Closes #559 Closes #235 Refs simonw/llm#60 - Uses `sqlean` in place of `sqlite3` if `sqlean.py` is installed - Uses `sqlite-dump` if available and `conn.iterdump()` does not exist - New `with db.ensure_autocommit_off()` method for ensuring autocommit is off, used by `enable_wal()` and `disable_wal()`.

simonw · 2023-06-25T23:27:47Z

The latest public version of llm doesn't seem to have an install subcommand? Maybe you meant pip?

Aah yes, that llm install command isn't out in a release yet.

I'm publishing a new release of sqlite-utils soon which should hopefully fix the issue:

Use sqlean if available in environment sqlite-utils#560

Refs #235, #559, #560 Refs simonw/llm#60 Refs nalgeon/sqlean.py#1

simonw · 2023-06-26T01:41:12Z

OK, the new fix for this should be installing the latest sqlite-utils AND sqlean.py in the same virtual environment as llm:

https://sqlite-utils.datasette.io/en/stable/changelog.html#v3-33

Tinche · 2023-06-26T16:37:09Z

Yep, that fixed it. Thanks!

simonw · 2023-08-18T17:38:35Z

Even better fix: sqlite-utils 3.35 no longer modifies sqlite_master at all, so everything should Just Work from that version upwards.

For this fix: - simonw/sqlite-utils#577 Refs #60, #116, #123

simonw added the bug Something isn't working label Jun 19, 2023

simonw added the documentation Improvements or additions to documentation label Jun 23, 2023

simonw mentioned this issue Jun 23, 2023

Feature request: a safe subset version of this nalgeon/sqlean.py#1

Closed

simonw added a commit to simonw/sqlite-utils that referenced this issue Jun 23, 2023

Try sqlean too

fa484a9

Refs simonw/llm#60

simonw added a commit to simonw/sqlite-utils that referenced this issue Jun 26, 2023

Release 3.33

9d38925

Refs #235, #559, #560 Refs simonw/llm#60 Refs nalgeon/sqlean.py#1

Tinche closed this as completed Jun 26, 2023

simonw mentioned this issue Aug 17, 2023

Get add_foreign_keys() to work without modifying sqlite_master simonw/sqlite-utils#577

Closed

simonw added a commit that referenced this issue Aug 21, 2023

sqlite-utils>=3.35.0

9547b51

For this fix: - simonw/sqlite-utils#577 Refs #60, #116, #123

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

sqlite3.OperationalError: table sqlite_master may not be modified #60

sqlite3.OperationalError: table sqlite_master may not be modified #60

Tinche commented Jun 19, 2023

simonw commented Jun 23, 2023 •

edited

simonw commented Jun 23, 2023

simonw commented Jun 23, 2023

simonw commented Jun 23, 2023

Tinche commented Jun 24, 2023

simonw commented Jun 25, 2023

simonw commented Jun 26, 2023

Tinche commented Jun 26, 2023

simonw commented Aug 18, 2023

sqlite3.OperationalError: table sqlite_master may not be modified #60

sqlite3.OperationalError: table sqlite_master may not be modified #60

Comments

Tinche commented Jun 19, 2023

simonw commented Jun 23, 2023 • edited

simonw commented Jun 23, 2023

simonw commented Jun 23, 2023

simonw commented Jun 23, 2023

Tinche commented Jun 24, 2023

simonw commented Jun 25, 2023

simonw commented Jun 26, 2023

Tinche commented Jun 26, 2023

simonw commented Aug 18, 2023

simonw commented Jun 23, 2023 •

edited