Copy Client (MoveClient) v12 - Several errors found using seed DB, looking for workarounds without touching the code

[Sergio Oropeza]

Hi everyone,

I've been working with the Copy Client process (MoveClient) in iDempiere v12 and I've run into several errors that I'd like to share with the community. I'm testing on the v12 seed database (minimal data), so in a real production DB with more data and customized tables, these errors will likely multiply.

My questions are at the bottom, but first I want to document what I found in case it's useful to others.

---

ENVIRONMENT

• iDempiere v12
• Seed database (minimal client data)
• Custom plugin installed: AMERPSOFT Editor - Location Extended by Luis Amesty GitHub: https://github.com/luisamesty/Amerpsoft-iDempiere-community This plugin extends the Location field reference to support additional political/demographic divisions (municipalities, parishes, etc.) beyond the standard country/region/city. It registers a custom AD_Reference with ID > 999999, which is directly related to one of the errors described below.

---

ERRORS FOUND (in order of appearance)

1. Non-official FK columns from plugins not recognized

Error: "There is data in unsupported non-official data type for column AD_OrgInfo.C_Location_ID (refID=1000004)"

This error is triggered specifically by the AMERPSOFT Location Extended plugin. The plugin registers a custom reference (AD_Reference_ID > 999999) for the C_Location_ID column. The MoveClient process does not support custom references from plugins — it only recognizes official references with AD_Reference_ID <= 999999. When it finds a column with a higher refID, it checks if there's data in it and aborts with the above error, even though the column is a perfectly valid FK pointing to C_Location.

In short: custom plugin references (AD_Reference_ID > 999999) are not supported by MoveClient v12.

Workaround: Enable "Skip Some Validations" parameter. This bypasses the check but also skips other validations that may be important.

2. CreatedBy / UpdatedBy validated as orphan FKs

The process throws orphan errors on these columns during validateOrphan(). In iDempiere by design, CreatedBy and UpdatedBy have NO physical FK constraint in PostgreSQL. The process treats them as real FKs and fails with false positives.

Workaround: Enable "Skip Some Validations". This skips the entire validateOrphan() phase.

Note: errors 1 and 2 are both resolved by "Skip Some Validations". The following errors are not.

3. AD_Attachment - duplicate key on ad_attachment_record

Error: "ERROR: duplicate key violates unique constraint ad_attachment_record. Key (ad_table_id, record_id)=(50008, 1000279) already exists"

When doing Copy Client within the same database, the source attachment record already exists and the converted Record_ID collides with it.

Workaround: Add AD_Attachment to "Tables to Exclude". Attachments won't be copied but the process continues.

4. AD_Private_Access - orphan Record_ID pointing to C_Invoice

Error: "Found orphan record in AD_Private_Access.Record_ID: 1000245 related to table C_Invoice"

AD_Private_Access is a polymorphic FK table (like AD_ChangeLog). It gets processed alphabetically before C_Invoice is inserted, so the FK resolution fails. AD_ChangeLog already has special treatment in canIgnoreNullConvertedId() but AD_Private_Access doesn't.

Workaround: Add AD_Private_Access to "Tables to Exclude".

5. Process stops at the first orphan found

This is probably the biggest usability issue: every run reveals exactly one error then stops. To discover all the problems you have to run the process N times. There's no way to get a complete picture of all conflicts in a single pass.

No workaround found for this one.

6. C_Currency - duplicate key on ISO_Code

Error: "ERROR: duplicate key violates unique constraint c_currencyisocode. Key (iso_code)=(usd) already exists"

System currencies (USD, EUR, etc.) already exist in the target DB. The process tries to re-insert them and aborts.

Workaround: Add C_Currency to "Tables to Exclude". Currency references still resolve correctly since currency IDs are system-level (< 999999).

---

STRUCTURAL OBSERVATIONS (not a complaint, just feedback for the community)

After going through all of this, I think some of these behaviors are worth discussing as potential improvements:

• Tables are inserted in alphabetical order (ORDER BY TableName), not in FK dependency order. This is the root cause of most orphan errors since parent tables aren't guaranteed to be inserted before child tables.

• Custom plugin references (AD_Reference_ID > 999999) are not supported at all. There is no mechanism to recognize plugin-registered FKs as valid, which makes the process incompatible out of the box with any environment that has third-party plugins extending the dictionary.

• There's no handling for unique constraint violations on INSERT. The process only accounts for FK orphans but not for cases where system data (currencies, UOMs, countries) already exists in the target DB.

• The fail-fast behavior makes diagnosis very slow when there are multiple issues. A "collect all errors and report at end" mode would help enormously, especially when working with the seed DB where you discover issues one by one per run.

• Audit columns (CreatedBy, UpdatedBy) and certain system tables (AD_Session, AD_ChangeLog, AD_Package_Imp*, AD_PInstance*) have no default exclusion in code, putting the burden entirely on the user to configure the right exclusion list.

• The pre-validation phase (validate()) and execution phase (moveClient()) detect different problems with partially duplicated logic, so passing validation doesn't guarantee a clean execution.

---

MY QUESTIONS

1. For errors 3, 4, 5 and 6 described above, the only workaround I found is adding the affected tables to "Tables to Exclude". Is that really the only option without modifying the code, or is there something else I'm missing?

2. Is there a recommended or official exclusion list for a typical Copy Client operation in v12? I know the wiki mentions some tables but I haven't found a comprehensive one that covers system tables like AD_Attachment, AD_Private_Access, C_Currency and similar.

3. Has anyone else hit these issues on production DBs? With real data volumes I imagine the list of affected tables grows significantly beyond what I'm seeing with the seed DB.

