V11: Error while creating a table with a long name on Oracle

[Nicolas Micoud]

Hi,

I got an error when creating a table with a long name.
Everything seems correct, and I noticed the error when trying to reimport it using 2Pack.
It throws a 'Failed to validate AD_TableIndex for NMD_TestVeryLongTableName_uu_idx' error (just did it on https://test-oracle.idempiere.org).

AFAIU, the TableIndex name generated contains _  (eg: NMD_TestTestTestTestTestTes_uu_idx).
But the DDL is
CONSTRAINT "NMD_TESTVERYLONGTABLENAMEUUIDX" UNIQUE ("NMD_TESTVERYLONGTABLENAME_UU")

So is not possible to drop or validate the index as there is a difference between what is in DB and in AD.

If I manually change the Index name to 'NMD_TESTTESTTESTTESTTESTTUUIDX', I'm able to drop it/validate it.

Not reproducible on PostgreSQL

https://test-oracle.idempiere.org/webui/index.zul?Action=Zoom&AD_Table_ID=50008&Record_ID=1000003

https://test.idempiere.org/webui/index.zul?Action=Zoom&AD_Table_ID=50008&Record_ID=1000008

I guess it isrelated to https://wiki.idempiere.org/en/NF11_UUID_Key_Tables but don't know where to start :-/

Thanks,

Nicolas

[Carlos Antonio Ruiz Gómez]

Hi Nicolas,

I'm checking, I think is not related to the ticket you mention, but anyways it is a bug and is better to open a JIRA ticket for that.

On my tests this is the situation:

postgresql is left with two constraints (duplicated):

    CONSTRAINT nmd_testverylongtablename_uu_idx UNIQUE (nmd_testverylongtablename_uu),   <- created by 2pack
    CONSTRAINT nmd_testverylongtablenameuuidx UNIQUE (nmd_testverylongtablename_uu),     <- created automatically

oracle doesn't allow that and errors with:
ORA-02261: such unique or primary key already exists in the table

Regards,

Carlos Ruiz

[Carlos Antonio Ruiz Gomez]

Found the cause with some extra debugging:

in org.compiere.model.MColumn.getConstraint the constraint is created automatically, the line 739 has a condition (because of old oracle restriction) that if the index name was longer that 30 chars then it was cut to the first 25+uuidx

So, as you pointed, I think the solution is to change your index name to match the name generated by iDempiere.

On the other hand - the ticket could be solved showing a friendlier error message to the user.

Regards,

Carlos Ruiz

Am 08.03.24 um 12:08 schrieb Carlos Antonio Ruiz Gómez:

[Nicolas Micoud]

Hi Carlos,

Not sure I have the whole picture, but wouldn't it make sense to validate the name of the index using MColumn.getConstraint when is created?

[Nicolas Micoud]

I used CreateTable process yesterday.

So the fix can be done there.

Haven't tested, but what about replacing line 243

MTableIndex tiuu = new MTableIndex(table, table.getTableName() + "_uu_idx");

with the same "fix" if index name has more than 30 characters.

(or create a 'central' method that will return a valid index name ; it would be called by CreateTable process and MColumn.getConstraint)

wdyt?

[Carlos Antonio Ruiz Gomez]

The "Create/Complete Table" process doesn't accept table names longer than 27 chars

So, I suppose when executed from the button is reaching the condition you mention,

So, yes, the line 243 must be consistent with the other method, that must be the bug to fix there.

Am 08.03.24 um 13:29 schrieb Nicolas Micoud: