Migration from 3.6 to 6.2

[udono]

@looooo

so this seems to be correct, right?

Yes, this looks correct to me.

@looooo

but still I am a bit confused. To update the database with trytond-admin ir_configuration_id_seq should not be stored in the database, right?

No, not correct. In the trytond-admin processing it tries to find out if your database is already a tryton database and in this case it starts the update. But in your case it decides it is not a tryton database and so it tries to initialize it with tryton tables and sequences which are already existing. When we are talking of Version 5.0.X it fails here.

Here is the test for update or init.. It tests for the existence of some tables to decide ‘update’. In some migrations some tables may be renamed meanwhile. Which of the tested tables are not existing in your setup?

[looooo (Lorenz Lechner)]

seems like ir_module_dependency is not stored in the database.

SELECT table_name FROM information_schema.tables WHERE table_name = 'ir_module_dependency';
 table_name 
------------
(0 rows)

[udono]

It seems there was something not migrated correctly. in your former attempts. In older versions some tables were named different:

ir_module_module
ir_module_module_dependency

It seems the errors in your custom module(s) from the beginning did break or avoid former migration steps. Now we could start to ALTER TABLE uvw RENAME TO XYZ but maybe fields and other important automated migration steps are also missing and we run from problem to problem…

I would recommend to restart migration from a fresh copy of your 3.6 database and proceed all the migration steps again: first 4.0, then 6.0, then 7.0. And create a backup after each migration step, for fast rewind in case of trouble.

If there is an error, we need to fix it in the first occurrence. Warnings often can be handled later. After each migration I would recommend to sync the model data. Also in the initial version 3.6 it would be good to have it cleaned. For this you need to log in Tryton go to Administration / Model / Model / Data and sync the models.

If your custom modules are making any problems (like not migrated into all the target versions), I would try to just delete them like here and remove the modules directories out of trytond/trytond/modules/. Usually you can re-activate the migrated custom modules later in 7.0, but I don’t know what your custom modules are implementing.

[looooo (Lorenz Lechner)]

@udono

ir_module_module
ir_module_module_dependency

yes that was the case. I tried to manually change the names and now trytond-admin runs for a while and then reports this error:

psycopg2.IntegrityError: could not create exclusion constraint "party_category_name_parent_exclude"
DETAIL:  Key (name, COALESCE(parent, '-1'::integer))=(N-2023, -1) conflicts with key (name, COALESCE(parent, '-1'::integer))=(N-2023, -1).

@udono

It seems the errors in your custom module(s) from the beginning did break or avoid former migration steps. Now we could start to ALTER TABLE uvw RENAME TO XYZ but maybe fields and other important automated migration steps are also missing and we run from problem to problem…

actually I am redoing the steps from the beginning all the time. Using the origin dump, doing the manual modification with a script and starting trytond and tryton_db docker container. I don’t see where ir_module_module etc. should have been altered.

@udono

I would recommend to restart migration from a fresh copy of your 3.6 database and proceed all the migration steps again: first 4.0, then 6.0, then 7.0. And create a backup after each migration step, for fast rewind in case of trouble.

I can try to update to 4.0 first if this makes sense. But I think it’s going to be very difficult to test this version with a tryton client.

@udono

If your custom modules are making any problems (like not migrated into all the target versions), I would try to just delete them like here and remove the modules directories out of trytond/trytond/modules/. Usually you can re-activate the migrated custom modules later in 7.0, but I don’t know what your custom modules are implementing.

I expect for sure errors with the modules as they were written for python2.7 and need to be updated to a recent python3 version. Also the tryton source has changed in this long time, so there are for sure some issues.
I wasn’t aware that it is possible to run my database without these extra modules. If this is the case it definetly makes sense to try to get a working state without the extra modules first.

[ced (Cédric Krier)]

@looooo

yes that was the case. I tried to manually change the names and now trytond-admin runs for a while and then reports this error:

psycopg2.IntegrityError: could not create exclusion constraint "party_category_name_parent_exclude"
DETAIL:  Key (name, COALESCE(parent, '-1'::integer))=(N-2023, -1) conflicts with key (name, COALESCE(parent, '-1'::integer))=(N-2023, -1).

