Re: [Koha] Fwd: Unable to reindex after massive upgrade
Previously, I noted that I was not able to reindex. I tracked it down to the following. My biblio_metadata file wasempty, and therefore my reindex could not retrieve any biblio information. Now that I know what to look for, there was this error during the upgrade: [Thu Jun 22 22:29:11 2017] updatedatabase.pl: DBD::mysql::db do failed: *Cannot add or update a child row: a foreign key constraint fails* (`kohadb2015`.`biblio_metadata`, CONSTRAINT `biblio_metadata_fk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE) [for Statement " [Thu Jun 22 22:29:11 2017] updatedatabase.pl: INSERT INTO biblio_metadata ( biblionumber, format, marcflavour, metadata ) SELECT biblionumber, 'marcxml', 'CHANGEME', marcxml FROM biblioitems; I think this is where the upgrade failed; the newly created biblio_metadata has a foreign key: *CONSTRAINT `biblio_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE* When doing the bulk migration, we probably need to turn off foreign key checks before doing the update: SET FOREIGN_KEY_CHECKS=0; And then put them back once it has been done: SET FOREIGN_KEY_CHECKS=1; [update having solved the problem] This glitch was caused primarily by a few different things: 1) a previous update to the mysql did not upgrade all the users. The debian-sys-maint user was somewhat broken. Various things that depended on this user failed during the upgrade. 2) We have some broken data in our install. At one time we had a number of records get corrupted, which resulted in records that do not have the records corresponding to their foreign keys. 3) I did not immediately notice the importance of the error logs (above) telling me that my data had not been copied over. To fix my mysql issues with debian-sys-maint, I ended up running # mysql_upgrade --force -uroot -p This repaired the mysql users table. Being a wise sysadmin, I had a backup database [kohadb2015] which had our original data. To populate that table with the marc data, I ran the following sql: SET FOREIGN_KEY_CHECKS=0; INSERT INTO koha_opac.biblio_metadata ( biblionumber, format, marcflavour, metadata ) SELECT biblionumber, 'marcxml', 'CHANGEME', marcxml FROM kohadb2015.biblioitems; UPDATE biblio_metadata SET marcflavour = (SELECT value FROM systempreferences WHERE variable="marcflavour"); SET FOREIGN_KEY_CHECKS=1; After that, I was able to reindex fine. When the reindex was complete, the pages would still not load. I was getting an error rendering the page. Can't call method "branchname" on an undefined value at /usr/share/koha/lib/C4/Biblio.pm line 1627 I am not entirely sure if this is yet another problem with my data, or if I have some odd setting somewhere. What I did, however, was to modify the koha code to avoid it blowing up: vi /usr/share/koha/lib/C4/Biblio.pm +1627 if ( $tagslib->{$tag}->{$subfield}->{'authorised_value'} eq "branches" ) { *if(defined(Koha::Libraries->find($value)))* { return Koha::Libraries->find($value)->branchname; } - Tim Young
You need to fix your data, so fields linked to branch codes actually contain valid branchcodes. The band aid will work, but I think you need to fix your data for good. Koha got stricter regarding the data quality/completeness. This particular case could be saved by some tweak like the one you propose. It is maybe worth filling a bug, because the previous behaviour worked more gracefuly. El mar., 27 jun. 2017 a las 10:25, Tim Young (<Tim.Young@lightsys.org>) escribió:
Previously, I noted that I was not able to reindex. I tracked it down to the following.
My biblio_metadata file wasempty, and therefore my reindex could not retrieve any biblio information.
Now that I know what to look for, there was this error during the upgrade:
[Thu Jun 22 22:29:11 2017] updatedatabase.pl: DBD::mysql::db do failed: *Cannot add or update a child row: a foreign key constraint fails* (`kohadb2015`.`biblio_metadata`, CONSTRAINT `biblio_metadata_fk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE) [for Statement " [Thu Jun 22 22:29:11 2017] updatedatabase.pl: INSERT INTO biblio_metadata ( biblionumber, format, marcflavour, metadata ) SELECT biblionumber, 'marcxml', 'CHANGEME', marcxml FROM biblioitems;
I think this is where the upgrade failed; the newly created biblio_metadata has a foreign key:
*CONSTRAINT `biblio_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE*
When doing the bulk migration, we probably need to turn off foreign key checks before doing the update:
SET FOREIGN_KEY_CHECKS=0;
And then put them back once it has been done:
SET FOREIGN_KEY_CHECKS=1;
[update having solved the problem] This glitch was caused primarily by a few different things: 1) a previous update to the mysql did not upgrade all the users. The debian-sys-maint user was somewhat broken. Various things that depended on this user failed during the upgrade. 2) We have some broken data in our install. At one time we had a number of records get corrupted, which resulted in records that do not have the records corresponding to their foreign keys. 3) I did not immediately notice the importance of the error logs (above) telling me that my data had not been copied over.
To fix my mysql issues with debian-sys-maint, I ended up running
# mysql_upgrade --force -uroot -p
This repaired the mysql users table.
Being a wise sysadmin, I had a backup database [kohadb2015] which had our original data. To populate that table with the marc data, I ran the following sql:
SET FOREIGN_KEY_CHECKS=0;
INSERT INTO koha_opac.biblio_metadata ( biblionumber, format, marcflavour, metadata ) SELECT biblionumber, 'marcxml', 'CHANGEME', marcxml FROM kohadb2015.biblioitems;
UPDATE biblio_metadata SET marcflavour = (SELECT value FROM systempreferences WHERE variable="marcflavour");
SET FOREIGN_KEY_CHECKS=1;
After that, I was able to reindex fine.
When the reindex was complete, the pages would still not load. I was getting an error rendering the page. Can't call method "branchname" on an undefined value at /usr/share/koha/lib/C4/Biblio.pm line 1627
I am not entirely sure if this is yet another problem with my data, or if I have some odd setting somewhere. What I did, however, was to modify the koha code to avoid it blowing up:
vi /usr/share/koha/lib/C4/Biblio.pm +1627
if ( $tagslib->{$tag}->{$subfield}->{'authorised_value'} eq "branches" ) { *if(defined(Koha::Libraries->find($value)))* { return Koha::Libraries->find($value)->branchname; }
- Tim Young
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
-- Tomás Cohen Arazi Theke Solutions (https://theke.io <http://theke.io/>) ✆ +54 9351 3513384 GPG: B2F3C15F
Awesome. Thanks. I may be able to figure it out (I am pretty good at poking and prodding, and finally figuring it out). I believe we only have one branch, so I should be able to simply replace everything in the sql to match the correct branch... (Yikes! Branchcode is used all over the place!) BUT, I am a sysadmin, not a Koha admin. This looks like it might be easier to fix through the web interface... So excuse the dumb questions. :) That particular error seems to stem from something be authorized by branches. My "authorised_values_branches" table is empty. Should I set up an authorized branch or something somewhere? We do have a branch configured (I can see it in the branches table)... The code seems to be looking at Koha::Libraries->find($value)->branchname; and (I have "value" being undefined, or defined improperly. But I do not see a libraries table...) But, at the same time: $tagslib->{$tag}->{$subfield}->{'authorised_value'} eq "branches". So I somewhere seem to be saying to authorize by branch, and I do not have any authorized branches (or the authorised_values_branches is empty)... Is there a simple web area I should check out before I jump into the mysql and make changes that might not be supported? - Tim On 6/27/2017 9:26 AM, Tomas Cohen Arazi wrote:
You need to fix your data, so fields linked to branch codes actually contain valid branchcodes. The band aid will work, but I think you need to fix your data for good.
Koha got stricter regarding the data quality/completeness. This particular case could be saved by some tweak like the one you propose. It is maybe worth filling a bug, because the previous behaviour worked more gracefuly.
Tim, the authorised_values_branches table is not related. The problem is that there are fields in your record(s) that are expected to contain a branchcode. And they don't. That's why Koha::Libraries->find() is returning an undefined value (undef in Perl). When you removed the FK constraint, you did hide the basic problem of inconsistency. I suggest that you find your only library's branchcode and make sure your items all have homebranch and holdingbranch set to that. Then reindex. You should do it like this: # Enter to mysql $ sudo koha-mysql lib
UPDATE items SET homebranch='LIB', holdingbranch='LIB'; \q $ sudo koha-rebuild-zebra -f -a -b -v lib
Note: replace 'lib' for your instance name, and 'LIB' for your library's branchcode. Regards. El mar., 27 jun. 2017 a las 13:59, Tim Young (<Tim.Young@lightsys.org>) escribió:
Awesome. Thanks. I may be able to figure it out (I am pretty good at poking and prodding, and finally figuring it out). I believe we only have one branch, so I should be able to simply replace everything in the sql to match the correct branch... (Yikes! Branchcode is used all over the place!)
BUT, I am a sysadmin, not a Koha admin. This looks like it might be easier to fix through the web interface... So excuse the dumb questions. :)
That particular error seems to stem from something be authorized by branches. My "authorised_values_branches" table is empty. Should I set up an authorized branch or something somewhere?
We do have a branch configured (I can see it in the branches table)...
The code seems to be looking at Koha::Libraries->find($value)->branchname;
and (I have "value" being undefined, or defined improperly. But I do not see a libraries table...)
But, at the same time: $tagslib->{$tag}->{$subfield}->{'authorised_value'} eq "branches". So I somewhere seem to be saying to authorize by branch, and I do not have any authorized branches (or the authorised_values_branches is empty)...
Is there a simple web area I should check out before I jump into the mysql and make changes that might not be supported?
- Tim
On 6/27/2017 9:26 AM, Tomas Cohen Arazi wrote:
You need to fix your data, so fields linked to branch codes actually contain valid branchcodes. The band aid will work, but I think you need to fix your data for good.
Koha got stricter regarding the data quality/completeness. This particular case could be saved by some tweak like the one you propose. It is maybe worth filling a bug, because the previous behaviour worked more gracefuly.
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
-- Tomás Cohen Arazi Theke Solutions (https://theke.io <http://theke.io/>) ✆ +54 9351 3513384 GPG: B2F3C15F
Great! Thanks. & Thanks for the sql. Worked fine. - Tim On 6/27/2017 12:55 PM, Tomas Cohen Arazi wrote:
Tim, the authorised_values_branches table is not related.
The problem is that there are fields in your record(s) that are expected to contain a branchcode. And they don't. That's why Koha::Libraries->find() is returning an undefined value (undef in Perl).
When you removed the FK constraint, you did hide the basic problem of inconsistency.
I suggest that you find your only library's branchcode and make sure your items all have homebranch and holdingbranch set to that. Then reindex. You should do it like this:
# Enter to mysql $ sudo koha-mysql lib
UPDATE items SET homebranch='LIB', holdingbranch='LIB'; \q $ sudo koha-rebuild-zebra -f -a -b -v lib
Note: replace 'lib' for your instance name, and 'LIB' for your library's branchcode.
Regards.
El mar., 27 jun. 2017 a las 13:59, Tim Young (<Tim.Young@lightsys.org <mailto:Tim.Young@lightsys.org>>) escribió:
Awesome. Thanks. I may be able to figure it out (I am pretty good at poking and prodding, and finally figuring it out). I believe we only have one branch, so I should be able to simply replace everything in the sql to match the correct branch... (Yikes! Branchcode is used all over the place!)
BUT, I am a sysadmin, not a Koha admin. This looks like it might be easier to fix through the web interface... So excuse the dumb questions. :)
That particular error seems to stem from something be authorized by branches. My "authorised_values_branches" table is empty. Should I set up an authorized branch or something somewhere?
We do have a branch configured (I can see it in the branches table)...
The code seems to be looking at Koha::Libraries->find($value)->branchname;
and (I have "value" being undefined, or defined improperly. But I do not see a libraries table...)
But, at the same time: $tagslib->{$tag}->{$subfield}->{'authorised_value'} eq "branches". So I somewhere seem to be saying to authorize by branch, and I do not have any authorized branches (or the authorised_values_branches is empty)...
Is there a simple web area I should check out before I jump into the mysql and make changes that might not be supported?
- Tim
On 6/27/2017 9:26 AM, Tomas Cohen Arazi wrote: > You need to fix your data, so fields linked to branch codes actually > contain valid branchcodes. The band aid will work, but I think you > need to fix your data for good. > > Koha got stricter regarding the data quality/completeness. This > particular case could be saved by some tweak like the one you propose. > It is maybe worth filling a bug, because the previous behaviour worked > more gracefuly. >
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz <mailto:Koha@lists.katipo.co.nz> https://lists.katipo.co.nz/mailman/listinfo/koha
-- Tomás Cohen Arazi Theke Solutions (https://theke.io <http://theke.io/>) ✆ +54 9351 3513384 GPG: B2F3C15F
I am revisiting an old topic here; we still have some inconsistencies in our data. Previously in this thread: We were getting an error to the likes of: *Can't call method "branchname" on an undefined value at /usr/share/koha/lib/C4/Biblio.pm line 1627* This was due to some bad data; our holding information had somehow gotten bad library codes. I was given some SQL to replace holdingbranch and homebranch in the items table. But, it turns out that the fields are being pulled from the marc data, not the items table. We still have some bad data stored in the marc fields somewhere. Luckily for me, I am a sysadmin. I have all sorts of potentially destructive, but very possible solutions. My plan is to do a big sql dump of the database, and then use a regular expression to do a database-wide change of that bad data to the good data. It is a pretty unique code that does not seem to appear anywhere else than "branch" areas. This bulk change would solve the problem, but it would also change any legacy archives and stuff like that... The data would be back in sync, but it is somewhat of a shotgun approach... Anyone have any wisdom for me before I take such a drastic measure? - Tim Young On 6/27/2017 1:46 PM, Tim Young wrote:
Great! Thanks. & Thanks for the sql. Worked fine.
- Tim
On 6/27/2017 12:55 PM, Tomas Cohen Arazi wrote:
Tim, the authorised_values_branches table is not related.
The problem is that there are fields in your record(s) that are expected to contain a branchcode. And they don't. That's why Koha::Libraries->find() is returning an undefined value (undef in Perl).
When you removed the FK constraint, you did hide the basic problem of inconsistency.
I suggest that you find your only library's branchcode and make sure your items all have homebranch and holdingbranch set to that. Then reindex. You should do it like this:
# Enter to mysql $ sudo koha-mysql lib
UPDATE items SET homebranch='LIB', holdingbranch='LIB'; \q $ sudo koha-rebuild-zebra -f -a -b -v lib
Note: replace 'lib' for your instance name, and 'LIB' for your library's branchcode.
Regards.
El mar., 27 jun. 2017 a las 13:59, Tim Young (<Tim.Young@lightsys.org <mailto:Tim.Young@lightsys.org>>) escribió:
Awesome. Thanks. I may be able to figure it out (I am pretty good at poking and prodding, and finally figuring it out). I believe we only have one branch, so I should be able to simply replace everything in the sql to match the correct branch... (Yikes! Branchcode is used all over the place!)
BUT, I am a sysadmin, not a Koha admin. This looks like it might be easier to fix through the web interface... So excuse the dumb questions. :)
That particular error seems to stem from something be authorized by branches. My "authorised_values_branches" table is empty. Should I set up an authorized branch or something somewhere?
We do have a branch configured (I can see it in the branches table)...
The code seems to be looking at Koha::Libraries->find($value)->branchname;
and (I have "value" being undefined, or defined improperly. But I do not see a libraries table...)
But, at the same time: $tagslib->{$tag}->{$subfield}->{'authorised_value'} eq "branches". So I somewhere seem to be saying to authorize by branch, and I do not have any authorized branches (or the authorised_values_branches is empty)...
Is there a simple web area I should check out before I jump into the mysql and make changes that might not be supported?
- Tim
On 6/27/2017 9:26 AM, Tomas Cohen Arazi wrote: > You need to fix your data, so fields linked to branch codes actually > contain valid branchcodes. The band aid will work, but I think you > need to fix your data for good. > > Koha got stricter regarding the data quality/completeness. This > particular case could be saved by some tweak like the one you propose. > It is maybe worth filling a bug, because the previous behaviour worked > more gracefuly. >
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz <mailto:Koha@lists.katipo.co.nz> https://lists.katipo.co.nz/mailman/listinfo/koha
-- Tomás Cohen Arazi Theke Solutions (https://theke.io <http://theke.io/>) ✆ +54 9351 3513384 GPG: B2F3C15F
participants (2)
-
Tim Young -
Tomas Cohen Arazi