[GH-ISSUE #1144] Shiori won't start - database problem? #473

Open
opened 2026-02-25 23:34:18 +03:00 by kerem · 9 comments
Owner

Originally created by @LoisGNS on GitHub (Sep 23, 2025).
Original GitHub issue: https://github.com/go-shiori/shiori/issues/1144

Data

  • Shiori version:
    1.74
  • Database Engine:
    SQLite
  • Operating system:
    Windows 10 Pro
  • CLI/Web interface/Web Extension:
    CLI (but nothing else is working either, since server won't start)

Describe the bug / actual behavior

When I try to start the server from the command line using this .bat file:

e:
set SHIORI_DIR = [my preferred db location]
cd [location where shiori.exe is]
shiori --portable
shiori server -p [myportnumber]

I get this error message:
time="2025-09-23T02:30:36-04:00" level=fatal msg="Error running migration" error="failed to run migration from 0.4.0 to 0.5.0: failed to run transaction: failed to execute migration 0.4.0 to 0.5.0: SQL logic error: no such column: modified (1)"

Shiori had been working previously, but I suddenly got this startup error, suggesting there is some confusion between Shiori and my database.

Expected behavior

Shiori server should start as usual

To Reproduce

see above

Screenshots

If applicable, add screenshots to help explain your problem.

Notes

I have tried the following (with no improvement)

  1. Restore shiori.db from a recent backup
  2. Power-cycle computer
  3. Use a SQLite browser to see if there's some database problem I can see (for example, there is no column named "modified (1)" or anything similar
  4. I ran sqlite3 and did a .dump of the database - so whatever is going on with it, sqlite can still read it.
  5. Renamed shori.db to something else to prevent it from being used
  6. Start Shiori again
  7. It starts and creates a new, empty database (no "modified (1)" column there, either.
  8. Conclusion: there is some mismatch in expectations between the Shiori code and the database, but I can't figure out what it might be. Error mentions SQL and migration. I'm not migrating anything (I've only ever used 1.74, for example and don't use SQLite for anything else).
Originally created by @LoisGNS on GitHub (Sep 23, 2025). Original GitHub issue: https://github.com/go-shiori/shiori/issues/1144 ## Data - **Shiori version**: 1.74 - **Database Engine**: SQLite - **Operating system**: Windows 10 Pro - **CLI/Web interface/Web Extension**: CLI (but nothing else is working either, since server won't start) ## Describe the bug / actual behavior When I try to start the server from the command line using this .bat file: ``` e: set SHIORI_DIR = [my preferred db location] cd [location where shiori.exe is] shiori --portable shiori server -p [myportnumber] ``` I get this error message: time="2025-09-23T02:30:36-04:00" level=fatal msg="Error running migration" error="failed to run migration from 0.4.0 to 0.5.0: failed to run transaction: failed to execute migration 0.4.0 to 0.5.0: SQL logic error: no such column: modified (1)" Shiori had been working previously, but I suddenly got this startup error, suggesting there is some confusion between Shiori and my database. ## Expected behavior Shiori server should start as usual ## To Reproduce see above ## Screenshots If applicable, add screenshots to help explain your problem. ## Notes I have tried the following (with no improvement) 1. Restore shiori.db from a recent backup 2. Power-cycle computer 3. Use a SQLite browser to see if there's some database problem I can see (for example, there is no column named "modified (1)" or anything similar 4. I ran sqlite3 and did a .dump of the database - so whatever is going on with it, sqlite can still read it. 5. Renamed shori.db to something else to prevent it from being used 6. Start Shiori again 7. It starts and creates a new, empty database (no "modified (1)" column there, either. 8. Conclusion: there is some mismatch in expectations between the Shiori code and the database, but I can't figure out what it might be. Error mentions SQL and migration. I'm not migrating anything (I've only ever used 1.74, for example and don't use SQLite for anything else).
Author
Owner

@mirkoperillo commented on GitHub (Sep 23, 2025):

I cannot reproduce the problem because I'm not a Windows user, trying a clean test on Linux works as expected.
BTW these instructions don't seem correct.

shiori --portable
shiori server -p [myportnumber]

Try something like this

shiori server --portable -p [myportnumber]
<!-- gh-comment-id:3322823999 --> @mirkoperillo commented on GitHub (Sep 23, 2025): I cannot reproduce the problem because I'm not a Windows user, trying a clean test on Linux works as expected. BTW these instructions don't seem correct. ``` shiori --portable shiori server -p [myportnumber] ``` Try something like this ``` shiori server --portable -p [myportnumber] ```
Author
Owner

@LoisGNS commented on GitHub (Sep 23, 2025):

The above batch file worked just fine until today, though. I tried your version and get the same error message. I think something happened so that Shiori isn't recognizing the database, but I don't know my way around the source code (or the language it's written in, for that matter), nor very much about SQLite.

<!-- gh-comment-id:3323011207 --> @LoisGNS commented on GitHub (Sep 23, 2025): The above batch file worked just fine until today, though. I tried your version and get the same error message. I think something happened so that Shiori isn't recognizing the database, but I don't know my way around the source code (or the language it's written in, for that matter), nor very much about SQLite.
Author
Owner

@mirkoperillo commented on GitHub (Sep 23, 2025):

The above batch file worked just fine until today

That's good. So your are already using Shiori and from your past messages you have been using only the last official version (1.7.4).

Do you remember if anything changed shortly before this problem occurred?

<!-- gh-comment-id:3323580762 --> @mirkoperillo commented on GitHub (Sep 23, 2025): > The above batch file worked just fine until today That's good. So your are already using Shiori and from your past messages you have been using only the last official version (1.7.4). Do you remember if anything changed shortly before this problem occurred?
Author
Owner

@LoisGNS commented on GitHub (Sep 23, 2025):

No, nothing at all changed. 

---- On Tue, 23 Sep 2025 07:23:31 -0400 @.*** wrote ----

mirkoperillo left a comment https://github.com/go-shiori/shiori/issues/1144#issuecomment-3323580762

The above batch file worked just fine until today

That's good. So your are already using Shiori and from your past messages you have been using only the last official version (1.7.4).

Do you remember if anything changed shortly before this problem occurred?


Reply to this email directly, https://github.com/go-shiori/shiori/issues/1144#issuecomment-3323580762 , or https://github.com/notifications/unsubscribe-auth/ASHAB6YN3CBH6RXOA4A3NJD3UEUTHAVCNFSM6AAAAACHHQ3F3GVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZTGMRTGU4DANZWGI .
You are receiving this because you authored the thread.

<!-- gh-comment-id:3324043673 --> @LoisGNS commented on GitHub (Sep 23, 2025): No, nothing at all changed.  ---- On Tue, 23 Sep 2025 07:23:31 -0400 ***@***.*** wrote ---- mirkoperillo left a comment https://github.com/go-shiori/shiori/issues/1144#issuecomment-3323580762 The above batch file worked just fine until today That's good. So your are already using Shiori and from your past messages you have been using only the last official version (1.7.4). Do you remember if anything changed shortly before this problem occurred? — Reply to this email directly, https://github.com/go-shiori/shiori/issues/1144#issuecomment-3323580762 , or https://github.com/notifications/unsubscribe-auth/ASHAB6YN3CBH6RXOA4A3NJD3UEUTHAVCNFSM6AAAAACHHQ3F3GVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZTGMRTGU4DANZWGI . You are receiving this because you authored the thread.
Author
Owner

@mirkoperillo commented on GitHub (Sep 23, 2025):

Let's try to understand better the sqlite db structure you have.

Can you paste the structure of table bookmark ? And what is the data inside the column database_schema_version of table shiori_system ?

<!-- gh-comment-id:3324274985 --> @mirkoperillo commented on GitHub (Sep 23, 2025): Let's try to understand better the sqlite db structure you have. Can you paste the structure of table `bookmark` ? And what is the data inside the column `database_schema_version` of table `shiori_system` ?
Author
Owner

@LoisGNS commented on GitHub (Sep 24, 2025):

I used DB Browser for SQLite to get this info:

Database_schema_version has 2 records, both with the value 0.4.0

Below is the structure of the bookmark table.

CREATE TABLE "bookmark"( id INTEGER PRIMARY KEY AUTOINCREMENT, url TEXT NOT NULL, title TEXT NOT NULL, excerpt TEXT NOT NULL DEFAULT "", author TEXT NOT NULL DEFAULT "", public INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, has_content BOOLEAN DEFAULT FALSE NOT NULL, modified_at TEXT NULL, CONSTRAINT bookmark_url_UNIQUE UNIQUE(url) )

For comparison, when I renamed my db so that Shiori wouldn't find it, it created a new db as follows:

Database_schema Version:

1 record; value 0.6.0
(Obviously different)

Bookmark table: (looks the same)

CREATE TABLE "bookmark"( id INTEGER PRIMARY KEY AUTOINCREMENT, url TEXT NOT NULL, title TEXT NOT NULL, excerpt TEXT NOT NULL DEFAULT "", author TEXT NOT NULL DEFAULT "", public INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, has_content BOOLEAN DEFAULT FALSE NOT NULL, modified_at TEXT NULL, CONSTRAINT bookmark_url_UNIQUE UNIQUE(url) )

If I remember correctly, in testing just yesterday I tried fiddling with the database_schema version value (including setting it to 0.6.0 and making sure there was only one record), but kept getting the same error. Just now, I did it again, setting it to match the one found in the empty db (one record, reading 0.6.0), and lo & behold it started working again. I'm sure I'd tried that exact same thing before with no success.

Assuming this lasts (at least for a while), what can I do to prevent future problems?

<!-- gh-comment-id:3326241960 --> @LoisGNS commented on GitHub (Sep 24, 2025): I used DB Browser for SQLite to get this info: Database_schema_version has 2 records, both with the value 0.4.0 Below is the structure of the bookmark table. CREATE TABLE "bookmark"( id INTEGER PRIMARY KEY AUTOINCREMENT, url TEXT NOT NULL, title TEXT NOT NULL, excerpt TEXT NOT NULL DEFAULT "", author TEXT NOT NULL DEFAULT "", public INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, has_content BOOLEAN DEFAULT FALSE NOT NULL, modified_at TEXT NULL, CONSTRAINT bookmark_url_UNIQUE UNIQUE(url) ) For comparison, when I renamed my db so that Shiori wouldn't find it, it created a new db as follows: Database_schema Version: 1 record; value 0.6.0 (Obviously different) Bookmark table: (looks the same) CREATE TABLE "bookmark"( id INTEGER PRIMARY KEY AUTOINCREMENT, url TEXT NOT NULL, title TEXT NOT NULL, excerpt TEXT NOT NULL DEFAULT "", author TEXT NOT NULL DEFAULT "", public INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, has_content BOOLEAN DEFAULT FALSE NOT NULL, modified_at TEXT NULL, CONSTRAINT bookmark_url_UNIQUE UNIQUE(url) ) If I remember correctly, in testing just yesterday I tried fiddling with the database_schema version value (including setting it to 0.6.0 and making sure there was only one record), but kept getting the same error. Just now, I did it again, setting it to match the one found in the empty db (one record, reading 0.6.0), and lo & behold it started working again. I'm sure I'd tried that exact same thing before with no success. Assuming this lasts (at least for a while), what can I do to prevent future problems?
Author
Owner

@mirkoperillo commented on GitHub (Sep 24, 2025):

Interesting.

So when you run Shiori in a clean situation and Shiori creates a new one empty db, everything goes ok and no errors are produced. Is it right ?

That's what I expect looking the data inside the new empty db.

Instead the first db you reported (I think it is the backuped one) is corrupted.

Database_schema_version has 2 records, both with the value 0.4.0

Looking the code this should be impossible, that table should contains only a record
github.com/go-shiori/shiori@ca949c5dab/internal/database/sqlite.go (L202)

Migration failure is between 0.4.0 to 0.5.0 so it involved migration file sqlite/0003_uniq_id

The error is there is no column named "modified
and I understand it because the bookmark structure you reported contains created_at instead of modified

CREATE TABLE "bookmark"( id INTEGER PRIMARY KEY AUTOINCREMENT, url TEXT NOT NULL, title TEXT NOT NULL, excerpt TEXT NOT NULL DEFAULT "", author TEXT NOT NULL DEFAULT "", public INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, has_content BOOLEAN DEFAULT FALSE NOT NULL, modified_at TEXT NULL, CONSTRAINT bookmark_url_UNIQUE UNIQUE(url) )

The strange thing is that the last migration currently present in Shiori
newFileMigration("0.5.0", "0.6.0", "sqlite/0004_created_time") is the responsible of renaming modified field in created_at in table bookmark

Content of sqlite/0004_created_time

ALTER TABLE bookmark
RENAME COLUMN modified to created_at;

In conclusion: your dumped db is corrupted, it seems it were a problem during the updated of database_schema_version table, because all migration seems to be applied correctly.

If I remember correctly, in testing just yesterday I tried fiddling with the database_schema version value (including setting it to 0.6.0 >and making sure there was only one record), but kept getting the same error. Just now, I did it again, setting it to match the one > found in the empty db (one record, reading 0.6.0), and lo & behold it started working again

Reading the code I think your workaround is good.

Assuming this lasts (at least for a while), what can I do to prevent future problems?

I don't know, because this seems a bug and at the moment not reproducible. I think the best think is to maintain a updated backup of the db.

<!-- gh-comment-id:3327682099 --> @mirkoperillo commented on GitHub (Sep 24, 2025): Interesting. So when you run Shiori in a clean situation and Shiori creates a new one empty db, everything goes ok and no errors are produced. Is it right ? That's what I expect looking the data inside the new empty db. Instead the first db you reported (I think it is the backuped one) is corrupted. > Database_schema_version has 2 records, both with the value 0.4.0 Looking the code this should be impossible, that table should contains only a record https://github.com/go-shiori/shiori/blob/ca949c5dabbd23e1e3a11ce9afae2dd766eb0f60/internal/database/sqlite.go#L202 Migration failure is between 0.4.0 to 0.5.0 so it involved migration file `sqlite/0003_uniq_id` The error is `there is no column named "modified` and I understand it because the `bookmark` structure you reported contains `created_at` instead of `modified` ``` CREATE TABLE "bookmark"( id INTEGER PRIMARY KEY AUTOINCREMENT, url TEXT NOT NULL, title TEXT NOT NULL, excerpt TEXT NOT NULL DEFAULT "", author TEXT NOT NULL DEFAULT "", public INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, has_content BOOLEAN DEFAULT FALSE NOT NULL, modified_at TEXT NULL, CONSTRAINT bookmark_url_UNIQUE UNIQUE(url) ) ``` The strange thing is that the last migration currently present in Shiori `newFileMigration("0.5.0", "0.6.0", "sqlite/0004_created_time")` is the responsible of renaming `modified` field in `created_at` in table `bookmark` Content of `sqlite/0004_created_time` ``` ALTER TABLE bookmark RENAME COLUMN modified to created_at; ``` In conclusion: your dumped db is corrupted, it seems it were a problem during the updated of `database_schema_version` table, because all migration seems to be applied correctly. > If I remember correctly, in testing just yesterday I tried fiddling with the database_schema version value (including setting it to 0.6.0 >and making sure there was only one record), but kept getting the same error. Just now, I did it again, setting it to match the one > found in the empty db (one record, reading 0.6.0), and lo & behold it started working again Reading the code I think your workaround is good. > Assuming this lasts (at least for a while), what can I do to prevent future problems? I don't know, because this seems a bug and at the moment not reproducible. I think the best think is to maintain a updated backup of the db.
Author
Owner

@LoisGNS commented on GitHub (Sep 25, 2025):

Yes, the "clean" new db worked ok in that it allowed Shiori to start up without the error message. Since I didn't really want to start over, I didn't try to actually use the new db.

Both the new clean db and my existing db have exactly the same bookmark table folder structure. If you look closely to what I posted:

Mine:

CREATE TABLE "bookmark"( id INTEGER PRIMARY KEY AUTOINCREMENT, url TEXT NOT NULL, title TEXT NOT NULL, excerpt TEXT NOT NULL DEFAULT "", author TEXT NOT NULL DEFAULT "", public INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, has_content BOOLEAN DEFAULT FALSE NOT NULL, modified_at TEXT NULL, CONSTRAINT bookmark_url_UNIQUE UNIQUE(url) )

Clean:

CREATE TABLE "bookmark"( id INTEGER PRIMARY KEY AUTOINCREMENT, url TEXT NOT NULL, title TEXT NOT NULL, excerpt TEXT NOT NULL DEFAULT "", author TEXT NOT NULL DEFAULT "", public INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, has_content BOOLEAN DEFAULT FALSE NOT NULL, modified_at TEXT NULL, CONSTRAINT bookmark_url_UNIQUE UNIQUE(url) )

They both have created_at and modified_at fields, and neither has a modified field. So the code you clipped doesn't seem like it would do anything (and I wonder, in that case, what it's for - maybe a relic of some previous version? And why would a program create a table for starters & then include code to rename it? Why not use the desired name in the first place? That part doesn't make much sense, but I'm not familiar at all with the code base or structure, so what do I know.

ALTER TABLE bookmark
RENAME COLUMN modified to created_at;

At any rate, I have added a command at the start of my batch file to copy shiori.db to a new file with the current date as part of the new name, so that I have a set of backups. I'll need to decide how many to keep, since my db is about 38mb, so keeping too many will take up extra space, even if I zip them.

<!-- gh-comment-id:3331669436 --> @LoisGNS commented on GitHub (Sep 25, 2025): Yes, the "clean" new db worked ok in that it allowed Shiori to start up without the error message. Since I didn't really want to start over, I didn't try to actually use the new db. Both the new clean db and my existing db have exactly the same bookmark table folder structure. If you look closely to what I posted: Mine: > CREATE TABLE "bookmark"( id INTEGER PRIMARY KEY AUTOINCREMENT, url TEXT NOT NULL, title TEXT NOT NULL, excerpt TEXT NOT NULL DEFAULT "", author TEXT NOT NULL DEFAULT "", public INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, has_content BOOLEAN DEFAULT FALSE NOT NULL, modified_at TEXT NULL, CONSTRAINT bookmark_url_UNIQUE UNIQUE(url) ) Clean: > CREATE TABLE "bookmark"( id INTEGER PRIMARY KEY AUTOINCREMENT, url TEXT NOT NULL, title TEXT NOT NULL, excerpt TEXT NOT NULL DEFAULT "", author TEXT NOT NULL DEFAULT "", public INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, has_content BOOLEAN DEFAULT FALSE NOT NULL, modified_at TEXT NULL, CONSTRAINT bookmark_url_UNIQUE UNIQUE(url) ) They both have `created_at` and `modified_at` fields, and neither has a `modified `field. So the code you clipped doesn't seem like it would do anything (and I wonder, in that case, what it's for - maybe a relic of some previous version? And why would a program create a table for starters & then include code to rename it? Why not use the desired name in the first place? That part doesn't make much sense, but I'm not familiar at all with the code base or structure, so what do I know. ``` ALTER TABLE bookmark RENAME COLUMN modified to created_at; ``` At any rate, I have added a command at the start of my batch file to copy shiori.db to a new file with the current date as part of the new name, so that I have a set of backups. I'll need to decide how many to keep, since my db is about 38mb, so keeping too many will take up extra space, even if I zip them.
Author
Owner

@mirkoperillo commented on GitHub (Sep 26, 2025):

Both the new clean db and my existing db have exactly the same bookmark table folder structure. If you look closely to what I posted:

Yes, I saw that. Actually the problem in your case is that all migration steps went well, but the update of the schema version failed. So every time Shiori starts, it thinks that your db structure is older than it actually is and therefore tries to apply migration steps starting from the perceived version (the one written in the database_schema_version up to the latest) and fails.

And why would a program create a table for starters & then include code to rename it? Why not use the desired name in the first place?

This concerns software engineering. Every software evolves over time as do databases. Db migration is a tool to automate the evolution of the database structure. It is a best practice.

At any rate, I have added a command at the start of my batch file to copy shiori.db to a new file with the current date as part of the new name, so that I have a set of backups. I'll need to decide how many to keep, since my db is about 38mb, so keeping too many will take up extra space, even if I zip them.

That's a good strategy

<!-- gh-comment-id:3339053935 --> @mirkoperillo commented on GitHub (Sep 26, 2025): > Both the new clean db and my existing db have exactly the same bookmark table folder structure. If you look closely to what I posted: Yes, I saw that. Actually the problem in your case is that all migration steps went well, but the update of the schema version failed. So every time Shiori starts, it thinks that your db structure is older than it actually is and therefore tries to apply migration steps starting from the perceived version (the one written in the `database_schema_version` up to the latest) and fails. > And why would a program create a table for starters & then include code to rename it? Why not use the desired name in the first place? This concerns software engineering. Every software evolves over time as do databases. Db migration is a tool to automate the evolution of the database structure. It is a best practice. > At any rate, I have added a command at the start of my batch file to copy shiori.db to a new file with the current date as part of the new name, so that I have a set of backups. I'll need to decide how many to keep, since my db is about 38mb, so keeping too many will take up extra space, even if I zip them. That's a good strategy
Sign in to join this conversation.
No milestone
No project
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference
starred/shiori#473
No description provided.