You have party categories with the same name and the same parent. This is no more allowed.
You must fix them before doing the upgrade.

[udono]

@looooo

I tried to manually change the names and now trytond-admin runs for a while and then reports this error:

This is good news as it indicates tryton-damin decides for update and no longer init. Can you please paste the complete log from trytond-admin -vv … in a paste bin.

@looooo

I don’t see where ir_module_module etc. should have been altered.

The internal migration is done closely to the model definition.

So make sure, this step is available in one of your migrations.

@looooo

I can try to update to 4.0 first if this makes sense. But I think it’s going to be very difficult to test this version with a tryton client.

The above migration is also included in 5.0 so there is no need for 4.0, I guess.

Not sure why it fails in your case. Could it be another postgres role / permissions problem? How is the database created? Is it the correct owner and not owner postgres? It can be a problem having a databases which is owned by role postgres, but including tables owned by another role.

[looooo (Lorenz Lechner)]

@ced

You have party categories with the same name and the same parent. This is no more allowed.
You must fix them before doing the upgrade.

I need to do this manually?

@udono

This is good news as it indicates tryton-damin decides for update and no longer init. Can you please paste the complete log from trytond-admin -vv … in a paste bin.

trytond-admin -vv -d mydatabase --all -c /etc/tryton/trytond.conf
INFO trytond.backend.postgresql.database connect to "mydatabase"
INFO trytond.modules ir:registering classes
INFO trytond.modules res:registering classes
INFO trytond.modules authentication_sms:registering classes
INFO trytond.modules country:registering classes
INFO trytond.modules currency:registering classes
INFO trytond.modules dashboard:registering classes
INFO trytond.modules ldap_authentication:registering classes
INFO trytond.modules tests:registering classes
INFO trytond.modules party:registering classes
INFO trytond.modules bank:registering classes
INFO trytond.modules company:registering classes
INFO trytond.modules google_maps:registering classes
INFO trytond.modules party_relationship:registering classes
INFO trytond.modules party_siret:registering classes
INFO trytond.modules web_user:registering classes
INFO trytond.modules account:registering classes
INFO trytond.modules company_work_time:registering classes
INFO trytond.modules notification_email:registering classes
INFO trytond.modules product:registering classes
INFO trytond.modules report_webkit:registering classes
INFO trytond.modules trytond-report-webkit:registering classes
INFO trytond.modules account_de_skr03:registering classes
INFO trytond.modules account_dunning:registering classes
INFO trytond.modules account_product:registering classes
INFO trytond.modules analytic_account:registering classes
INFO trytond.modules carrier:registering classes
INFO trytond.modules customs:registering classes
INFO trytond.modules edocument_unece:registering classes
INFO trytond.modules product_attribute:registering classes
INFO trytond.modules product_classification:registering classes
INFO trytond.modules product_cost_history:registering classes
INFO trytond.modules product_measurements:registering classes
INFO trytond.modules product_price_list:registering classes
INFO trytond.modules stock:registering classes
INFO trytond.modules timesheet:registering classes
INFO trytond.modules account_credit_limit:registering classes
INFO trytond.modules account_dunning_letter:registering classes
INFO trytond.modules account_invoice:registering classes
INFO trytond.modules account_stock_continental:registering classes
INFO trytond.modules product_classification_taxonomic:registering classes
INFO trytond.modules product_cost_fifo:registering classes
INFO trytond.modules product_price_list_parent:registering classes
INFO trytond.modules production:registering classes
INFO trytond.modules project:registering classes
INFO trytond.modules stock_forecast:registering classes
INFO trytond.modules stock_inventory_location:registering classes
INFO trytond.modules stock_location_sequence:registering classes
INFO trytond.modules stock_lot:registering classes
INFO trytond.modules stock_package:registering classes
INFO trytond.modules stock_split:registering classes
INFO trytond.modules timesheet_cost:registering classes
INFO trytond.modules account_deposit:registering classes
INFO trytond.modules account_dunning_email:registering classes
INFO trytond.modules account_dunning_fee:registering classes
INFO trytond.modules account_eu:registering classes
INFO trytond.modules account_fr:registering classes
INFO trytond.modules account_invoice_correction:registering classes
INFO trytond.modules account_invoice_history:registering classes
INFO trytond.modules account_invoice_line_standalone:registering classes
INFO trytond.modules account_invoice_stock:registering classes
INFO trytond.modules account_payment:registering classes
INFO trytond.modules account_statement:registering classes
INFO trytond.modules account_stock_landed_cost:registering classes
INFO trytond.modules account_tax_cash:registering classes
INFO trytond.modules edocument_uncefact:registering classes
INFO trytond.modules production_split:registering classes
INFO trytond.modules project_plan:registering classes
INFO trytond.modules stock_lot_sled:registering classes
INFO trytond.modules stock_lot_unit:registering classes
INFO trytond.modules stock_product_location:registering classes
INFO trytond.modules stock_shipment_measurements:registering classes
INFO trytond.modules account_be:registering classes
INFO trytond.modules account_es:registering classes
INFO trytond.modules account_fr_chorus:registering classes
INFO trytond.modules account_payment_clearing:registering classes
INFO trytond.modules account_payment_stripe:registering classes
INFO trytond.modules account_statement_aeb43:registering classes
INFO trytond.modules account_statement_coda:registering classes
INFO trytond.modules account_statement_ofx:registering classes
INFO trytond.modules account_stock_anglo_saxon:registering classes
INFO trytond.modules account_stock_landed_cost_weight:registering classes
INFO trytond.modules purchase:registering classes
INFO trytond.modules sale:registering classes
INFO trytond.modules account_asset:registering classes
INFO trytond.modules account_payment_sepa:registering classes
INFO trytond.modules commission:registering classes
INFO trytond.modules project_revenue:registering classes
INFO trytond.modules purchase_invoice_line_standalone:registering classes
INFO trytond.modules purchase_request:registering classes
INFO trytond.modules purchase_shipment_cost:registering classes
INFO trytond.modules reports:registering classes
INFO trytond.modules sale_complaint:registering classes
INFO trytond.modules sale_credit_limit:registering classes
INFO trytond.modules sale_invoice_grouping:registering classes
INFO trytond.modules sale_opportunity:registering classes
INFO trytond.modules sale_payment:registering classes
INFO trytond.modules sale_price_list:registering classes
INFO trytond.modules sale_shipment_grouping:registering classes
INFO trytond.modules sale_shipment_tolerance:registering classes
INFO trytond.modules sale_subscription:registering classes
INFO trytond.modules stock_supply_day:registering classes
INFO trytond.modules template:registering classes
INFO trytond.modules account_payment_sepa_cfonb:registering classes
INFO trytond.modules analytic_invoice:registering classes
INFO trytond.modules commission_waiting:registering classes
INFO trytond.modules energiebig:registering classes
INFO trytond.modules product_price_list_dates:registering classes
INFO trytond.modules project_invoice:registering classes
INFO trytond.modules purchase_request_quotation:registering classes
INFO trytond.modules purchase_requisition:registering classes
INFO trytond.modules sale_extra:registering classes
INFO trytond.modules sale_promotion:registering classes
INFO trytond.modules sale_subscription_asset:registering classes
INFO trytond.modules sale_supply:registering classes
INFO trytond.modules stock_supply:registering classes
INFO trytond.modules analytic_purchase:registering classes
INFO trytond.modules analytic_sale:registering classes
INFO trytond.modules sale_advance_payment:registering classes
INFO trytond.modules sale_promotion_coupon:registering classes
INFO trytond.modules sale_shipment_cost:registering classes
INFO trytond.modules sale_stock_quantity:registering classes
INFO trytond.modules sale_supply_drop_shipment:registering classes
INFO trytond.modules stock_consignment:registering classes
INFO trytond.modules stock_location_move:registering classes
INFO trytond.modules stock_supply_forecast:registering classes
INFO trytond.modules stock_supply_production:registering classes
INFO trytond.modules account_tax_rule_country:registering classes
INFO trytond.modules carrier_percentage:registering classes
INFO trytond.modules carrier_weight:registering classes
INFO trytond.modules production_routing:registering classes
INFO trytond.modules stock_package_shipping:registering classes
INFO trytond.modules production_outsourcing:registering classes
INFO trytond.modules production_work:registering classes
INFO trytond.modules stock_package_shipping_dpd:registering classes
INFO trytond.modules stock_package_shipping_ups:registering classes
INFO trytond.modules production_work_timesheet:registering classes
INFO trytond.pool init pool for "mydatabase"
INFO trytond.modules ir
INFO trytond.pool setup pool for "mydatabase"
INFO trytond.modules ir:register ir.configuration
INFO trytond.modules ir:register ir.translation
INFO trytond.modules ir:register ir.translation.set.start
INFO trytond.modules ir:register ir.translation.set.succeed
INFO trytond.modules ir:register ir.translation.clean.start
INFO trytond.modules ir:register ir.translation.clean.succeed
INFO trytond.modules ir:register ir.translation.update.start
INFO trytond.modules ir:register ir.translation.export.start
INFO trytond.modules ir:register ir.translation.export.result
INFO trytond.modules ir:register ir.sequence.type
INFO trytond.modules ir:register ir.sequence
INFO trytond.modules ir:register ir.sequence.strict
INFO trytond.modules ir:register ir.ui.menu
INFO trytond.modules ir:register ir.ui.menu.favorite
INFO trytond.modules ir:register ir.ui.view
INFO trytond.modules ir:register ir.ui.view.show.start
INFO trytond.modules ir:register ir.ui.view_tree_width
INFO trytond.modules ir:register ir.ui.view_tree_state
INFO trytond.modules ir:register ir.ui.view_search
INFO trytond.modules ir:register ir.ui.icon
INFO trytond.modules ir:register ir.action
INFO trytond.modules ir:register ir.action.keyword
INFO trytond.modules ir:register ir.action.report
INFO trytond.modules ir:register ir.action.act_window
INFO trytond.modules ir:register ir.action.act_window.view
INFO trytond.modules ir:register ir.action.act_window.domain
INFO trytond.modules ir:register ir.action.wizard
INFO trytond.modules ir:register ir.action.url
INFO trytond.modules ir:register ir.model
INFO trytond.modules ir:register ir.model.field
INFO trytond.modules ir:register ir.model.access
INFO trytond.modules ir:register ir.model.field.access
INFO trytond.modules ir:register ir.model.button
INFO trytond.modules ir:register ir.model.button.rule
INFO trytond.modules ir:register ir.model.button.click
INFO trytond.modules ir:register ir.model.button-button.reset
INFO trytond.modules ir:register ir.model.data
INFO trytond.modules ir:register ir.model.print_model_graph.start
INFO trytond.modules ir:register ir.attachment
INFO trytond.modules ir:register ir.note
INFO trytond.modules ir:register ir.note.read
INFO trytond.modules ir:register ir.cron
INFO trytond.modules ir:register ir.lang
INFO trytond.modules ir:register ir.export
INFO trytond.modules ir:register ir.export.line
INFO trytond.modules ir:register ir.rule.group
INFO trytond.modules ir:register ir.rule
INFO trytond.modules ir:register ir.module
INFO trytond.modules ir:register ir.module.dependency
INFO trytond.modules ir:register ir.module.config_wizard.item
INFO trytond.modules ir:register ir.module.config_wizard.first
INFO trytond.modules ir:register ir.module.config_wizard.other
INFO trytond.modules ir:register ir.module.config_wizard.done
INFO trytond.modules ir:register ir.module.activate_upgrade.start
INFO trytond.modules ir:register ir.module.activate_upgrade.done
INFO trytond.modules ir:register ir.cache
INFO trytond.modules ir:register ir.date
INFO trytond.modules ir:register ir.trigger
INFO trytond.modules ir:register ir.trigger.log
INFO trytond.modules ir:register ir.session
INFO trytond.modules ir:register ir.session.wizard
INFO trytond.modules ir:register ir.queue
INFO trytond.modules ir:register ir.translation.set
INFO trytond.modules ir:register ir.translation.clean
INFO trytond.modules ir:register ir.translation.update
INFO trytond.modules ir:register ir.translation.export
INFO trytond.modules ir:register ir.translation.report
INFO trytond.modules ir:register ir.ui.view.show
INFO trytond.modules ir:register ir.model.print_model_graph
INFO trytond.modules ir:register ir.module.config_wizard
INFO trytond.modules ir:register ir.module.activate_upgrade
INFO trytond.modules ir:register ir.module.config
INFO trytond.modules ir:register ir.model.graph
INFO trytond.modules ir:register ir.model.workflow_graph
INFO trytond.modules ir:loading ir.xml
INFO trytond.modules ir:loading ui/ui.xml
WARNING trytond.convert Field icon of 2@ir.ui.menu not updated (id: menu_ui), because it has changed since the last update
INFO trytond.modules ir:loading ui/icon.xml
INFO trytond.modules ir:loading ui/menu.xml
INFO trytond.modules ir:loading ui/view.xml
INFO trytond.modules ir:loading action.xml
WARNING trytond.convert Field icon of 9@ir.ui.menu not updated (id: menu_action), because it has changed since the last update
INFO trytond.modules ir:loading model.xml
WARNING trytond.convert Field icon of 15@ir.ui.menu not updated (id: menu_models), because it has changed since the last update
INFO trytond.modules ir:loading sequence.xml
WARNING trytond.convert Field icon of 22@ir.ui.menu not updated (id: menu_sequences), because it has changed since the last update
INFO trytond.modules ir:loading attachment.xml
INFO trytond.modules ir:loading note.xml
INFO trytond.modules ir:loading cron.xml
WARNING trytond.convert Field icon of 27@ir.ui.menu not updated (id: menu_scheduler), because it has changed since the last update
INFO trytond.modules ir:loading lang.xml
WARNING trytond.convert Field icon of 29@ir.ui.menu not updated (id: menu_localization), because it has changed since the last update
INFO trytond.modules ir:loading translation.xml
INFO trytond.modules ir:loading export.xml
INFO trytond.modules ir:loading rule.xml
INFO trytond.modules ir:loading module.xml
WARNING trytond.convert Field icon of 40@ir.ui.menu not updated (id: menu_modules), because it has changed since the last update
INFO trytond.modules ir:loading trigger.xml
INFO trytond.modules ir:loading locale/de.po
INFO trytond.modules res
INFO trytond.pool setup pool for "mydatabase"
INFO trytond.modules res:register res.group
INFO trytond.modules res:register res.user
INFO trytond.modules res:register res.user.login.attempt
WARNING trytond.backend.postgresql.table Unable to migrate column login on table res_user_login_attempt from varchar() to varchar(512).
INFO trytond.modules res:register res.user-ir.action
INFO trytond.modules res:register res.user-res.group
INFO trytond.modules res:register res.user.warning
INFO trytond.modules res:register res.user.application
INFO trytond.modules res:register res.user.config.start
INFO trytond.modules res:register ir.ui.menu-res.group
INFO trytond.modules res:register ir.action-res.group
INFO trytond.modules res:register ir.model.field-res.group
INFO trytond.modules res:register ir.model.button-res.group
INFO trytond.modules res:register ir.model.button.rule
INFO trytond.modules res:register ir.model.button.click
INFO trytond.modules res:register ir.rule.group-res.group
INFO trytond.modules res:register ir.lang
INFO trytond.modules res:register ir.sequence.type
INFO trytond.modules res:register ir.sequence.type-res.group
INFO trytond.modules res:register ir.sequence
INFO trytond.modules res:register ir.sequence.strict
INFO trytond.modules res:register ir.module.config_wizard.item
INFO trytond.modules res:register res.user.config
INFO trytond.modules res:register res.user.email_reset_password
INFO trytond.modules res:loading res.xml
143 129297797261120 [2025-01-10 00:46:43,911] WARNING trytond.convert Field icon of 45@ir.ui.menu not updated (id: menu_res), because it has changed since the last update
INFO trytond.modules res:loading group.xml
INFO trytond.modules res:loading user.xml
INFO trytond.modules res:loading ir.xml
INFO trytond.modules res:loading locale/de.po
INFO trytond.modules country
INFO trytond.pool setup pool for "mydatabase"
INFO trytond.modules country:register country.country
INFO trytond.modules country:register country.subdivision
INFO trytond.modules country:register country.zip
INFO trytond.modules country:loading country.xml
INFO trytond.modules country:loading data.xml
INFO trytond.modules country:loading locale/de.po
INFO trytond.modules currency
INFO trytond.pool setup pool for "mydatabase"
INFO trytond.modules currency:register currency.currency
INFO trytond.modules currency:register currency.currency.rate
INFO trytond.modules currency:loading data.xml
INFO trytond.modules currency:loading currency.xml
INFO trytond.modules currency:loading locale/de.po
INFO trytond.modules ldap_authentication
INFO trytond.pool setup pool for "mydatabase"
INFO trytond.modules ldap_authentication:register res.user
INFO trytond.modules ldap_authentication:loading locale/de.po
INFO trytond.modules party
INFO trytond.pool setup pool for "mydatabase"
INFO trytond.modules party:register party.category
Traceback (most recent call last):
  File "/usr/local/bin/trytond-admin", line 21, in <module>
    admin.run(options)
  File "/usr/local/lib/python3.7/dist-packages/trytond/admin.py", line 54, in run
    activatedeps=options.activatedeps)
  File "/usr/local/lib/python3.7/dist-packages/trytond/pool.py", line 160, in init
    lang=lang, activatedeps=activatedeps)
  File "/usr/local/lib/python3.7/dist-packages/trytond/modules/__init__.py", line 418, in load_modules
    _load_modules(update)
  File "/usr/local/lib/python3.7/dist-packages/trytond/modules/__init__.py", line 388, in _load_modules
    load_module_graph(graph, pool, update, lang)
  File "/usr/local/lib/python3.7/dist-packages/trytond/modules/__init__.py", line 216, in load_module_graph
    cls.__register__(module)
  File "/usr/local/lib/python3.7/dist-packages/trytond/modules/party/category.py", line 44, in __register__
    super(Category, cls).__register__(module_name)
  File "/usr/local/lib/python3.7/dist-packages/trytond/model/modelsql.py", line 291, in __register__
    table.add_constraint(ident, constraint)
  File "/usr/local/lib/python3.7/dist-packages/trytond/backend/postgresql/table.py", line 426, in add_constraint
    % (self.table_name, ident, constraint), constraint.params)
  File "/usr/local/lib/python3.7/dist-packages/trytond/backend/postgresql/database.py", line 67, in execute
    cursor.execute(self, sql, args)
