Visual indicator: Yellow exclamation point warning appears in 1.9.0 interface
Attempted Migration to 1.17.3
Why migration was attempted: To resolve search issues and upgrade to current version
Migration Attempt #1: Direct Upgrade
Process:
Upgraded from Tropy 1.9.0 to 1.17.3
Attempted to open existing Spanish Flu.tpy database
Result: Complete failure - database won’t open in 1.17.3
Error Log:
{"level":40,"time":1769813421571,"type":"renderer","name":"project","stack":"*** in database main ***\nTree 21 page 21 cell 168: Rowid 3848290697217 out of order\nTree 21 page 21 cell 167: Rowid 3710851743746 out of order\nmalformed inverted index for FTS5 table main.fts_notes","msg":"integrity check failed!"}
{"level":40,"time":1769813421572,"type":"renderer","name":"project","stack":"Error: 2 integrity check(s) failed\n at Connection.check (file:///Applications/Tropy.app/Contents/Resources/app.asar/lib/bootstrap.mjs:17818:15)","msg":"unexpected error in *project.open"}
Key corruption details:
Tree 21 page 21: Rowid ordering corruption
Malformed inverted index for FTS5 table (full-text search) - explains search issues in 1.9.0
Migration Attempt #2: Fresh Import via JSON-LD Export
Process:
Exported all items from 1.9.0 database as JSON-LD (62MB file)
Created fresh project in 1.17.3
Imported all photos (~27,000 files)
Edited JSON file to update file paths (find/replace: 111,111 instances)
Imported edited JSON-LD metadata
Results:
What Works:
All photos imported and display correctly
All tags imported and functional
All notes imported and visible
Item metadata (dates, locations, etc.) intact
What Doesn’t Work:
Problem 1 - Search Still Broken:
Same search issue persists in 1.17.3 after JSON import
Example: Tag with 68 items only returns 2 items when searched
Corrupted FTS5 search index appears to transfer through JSON export/import
Problem 2 - Folder/List Organization Missing:
Original database had extensive folder/list organization
After JSON import, all folder/list structures missing
All items show as “Unlisted Items”
Only tags survived export/import
Problem 3 - Duplicate Items with Broken Photo Links:
Many items appear as duplicates with different photo link statuses:
Working duplicate: All photos display correctly
Broken duplicate: First photo shows, remaining photos show exclamation marks (“photo not found”)
Hundreds of photos remain as unorganized single-file items (IMG_1234.jpg)
Despite path find/replace (111,111 corrections), duplicates still created
Can the FTS5 search index be rebuilt in 1.9.0 without affecting data? This would solve the primary issue and allow continued use of stable 1.9.0 database.
If 1.9.0 repair isn’t possible, what’s the proper migration path to 1.17.3 for a database with corrupted search index?
Why does JSON-LD export/import transfer search index corruption? Is there a way to export data without carrying over corrupted indexes?
Why don’t folder/list structures export in JSON-LD format? Is there an alternative export method that preserves organizational structure?
Files Available for Debugging
Original Spanish Flu.tpy (1.9.0 database - functional except search)
Fresh Spanish Flu 2025.tpy (1.17.3 database - post-import with issues)
flu_export_CURRENT.json (62MB JSON-LD export)
Full error logs from migration attempts
Preferred Outcome
Repair search functionality in existing 1.9.0 database to continue using stable, fully-organized database or cleanly and completely migrate everything to 1.17.
Hi! Sorry, should have provided some context. I’m not a technical person, so I worked with GPT on this issue. Basically, I have a huge database of 1918 flu survivor letters and now have a critical problem either making my version (1.9) work (no search feature) or migrating to 1.17. Sorry for not providing context! Thanks–John Eicher
I’ll briefly tackle the final four questions, but I suspect we’ll have to tackle this differently.
The database schema is designed in a way to allow for the FTS search index to be rebuilt. So, yes, that’s totally possible unless the db file itself is corrupted or damaged. We added scripts to do this, but these may not be available in 1.9.0 – these are just a couple lines in any SQLite client though so it shouldn’t be difficult to do.
All the migrations carry over so a migration from 1.9 to 1.17 should be possible.
It shouldn’t. The search index is not part of the JSON-LD export, it is re-created as you import the data.
Lists are currently not imported from the JSON data for various reasons, but we’re considering to add an option to allow this in the future.
Having said that, tags are not part of the current FTS index, so the search query and the tag are two separate factors in the current search interface. To filter by tag, you can select one or more (with modifier keys) tags in the sidebar. If you add a search query, only items with the selected tags will be included in the search results.
In any case, if you could share the tpy file with us, we can take a look at it to see why the migration doesn’t work and hopefully fix that issue.
Thank you so much for the quick and helpful response!!! Much appreciated
Search Issue Clarification:
You mentioned that tags aren’t part of the FTS index, which makes sense. However, I’m experiencing broken search for text that exists in my notes (not tags).
Specific example:
-I have notes containing the phrase “NOTE: SUBSTANTIAL NARRATIVE”
-In Tropy 1.9.0: Searching this exact phrase returns zero results
-In Tropy 1.17.3 (after JSON import): Searching for the phrase returns some items–hundreds, in fact–but there should only be 90 such instances. … removing the “NOTE:” part, “SUBSTANTIAL NARRATIVE” gives a partial list, but only the items with this in the “notes” section.
So the search index on 1.9 does appear to be corrupted, as your diagnosis suggested. The search index in 1.17 seems to work (if searching with a “:” is a no-no), but 1.17 doesn’t have my folder list. So either I use 1.17 and lose my folders or I use 1.9 and can’t search.
List/Folder Migration - Critical Issue
Regarding point #4 (lists not importing from JSON): This is actually a major blocker for migration to 1.17.3.
My database has dozens of top-level folders with hundreds of subfolders organizing items by archival research trips (by country, archive, collection)
Without these folder structures migrating, I’m essentially locked into 1.9.0 permanently.
Would it be possible to:
Add list/folder import to JSON-LD in a future update? This would make migration viable.
Or provide an alternative export format that preserves folder structures?
Or include a migration tool that carries over lists when upgrading from 1.9 to 1.17?
File Sharing
I’m happy to share my .tpy file for diagnosis. What’s the best way to send it to you? (It’s approximately 70mb for the v.1.9 file and 131mb for the v.1.17 file… not sure why they’re so wildly different in size…
Are the search results more stable if you wrap your query quotations marks?
Just to clarify, by migrating the database I mean using the scripts built into Tropy that will automatically update your project to the latest version. Exporting and importing is something else, you’re really re-importing everything into a new project. Once we add an option to include lists in this, it will be possible to create a copy of an entire project via copy/paste – but it’s still a copy. What we should try to get working, ideally, is migrating the original project file.
70 MB is probably too much for direct upload on here. But you can share it by other means and share a link here. If you don’t have a preferred service for this, I’d recommend the successor to Firefox Send.
Searching "SUBSTANTIAL NARRATIVE" with quotes does help stabilize results
Search in 1.17.3 appears functional with proper syntax
However, search in 1.9.0 is completely non-functional - returns zero results regardless of search terms or syntax.
Migration vs. Import:
I understand the distinction now. What I did was export/import (creating a new project), not migration (upgrading the original file).
I’d prefer to get migration working - upgrading my original 1.9.0 database to 1.17.3 so I keep my folder/list structures intact. That would solve both the search issue and preserve my organizational system.
File Sharing:
I’ve uploaded my original 1.9.0 .tpy file (70MB) “Spanish Flu (OLD)” and my new 1.17 .tpy file “1918 Flu” via WeTransfer:
Great! I looked at the old file and, as you’ve already noticed, the notes full-text index was damaged. Because of this damage the database integrity check failed. If the integrity check fails, Tropy aborts migration as a precaution. That explains why the migration failed.
The issue with the full-text index isn’t crucial, because the index can be easily rebuilt. In the current release we expose this as ‘Reindex Project Database’ in the developer menu. I rebuilt the index and afterwards, when opening the project in 1.17.3 the project db was migrated without any issues. I’m linking to the old file, before migration, but with the restored index here:
If you open this version in 1.17.3 I hope it should update the db with no further problem. After you migrate the project, I would generally recommend not opening it with version 1.9 anymore. I can’t think of anything specific that would break in this case, but we do not test using newer versions of the project db schema with old versions of Tropy.
I didn’t look at the newer version you linked to above. It’s suspicious that it is so much bigger, I would suspect that maybe the json data was imported more than once, or maybe imported, deleted and then imported again – in this case the db file can still contain old data that hasn’t yet been removed.
Thank you so much for diagnosing and fixing the corrupted search index. The database migrated smoothly and i now have:
All photos displaying correctly
All tags and notes intact
All folder/list structures preserved
Working search functionality
I really appreciate you taking the time to repair the file and explain the issue so clearly.
One minor folder display question.
I’m noticing different behavior with nested folders.
So for single-level folders:
When I click a top-level folder that has only one layer of subfolders beneath it I can see all items from all subfolders displayed together
But for multi-level nested folders:
When I click a top-level folder that has multiple layers of subfolders nested beneath it I cannot see all items from all subfolder levels at once
Is this expected behavior for multi-level nested folders in 1.17? Or is there a setting to show all items from all subfolder levels when clicking a top-level folder?
Not a critical issue!
Thanks again! This completely solved my migration migraine!!!
Yes, the current behavior is to show only those items in the selected list, not including those in sub-folders. We may add a preference to show all nested items instead in the future though.
What Works:
What Doesn’t Work: