The Migration That Counted Every Row and Still Shipped a Broken Database

A developer on a project we were supporting reported that one screen crashed for him and worked for everyone else. The traceback was:

KeyError: 'currency_id'

It was raised reading product.template. There is nothing obviously wrong with currency_id on product.template, and the same page loaded perfectly for other users. So the first diagnosis, which was ours and which was wrong, was stale browser assets. Clear the cache, regenerate assets, close the ticket.

He hit it again two days later. This post is about what it actually was, and about the two habits that would have caught it the first day.

The Actual Cause

During the migration, the uom_uom table had been re-seeded. The new system generated its own unit-of-measure records with new IDs. This was known at the time and was even written down in the migration audit as an expected ID shift rather than a defect.

What was not done was checking what still pointed at the old IDs.

Two product_template rows still carried unit-of-measure IDs from the source system. Those IDs no longer referred to anything. When Odoo read one of those rows and followed the reference, it raised a MissingError partway through building the record, leaving a partially constructed record in the cache. The next field access on that half-built record is what surfaced as KeyError: 'currency_id' — a completely different field, which is why the error name sends you looking in the wrong place entirely.

And it only crashed for some users because record rules decide which rows each user actually reads. Users whose rules never exposed those two rows never saw a problem. That is what made it look like a client-side issue.

Why PostgreSQL Did Not Stop This

The reasonable assumption is that a foreign key constraint would have blocked it. In a stock installation on stock columns, often yes.

But custom modules add many2one columns all the time, and a column added by a migration script, or by an older module, or by a hand-written ALTER TABLE, frequently has no foreign key constraint at all. The database will store a reference to a row that does not exist and never say a word.

So on a migration, "the database would have complained" is not a safety net you get to rely on.

The Targeted Check

Any time a parent table is re-seeded, ID-shifted, or partially copied, sweep every column that references it, immediately:

SELECT count(*)
FROM   product_template
WHERE  uom_id IS NOT NULL
  AND  uom_id NOT IN (SELECT id FROM uom_uom);

Anything other than zero is a bug you are about to ship. This is a cheap query and it should be run the moment you know the parent changed, not at the end.

The Whole-Database Sweep

The targeted check only helps for the tables you thought of. Before calling any migration done, sweep everything. Odoo describes its own schema in ir_model_fields, so you can generate the checks rather than write them:

SELECT f.model, f.name AS field, f.relation
FROM   ir_model_fields f
WHERE  f.ttype = 'many2one'
  AND  f.store IS TRUE
  AND  f.relation IS NOT NULL
ORDER  BY f.model, f.name;

Turn model names into table names (replace dots with underscores), then for each row run the same NOT IN count against the parent table. A short shell or Python loop over that result set will check a few thousand columns in a few minutes and give you a per-column count of dangling references across the entire database.

We keep this as a script and run it per project. It has found problems on migrations that everyone, including us, believed were clean.

The Silent Case, Which Is Worse

A dangling reference crashes, and a crash gets reported. There is a nastier variant that does not.

If the parent table was re-seeded and the IDs happened to be reused, a child row can point at an ID that does exist and refers to the wrong record. Nothing raises. Nothing is logged. A product silently gets a different unit of measure, or an invoice line a different tax, and the first person to notice is whoever reconciles the numbers, months later.

So the ID check is necessary and not sufficient. Where a parent was re-seeded, verify a sample by name rather than by ID:

SELECT p.id, p.name AS product, u.id AS uom_id, u.name AS uom_name
FROM   product_template p
JOIN   uom_uom u ON u.id = p.uom_id
LIMIT  50;

Read the output. If a product that should be measured in acres now says kilograms, you have the silent case, and no integrity query would ever have told you.

The Second Habit

The other thing that went wrong here was procedural, and it cost two days.

When the error was first reported, we built our own reproduction from the view definition. It passed. On that basis the ticket was closed as a client-side problem.

The reproduction we built was missing one field that the real request included, and that field was the entire cause. A reproduction you write yourself proves that your reproduction works. It proves nothing about what the user's browser actually sent.

Capture the real request instead. Any browser automation tool can intercept and record the outgoing payload, and most browsers will let you copy it straight out of the network tab. Then replay that exact payload, as that exact user, because record rules make the result user-dependent. If it passes for three different users including the reporter, then you have evidence. Otherwise you have a guess.

Checklist

• Any parent table re-seeded or ID-shifted, sweep every referencing column that same day
• Do not rely on foreign keys, custom many2one columns frequently have none
• Run the whole-database sweep from ir_model_fields before declaring done
• Where IDs were reused, verify a sample by name, not by ID
• Never close a user-reported error on a reproduction you wrote yourself

Row counts and ID ranges are not an integrity audit. They are the part of the audit that is easy.