psycopg2.IntegrityError: could not create exclusion constraint "party_category_name_parent_exclude"
DETAIL:  Key (name, COALESCE(parent, '-1'::integer))=(N-2023, -1) conflicts with key (name, COALESCE(parent, '-1'::integer))=(N-2023, -1).

@udono

The internal migration is done closely to the model definition.

Is the internal migration done when I run tryton-admin -d mydatabase --all ...? If it is this way, there is no chance that eg. ir_module_module is changed to ir_module because of the non passing test.

[udono]

@looooo

@ced

You have party categories with the same name and the same parent. This is no more allowed.
You must fix them before doing the upgrade.

I need to do this manually?

Yes. The system can’t decide for you how to rename the categories. You must set different names for every category. Just put some suffix to the name and clean-up later.

@looooo

Is the internal migration done when I run tryton-admin -d mydatabase --all ...?

Yes, exactly

@looooo

If it is this way, there is no chance that eg. ir_module_module is changed to ir_module because of the non passing test.

Which test you mean? Why is the test not passing? IIUC you already renamed the table manually, so there is no longer ir_module_module. I think here everything is ok.

[looooo (Lorenz Lechner)]

@udono

Yes. The system can’t decide for you how to rename the categories. You must set different names for every category. Just put some suffix to the name and clean-up later.

