Bug 8835 - Two indexes on biblioitems.issn
Summary: Two indexes on biblioitems.issn
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: master
Hardware: All All
: P5 - low minor (vote)
Assignee: Marcel de Rooy
QA Contact:
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2012-09-27 07:21 UTC by Fridolin Somers
Modified: 2018-06-04 20:10 UTC (History)
6 users (show)

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


Attachments
Bug 8835: Drop issn_idx for completeness (1.48 KB, patch)
2016-10-12 13:37 UTC, Marcel de Rooy
Details | Diff | Splinter Review
Bug 8835: New db rev for issn_idx (2.04 KB, patch)
2016-10-13 09:38 UTC, Marcel de Rooy
Details | Diff | Splinter Review
Bug 8835: Drop issn_idx for completeness (1.49 KB, patch)
2016-10-14 09:18 UTC, Nicolas Legrand
Details | Diff | Splinter Review
Bug 8835: New db rev for issn_idx (2.06 KB, patch)
2016-10-14 09:19 UTC, Nicolas Legrand
Details | Diff | Splinter Review
Bug 8835: Drop issn_idx for completeness (1.56 KB, patch)
2016-11-22 12:50 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 8835: New db rev for issn_idx (2.12 KB, patch)
2016-11-22 12:50 UTC, Jonathan Druart
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Fridolin Somers 2012-09-27 07:21:15 UTC
In update database script :
3.01.00.006 adds an index called 'issn' on biblioitems.issn.
3.01.00.093 also adds an index on biblioitems.issn, called 'issn_idx'.

In kohastructure, only 'issn' index exists for biblioitems.

So udpate 3.01.00.093 is useless.
Can it be removed ?
Comment 1 Jonathan Druart 2015-04-01 13:51:07 UTC
Since 3.01 is a quite old version, I think this bug report could be closed.
Fridolin, do you agree?
Comment 2 Katrin Fischer 2015-06-07 00:29:49 UTC
We have fixed some occurrences of this is our recent update to 3.18 - I believe no harm is done by the differently named indexes, but it shows up as an error in the web installer output.
Comment 3 Tomás Cohen Arazi 2015-06-10 15:12:16 UTC
I think we should (at this point) have an update action that removes the issn_idx index if present.
Comment 4 Jonathan Druart 2015-06-16 13:58:59 UTC
Actually, there is also
   3.15.00.049 - Bug 5377 - Biblioitems isbn and issn fields too small for multiple ISBN and ISSN

