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.klei...@bsz-bw.de
http://www.bsz-bw.de

On Wed, 12 Jul 2017 at 15:11 Jonathan Druart <jonathan.dru...@bugs.koha-community.org <mailto:jonathan.dru...@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@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/


Attachment: smime.p7s
Description: S/MIME Cryptographic Signature

_______________________________________________
Koha-devel mailing list
Koha-devel@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/

Reply via email to