is this possible to be done with a sql-query? because currently I do not have the possibilty to run tryton-client on my development environment. I tried to get tryton3.6 running, but it’s too difficult with the old dependencies (gtk2 pygtk…).

@udono

Which test you mean? Why is the test not passing? IIUC you already renamed the table manually, so there is no longer ir_module_module. I think here everything is ok.

yes by manually altering ir_module_module the internal migration is started. But if I do not update them manually the trytond-admin is not able to reach the point where the automatic altering of ir_module_module is done. I know, maybe nobody is using such an old version as we do, but maybe it would be good to add these informations to the manual migration steps here: Migration — Tryton Documentation

[looooo (Lorenz Lechner)]

@looooo

is this possible to be done with a sql-query? because currently I do not have the possibilty to run tryton-client on my development environment. I tried to get tryton3.6 running, but it’s too difficult with the old dependencies (gtk2 pygtk…)

ok found a way with this sql command:

WITH numbered_categories AS (
    SELECT 
        id, 
        name, 
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS row_num
    FROM 
        party_category
)
UPDATE 
    party_category pc
SET 
    name = CONCAT(pc.name, '_', nc.row_num)
FROM 
    numbered_categories nc
WHERE 
    pc.id = nc.id
    AND nc.row_num > 1;

now running trytond-admin to update the database I am running into this:

