[Koha-bugs] [Bug 18966] Move of checkouts - Deal with duplicate IDs at DBMS level

[bugzilla-daemon at bugs.koha-community.org]

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=18966

Julian Maurice <julian.maurice at biblibre.com> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
  Attachment #65203|0                           |1
        is obsolete|                            |

--- Comment #19 from Julian Maurice <julian.maurice at biblibre.com> ---
Created attachment 65272
  -->
https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=65272&action=edit
Bug 18966: Do not deal with duplicate issue_id on checkin

Koha suffers of big bugs due to its history: When data are deleted, they
are moved to another tables.
For instance issues and old_issues: when a checkin is done, it is moved
to the old_issues table.
That leads to a main problem that is described on
https://wiki.koha-community.org/wiki/DBMS_auto_increment_fix

However we tried first to fix the problem (for issues/old_issues) at
code level on bug 18242.
The goal was to prevent data lost.
Data lost may happens in this case:
Check an item out (issue_id = 1)
Check an item in (issue_id = 1)
Restart MySQL (reset auto increment for issue_id to 1)
Check an item out (issue_id = 1)
Check an item in => BOOM, the issue_id is a PK in old_issues and the
move fails.
Before bug 18242 the data were lost, we inserted the value into
old_issues, which fails silently (because of RaiseError set to 0 in
Koha::Database), then delete the row from issues.
That has been fixed using a transaction.

This patch introduced a regression we tried to fix on bug 18651 comment
0, the patron was charged even if the checkin was rejected.
A good way to fix that would have been to LOCK the tables:
1- Start a transaction
2- LOCK the table to make sure nobody will read id and avoid race
   conditions
3- Move the content from one table to the other, dealing with ids
4- UNLOCK the table
5- Commit the transaction
But there were problems using LOCK and DBIx::Class (See commit
905572910b3a - Do no LOCK/UNLOCK the table).

Finally the solution implemented is not acceptable for several reasons:
- batch checkins may fail
- issue_id will always stay out of sync (between issues and old_issues)
See 18651 comment 66.

Since the next stable releases are very soon, and we absolutely need to
fix this problem, I am suggesting to:
1- Execute the move in a transaction to avoid data lost and reject the
   checkin if we face IDs dup
=> It will only reject 1 checkin (max is 1 * MySQL restart), no need to
   deal with race conditions,
2- Display a warning on the checkin page and link to a
   solution/explanation
3- Communicate as much as we can on the proper fix: Update auto
   increment values when the DBMS is restarted -
    https://wiki.koha-community.org/wiki/DBMS_auto_increment_fix
4- Display a warning on the about page for corrupted data (see bug
   18931)
5- Write and make available a maintenance script to fix corrupted data
   (TODO LATER)

Signed-off-by: Tomas Cohen Arazi <tomascohen at theke.io>
Signed-off-by: Julian Maurice <julian.maurice at biblibre.com>

-- 
You are receiving this mail because:
You are watching all bug changes.