[12.0] migration of assets fails

[linuxnow]

I'm 'trying to migrate from 11.0 to 12.0 using the doodba docker image but I hit this error that I do not know how to fix:

psycopg2.IntegrityError: insert or update on table "account_move_line" violates foreign key constraint "account_move_line_asset_id_fkey" DETAIL: Key (asset_id)=(1) is not present in table "account_asset".

I've checked the different orginal tables they seem to be consistent:

futini=# select id, name from account_asset_asset; id | name
----+------------------ 1 | Locals Bonavista (1 row)

futini=# select id,name,asset_id from account_move_line where asset_id=1; id | name | asset_id -------+------+---------- 132 | / | 1 2732 | / | 1 6993 | / | 1 10990 | / | 1 8583 | / | 1

I cannot check account_asset because it's probablyu created and destroyed, but it looks like it's not populated with the existing records.

This is the error I get:

2020-01-14 23:55:21,449 1 INFO futini odoo.modules.migration: module account_asset_management: Running migration [>12.0.1.0.0] pre-migration 2020-01-14 23:55:21,529 1 INFO futini odoo.modules.migration: module account_asset_management: Running migration [>12.0.2.0.0] pre-migration 2020-01-14 23:55:21,681 1 INFO futini odoo.modules.registry: module account_asset_management: creating or updating database tables 2020-01-14 23:55:21,757 1 INFO futini odoo.models: Computing parent_path for table account_asset_group... 2020-01-14 23:55:22,019 1 INFO futini odoo.models: Storing computed values of account.asset.depreciation_base 2020-01-14 23:55:22,020 1 INFO futini odoo.models: Storing computed values of account.asset.value_residual 2020-01-14 23:55:22,020 1 INFO futini odoo.models: Storing computed values of account.asset.value_depreciated 2020-01-14 23:55:22,030 1 INFO futini odoo.models: Storing computed values of account.asset.company_currency_id 2020-01-14 23:55:22,157 1 INFO futini odoo.models: Storing computed values of account.asset.line.remaining_value 2020-01-14 23:55:22,157 1 INFO futini odoo.models: Storing computed values of account.asset.line.depreciated_value 2020-01-14 23:55:22,159 1 INFO futini odoo.models: Storing computed values of account.asset.line.move_check 2020-01-14 23:55:22,588 1 ERROR futini odoo.sql_db: bad query: ALTER TABLE "account_move_line" ADD FOREIGN KEY ("asset_id") REFERENCES "account_asset"("id") ON DELETE restrict ERROR: insert or update on table "account_move_line" violates foreign key constraint "account_move_line_asset_id_fkey" DETAIL: Key (asset_id)=(1) is not present in table "account_asset".

2020-01-14 23:55:22,591 1 WARNING futini odoo.modules.loading: Transient module states were reset 2020-01-14 23:55:22,593 1 ERROR futini odoo.modules.registry: Failed to load registry Traceback (most recent call last): File "/opt/odoo/custom/src/odoo/odoo/modules/registry.py", line 86, in new odoo.modules.load_modules(registry._db, force_demo, status, update_module) File "/opt/odoo/custom/src/odoo/odoo/modules/loading.py", line 476, in load_modules force, status, report, loaded_modules, update_module, models_to_check, upg_registry) File "/opt/odoo/custom/src/odoo/odoo/modules/loading.py", line 360, in load_marked_modules upg_registry=upg_registry, File "/opt/odoo/custom/src/odoo/odoo/modules/loading.py", line 222, in load_module_graph registry.init_models(cr, model_names, {'module': package.name}) File "/opt/odoo/custom/src/odoo/odoo/modules/registry.py", line 304, in init_models func() File "/opt/odoo/custom/src/odoo/odoo/fields.py", line 2185, in update_db_foreign_key new = process(model._cr, model._table, self.name, comodel._table, 'id', self.ondelete or 'set null') File "/opt/odoo/custom/src/odoo/odoo/tools/sql.py", line 179, in fix_foreign_key return add_foreign_key(cr, tablename1, columnname1, tablename2, columnname2, ondelete) File "/opt/odoo/custom/src/odoo/odoo/tools/sql.py", line 152, in add_foreign_key cr.execute(query.format(tablename1, columnname1, tablename2, columnname2, ondelete)) File "/opt/odoo/custom/src/odoo/odoo/sql_db.py", line 148, in wrapper return f(self, *args, **kwargs) File "/opt/odoo/custom/src/odoo/odoo/sql_db.py", line 225, in execute res = self._obj.execute(query, params) psycopg2.IntegrityError: insert or update on table "account_move_line" violates foreign key constraint "account_move_line_asset_id_fkey" DETAIL: Key (asset_id)=(1) is not present in table "account_asset".