psycopg2.IntegrityError: insert or update on table "party_party_account" violates foreign key constraint "party_party_account_party_fkey"
DETAIL:  Key (party)=(10) is not present in table "party_party".

[edbo]

First I hope you are writing down what changes you are making to align the data for the upgrade. When everything is running with the new version, you can visit those changes and decide what to do with it. Also I hope you’ve made backups before you made some changes in the database, so you can easily roll back when needed.

The error you are now walking into it that there should be a party with id 10, but none exists. Now you can try to find that party in your party_party_account database table and see if those records can also be deleted, or you add manually a party in the database with id 10.

[ced (Cédric Krier)]

This is an issue with the migrate_property.
In the table ir_property you must have reference to the party with id=10 which does not exist any more.
You need to clean the ir_property from the missing resource.

As historically properties were not managed with foreign key so there was no delete on cascade.

[looooo (Lorenz Lechner)]

@edbo

First I hope you are writing down what changes you are making to align the data for the upgrade. When everything is running with the new version, you can visit those changes and decide what to do with it. Also I hope you’ve made backups before you made some changes in the database, so you can easily roll back when needed.

thanks for the hint. Yes I am working with a copy of the database, trying to migrate it to trytond 5.0 at the moment. So this is more like a toy-project. I have a script which does all the manual migrations. and I document every modification so I can redo the necesary steps with the uptodate database once I have finished with the “toy-project”