4. Regarding the fail-fast behavior — every run reveals exactly one error then stops, making diagnosis extremely slow. Is there any known trick or parameter to get a full picture of all conflicts in a single run without modifying the code?

5. Is there an open ticket or active discussion about improving the resilience of this process? I noticed Carlos Ruiz (globalqss) has a dedicated repo for MoveClient — is that the right place to follow up or should I open a ticket in the official tracker?

Thanks in advance, any input is appreciated.

[Carlos Antonio Ruiz Gomez]

Hi Sergio,

Let me answer some of the questions (probably not all).

> Non-official FK columns from plugins not recognized

Yes, in principle, how can the core know that AMERPSOFT Location is exactly equivalent to C_Location_ID?

To achieve that I think we would need to add some new methods to the interface that creates new field types.  But at this moment is not possible to know if a custom field is a FK or a JSON or whatever.

Having said that, I wonder why the need to create a new reference for the location?  There can be other ways to customize the location dialog.

> CreatedBy / UpdatedBy validated as orphan FKs

Yes, that's needed, we must not allow copying/inserting wrong data, even if the database doesn't enforce the FK.

> AD_Attachment - duplicate key on ad_attachment_record

This sounds strange, would like to know more about the case that is triggering this problem.

> AD_Private_Access - orphan Record_ID pointing to C_Invoice

This is also strange, probably you really have an orphan there.

AD_ChangeLog is a very special table because AD_ChangeLog_ID is not unique.  But that's not the case for AD_Private_Access, in general AD_Table_ID+Record_ID must be managed without problems.

> C_Currency - duplicate key on ISO_Code

This is also weird, are you creating currencies in tenant?

> Tables are inserted in alphabetical order (ORDER BY TableName), not in FK dependency order

That's not a big deal, all iDempiere foreign keys are defined as DEFERRABLE INITIALLY DEFERRED, so they are validated on commit.

Figuring out the FK dependency order is a nightmare, to not say that probably is not possible because there are "deadlock" dependencies.

> There's no handling for unique constraint violations on INSERT

It is not expected, that's why there is no handling, you can have problems in your data.

> Is there a recommended or official exclusion list for a typical Copy Client operation in v12?

That depends totally on your data.

> Has anyone else hit these issues on production DBs?

Yes, with databases with wrong data, f.e. cross tenant IDs.

> Regarding the fail-fast behavior — every run reveals exactly one error then stops, making diagnosis extremely slow. Is there any known trick or parameter to get a full picture of all conflicts in a single run without modifying the code?

Again, there must be problems with your data, some FKs are reported all at once, not failing on the first.

> Is there an open ticket or active discussion about improving the resilience of this process? I noticed Carlos Ruiz (globalqss) has a dedicated repo for MoveClient — is that the right place to follow up or should I open a ticket in the official tracker?

No, I don't have a dedicated repo for that, it is in core, you can open JIRA tickets and pull requests same as in core.

Regards,

Carlos Ruiz

--

[Sergio Oropeza]

Hi Carlos,

Thank you for the detailed response, really appreciated.

Let me clarify the cases that seemed strange to you:

---

AD_Attachment - duplicate key

I should have been more specific in the original post. I'm using the Copy Client mode (not moving from an external database), meaning source and target are the same database.

I did some debugging and found the root cause. The attachment points to a system table record (AD_Package_Imp_Proc, AD_Table_ID=50008). Since that table has AD_Client_ID=0, it's never processed into T_MoveClient. The conversion falls back to getLocalKeyFor, which finds the same system record by UUID and returns the original ID unchanged. The new AD_Attachment is then inserted with the same (AD_Table_ID, Record_ID) combination that already exists in the target DB, triggering the unique constraint on ad_attachment_record.

So it's not corrupt data — it's a structural case where Copy Client tries to copy an attachment pointing to a system record, and since system IDs are preserved, the constraint fires. Workaround for now: exclude AD_Attachment from the copy. A possible fix would be to skip attachments pointing to system records when a duplicate already exists in the target.

---

C_Currency - duplicate key

Same clarification here. I'm not creating currencies at tenant level. The USD currency exists at system level (AD_Client_ID=0) in the target DB, and the process is trying to re-insert it. My guess is this is related to the seed DB having some currency data associated to the template client that overlaps with system data.

---

AD_Private_Access - orphan

Understood, if the process says it's an orphan it probably is. I'll investigate the data directly to confirm whether that AD_Private_Access record is genuinely pointing to a non-existing C_Invoice.

---

Regarding the alphabetical order and DEFERRABLE INITIALLY DEFERRED

Thank you for clarifying that, I wasn't aware that all iDempiere FKs are defined as DEFERRABLE INITIALLY DEFERRED. That completely changes the picture and invalidates part of my analysis. Good to know.

---

General takeaway

Based on your answers and the debugging session, the AD_Attachment case seems like a legitimate edge case worth a JIRA ticket. For the rest, I'll do a data cleanup pass and retry. If after cleanup I still reproduce the C_Currency issue in a clean scenario, I'll open a ticket with full reproduction steps as well.

Thanks again for your time and patience Carlos.

Regards,
Sergio

[Carlos Antonio Ruiz Gomez]

Hi Sergio,

> AD_Attachment - duplicate key

Well, this was allowed before but is forbidden now, so, is kind of a bad data case.

My recommendation would be to check why that attachment is in the tenant, and move it to System.

See IDEMPIERE-6314 - there is explained why we closed this option.  Is kind of a security issue.

Regards,

Carlos Ruiz