Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

oracle has M_INVENTORYLINEMA_KEY is reason error when complete Internal Use Inventory

Description

1. at https://test-oracle.idempiere.org/webui/index.zul create Material Receipt for PTable with qty = 20
so PTable has 2 onhand record with qty 12 and 20
2. create Internal Use Inventory (docno=10000000) for PTable with qty = 15 and compete
3. system create 2 record M_INVENTORYLINEMA with same M_INVENTORYLINE_ID, M_ATTRIBUTESETINSTANCE_ID
it violated unique constraint M_INVENTORYLINEMA_KEY so get error

Could not save record - Require unique data: Please change information.: ORA-00001: unique constraint (IDEMPIERE.M_INVENTORYLINEMA_KEY) violated

reason: oracle database has unique constraint M_INVENTORYLINEMA_KEY but postgresql hasn't

Environment

None

Attachments

1
  • 03 Feb 2021, 04:45 PM

Activity

Show:

Carlos Ruiz February 3, 2021 at 4:45 PM

Hi

I did an audit of oracle indexes vs postgresql, this is what I found:

  • 1347 identical indexes

  • 549 same definition with different name -> _pkey in postgres, _key in oracle

  • 37 same definition with different name

  • 9 duplicated indexes in postgresql (same columns, different name)

  • 4 different order in columns and name between postgresql and oracle

  • 2 in oracle that doesn't exist in postgresql

Attached is the spreadsheet with the findings:

 

A script to solve at least the last three would be good.

 

Regards,

Carlos Ruiz

Hiep Lq February 3, 2021 at 2:31 PM

so i should create a PR for drop index?

Carlos Ruiz February 3, 2021 at 12:03 PM

Hi - I think the script was executed, at least the element on the first line exists in oracle.

The problem must be that this statement on line 315:

ALTER TABLE M_InventoryLineMA DROP CONSTRAINT m_inventorylinema_key

drops the constraint but it doesn’t drop the index.

Regards,

Carlos Ruiz

Hiep Lq January 29, 2021 at 9:29 AM

seem this script isn't include on dump
migration-historic/i1.0z/oracle/201310141246_IDEMPIERE-386.sql

Hiep Lq January 29, 2021 at 9:22 AM

Fixed

Details

Assignee

Reporter

Labels

Fix versions

Affects versions

Priority

Created January 29, 2021 at 9:15 AM
Updated December 2, 2023 at 1:23 PM
Resolved October 18, 2023 at 11:40 AM