2020-01-14 23:55:22,594 1 CRITICAL futini odoo.service.server: Failed to initialize database futini. Traceback (most recent call last): File "/opt/odoo/custom/src/odoo/odoo/service/server.py", line 1126, in preload_registries registry = Registry.new(dbname, update_module=update_module) File "/opt/odoo/custom/src/odoo/odoo/modules/registry.py", line 86, in new odoo.modules.load_modules(registry._db, force_demo, status, update_module) File "/opt/odoo/custom/src/odoo/odoo/modules/loading.py", line 476, in load_modules force, status, report, loaded_modules, update_module, models_to_check, upg_registry) File "/opt/odoo/custom/src/odoo/odoo/modules/loading.py", line 360, in load_marked_modules upg_registry=upg_registry, File "/opt/odoo/custom/src/odoo/odoo/modules/loading.py", line 222, in load_module_graph registry.init_models(cr, model_names, {'module': package.name}) File "/opt/odoo/custom/src/odoo/odoo/modules/registry.py", line 304, in init_models func() File "/opt/odoo/custom/src/odoo/odoo/fields.py", line 2185, in update_db_foreign_key new = process(model._cr, model._table, self.name, comodel._table, 'id', self.ondelete or 'set null') File "/opt/odoo/custom/src/odoo/odoo/tools/sql.py", line 179, in fix_foreign_key return add_foreign_key(cr, tablename1, columnname1, tablename2, columnname2, ondelete) File "/opt/odoo/custom/src/odoo/odoo/tools/sql.py", line 152, in add_foreign_key cr.execute(query.format(tablename1, columnname1, tablename2, columnname2, ondelete)) File "/opt/odoo/custom/src/odoo/odoo/sql_db.py", line 148, in wrapper return f(self, *args, **kwargs) File "/opt/odoo/custom/src/odoo/odoo/sql_db.py", line 225, in execute res = self._obj.execute(query, params) psycopg2.IntegrityError: insert or update on table "account_move_line" violates foreign key constraint "account_move_line_asset_id_fkey" DETAIL: Key (asset_id)=(1) is not present in table "account_asset".

2020-01-14 23:55:22,595 1 INFO futini odoo.service.server: Initiating shutdown

Thank you for your help Pau

[linuxnow]

I've been struggling with this for days, fixing all the migration warnings I could but it still fails. The only asset is there, the existing amortizations are in place but the migration fails.

I migrated another DB from 11 to 12 and I wasn't able to migrate them. As the only asset was acquired this year, I didn't care much.

But in this case I've amortised the asset for 4 years and I'd rather migrate it.

Any help would be welcome.

2020-01-21 19:42:20,168 1 ERROR futini odoo.sql_db: bad query: ALTER TABLE "account_move_line" ADD FOREIGN KEY ("asset_id") REFERENCES "account_asset"("id") ON DELETE restrict ERROR: insert or update on table "account_move_line" violates foreign key constraint "account_move_line_asset_id_fkey" DETAIL: Key (asset_id)=(1) is not present in table "account_asset".

I've checked the account_move_line table:

