[
https://issues.apache.org/jira/browse/FINERACT-1946?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Adam Saghy updated FINERACT-1946:
---------------------------------
Description:
Due to the nature of JPA specification the order of the persistence of the
managed entities are not defined.
When multiple entities are reverse-replayed the new entities will be persisted
not in a defined order hence the IDENTITY primary key generation:
"{_}Since the provider needs to get the generated ID after each {{{}INSERT{}}},
it splits the batch operation to single {{INSERT}} operators and fetches the
generated ID value after each execution. We just cannot send a batch of
{{{}INSERT statements and get a batch of generated IDs because we won’t be able
to associate generated IDs to JPA objects reliably. The reason is that database
does not guarantee that the order of the generated IDs will be the same as the
order of INSERTS{}}}. Moreover, {{INSERT}} statements may not be executed in
the same order as in the batch. So, the only reliable way to get IDs for
inserted records – split the batch.{_}"
source: [https://jpa-buddy.com/blog/the-ultimate-guide-on-db-generated/]
Since the loan transactions are ordered primarily based on transaction id and
secondarily based on the primary key if multiple transactions with the same
transaction date got reverse-replayed the order of the transactions might be
changed and hence not consistent.
This could be a major problem, however it can be considered as an edge case in
most situations.
h3. Solution
*Step 1* - Ordering should be based on {{transaction date}} + {{creation date}}
+ {{ID}} in this order (For years mysql was used with second precision. That
implies keeping the ID in the ordering sequence.)
*Step 2* - Set precision of 6 for DATETIME / TIMESTAMP for Mysql / MariaDB
(PostgresDB is not effected, by default it is using precision of 6 digits after
seconds)
*It should not cause any backward compatibility issues by default, but having
more precision we can rely on the ordering by creation date time for the same
day transactions!*
*Moreover, the* {{LoanTransactionComparator}} *was using* {{creation date
time}} *as the secondary ordering attribute since 2015!*
*Acceptance criteria*
* The order of the transactions shall be consistent after the reverse-replaying
* The order of the reverse-replayed transactions shall match with the original
order
was:
Due to the nature of JPA specification the order of the persistence of the
managed entities are not defined.
When multiple entities are reverse-replayed the new entities will be persisted
not in a defined order hence the IDENTITY primary key generation:
"{_}Since the provider needs to get the generated ID after each {{{}INSERT{}}},
it splits the batch operation to single {{INSERT}} operators and fetches the
generated ID value after each execution. We just cannot send a batch of
{{INSERT }}statements and get a batch of generated IDs because we won’t be able
to associate generated IDs to JPA objects reliably. The reason is that database
does not guarantee that the order of the generated IDs will be the same as the
order of {{{}INSERTS{}}}. Moreover, {{INSERT}} statements may not be executed
in the same order as in the batch. So, the only reliable way to get IDs for
inserted records – split the batch.{_}"
source: https://jpa-buddy.com/blog/the-ultimate-guide-on-db-generated/
Since the loan transactions are ordered primarily based on transaction id and
secondarily based on the primary key if multiple transactions with the same
transaction date got reverse-replayed the order of the transactions might be
changed and hence not consistent.
This could be a major problem, however it can be considered as an edge case in
most situations.
h3. Solution
*Step 1* - Ordering should be based on {{transaction date}} + {{creation date}}
+ {{ID}} in this order (For years mysql was used with second precision. That
implies keeping the ID in the ordering sequence.)
*Step 2* - Set precision of 6 for DATETIME / TIMESTAMP for Mysql / MariaDB
(PostgresDB is not effected, by default it is using precision of 6 digits after
seconds)
*It should not cause any backward compatibility issues by default, but having
more precision we can rely on the ordering by creation date time for the same
day transactions!*
*Moreover, the* {{LoanTransactionComparator}} *was using* {{creation date
time}} *as the secondary ordering attribute since 2015!*
*Acceptance criteria*
* The order of the transactions shall be consistent after the reverse-replaying
* The order of the reverse-replayed transactions shall match with the original
order
> Random order of primary key generation at reverse-replaying multiple
> transactions
> ---------------------------------------------------------------------------------
>
> Key: FINERACT-1946
> URL: https://issues.apache.org/jira/browse/FINERACT-1946
> Project: Apache Fineract
> Issue Type: Bug
> Affects Versions: 1.8.3
> Reporter: Adam Saghy
> Priority: Major
>
> Due to the nature of JPA specification the order of the persistence of the
> managed entities are not defined.
> When multiple entities are reverse-replayed the new entities will be
> persisted not in a defined order hence the IDENTITY primary key generation:
> "{_}Since the provider needs to get the generated ID after each
> {{{}INSERT{}}}, it splits the batch operation to single {{INSERT}} operators
> and fetches the generated ID value after each execution. We just cannot send
> a batch of {{{}INSERT statements and get a batch of generated IDs because we
> won’t be able to associate generated IDs to JPA objects reliably. The reason
> is that database does not guarantee that the order of the generated IDs will
> be the same as the order of INSERTS{}}}. Moreover, {{INSERT}} statements may
> not be executed in the same order as in the batch. So, the only reliable way
> to get IDs for inserted records – split the batch.{_}"
> source: [https://jpa-buddy.com/blog/the-ultimate-guide-on-db-generated/]
> Since the loan transactions are ordered primarily based on transaction id and
> secondarily based on the primary key if multiple transactions with the same
> transaction date got reverse-replayed the order of the transactions might be
> changed and hence not consistent.
> This could be a major problem, however it can be considered as an edge case
> in most situations.
> h3. Solution
> *Step 1* - Ordering should be based on {{transaction date}} + {{creation
> date}} + {{ID}} in this order (For years mysql was used with second
> precision. That implies keeping the ID in the ordering sequence.)
> *Step 2* - Set precision of 6 for DATETIME / TIMESTAMP for Mysql / MariaDB
> (PostgresDB is not effected, by default it is using precision of 6 digits
> after seconds)
> *It should not cause any backward compatibility issues by default, but having
> more precision we can rely on the ordering by creation date time for the same
> day transactions!*
> *Moreover, the* {{LoanTransactionComparator}} *was using* {{creation date
> time}} *as the secondary ordering attribute since 2015!*
> *Acceptance criteria*
> * The order of the transactions shall be consistent after the
> reverse-replaying
> * The order of the reverse-replayed transactions shall match with the
> original order
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
