llm: Error applying migration m008_reply_to_id_foreign_key

On a fresh install of llm, I am running into a sqlite error when running llm logs status:

Traceback (most recent call last):
  File "/Users/schaulka/.local/bin/llm", line 8, in <module>
    sys.exit(cli())
  File "/Users/schaulka/.local/pipx/venvs/llm/lib/python3.10/site-packages/click/core.py", line 1157, in __call__
    return self.main(*args, **kwargs)
  File "/Users/schaulka/.local/pipx/venvs/llm/lib/python3.10/site-packages/click/core.py", line 1078, in main
    rv = self.invoke(ctx)
  File "/Users/schaulka/.local/pipx/venvs/llm/lib/python3.10/site-packages/click/core.py", line 1688, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/Users/schaulka/.local/pipx/venvs/llm/lib/python3.10/site-packages/click/core.py", line 1688, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/Users/schaulka/.local/pipx/venvs/llm/lib/python3.10/site-packages/click/core.py", line 1434, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/Users/schaulka/.local/pipx/venvs/llm/lib/python3.10/site-packages/click/core.py", line 783, in invoke
    return __callback(*args, **kwargs)
  File "/Users/schaulka/.local/pipx/venvs/llm/lib/python3.10/site-packages/llm/cli.py", line 431, in logs_list
    migrate(db)
  File "/Users/schaulka/.local/pipx/venvs/llm/lib/python3.10/site-packages/llm/migrations.py", line 14, in migrate
    fn(db)
  File "/Users/schaulka/.local/pipx/venvs/llm/lib/python3.10/site-packages/llm/migrations.py", line 139, in m008_reply_to_id_foreign_key
    db["logs"].add_foreign_key("reply_to_id", "logs", "id")
  File "/Users/schaulka/.local/pipx/venvs/llm/lib/python3.10/site-packages/sqlite_utils/db.py", line 2280, in add_foreign_key
    self.db.add_foreign_keys([(self.name, column, other_table, other_column)])
  File "/Users/schaulka/.local/pipx/venvs/llm/lib/python3.10/site-packages/sqlite_utils/db.py", line 1174, in add_foreign_keys
    cast(Table, self[table]).transform(add_foreign_keys=fks)
  File "/Users/schaulka/.local/pipx/venvs/llm/lib/python3.10/site-packages/sqlite_utils/db.py", line 1733, in transform
    sqls = self.transform_sql(
  File "/Users/schaulka/.local/pipx/venvs/llm/lib/python3.10/site-packages/sqlite_utils/db.py", line 1903, in transform_sql
    self.db.create_table_sql(
  File "/Users/schaulka/.local/pipx/venvs/llm/lib/python3.10/site-packages/sqlite_utils/db.py", line 887, in create_table_sql
    raise AlterError(
sqlite_utils.db.AlterError: No such column: log.id

Best I can tell, this call fails because after renaming the table from log to logs, a foreign key remains pointing to the outdated log table name.

This call:

db["logs"].add_foreign_key("reply_to_id", "logs", "id")

ends up attempting to alter the table log, which no longer exists.

It may be related to this recent sqlite-utils change. I’m unsure if the bug is in sqlite-utils or llm. Possibly llm needs to adjust the previous foreign key directly?

About this issue

Original URL
State: closed
Created 10 months ago
Comments: 26 (18 by maintainers)

Commits related to this issue

Fix migration of foreign keys The tests currently fail, due to migration m008_reply_to_id_foreign_key failing. I suspect this is related to a recent change in squlite-utils [0]. This PR avoids that ... — committed to kevinschaul/llm by kevinschaul 10 months ago
Fix migration of foreign keys The tests currently fail, due to migration m008_reply_to_id_foreign_key failing. I suspect this is related to a recent change in squlite-utils [0]. This PR avoids that ... — committed to kevinschaul/llm by kevinschaul 10 months ago
Fix for broken migrations on sqlite-utils 3.35, refs #162 — committed to simonw/llm by simonw 10 months ago
Test for migrations fix in #162 — committed to simonw/llm by simonw 10 months ago
Fix for broken migrations on sqlite-utils 3.35, refs #162 — committed to simonw/llm by simonw 10 months ago
Test for migrations fix in #162 — committed to simonw/llm by simonw 10 months ago

Most upvoted comments

Yes! That worked for me too.

OK, so we know the root cause now. Thanks for helping me find that.

Next I need to figure out if this should be fixed just in llm or in sqlite-utils itself. I think it may require a sqlite-utils release.

simonw on Aug 19, 2023

Yes! I have replicated the bug.

Using this pattern: https://til.simonwillison.net/python/quick-testing-pyenv

pyenv install 3.10.4

Then to create a venv with it:

~/.pyenv/versions/3.10.4/bin/python -m venv venv310
source venv310/bin/activate
pip install llm

And now:

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

Gives me this error:

Traceback (most recent call last):
  File "<string>", line 5, in <module>
  File "/private/tmp/llm/llm/migrations.py", line 14, in migrate
    fn(db)
  File "/private/tmp/llm/llm/migrations.py", line 139, in m008_reply_to_id_foreign_key
    db["logs"].add_foreign_key("reply_to_id", "logs", "id")
  File "/private/tmp/llm/venv310/lib/python3.10/site-packages/sqlite_utils/db.py", line 2280, in add_foreign_key
    self.db.add_foreign_keys([(self.name, column, other_table, other_column)])
  File "/private/tmp/llm/venv310/lib/python3.10/site-packages/sqlite_utils/db.py", line 1174, in add_foreign_keys
    cast(Table, self[table]).transform(add_foreign_keys=fks)
  File "/private/tmp/llm/venv310/lib/python3.10/site-packages/sqlite_utils/db.py", line 1733, in transform
    sqls = self.transform_sql(
  File "/private/tmp/llm/venv310/lib/python3.10/site-packages/sqlite_utils/db.py", line 1903, in transform_sql
    self.db.create_table_sql(
  File "/private/tmp/llm/venv310/lib/python3.10/site-packages/sqlite_utils/db.py", line 887, in create_table_sql
    raise AlterError(
sqlite_utils.db.AlterError: No such column: log.id

simonw on Aug 19, 2023

Aha! Pragma legacy_alter_table: 1 looks suspicious. I’ll try that.

simonw on Aug 19, 2023