select * from account_move_line where asset_id = 1; id | create_date | statement_id | company_id | currency_id | date_maturity | partner_id | reconcile_partial_id | blocked | analytic_account_id | create_uid | credit | centralisation | journal_id | reconcile_ref | tax_code_id | state | debit | ref | account_id | period_id | write_date | date_created | date | write_uid | move_id | name | reconcile_id | tax_amount | product_id | account_tax_id | product_uom_id | amount_currency | quantity | asset_id | debit_cash_basis | credit_cash_basis | balance_cash_basis | user_type_id | amount_residual | amount_residual_currency | reconciled | payment_id | company_currency_id | invoice_id | tax_line_id | balance | full_reconcile_id | payment_mode_id | partner_bank_id | bank_payment_line_id | mandate_id | tax_exigible | tax_base_amount | statement_line_id -------+----------------------------+--------------+------------+-------------+---------------+------------+----------------------+---------+---------------------+------------+--------+----------------+------------+---------------+-------------+-------+----------+------------------+------------+-----------+----------------------------+--------------+------------+-----------+---------+------+--------------+------------+------------+----------------+----------------+-----------------+----------+----------+------------------+-------------------+--------------------+--------------+-----------------+--------------------------+------------+------------+---------------------+------------+-------------+----------+-------------------+-----------------+-----------------+----------------------+------------+--------------+-----------------+------------------- 2732 | 2017-01-30 18:23:58.82106 | | 1 | | 2016-12-31 | 1 | | f | | 5 | 0.00 | normal | 5 | | | valid | 27705.89 | Local | 1437 | 29 | 2020-01-19 19:59:54.224091 | 2017-01-30 | 2016-12-31 | 1 | 950 | / | | | | | | 0.00 | | 1 | 27705.89 | 0.0 | 27705.89 | 9 | 0.0 | 0.0 | f | | 1 | | | 27705.89 | | | | | | t | 0 |
6993 | 2018-02-01 11:38:40.594911 | | 1 | | 2017-12-31 | 1 | | f | | 5 | 0.00 | normal | 5 | | | valid | 27705.89 | Local | 1437 | 36 | 2020-01-19 19:59:54.224091 | 2018-02-01 | 2017-12-31 | 1 | 2262 | / | | | | | | 0.00 | | 1 | 27705.89 | 0.0 | 27705.89 | 9 | 0.0 | 0.0 | f | | 1 | | | 27705.89 | | | | | | t | 0 |
132 | 2015-11-30 19:33:52.738807 | | 1 | | 2015-12-31 | 1 | | f | | 1 | 0.00 | normal | 5 | | | valid | 5844.80 | Local | 1437 | 22 | 2020-01-19 19:59:54.224091 | 2015-11-30 | 2015-12-31 | 1 | 37 | / | | | | | | 0.00 | | 1 | 5844.8 | 0.0 | 5844.8 | 9 | 0.0 | 0.0 | f | | 1 | | | 5844.80 | | | | | | t | 0 |
10990 | 2019-01-28 22:27:49.14709 | | 1 | | 2018-12-31 | 1 | | f | | 5 | 0.00 | normal | 5 | | | valid | 27705.89 | Local | 1437 | 43 | 2020-01-19 19:59:54.224091 | 2019-01-28 | 2018-12-31 | 1 | 3121 | / | | | | | | 0.00 | | 1 | 27705.89 | 0.0 | 27705.89 | 9 | 0.0 | 0.0 | f | | 1 | | | 27705.89 | | | | | | t | 0 |
(4 rows)

Then I check account_asset_asset

select * from account_asset_asset; id | code | create_date | method_end | prorata | salvage_value | write_uid | currency_id | partner_id | create_uid | method_progress_factor | company_id | note | parent_id | state | method_period | date | method | method_number | method_time | write_date | active | name | category_id | ext_method_time | move_end_period | method_percentage | start_depreciation_date | annual_percentage | message_last_post | invoice_id | value
----+------------------+----------------------------+------------+---------+---------------+-----------+-------------+------------+------------+------------------------+------------+------------------------------------------------------------------------------------------------------------------+-----------+-------+---------------+------------+--------+---------------+-------------+---------------------------+--------+------------------+-------------+-----------------+-----------------+-------------------+-------------------------+-------------------+-------------------+------------+---------- 1 | Local | 2015-11-30 18:40:02.276135 | | t | 0.00 | 1 | 1 | 1 | 1 | 0.3 | 1 | Es poden depreciar un 3% i multiplicats per 2 en amortització accel·lerada, per tant un 6% | | open | 12 | 2015-10-16 | linear | 5 | percentage | 2015-11-30 19:33:42.18287 | t | Local| 1 | percentage | t | 6.00 | | 100.00 | | | 461764.8 (1 row)

[cubells]

DETAIL: Key (asset_id)=(1) is not present in table "account_asset".

Rename table account_asset_asset to account_asset.

[cubells]

Or better, duplicate it:

CREATE TABLE account_asset (LIKE account_asset_asset INCLUDING ALL);
insert into account_asset select * from account_asset_asset;
alter table account_asset owner to user_owner_of_table;

[linuxnow]

Thank you, duplicating the table solves this problem and the migration continues until the next errror.

_I had found a manual workaround: update account_move_line set asset_id = NULL where id in (affected_lines_wioth_assetid) which I'd have fixed after migrating.

But now I hit another bug as the fixed percentage method is not supported: Mètode de càlcul | Lineal Mètode de temps basat en | Fixed percentage Prorata Temporis |At the end of the period

