Bug 3168 - `accountlines.accountno` is SMALL-INT, and maxs too quickly
Summary: `accountlines.accountno` is SMALL-INT, and maxs too quickly
Status: CLOSED INVALID
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: rel_3_0
Hardware: PC All
: P5 - low normal (vote)
Assignee: Galen Charlton
QA Contact:
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2009-04-28 01:07 UTC by Chris Cormack
Modified: 2012-10-26 00:51 UTC (History)
2 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Chris Cormack 2010-05-21 01:07:15 UTC


---- Reported by mason@kohaaloha.com 2009-04-28 13:07:19 ----

Can we bump this to an INT folks?

for example, we have a DB with 1.2 million accountline records
a smallint handles just 32767 records :/

i'll make a patch for this, if people agree with the change...

any thoughts??

------------------------------------
CREATE TABLE `accountlines` (                                                   
  `borrowernumber` int(11) NOT NULL default 0,                                  
  `accountno` smallint(6) NOT NULL default 0, <<< OOPS!!
  `itemnumber` int(11) default NULL,



---- Additional Comments From mason@kohaaloha.com 2009-05-13 10:38:46 ----

(In reply to comment #0)
> Can we bump this to an INT folks?
> 
> for example, we have a DB with 1.2 million accountline records
> a smallint handles just 32767 records :/
> 
> i'll make a patch for this, if people agree with the change...
> 
> any thoughts??
> 
> ------------------------------------
> CREATE TABLE `accountlines` (                                                   
>   `borrowernumber` int(11) NOT NULL default 0,                                  
>   `accountno` smallint(6) NOT NULL default 0, <<< OOPS!!
>   `itemnumber` int(11) default NULL,
> 

this isnt a bug, 

'accountno' column is incremented for *each* borrower - closing



--- Bug imported by chris@bigballofwax.co.nz 2010-05-21 01:07 UTC  ---

This bug was previously known as _bug_ 3168 at http://bugs.koha.org/cgi-bin/bugzilla3/show_bug.cgi?id=3168

Actual time not defined. Setting to 0.0
The original reporter of this bug does not have
   an account here. Reassigning to the person who moved
   it here: chris@bigballofwax.co.nz.
   Previous reporter was mason@kohaaloha.com.

Comment 1 Liz Rea 2011-09-14 16:55:05 UTC
actually you might have been on to something: we just found that a writeoff was grabbing the max accountno *from the whole table* and incrementing that.  It certainly is possible to max it, in those conditions (we did, I have proof).
Comment 2 Liz Rea 2011-09-14 16:55:51 UTC
(the bug in the above comment was fixed with the application of the patch for bug 3498 - but that may be why you were having the feeling that it was maxing quickly)
Comment 3 Fred P 2012-02-09 20:00:03 UTC
Updating to Koha 3.6 will prevent future problems, but libraries where patrons already have several accountlines with accountno 32767 (due to previous Koha writeoff bug) may still experience problems. At our library we currently have more than 319,000 accountlines set to accountno 32767!

When making a payment, several lines may disappear from the pay fines table, because the process intended to act on one line, actually affects all lines with accountno 32767. 

Jorge is authoring a cleanup script that will rewrite accountlines for each patron with sequential accountno's starting at 1. That will reset all 32767 lines to proper sequential values. Will post as soon as it's ready!
Comment 4 Liz Rea 2012-02-09 23:03:40 UTC
Fred: 
NEKLS had this issue, there should be a script available from bugzilla that you can run against your database to clean up the problems.

The patch can be downloaded from bug 6868.

Good Luck!