[Important] Update your MySQL|MariaDB configuration to avoid data loss
Hello everybody, This is an important email and you should not ignore it. If you are a librarian and have no technical skills you should forward this email to the administrator of your Koha installation. We have been dealing with circulation history loss in the last versions of Koha, and we ended up with a solution. You should read the following wiki page attentively and set up the proposed solution: https://wiki.koha-community.org/wiki/DBMS_auto_increment_fix Cheers, Jonathan
For a multi-tenant installation with multiple MySQL databases... "The Solution" (https://wiki.koha-community.org/wiki/DBMS_auto_increment_fix) recommends adding "init-file=/var/lib/mysql/init-file_kohadev.sql" in the my.cnf MySQL configuration file, then creating the appropriately configured "init-file_kohadev.sql" file. Where there are multiple Koha databases, should one add multiple "init-file=..." statements to my.cnf as well as multiple init-file_kohadev.sql files? Or will MySQL only accept one init-file directive in which case the .sql file would include multiple blocks of code...one for each database? gcb -----Original Message----- From: Koha [mailto:koha-bounces@lists.katipo.co.nz] On Behalf Of Jonathan Druart Sent: Tuesday, August 01, 2017 6:23 AM To: koha@lists.katipo.co.nz; koha-devel@lists.koha-community.org Subject: [Koha] [Important] Update your MySQL|MariaDB configuration to avoid data loss Hello everybody, This is an important email and you should not ignore it. If you are a librarian and have no technical skills you should forward this email to the administrator of your Koha installation. We have been dealing with circulation history loss in the last versions of Koha, and we ended up with a solution. You should read the following wiki page attentively and set up the proposed solution: https://wiki.koha-community.org/wiki/DBMS_auto_increment_fix Cheers, Jonathan _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
On Tue, 1 Aug 2017, Bortel, Gary wrote:
For a multi-tenant installation with multiple MySQL databases...
"The Solution" (https://wiki.koha-community.org/wiki/DBMS_auto_increment_fix) recommends adding "init-file=/var/lib/mysql/init-file_kohadev.sql" in the my.cnf MySQL configuration file, then creating the appropriately configured "init-file_kohadev.sql" file. Where there are multiple Koha databases, should one add multiple "init-file=..." statements to my.cnf as well as multiple init-file_kohadev.sql files? Or will MySQL only accept one init-file directive in which case the .sql file would include multiple blocks of code...one for each database?
I'd say that its up to you really - both will work. If you use just one "init-file_kohadev.sql" file you'll just have to repeat all the SQL, just changing the database in the "use" line. If you've got lots of Koha instances that file is going to be quite long. Personally for tidiness and ease of maintenance I'd probably opt for one init file per Koha database, as those would be clearer and could then be maintained more easily using normal Linux command line tools and a few Perl scripts. I assume people hosting lots of tenants are probably using tools like Ansible to set up provisioning of a new Koha instance so these files would be created from a template and just one line added/removed from the my.conf file. Jon
I tried implementing multiple init-file= statements(...one for each instance of Koha hosted on our machine...) in our my.cnf file along with multiple init-file_kohaxxx.sql files. Looking at the mysql error.log, it appears only the last init-file= statement was read and executed upon restart. I've dropped back to using a single init-file= statement and one mega-file incorporating sql code for all Koha instance. Things seem to be working fine for now. Thanks gcb -----Original Message----- From: Jon Knight [mailto:J.P.Knight@lboro.ac.uk] Sent: Wednesday, August 02, 2017 2:46 AM To: Bortel, Gary <gary.bortel@sos.wa.gov> Cc: koha@lists.katipo.co.nz Subject: Re: [Koha] [Important] Update your MySQL|MariaDB configuration to avoid data loss On Tue, 1 Aug 2017, Bortel, Gary wrote:
For a multi-tenant installation with multiple MySQL databases...
"The Solution" (https://wiki.koha-community.org/wiki/DBMS_auto_increment_fix) recommends adding "init-file=/var/lib/mysql/init-file_kohadev.sql" in the my.cnf MySQL configuration file, then creating the appropriately configured "init-file_kohadev.sql" file. Where there are multiple Koha databases, should one add multiple "init-file=..." statements to my.cnf as well as multiple init-file_kohadev.sql files? Or will MySQL only accept one init-file directive in which case the .sql file would include multiple blocks of code...one for each database?
I'd say that its up to you really - both will work. If you use just one "init-file_kohadev.sql" file you'll just have to repeat all the SQL, just changing the database in the "use" line. If you've got lots of Koha instances that file is going to be quite long. Personally for tidiness and ease of maintenance I'd probably opt for one init file per Koha database, as those would be clearer and could then be maintained more easily using normal Linux command line tools and a few Perl scripts. I assume people hosting lots of tenants are probably using tools like Ansible to set up provisioning of a new Koha instance so these files would be created from a template and just one line added/removed from the my.conf file. Jon
Hi Jonathan, This still feels like a workaround to me and not a real long-term solution. If someone uses koha-create or koha-remove, they still need to remember to update that MySQL init-file, as that won’t happen automatically. I think it would require a lot of on-going maintenance that not all Koha users are able to achieve. Plus, if they have a lot of Koha instances, that init file is going to be huge and difficult to manage. I was thinking another option might be to add a trigger for borrowers, biblio, biblioitems, items, issues, and reserves, which makes sure that the number selected is higher than any existing number from the table or deletedtable. Of course, that might slow down inserts a bit. I haven’t tried it. If we use triggers, we could add/remove them along with koha-create and koha-remove dynamically. (See the comments of https://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html#...). I think that would be a more elegant solution. I think it’s one that could also be handled via updatedatabase.pl for people upgrading without any administrator intervention. I might actually look at implementing that locally. I’m happy to do that and report back. I use BEFORE INSERT triggers in PostgreSQL on non-Koha databases with a much higher volume of transactions, and they work well. But I’m happy to try and then report back. David Cook Systems Librarian Prosentient Systems 72/330 Wattle St Ultimo, NSW 2007 Australia Office: 02 9212 0899 Direct: 02 8005 0595 From: koha-devel-bounces@lists.koha-community.org [mailto:koha-devel-bounces@lists.koha-community.org] On Behalf Of Jonathan Druart Sent: Tuesday, 1 August 2017 11:23 PM To: koha@lists.katipo.co.nz; koha-devel@lists.koha-community.org Subject: [Koha-devel] [Important] Update your MySQL|MariaDB configuration to avoid data loss Hello everybody, This is an important email and you should not ignore it. If you are a librarian and have no technical skills you should forward this email to the administrator of your Koha installation. We have been dealing with circulation history loss in the last versions of Koha, and we ended up with a solution. You should read the following wiki page attentively and set up the proposed solution: https://wiki.koha-community.org/wiki/DBMS_auto_increment_fix Cheers, Jonathan
Hello list, I have upgraded to 17.05.02 and taken care of two duplicate IDs on one of my instances. Do I still need to make the changes to the /etc/myscl/my.cnf file? Or did the fix in 17.05.02 make that unnecessary? Thanks, Doug From: koha-devel-bounces@lists.koha-community.org [mailto:koha-devel-bounces@lists.koha-community.org] On Behalf Of Jonathan Druart Sent: Tuesday, August 1, 2017 7:23 AM To: koha@lists.katipo.co.nz; koha-devel@lists.koha-community.org Subject: [Koha-devel] [Important] Update your MySQL|MariaDB configuration to avoid data loss Hello everybody, This is an important email and you should not ignore it. If you are a librarian and have no technical skills you should forward this email to the administrator of your Koha installation. We have been dealing with circulation history loss in the last versions of Koha, and we ended up with a solution. You should read the following wiki page attentively and set up the proposed solution: https://wiki.koha-community.org/wiki/DBMS_auto_increment_fix Cheers, Jonathan
Yes, the fix is for all Koha versions. On Thu, 31 Aug 2017 at 13:24 Doug Dearden <dearden@sarsf.org> wrote:
Hello list,
I have upgraded to 17.05.02 and taken care of two duplicate IDs on one of my instances. Do I still need to make the changes to the /etc/myscl/my.cnf file? Or did the fix in 17.05.02 make that unnecessary?
Thanks,
Doug
*From:* koha-devel-bounces@lists.koha-community.org [mailto: koha-devel-bounces@lists.koha-community.org] *On Behalf Of *Jonathan Druart *Sent:* Tuesday, August 1, 2017 7:23 AM *To:* koha@lists.katipo.co.nz; koha-devel@lists.koha-community.org *Subject:* [Koha-devel] [Important] Update your MySQL|MariaDB configuration to avoid data loss
Hello everybody,
This is an important email and you should not ignore it.
If you are a librarian and have no technical skills you should forward this email to the administrator of your Koha installation.
We have been dealing with circulation history loss in the last versions of Koha, and we ended up with a solution.
You should read the following wiki page attentively and set up the proposed solution: https://wiki.koha-community.org/wiki/DBMS_auto_increment_fix
Cheers,
Jonathan
Hi Doug, Note though that there are other tables than just ‘issues’ where you might still get duplicates. I usually find it happens to us with the ‘reserves’ table. I don’t know that there are any protections for this table in Koha if you don’t change your my.cnf file. I’m planning to look at adding insert triggers to prevent duplicate IDs, but I just haven’t had the time to work on this yet… David Cook Systems Librarian Prosentient Systems 72/330 Wattle St Ultimo, NSW 2007 Australia Office: 02 9212 0899 Direct: 02 8005 0595 From: koha-devel-bounces@lists.koha-community.org [mailto:koha-devel-bounces@lists.koha-community.org] On Behalf Of Jonathan Druart Sent: Friday, 1 September 2017 2:52 AM To: koha@lists.katipo.co.nz; koha-devel@lists.koha-community.org Subject: Re: [Koha-devel] [Important] Update your MySQL|MariaDB configuration to avoid data loss Yes, the fix is for all Koha versions. On Thu, 31 Aug 2017 at 13:24 Doug Dearden <dearden@sarsf.org <mailto:dearden@sarsf.org> > wrote: Hello list, I have upgraded to 17.05.02 and taken care of two duplicate IDs on one of my instances. Do I still need to make the changes to the /etc/myscl/my.cnf file? Or did the fix in 17.05.02 make that unnecessary? Thanks, Doug From: koha-devel-bounces@lists.koha-community.org <mailto:koha-devel-bounces@lists.koha-community.org> [mailto:koha-devel-bounces@lists.koha-community.org <mailto:koha-devel-bounces@lists.koha-community.org> ] On Behalf Of Jonathan Druart Sent: Tuesday, August 1, 2017 7:23 AM To: koha@lists.katipo.co.nz <mailto:koha@lists.katipo.co.nz> ; koha-devel@lists.koha-community.org <mailto:koha-devel@lists.koha-community.org> Subject: [Koha-devel] [Important] Update your MySQL|MariaDB configuration to avoid data loss Hello everybody, This is an important email and you should not ignore it. If you are a librarian and have no technical skills you should forward this email to the administrator of your Koha installation. We have been dealing with circulation history loss in the last versions of Koha, and we ended up with a solution. You should read the following wiki page attentively and set up the proposed solution: https://wiki.koha-community.org/wiki/DBMS_auto_increment_fix Cheers, Jonathan
No protection exist for other tables than issues/old_issues. Note that bug 19059 (SO) will handle reserves/old_reserves. On Thu, 31 Aug 2017 at 21:04 David Cook <dcook@prosentient.com.au> wrote:
Hi Doug,
Note though that there are other tables than just ‘issues’ where you might still get duplicates. I usually find it happens to us with the ‘reserves’ table. I don’t know that there are any protections for this table in Koha if you don’t change your my.cnf file.
I’m planning to look at adding insert triggers to prevent duplicate IDs, but I just haven’t had the time to work on this yet…
David Cook
Systems Librarian
Prosentient Systems
72/330 Wattle St
Ultimo, NSW 2007
Australia
Office: 02 9212 0899 <02%2092%2012%2008%2099>
Direct: 02 8005 0595 <02%2080%2005%2005%2095>
*From:* koha-devel-bounces@lists.koha-community.org [mailto: koha-devel-bounces@lists.koha-community.org] *On Behalf Of *Jonathan Druart *Sent:* Friday, 1 September 2017 2:52 AM *To:* koha@lists.katipo.co.nz; koha-devel@lists.koha-community.org *Subject:* Re: [Koha-devel] [Important] Update your MySQL|MariaDB configuration to avoid data loss
Yes, the fix is for all Koha versions.
On Thu, 31 Aug 2017 at 13:24 Doug Dearden <dearden@sarsf.org> wrote:
Hello list,
I have upgraded to 17.05.02 and taken care of two duplicate IDs on one of my instances. Do I still need to make the changes to the /etc/myscl/my.cnf file? Or did the fix in 17.05.02 make that unnecessary?
Thanks,
Doug
*From:* koha-devel-bounces@lists.koha-community.org [mailto: koha-devel-bounces@lists.koha-community.org] *On Behalf Of *Jonathan Druart *Sent:* Tuesday, August 1, 2017 7:23 AM *To:* koha@lists.katipo.co.nz; koha-devel@lists.koha-community.org *Subject:* [Koha-devel] [Important] Update your MySQL|MariaDB configuration to avoid data loss
Hello everybody,
This is an important email and you should not ignore it.
If you are a librarian and have no technical skills you should forward this email to the administrator of your Koha installation.
We have been dealing with circulation history loss in the last versions of Koha, and we ended up with a solution.
You should read the following wiki page attentively and set up the proposed solution: https://wiki.koha-community.org/wiki/DBMS_auto_increment_fix
Cheers,
Jonathan
participants (5)
-
Bortel, Gary -
David Cook -
Doug Dearden -
Jon Knight -
Jonathan Druart