@edbo

The error you are now walking into it that there should be a party with id 10, but none exists. Now you can try to find that party in your party_party_account database table and see if those records can also be deleted, or you add manually a party in the database with id 10.

actually there is no table party_party_account in my database:

 ...
 public | party_identifier                                | table | tryton
 public | party_party                                     | table | tryton
 public | party_party_lang                                | table | tryton
 public | product_category                                | table | tryton
 ...

[edbo]

That’s indeed not a database table, but what @ced says, a property which you have to clean manually.

[looooo (Lorenz Lechner)]

@ced

This is an issue with the migrate_property.
In the table ir_property you must have reference to the party with id=10 which does not exist any more.
You need to clean the ir_property from the missing resource.

As historically properties were not managed with foreign key so there was no delete on cascade.

thanks, if I got it correctly I need to remove this with

DELETE FROM ir_property WHERE res = 'party.party,10'

if I do this I run into more issues like this:

psycopg2.IntegrityError: insert or update on table "party_party_account" violates foreign key constraint "party_party_account_party_fkey"
DETAIL:  Key (party)=(100) is not present in table "party_party".

so I continue removing these properties. But it continues with 10;100;1000;1001;1002;1003;1004;....
is there a smarter way to see which properties have to be cleaned?

[udono]

@looooo

