[ https://issues.apache.org/jira/browse/FINERACT-1946?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Adam Saghy updated FINERACT-1946: --------------------------------- Summary: Random order of primary key generation at reverse-replaying multiple transactions on the same day (was: Random order of primary key generation at reverse-replaying multiple transactions) > Random order of primary key generation at reverse-replaying multiple > transactions on the same day > ------------------------------------------------------------------------------------------------- > > 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 > Fix For: 1.9.0 > > > 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)