One of the things you discover when working with any database where users generate records is that if a constraint doesn't exist, no matter how obvious it is that entering that data is a bad idea, then users will. In fact sometimes I feel like the more obvious the constraint should be, the more likely it is that users will abuse it. So to our first error(s) to fix, unapplied uom constraints.
In all these fixes a decision has to be made about where to apply the fix, is it in current production, or is it at point of migration.
UoM Mismatches
The final error we get which crashes the migration is
(u'Error!', u'Conversion from Product UoM PCE to Default UoM kg is not possible as they both belong to different Category!.')
So we find the records
select p.create_date, p.id, pu1.name, pu2.name, pp.name from stock_inventory_line p
LEFT JOIN product_product pp ON p.product_id=pp.id
LEFT JOIN product_template pt ON pt.id=pp.product_tmpl_id
LEFT JOIN product_uom pu1 ON p.product_uom=pu1.id
LEFT JOIN product_uom pu2 ON pu2.id = pt.uom_id
WHERE pu1.category_id != pu2.category_id; (16 rows)
select p.create_date, p.id, pu1.name, pu2.name, pp.name from stock_move p
LEFT JOIN product_product pp ON p.product_id=pp.id
LEFT JOIN product_template pt ON pt.id=pp.product_tmpl_id
LEFT JOIN product_uom pu1 ON p.product_uom=pu1.id
LEFT JOIN product_uom pu2 ON pu2.id = pt.uom_id
WHERE pu1.category_id != pu2.category_id; (35 rows)
Work out how to fix them. In this case, the records are all historical, stocktakes have been done, inventory adjusted and if not they will in future so we decide the best course is just to manually update them. There isn't much other choice anyway.
update stock_move set product_uom=pu2.id
from product_product pp,product_template pt,product_uom pu1,product_uom pu2
WHERE pt.id=pp.product_tmpl_id
AND stock_move.product_uom=pu1.id
AND pu2.id = pt.uom_id
AND pt.id=pp.product_tmpl_id
and pu1.category_id != pu2.category_id
and pp.id=stock_move.product_id;
UPDATE 35
update stock_inventory_line set product_uom=pu2.id
from product_product pp,product_template pt,product_uom pu1,product_uom pu2
WHERE pt.id=pp.product_tmpl_id
AND stock_inventory_line.product_uom=pu1.id
AND pu2.id = pt.uom_id
AND pt.id=pp.product_tmpl_id
and pu1.category_id != pu2.category_id
and pp.id=stock_inventory_line.product_id;
UPDATE 16
Now the decision is where to update them. In this case migration is some way away but actually its a problem now. The queries only take a second or two to run and fixing in production will take an hour or two to write the constraints, test them etc so in this case we just add to our migration script entrypoint.
Product Template
Table 'product_template': unable to set a NOT NULL constraint on column 'name' !
If you want to have it, you should update the records and execute manually:
Find the records in question. Turns out it is just one and a user mistake so we manually edit the record to give it a name. For the purpose of keeping our migration running we just run
update product_template set name=product_product.name from product_product
WHERE product_product.product_tmpl_id=product_template.id
and product_product.name is NOT NULL and product_template.name IS NULL;
Production Lots
Table 'stock_production_lot': unable to add 'unique (name, ref, product_id)' constraint !
If you want to have it, you should update the records and execute manually:
A check reveals this is a mess. An automated script and user abuse has created thousands of these. And actually, at least some of the abuse isn't entirely unjustified so deletion isn't an option. Adding to the problem is the lack of company awareness of this model, and that stock_moves will likely be across multiple companies so we cannot simply merge the records. Whats more is the problem will continue unless we fix it in production. So we need to dedupe but retain the records and create a constraint to prevent it happening again.
NOTE: If you just wanted to remove the duplicates, here's an example from fixing another error from the entrypoint file.
DELETE FROM mail_followers T1 USING mail_followers T2 WHERE T1.id < T2.id AND T1.res_model = T2.res_model AND T1.res_id = T2.res_id AND T1.partner_id = T2.partner_id;
Anyway we end up deduping and applying our constraint with
UPDATE stock_production_lot SET ref=COALESCE(ref,'') || '-' || md5(random()::text)
FROM (select count(*), array_agg(id) AS ids from stock_production_lot GROUP BY name, ref, product_id having count(*) > 1) as dupes
WHERE stock_production_lot.id=ANY(dupes.ids);
ALTER TABLE stock_production_lot DROP CONSTRAINT stock_production_lot_name_ref_uniq;
ALTER TABLE "stock_production_lot" ADD CONSTRAINT "stock_production_lot_name_ref_uniq" unique (name, ref, product_id);
It's a pretty simple sql statement that looks hard. We just add some random text to the ref field if the record is a duplicate.
As I go to add the index, it errors with an already exists error. I'm using postgres 10.4 for this migration, and the production database is 9.5. NULL handling is a fun topic, but suffice to say in postgres usually NULL != NULL. So we'll add this to our migration script as well just in case.
Pickings
WARNING: unable to set column picking_type_id of table stock_picking not null
Initial suspicion is this is a new field set without a default. Turns out it is and is filled post migration so nothing to do here. Same with the half a dozen or so similar ones.
Time to rebuild and restart with a fresh database. Starting at 6:15. Using a fresh database allows all the above error fixes to be checked and tested as well as get a better idea of timing. The actual command I use here is
docker-compose run --rm odoo -u all --workers=0 --stop-after-init >> log.txt
so I have a copy of the logs.
I see a few other non critical errors to look at.
ERROR upgrade1 OpenUpgrade: Invalid value 'aeroo' in the table 'ir_act_report_xml' for the field 'report_type'. (5 rows).
2018-07-27 06:17:56,504 1 ERROR upgrade1 OpenUpgrade: Invalid value 'tree_account_reconciliation' in the table 'ir_ui_view' for the field 'type'. (1 rows).
ERROR upgrade1 OpenUpgrade: Invalid value 'other' in the model 'ir_actions_server' for the field 'state'. (id 615).Please overload the new ir_actions_server._get_state function.
Couple of hours in and stock is still upgrading, so looks like we might need to optimize here as well. 24 minutes to update theoretical_qty seemed reasonable but it does seem to have a pointless loop making a few thousand UPDATE calls.
After a couple of hours we get stuck creating quants and reprocessing all done moves. so that will be our next days task.