LWT 3.4.1: Databases repair their own foreign keys
A database repair release. Worth taking for anyone whose install has been upgraded more than once.
Foreign keys earlier upgrades dropped are put back
Every release before 3.4.0 dropped the foreign keys to make way for its migrations and put back only the ones a pending migration happened to own. The constraints created by migrations that had already run were gone for good, and no later upgrade could recover them: a 3.3.0 database had 14 of the 28 constraints the schema declares.
Nothing looked broken, which is why it went unnoticed for so long. What was actually missing:
- deleting a text left its occurrences and sentences behind
- no orphan protection on
word_tag_map,books,local_dictionaries,news_feedsorwhisper_jobs
3.4.0 stopped the bleeding — an upgrade no longer destroys constraints — but preserving what is there cannot bring back what is already gone. SchemaConstraints::FOREIGN_KEYS now states what the current schema should have, and the upgrade adds whatever is absent, so an affected database mends itself on first boot. No manual SQL.
Rows that a missing constraint would have prevented are already in these databases, so constraints are added with checks disabled: existing data is left exactly as it is, and writes are gated from here on. A constraint the engine still refuses is listed on the admin Server Data page instead of failing the upgrade.
What it costs
Timed on a deliberately large database — 3.05M occurrences, 155k terms, 760 texts, 210 MB:
| First boot after upgrading | 8 to 10 seconds, once |
| Later upgrades on the same database | under a second |
Creating the constraints is not the expensive part; each takes well under a second, because the columns already carry a covering index. The cost is the column realignment introduced in 3.4.0, which rewrites the table — so a table's columns are now realigned in a single ALTER rather than one each. word_occurrences has four of them, and rewriting a three-million-row table four times took 27 seconds where one pass takes under ten.
Expect that to scale roughly with your largest table. Nothing is asked of you either way: the pause happens once, on the first page load after the upgrade.
Upgrading is the usual docker compose pull && docker compose up -d --force-recreate. Full detail in CHANGELOG.md.