[Koha-devel] AUTO_INCREMENT fix - prevent data lost

[Ulrich Kleiber]

Hi Jonathan,

I have tested the solution from 
https://wiki.koha-community.org/wiki/DBMS_auto_increment_fix.
A restart of the MySQL Server with a large Koha Database (several 
hundred thousand biblio) or a large number of Koha Sites can take a long 
time. This is mainly because the ALTER TABLE command rebuilds the entire 
table.
Usually you can save a lot of time and resources when you update 
AUTO_INCREMENT only if necessary. Here is an attempt to solve this using 
the example of biblioitems/deletedbiblioitems.

USE koha_kohadev;

SET @ai = ( IFNULL( ( SELECT AUTO_INCREMENT FROM 
information_schema.tables WHERE TABLE_SCHEMA = 'koha_kohadev' AND 
TABLE_NAME = 'biblioitems' ), 0 ) );

SET @shadow_ai = ( IFNULL( ( SELECT MAX(biblioitemnumber) FROM 
deletedbiblioitems ), 0 ) + 1 );

SET @sql = ( SELECT IF(@shadow_ai > @ai, CONCAT( 'ALTER TABLE 
biblioitems AUTO_INCREMENT = ', @shadow_ai ), 'SELECT "skip"' ) );

PREPARE st FROM @sql;

EXECUTE st;

Cheers,
Uli

Ulrich Kleiber
Bibliotheksservice-Zentrum Baden-Württemberg (BSZ)
78457 Konstanz / Germany
Phone: +49 7531 88 4179
E-Mail: ulrich.kleiber at bsz-bw.de
http://www.bsz-bw.de

> On Wed, 12 Jul 2017 at 15:11 Jonathan Druart 
> <jonathan.druart at bugs.koha-community.org 
> <mailto:jonathan.druart at bugs.koha-community.org>> wrote:
> 
>     Hi devs,
> 
>     Please review and test
>     https://wiki.koha-community.org/wiki/DBMS_auto_increment_fix
> 
>     I have added a ref from the Koha on Debian wiki page
>     https://wiki.koha-community.org/wiki/Koha_on_Debian#Further_Configuration
>     It sounds like I should add it somewhere more visible, but do not
>     find where, any ideas?
> 
>     See also bug 18931 where I would like to add a warning on the about
>     page if data are corrupted.
> 
>     Please review quickly, that way we can share the tips on the general
>     mailing list.
> 
>     Cheers,
>     Jonathan
> 
> 
> 
> _______________________________________________
> Koha-devel mailing list
> Koha-devel at lists.koha-community.org
> http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel
> website : http://www.koha-community.org/
> git : http://git.koha-community.org/
> bugs : http://bugs.koha-community.org/
> 

-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 5371 bytes
Desc: S/MIME Cryptographic Signature
URL: <http://lists.koha-community.org/pipermail/koha-devel/attachments/20170809/5e73c31e/attachment.bin>