which remove the index named "issn" and modify the datatype for the issn field.
If an index existed at this point, the change would have failed.
Comment 5 Marcel de Rooy 2016-10-12 13:36:46 UTC
(In reply to Jonathan Druart from comment #4)
> Actually, there is also
>    3.15.00.049 - Bug 5377 - Biblioitems isbn and issn fields too small for
> multiple ISBN and ISSN
> 
> which remove the index named "issn" and modify the datatype for the issn
> field.
> If an index existed at this point, the change would have failed.

Yes, I saw that happening.
Comment 6 Marcel de Rooy 2016-10-12 13:37:17 UTC
Created attachment 56278 [details] [review]
Bug 8835: Drop issn_idx for completeness

When dbrev 3.15.00.049 failed, the still existing issn_idx must have
been the cause.
This patch only adjusts the old db rev.
Still thinking if we also need a new db rev to fix isbn and issn for
those installs where 3.15.00.049 failed too. (In that case the fields
may not have been changed to mediumtext and no indexes were added.)

Test plan:
Ideally, run an upgrade from something older than 3.15.00.49.
But running the added sql command from the command line might convince
you too.
Comment 7 Marcel de Rooy 2016-10-13 09:38:36 UTC
Created attachment 56316 [details] [review]
Bug 8835: New db rev for issn_idx

This db rev reapplies dbrev 3.15.00.049 in case that one failed due to
existence of issn_idx.

Test plan:
If you do not have index issn_idx, recreate it:
    ALTER TABLE biblioitems ADD INDEX issn_idx (issn (255))
Run this db rev. You should see: Removed issn_idx.
Rerun the dbrev. You should see: Everything is fine.
Comment 8 Nicolas Legrand 2016-10-14 09:18:50 UTC
Created attachment 56489 [details] [review]
Bug 8835: Drop issn_idx for completeness

When dbrev 3.15.00.049 failed, the still existing issn_idx must have
been the cause.
This patch only adjusts the old db rev.
Still thinking if we also need a new db rev to fix isbn and issn for
those installs where 3.15.00.049 failed too. (In that case the fields
may not have been changed to mediumtext and no indexes were added.)

Test plan:
Ideally, run an upgrade from something older than 3.15.00.49.
But running the added sql command from the command line might convince
you too.

Signed-off-by: Nicolas Legrand <nicolas.legrand@bulac.fr>
Comment 9 Nicolas Legrand 2016-10-14 09:19:31 UTC
Created attachment 56490 [details] [review]
Bug 8835: New db rev for issn_idx

This db rev reapplies dbrev 3.15.00.049 in case that one failed due to
existence of issn_idx.

Test plan:
If you do not have index issn_idx, recreate it:
    ALTER TABLE biblioitems ADD INDEX issn_idx (issn (255))
Run this db rev. You should see: Removed issn_idx.
Rerun the dbrev. You should see: Everything is fine.

Signed-off-by: Nicolas Legrand <nicolas.legrand@bulac.fr>
Comment 10 Marcel de Rooy 2016-10-14 10:44:17 UTC
(In reply to Nicolas Legrand from comment #9)
> Signed-off-by: Nicolas Legrand <nicolas.legrand@bulac.fr>
Thx
Comment 11 Katrin Fischer 2016-11-06 10:23:54 UTC
I've got a problem running the update:
$ perl installer/data/mysql/updatedatabase.pl
DEV atomic update: 8835_dbrev.perl
Error flock (1) on '/home/vagrant/kohaclone/installer/data/mysql/atomicupdate/8835_dbrev.perl': No locks available at installer/data/mysql/updatedatabase.pl line 13637.

Tested from koha-shell on kohadevbox.
Comment 12 Marcel de Rooy 2016-11-07 09:10:16 UTC
(In reply to Katrin Fischer from comment #11)
> I've got a problem running the update:
> $ perl installer/data/mysql/updatedatabase.pl
> DEV atomic update: 8835_dbrev.perl
> Error flock (1) on
> '/home/vagrant/kohaclone/installer/data/mysql/atomicupdate/8835_dbrev.perl':
> No locks available at installer/data/mysql/updatedatabase.pl line 13637.
> 
> Tested from koha-shell on kohadevbox.

Thanks for trying, Katrin.
I restested this dbrev with Debian 8/Plack and Debian 7 now without any problems.
Your problem seems to be an issue on kohadevbox. The referred line in updatedatabase tries to read the atomicupdate file with Path::Tiny (a recent adjustment). Path::Tiny uses flock. (So you did not even reach the new code of this dbrev.)

Could you test on another system? Or resolve the flock issues on your devbox?
Comment 13 Jonathan Druart 2016-11-18 15:16:16 UTC
I get the same error, I do not manage to find the source of the problem. Tomas, Have you already seen that happening?
Comment 14 Jonathan Druart 2016-11-22 08:23:18 UTC
(In reply to Jonathan Druart from comment #13)
> I get the same error, I do not manage to find the source of the problem.
> Tomas, Have you already seen that happening?

See bug 17666
Comment 15 Jonathan Druart 2016-11-22 12:50:51 UTC
Created attachment 57706 [details] [review]
Bug 8835: Drop issn_idx for completeness

When dbrev 3.15.00.049 failed, the still existing issn_idx must have
been the cause.
This patch only adjusts the old db rev.
Still thinking if we also need a new db rev to fix isbn and issn for
those installs where 3.15.00.049 failed too. (In that case the fields
may not have been changed to mediumtext and no indexes were added.)

Test plan:
Ideally, run an upgrade from something older than 3.15.00.49.
But running the added sql command from the command line might convince
you too.

Signed-off-by: Nicolas Legrand <nicolas.legrand@bulac.fr>

Signed-off-by: Jonathan Druart <jonathan.druart@biblibre.com>
Comment 16 Jonathan Druart 2016-11-22 12:50:55 UTC
Created attachment 57707 [details] [review]
Bug 8835: New db rev for issn_idx

This db rev reapplies dbrev 3.15.00.049 in case that one failed due to
existence of issn_idx.

Test plan:
If you do not have index issn_idx, recreate it:
    ALTER TABLE biblioitems ADD INDEX issn_idx (issn (255))
Run this db rev. You should see: Removed issn_idx.
Rerun the dbrev. You should see: Everything is fine.

Signed-off-by: Nicolas Legrand <nicolas.legrand@bulac.fr>

Signed-off-by: Jonathan Druart <jonathan.druart@biblibre.com>
Comment 17 Kyle M Hall 2016-11-22 18:00:10 UTC
Pushed to master for 16.11, thanks Marcel!
Comment 18 Mason James 2016-12-14 11:57:34 UTC
Possible selection for 16.05.x (16.05.07)