Migration problem: 1.9 to 1.17

Troubleshooting
1

Tropy Support Request - Database Migration and Search Issues

System Information

  • Original Tropy Version: 1.9.0 (installed ~2020)
  • Current Tropy Version: 1.17.3
  • Operating System: macOS 25.2.0 (Apple Silicon)
  • Database Size: ~27,000 photos, 10,000+ items

Primary Issue: Search Function Malfunction in 1.9.0

The main problem: Search function in Tropy 1.9.0 returns incomplete results.

Specific behavior:

  • Example: Tag “THEORY VIRUS WAS OR RESEMBLED PLAGUE” has 68 items
  • Clicking the tag in sidebar correctly displays all 68 items
  • Searching for exact tag text “THEORY VIRUS WAS OR RESEMBLED PLAGUE” in search box returns only 2 items
  • This pattern occurs across all searches - consistently incomplete results
  • Otherwise, 1.9.0 database is fully functional (all photos display, tags work, notes accessible, folders intact)

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:

  1. Upgraded from Tropy 1.9.0 to 1.17.3
  2. 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:

  1. Exported all items from 1.9.0 database as JSON-LD (62MB file)
  2. Created fresh project in 1.17.3
  3. Imported all photos (~27,000 files)
  4. Edited JSON file to update file paths (find/replace: 111,111 instances)
  5. Imported edited JSON-LD metadata

Results:

:white_check_mark: What Works:

  • All photos imported and display correctly
  • All tags imported and functional
  • All notes imported and visible
  • Item metadata (dates, locations, etc.) intact

:cross_mark: 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

Current Situation

Using Tropy 1.9.0:

  • Database fully functional except search returns incomplete results
  • All photos, tags, notes, and folders accessible
  • Prefer to stay on 1.9.0 if search can be fixed

Questions for Tropy Support

  1. 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.
  2. 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?
  3. Why does JSON-LD export/import transfer search index corruption? Is there a way to export data without carrying over corrupted indexes?
  4. 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.

2

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

3

I’ll briefly tackle the final four questions, but I suspect we’ll have to tackle this differently.

  1. 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.
  2. All the migrations carry over so a migration from 1.9 to 1.17 should be possible.
  3. It shouldn’t. The search index is not part of the JSON-LD export, it is re-created as you import the data.
  4. 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.

4

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:

  1. Add list/folder import to JSON-LD in a future update? This would make migration viable.
  2. Or provide an alternative export format that preserves folder structures?
  3. 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…

Thank you again for your help with this!

5

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.