2020-01-22 06:01:20,829 1 INFO futini odoo.modules.migration: module l10n_es_account_asset: Running migration [12.0.1.0.0>] post-migration 2020-01-22 06:01:20,834 1 INFO futini OpenUpgrade: l10n_es_account_asset: post-migration script called with version 11.0.1.0.2 2020-01-22 06:01:20,835 1 ERROR futini odoo.sql_db: bad query: UPDATE "account_asset_profile" SET method_time = 'percentage' WHERE "openupgrade_legacy_12_0_method_time" = 'percentage' ERROR: column "openupgrade_legacy_12_0_method_time" does not exist LINE 4: WHERE "openupgrade_legacy_12_0_method_time" ... ^ Any idea?

-- Pau

[cubells]

I had found a manual workaround: update account_move_line set asset_id = NULL where id in (affected_lines_wioth_asset_id) which I'd have fixed after migrating.

Bad idea. You broke the relation between account_move_line and related asset.

[linuxnow]

Any idea on how to solve the openupgrade_legacy_12_0_method_time 'percentage' issue?

[cubells]

Do you have any data in the table account_asset_profile?

You have to understand what openupgrade scripts do, and you have to understand that openupgrade scripts are for standard databases data. If you have not standard database data, then you have three options:

• correct data in your tables
• comment code to not execute migration scripts because migration is not needed.
• update openupgrade scripts that solve standard migration and your custom data, and publish new scripts here.

In this case, you must read post-migration script (A few lines above your log must show you the path of the script and the exact line where openupgrade fails), and then decide what you have to do.

I don't know what data you have in account_asset_profile table.

[linuxnow]

In fact my database should be absolutely standard, I have not used any modules outside l10n_spain or required by these modules. Nonetheless I have found plenty of inconsistencies that I've tried to fix manually.

The only way to finish the migration has been creating account_asset, as you suggested, and removing l10n_es_account_asset which is the only way I've found to skip the openupgrade_legacy_12_0_method_time 'percentage' issue.

After migrating from v12 to v11 account_asset_category has one record but account_asset_profile is empty. I guess it should have been migrated.

Thank you

[cubells]

After migrating from v12 to v11 account_asset_category has one record but account_asset_profile is empty. I guess it should have been migrated.

Ok, if you know what values has to be in account_asset_profile table because you only know what values you have, then insert these values in account_asset_profile table and your migration will be done.

If you prefer, you can send me a private mail with the values you have in v11 and the values that must exist in v12 and I'll do a new pr to correct migration scripts.

I've done several migrations to v12, and I have not problems with account assets tables. I can't say you any more. thanks @linuxnow

[treviser]

@cubells FWIW I have tried to migrate to OCA asset management and I did provide the new OCA asset management module in the addons path, as @pedrobaeza has recommended. In my case, it seems like the pre- and post-migration scripts were not executed at all despite having Openupgrade and openupgradelib properly installed with the actual versions. All new tables were empty and I had to manually copy all relevant tables content and to populate all foreign keys properly, which was a very cumbersome and tedious process.

What I really missed is that there is no logging (in the Odoo log), no progress messages, no error messages, which would help to trace down the issues/causes.

[pedrobaeza]

@treviser that's because you had both account_asset and account_asset_mangement installed in previous version.

I'm closing this issue because no more relevant information has been put, and this kind of conversation should happen in the new OpenUpgrade mailing list: https://odoo-community.org/groups/openupgrade-87?mode=thread

[treviser]

@pedrobaeza Thanks for your reply and I follow your advice, just wanted to remark that is not possible to install both apps on Odoo 11 and so I did not install both of them.

[treviser]

[pedrobaeza]

Yes, but maybe tables are there. The code that checks this is:

https://github.com/OCA/account-financial-tools/blob/418a0ddfaeefe0efaf6d18b6d8b175a0cd1f82d8/account_asset_management/migrations/12.0.1.0.0/pre-migration.py#L82

[treviser]

@pedrobaeza OMG.....knowing that would have saved me 14 hours of work.

So I stay here and would like to make an improvement suggestion:

If these tables already exist and there is no merging function yet available, the premigration script should stop with a corresponding error message, because then IMHO it is useless.

[pedrobaeza]

I don't think so. Everything works OK, but no "merge" of the records is done, so stopping the migration at all is very hard IMO.

[treviser]

Or a loud warning message and a clear documentation? I don't know how this table made it into my customers database, but obviously, it is possible.

[pedrobaeza]

OK, PRs adding that warning is welcome.

[treviser]

FYI: the table did not exist in the 11.0-database I wanted to migrate, so it must have been created somehow during the OpenUpgrade process before the pre-migration script has been executed.