sqlite_utils.db.AlterError: No such column: log.id

[hydrosquall]

Hi,

I recently upgraded sqlite-utils from 3.33 to 3.35 in the environment that has llm installed

I'm getting the error

sqlite_utils.db.AlterError: No such column: log.id

I'm assuming there is a database used for logging that might have had the wrong schema so I tried running llm logs status, and hit a similar error.

I tried deleting the database file and the regenerating a new log using the llm mlc package, but the original error persists.

Any ideas for what I could do to fix the logging capability?

Metadata

• M1 Mac
• llm version 0.7
• Python 3.9.9
• sqlite-utils 3.35

Here's the schema of the table that does exist, using

datasette "$(llm logs path)"

[hydrosquall]

Upon closer inspection, this may be a duplicate of #162 .

[kevinschaul]

I think you're right @hydrosquall. By chance are you able to see if #163 fixes it for you?

[simonw]

I'm having trouble replicating this issue. Could you confirm your SQLite version too, along with the compile options?

Could you try running this and report back the results:

import sqlite3
import sys
conn = sqlite3.connect(":memory:")
print("Python version:", sys.version)
print("SQLite version:", conn.execute("select sqlite_version()").fetchone()[0])
print("SQLite compile options\n  " + "\n  ".join(r[0] for r in conn.execute("pragma compile_options").fetchall()))
for pragma in (
        "foreign_keys", "defer_foreign_keys", "ignore_check_constraints", "legacy_alter_table",
        "recursive_triggers", "writable_schema",
    ):
    output = conn.execute("pragma %s" % pragma).fetchone()
    print("Pragma {}: {}".format(pragma, output[0]))
'

Here's what I get on a system that I've not yet managed to replicate the bug on:

Python version: 3.10.10 (main, Mar 21 2023, 13:41:05) [Clang 14.0.6 ]
SQLite version: 3.41.1
SQLite compile options
  ATOMIC_INTRINSICS=1
  COMPILER=clang-14.0.6
  DEFAULT_AUTOVACUUM
  DEFAULT_CACHE_SIZE=-2000
  DEFAULT_FILE_FORMAT=4
  DEFAULT_JOURNAL_SIZE_LIMIT=-1
  DEFAULT_MMAP_SIZE=0
  DEFAULT_PAGE_SIZE=4096
  DEFAULT_PCACHE_INITSZ=20
  DEFAULT_RECURSIVE_TRIGGERS
  DEFAULT_SECTOR_SIZE=4096
  DEFAULT_SYNCHRONOUS=2
  DEFAULT_WAL_AUTOCHECKPOINT=1000
  DEFAULT_WAL_SYNCHRONOUS=2
  DEFAULT_WORKER_THREADS=0
  ENABLE_COLUMN_METADATA
  ENABLE_DBSTAT_VTAB
  ENABLE_FTS3
  ENABLE_FTS3_TOKENIZER
  ENABLE_FTS4
  ENABLE_FTS5
  ENABLE_GEOPOLY
  ENABLE_MATH_FUNCTIONS
  ENABLE_RTREE
  ENABLE_UNLOCK_NOTIFY
  MALLOC_SOFT_LIMIT=1024
  MAX_ATTACHED=10
  MAX_COLUMN=2000
  MAX_COMPOUND_SELECT=500
  MAX_DEFAULT_PAGE_SIZE=8192
  MAX_EXPR_DEPTH=10000
  MAX_FUNCTION_ARG=127
  MAX_LENGTH=1000000000
  MAX_LIKE_PATTERN_LENGTH=50000
  MAX_MMAP_SIZE=0x7fff0000
  MAX_PAGE_COUNT=1073741823
  MAX_PAGE_SIZE=65536
  MAX_SQL_LENGTH=1000000000
  MAX_TRIGGER_DEPTH=1000
  MAX_VARIABLE_NUMBER=250000
  MAX_VDBE_OP=250000000
  MAX_WORKER_THREADS=8
  MUTEX_PTHREADS
  SECURE_DELETE
  SYSTEM_MALLOC
  TEMP_STORE=1
  THREADSAFE=1
Pragma foreign_keys: 0
Pragma defer_foreign_keys: 0
Pragma ignore_check_constraints: 0
Pragma legacy_alter_table: 0
Pragma recursive_triggers: 0
Pragma writable_schema: 0

[simonw]

Turned that recipe into a TIL :https://til.simonwillison.net/sqlite/python-sqlite-environment

[simonw]

Also try this:

python -c '
from llm.migrations import migrate
from sqlite_utils import Database
db = Database(memory=True)
migrate(db)
print(db.schema)
'

That purely runs the migrations against an in-memory database, and should show if you get that error or not for that simplest possible case.

[simonw]

I think this is fixed:

• Error applying migration m008_reply_to_id_foreign_key #162 (comment)

[hydrosquall]

Confirmed that upgrading to 0.7.1 and deleting the old database together have successfully fixed the issue. Thanks for the fast response and documenting how to debug similar issues for next time!