is there a smarter way to see which properties have to be cleaned?

Maybe something like this?

DELETE FROM ir_property WHERE res LIKE 'party.party,%' AND SUBSTRING(res, POSITION(',' IN res) + 1)::integer NOT IN (SELECT id FROM party_party);

[looooo (Lorenz Lechner)]

@udono

Maybe something like this?

DELETE FROM ir_property WHERE res LIKE 'party.party,%' AND SUBSTRING(res, POSITION(',' IN res) + 1)::integer NOT IN (SELECT id FROM party_party);

perfect, thanks, that worked.

I needed to do the same for product_template and product_category and now running into:

psycopg2.ProgrammingError: foreign key constraint "product_template_account_category_fkey" cannot be implemented
DETAIL:  Key columns "account_category" and "id" are of incompatible types: boolean and integer

[udono]

please make more -vvvv

[looooo (Lorenz Lechner)]

sure

743 131460110001984 [2025-01-10 18:06:16,293] INFO trytond.modules account_product:register account.create_chart.properties
743 131460110001984 [2025-01-10 18:06:16,296] INFO trytond.modules account_product:register product.template
743 131460110001984 [2025-01-10 18:06:16,315] WARNING trytond.backend.postgresql.table Unable to migrate column account_category on table product_template from bool to int4.
Traceback (most recent call last):
  File "/usr/local/bin/trytond-admin", line 21, in <module>
    admin.run(options)
  File "/usr/local/lib/python3.7/dist-packages/trytond/admin.py", line 54, in run
    activatedeps=options.activatedeps)
  File "/usr/local/lib/python3.7/dist-packages/trytond/pool.py", line 160, in init
    lang=lang, activatedeps=activatedeps)
  File "/usr/local/lib/python3.7/dist-packages/trytond/modules/__init__.py", line 418, in load_modules
    _load_modules(update)
  File "/usr/local/lib/python3.7/dist-packages/trytond/modules/__init__.py", line 388, in _load_modules
    load_module_graph(graph, pool, update, lang)
  File "/usr/local/lib/python3.7/dist-packages/trytond/modules/__init__.py", line 216, in load_module_graph
    cls.__register__(module)
  File "/usr/local/lib/python3.7/dist-packages/trytond/modules/product/product.py", line 84, in __register__
    super(Template, cls).__register__(module_name)
  File "/usr/local/lib/python3.7/dist-packages/trytond/model/modelsql.py", line 261, in __register__
    table.add_fk(field_name, ref, field.ondelete)
  File "/usr/local/lib/python3.7/dist-packages/trytond/backend/postgresql/table.py", line 325, in add_fk
    'ON DELETE ' + on_delete)
  File "/usr/local/lib/python3.7/dist-packages/trytond/backend/postgresql/database.py", line 67, in execute
    cursor.execute(self, sql, args)
psycopg2.ProgrammingError: foreign key constraint "product_template_account_category_fkey" cannot be implemented
DETAIL:  Key columns "account_category" and "id" are of incompatible types: boolean and integer.

[looooo (Lorenz Lechner)]

account_category of product_template:

select account_category from product_template;

account_category 
------------------
 t
 t
 t
 t
 t

the problem was that there was a default value for account_category. So I did this to change the type from boolean to int:

ALTER TABLE product_template ALTER COLUMN account_category DROP DEFAULT;
ALTER TABLE product_template ALTER COLUMN account_category TYPE INT USING (CASE WHEN account_category THEN 1 ELSE 0 END);
ALTER TABLE product_template ALTER COLUMN account_category SET DEFAULT 0;

now the next error is:

psycopg2.IntegrityError: insert or update on table "product_template" violates foreign key constraint "product_template_account_category_fkey"
DETAIL:  Key (account_category)=(1) is not present in table "product_category".