Foo Developer created FINERACT-2868:
---------------------------------------
Summary: Duplicate product name / account number returns "Unknown
data integrity issue" on PostgreSQL instead of the "already exists" error
Key: FINERACT-2868
URL: https://issues.apache.org/jira/browse/FINERACT-2868
Project: Apache Fineract
Issue Type: Bug
Components: Savings
Reporter: Foo Developer
Assignee: Foo Developer
h3. Summary
On PostgreSQL, creating a savings product, fixed deposit product, loan product,
savings/deposit account or loan whose unique field (name, short name, external
ID, account number) is already taken does not return the specific "already
exists" error. The request fails with the generic "Unknown data integrity
issue" message instead, so the user cannot tell which field is the problem.
h3. Steps to reproduce (stock Fineract on PostgreSQL)
# {{POST /fineract-provider/api/v1/savingsproducts}} with a valid body, e.g.
{code:json}{"name":"Dup
SP","shortName":"DSP","currencyCode":"USD","digitsAfterDecimal":2,"inMultiplesOf":0,"nominalAnnualInterestRate":0,"interestCompoundingPeriodType":1,"interestPostingPeriodType":4,"interestCalculationType":1,"interestCalculationDaysInYearType":365,"accountingRule":1,"locale":"en"}
{code}
# Send the same request again (or the same name with a different short name).
# Likewise {{POST /v1/loanproducts}} with the name of an existing loan product.
h3. Expected
{{error.msg.savingsproduct.duplicate.name}} ("Savings product with name {{Dup
SP}} already exists", parameter {{name}}), and
{{error.msg.product.loan.duplicate.name}} for the loan product.
h3. Actual
Both the second savings product request and the loan product request answer
HTTP 403 with the generic fallback:
{code:json}{"httpStatusCode":"403","defaultUserMessage":"Unknown data integrity
issue with savings
product.","userMessageGlobalisationCode":"error.msg.savingsproduct.unknown.data.integrity.issue"}
{code}
{code:json}{"httpStatusCode":"403","defaultUserMessage":"Unknown data integrity
issue with
resource.","userMessageGlobalisationCode":"error.msg.product.loan.unknown.data.integrity.issue"}
{code}
The same happens for a duplicate account number or external ID on
savings/deposit accounts and loans.
h3. Root cause
The {{handleDataIntegrityIssues}} methods match the violated constraint by
name, using the explicit names from the old MySQL schema ({{sp_unq_name}},
{{sp_unq_short_name}}, {{sa_account_no_UNIQUE}}, {{sa_external_id_UNIQUE}} /
{{sa_externalid_UNIQUE}}, {{unq_name}}, {{external_id}},
{{loan_account_no_UNIQUE}}, {{loan_externalid_UNIQUE}}). The Liquibase
changelog declares these columns with a plain {{unique="true"}} and no
constraint name, so on PostgreSQL the constraints get PostgreSQL's generated
names:
||table||constraints on PostgreSQL||
|{{m_savings_product}}|{{m_savings_product_name_key}},
{{m_savings_product_short_name_key}}|
|{{m_savings_account}}|{{m_savings_account_account_no_key}},
{{m_savings_account_external_id_key}}|
|{{m_product_loan}}|{{m_product_loan_name_key}},
{{m_product_loan_external_id_key}}, {{m_product_loan_short_name_key}}|
|{{m_loan}}|{{m_loan_account_no_key}}, {{m_loan_external_id_key}}|
None of the handlers' strings occur in PostgreSQL's {{duplicate key value
violates unique constraint "..."}} message, so every branch is skipped and the
generic fallback is thrown.
{{LoanProductWritePlatformServiceJpaRepositoryImpl}} already works around this
for the short name only ({{containsDuplicateShortnameErrorForPostgreSQL}},
matching {{m_product_loan_short_name_key}}); name and external ID never got the
same treatment.
h3. Proposed fix
In each handler, also match the PostgreSQL-generated constraint name next to
the existing one, without changing the error codes or messages:
* {{SavingsProductWritePlatformServiceJpaRepositoryImpl}},
{{FixedDepositProductWritePlatformServiceJpaRepositoryImpl}} (same
{{m_savings_product}} table)
* {{SavingsApplicationProcessWritePlatformServiceJpaRepositoryImpl}},
{{DepositApplicationProcessWritePlatformServiceJpaRepositoryImpl}}
* {{LoanProductWritePlatformServiceJpaRepositoryImpl}} (name, external ID)
* {{LoanApplicationWritePlatformServiceJpaRepositoryImpl}} (account number,
external ID)
Renaming the constraints in a migration would also work but touches existing
databases; the matching change is local and keeps MySQL/MariaDB behaviour as it
is.
h3. Test
A unit test for {{SavingsProductWritePlatformServiceJpaRepositoryImpl}} that
feeds a PostgreSQL unique-violation ({{m_savings_product_name_key}} /
{{m_savings_product_short_name_key}}) through {{create}} and asserts the
{{duplicate.name}} / {{duplicate.short.name}} codes, plus the existing
MySQL-name case.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
