[17.0] repair: not-null constraint conflict

[FranciscoBigWings]

The migration is failing due to a conflict between OpenUpgrade's data cleanup logic. The failure occurs because a temporary action by the migration script—deleting a placeholder location (ID 26 in this case)—triggers a ON DELETE SET NULL, but the field being updated (location_dest_id) is simultaneously required to be non-empty (NOT NULL).

Module

Repair

Describe the bug

OpenUpgrade runs ALTER TABLE commands to add the new Odoo 17 stock location fields.
ALTER TABLE "repair_order" ADD COLUMN "location_dest_id" int4

Later the scripts does this:
JOIN stock_location sld ON sld.id = rl.location_dest_id

And this:
UPDATE repair_order ro SET ... location_dest_id = spt.default_location_dest_id ...

The OpenUpgrade script reaches its final cleanup stage and attempts to delete the temporary location ID 26:
OpenUpgrade Action: DELETE FROM "stock_location" WHERE id IN (26). Because one or more constraints exist between stock_location and repair_order (likely set to ON DELETE SET NULL), the database automatically attempts to run the cascade query: UPDATE ONLY "public"."repair_order" SET "location_dest_id" = NULL WHERE ... location_dest_id = 26

The Conflict: null value in column "location_dest_id" violates not-null constraint

Additional context
Python 3.12

The uncommented log for the reference

2025-12-13 17:27:16,238 15469 DEBUG odoo17 OpenUpgrade: -1 rows affected after 0:00:00.001639 running ALTER TABLE "stock_warehouse" ADD COLUMN "repair_type_id" int4 
2025-12-13 17:27:16,240 15469 DEBUG odoo17 OpenUpgrade: -1 rows affected after 0:00:00.000961 running ALTER TABLE "repair_order" ADD COLUMN "picking_type_id" int4 
2025-12-13 17:27:16,241 15469 DEBUG odoo17 OpenUpgrade: -1 rows affected after 0:00:00.000792 running ALTER TABLE "repair_order" ADD COLUMN "location_dest_id" int4 
2025-12-13 17:27:16,242 15469 DEBUG odoo17 OpenUpgrade: -1 rows affected after 0:00:00.000492 running ALTER TABLE "repair_order" ADD COLUMN "parts_location_id" int4 
2025-12-13 17:27:16,244 15469 DEBUG odoo17 OpenUpgrade: -1 rows affected after 0:00:00.000506 running ALTER TABLE "repair_order" ADD COLUMN "recycle_location_id" int4 
2025-12-13 17:27:18,848 15469 DEBUG odoo17 OpenUpgrade: 8317 rows affected after 0:00:02.006967 running 
        UPDATE repair_order
        SET picking_type_id = 14,
        location_dest_id = 26,
        parts_location_id = 26,
        recycle_location_id = 26


.......


2025-12-13 17:27:31,364 15469 DEBUG odoo17 OpenUpgrade: 71 rows affected after 0:00:00.069785 running 
        INSERT INTO stock_move (
            old_repair_line_id,
            sequence, priority,
            propagate_cancel,
            additional,
            picked,
            create_uid, create_date, write_uid, write_date,
            repair_id, repair_line_type,
            picking_type_id, location_id, location_dest_id,
            product_id, product_uom,
            product_uom_qty, product_qty, quantity,
            name, description_picking,
            date, company_id,
            origin, reference,
            procure_method,
            scrapped,
            state
        )
        SELECT rl.id,
            10 AS sequence, '0' AS priority,
            true AS propagate_cancel,
            false AS additional,
            false AS picked,
            rl.create_uid, rl.create_date, rl.write_uid, rl.write_date,
            rl.repair_id, rl.type AS repair_line_type,
            ro.picking_type_id, rl.location_id, rl.location_dest_id,
            rl.product_id, rl.product_uom,
            rl.product_uom_qty,
            ROUND(
                ((rl.product_uom_qty / rl_uom.factor) * pt_uom.factor),
                SCALE(pt_uom.rounding)
                ) AS product_qty,
            0 AS quantity,
            ro.name, rl.name AS description_picking,
            ro.schedule_date AS date, rl.company_id,
            ro.name AS origin, ro.name AS reference,
            'make_to_stock' AS procure_method,
            sld.scrap_location AS scrapped,
            CASE WHEN ro.state IN ('draft', 'cancel')
                THEN ro.state ELSE 'confirmed'
            END AS state
        FROM repair_line rl
            JOIN repair_order ro ON rl.repair_id = ro.id
            JOIN stock_location sld ON sld.id = rl.location_dest_id
            JOIN product_product pp ON pp.id = rl.product_id
            JOIN product_template pt ON pt.id = pp.product_tmpl_id
            JOIN uom_uom rl_uom ON rl_uom.id = rl.product_uom
            JOIN uom_uom pt_uom ON pt_uom.id = pt.uom_id
        WHERE rl.move_id IS NULL AND rl.type IS NOT NULL

......

2025-12-13 17:27:32,875 15469 DEBUG odoo17 OpenUpgrade: 4991 rows affected after 0:00:01.497093 running 
        UPDATE repair_order ro
        SET picking_type_id = sw.repair_type_id,
            location_dest_id = spt.default_location_dest_id,
            parts_location_id = spt.default_remove_location_dest_id,
            recycle_location_id = spt.default_recycle_location_dest_id
        FROM stock_location sl
            JOIN stock_warehouse sw ON sw.id = sl.warehouse_id
            JOIN stock_picking_type spt ON spt.id = sw.repair_type_id
        WHERE ro.location_id = sl.id
....

2025-12-13 17:27:33,719 15469 ERROR odoo17 odoo.sql_db: bad query: DELETE FROM "stock_location" WHERE id IN (26)
ERROR: null value in column "location_dest_id" violates not-null constraint
DETAIL:  Failing row contains (5247, 17098, 2021-06-16 06:49:54.830732, 1, null, 1, 1.00, null, null, 17098, 9, 9, 9, 1, 1.89, done, 1, t, 2025-12-13 17:27:10.292693, 17098, 121308, 10.89, RMA21386, null, after_repair, 582, t, null, 9, 25728, null, 2021-06-16 00:00:00, null, 0, null, done, 14, null, 26, 26, null, null, null, f, f).
CONTEXT:  SQL statement "UPDATE ONLY "public"."repair_order" SET "location_dest_id" = NULL WHERE $1 OPERATOR(pg_catalog.=) "location_dest_id""
 
2025-12-13 17:27:33,719 15469 ERROR odoo17 OpenUpgrade: repair: error in migration script /opt/odoo/openupgrade17/OpenUpgrade/openupgrade_scripts/scripts/repair/17.0.1.0/post-migration.py: null value in column "location_dest_id" violates not-null constraint
DETAIL:  Failing row contains (5247, 17098, 2021-06-16 06:49:54.830732, 1, null, 1, 1.00, null, null, 17098, 9, 9, 9, 1, 1.89, done, 1, t, 2025-12-13 17:27:10.292693, 17098, 121308, 10.89, RMA21386, null, after_repair, 582, t, null, 9, 25728, null, 2021-06-16 00:00:00, null, 0, null, done, 14, null, 26, 26, null, null, null, f, f).
CONTEXT:  SQL statement "UPDATE ONLY "public"."repair_order" SET "location_dest_id" = NULL WHERE $1 OPERATOR(pg_catalog.=) "location_dest_id""
 
2025-12-13 17:27:33,720 15469 ERROR odoo17 OpenUpgrade: null value in column "location_dest_id" violates not-null constraint
DETAIL:  Failing row contains (5247, 17098, 2021-06-16 06:49:54.830732, 1, null, 1, 1.00, null, null, 17098, 9, 9, 9, 1, 1.89, done, 1, t, 2025-12-13 17:27:10.292693, 17098, 121308, 10.89, RMA21386, null, after_repair, 582, t, null, 9, 25728, null, 2021-06-16 00:00:00, null, 0, null, done, 14, null, 26, 26, null, null, null, f, f).
CONTEXT:  SQL statement "UPDATE ONLY "public"."repair_order" SET "location_dest_id" = NULL WHERE $1 OPERATOR(pg_catalog.=) "location_dest_id""

[FranciscoBigWings]

The reason for this error is due to repair orders with stock location_id set to partners/cutomers; this location_id has warehouse null and therefore the script fails.

The solution to avoid the error was to reasign the location_id of the repair orders to a location where the warehouse id is set.

I won't close this issue in case the maintainers want to consider this case in the migration scripts, if they consider it out of scope, this issue can be closed.

Regards

[pleirb]

Personally, to resolve the issue and analyze it later, I edited post-migration.py in the OpenUpgrade repair module and replaced the code between lines 210 and 215 with this:

    # created during the migration
    temp_loc = env["stock.location"].search([("name", "=", "Temporary Location OpenUpgrade")])
    if temp_loc:
        temp_loc.write({'name': 'Historical Location (Migration)', 'active': False})

    temp_type = env["stock.picking.type"].search([("name", "=", "SPT Repair OpenUpgrade")])
    if temp_type:
        temp_type.write({'name': 'Historical Location (Migration)', 'active': False})