Re: [Koha] SQL queries to delete records from the database?
Greetings, Did I hear someone say 'recurrent index problems'? I think this means I should repaste my indexing journey here. This was on an Ubuntu 10.04 LTS VM with only 512MB of memory. As such, your mileage may vary. And if there is something wrong in here, someone can comment and correct me. --- BEGIN POST FROM ZEBRA LIST --- Greetings, I am sharing this in hopes that this helps people who are frustrated trying to figure out why Zebra indexing is not working for them. When I initially did a standard install, I ran the full reindex as root. This was a mistake! This created a whole bunch of files with root.root as the owner.group in the subdirectories of the /var/lib/koha/zebradb/ directory. I corrected this problem with: adminuser$ sudo chown –R –v koha.koha /var/lib/koha/zebradb This, however, did not solve the problem. It was then suggested that I restart the koha-zebra-daemon (Thanks to jcamins): adminuser$ sudo service koha-zebra-daemon restart This corrected the problem temporarily, and everything was running as koha (the crontab to do a full reindex, etc.) adminuser$ sudo cat /etc/cron.d/koha [it had ‘koha’ in the ‘run as this user’ column] adminuser$ ps aux | grep zebra koha 11635 0.0 1.3 99632 7192 ?S10:40 0:00 /usr/bin/zebrasrv -v none,fatal,warn -f /etc/koha/koha-conf.xml koha 12563 0.0 0.1 18296 584 ?Ss Jun29 0:00 daemon --name=koha-zebra-ctl.kohadata --errlog=/var/log/koha/koha-zebradaemon.err --stdout=/var/log/koha/koha-zebradaemon.log --output=/var/log/koha/koha-zebradaemon-output.log --verbose=1 --respawn --delay=30 -- /usr/bin/zebrasrv -v none,fatal,warn -f /etc/koha/koha-conf.xml adminuser 12722 0.0 0.1 6160 680 pts/0S+ 11:49 0:00 grep --color=auto zebra However, our VM was running low on memory (512MB total) and full indexes were triggering out of memory problems. I found that stopping the apache server, doing the full reindex, restarting the koha-zebra-daemon and then starting the apache server allowed for a successful reindex and searching to work properly. adminuser$ sudo service apache2 stop adminuser$ sudo su – koha koha$ cd /usr/share/koha/bin/migration_tools koha$ echo $KOHA_CONF [this better be set correctly] koha$ echo $PERL5LIB [this better be set correctly] koha$ ./rebuild_zebra –b –a –r –v [long indexing/exporting output which could take several hours – DO THIS AFTER HOURS!! – unless it is currently broken] koha$ exit adminuser$ sudo service apache2 start How did I know we were having memory problems? I opened a secondary window and watched while reindexing, and I noticed some errors in the logs adminuser$ free –m –s 1 [output snipped] ^C adminuser$ cd /var/log/koha adminuser$ grep memory * [you’ll recognize the problem when you see the output] If you don’t have swap and can turn it on, do so. This is left as an exercise for sysadmin administrators. I don’t know why I have to restart the koha-zebra-daemon after a full-reindex, but I’m hoping to get a memory bump to 1GB from 512MB, and then not have to stop apache. We sadly, can not turn on swap. Anyways, I hope this helps someone somewhere. Also, if someone can explain why I need to restart the koha-zebra-daemon after a full reindex even as koha, that would be appreciated too. I was thinking that perhaps the daemon has filehandles open when the files are deleted, and so the filehandles it has are stale? GPML, Mark Tompsett --- END POST FROM ZEBRA LIST --- ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
Re: [Koha] SQL queries to delete records from the database?
OK, thanks. Yes, I am 100% sure I want to do this. We have been dealing with that recurrent index problem for months now. I am despaired. I could try anything. :) (and deleting a few lines in the biblio table won't probably crash anything...) I don't know how to restore from a back-up but someone is here to deal with major issues like that, and I could learn. Sonia. > From: ch...@bigballofwax.co.nz > Date: Mon, 23 Jul 2012 16:00:21 +1200 > Subject: Re: [Koha] SQL queries to delete records from the database? > To: sossola...@hotmail.com > CC: koha@lists.katipo.co.nz > > On 23 July 2012 15:57, Sonia P. wrote: > > > > > > > > So something like that? > > > > > > > > > > > > mysqldump -u kohauser -p kohalibrary > savedatabase230712.sql > > > > > > mysql -u username -p[password]use kohalibrary;DELETE FROM biblio WHERE > > biblionumber='2315'; > > (never done mysql things in commandline before...) > > Cheers, > > Sonia. > > > Yes > > > If you are incredibly sure you want do this, and you are sure you know > how to restore from a backup, and no one is using or has used Koha > since you made that backup. > > Chris ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
Re: [Koha] SQL queries to delete records from the database?
On 23 July 2012 15:57, Sonia P. wrote: > > > > So something like that? > > > > > > mysqldump -u kohauser -p kohalibrary > savedatabase230712.sql > > > mysql -u username -p[password]use kohalibrary;DELETE FROM biblio WHERE > biblionumber='2315'; > (never done mysql things in commandline before...) > Cheers, > Sonia. > Yes If you are incredibly sure you want do this, and you are sure you know how to restore from a backup, and no one is using or has used Koha since you made that backup. Chris ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
Re: [Koha] SQL queries to delete records from the database?
So something like that? mysqldump -u kohauser -p kohalibrary > savedatabase230712.sql mysql -u username -p[password]use kohalibrary;DELETE FROM biblio WHERE biblionumber='2315'; (never done mysql things in commandline before...) Cheers, Sonia. > Date: Mon, 23 Jul 2012 15:42:59 +1200 > From: chr...@catalyst.net.nz > To: sossola...@hotmail.com > CC: koha@lists.katipo.co.nz > Subject: Re: [Koha] SQL queries to delete records from the database? > > * Sonia P. (sossola...@hotmail.com) wrote: > > > > Hello > > > > It seems that we can't use 'delete' commands in the Koha admin website > > (build SQL reports or so). How can I do that? > > I need to delete these records, I know the proper command, I just want to > > be allowed to do it. > > > Yes, that is because its a hugely dangerous thing to allow someone to > type a delete into the web interface. Imagine, delete from issues; > > For doing these kind of operations, you should do it in the mysql > prompt on the commandline. After backing up the database of course. > > Chris > > > (I am now down to 20 faulty records, so I will just delete them from the > > database one by one, I know they are only present in the 'biblio' table) > > > > Cheers, > > > > Sonia. > > > > > > > From: sossola...@hotmail.com > > > To: koha@lists.katipo.co.nz > > > Date: Sun, 22 Jul 2012 00:00:42 +0200 > > > Subject: [Koha] SQL queries to delete records from the database? > > > > > > > > > Hi there > > > > > > (re: rebuild-zebra stops working very often, which is not a proper title > > > any more... :p) > > > > > > I tried to edit a few records from these non-indexed records. As we > > > speak another language, we have accent issues. So I need to make sure > > > (again) that our librarians doesn't use any accent on the letters. So > > > all the records which I have found from that list of non-indexed records > > > have accent problems. > > > > > > On some records, I can edit the content and remove the accents. > > > > > > > > > But for some records, I can't even edit the content, because then there > > > is an error with Koha, the page doesn't show anything with something > > > like that: << Software error: Can't call method "field" on an > > > undefined value at /usr/share/koha/lib/C4/Items.pm line 1421.>> > > > which is just a random error because it can't find the record anymore > > > (it finds the records on certain pages, for instance if I want to see > > > its history, but it can't find the record on any page where it should > > > show its title). So no way to solve the problem using the Koha > > > interface... > > > > > > > > > I guess I need a sql query to delete these records from the > > > database, but something that would really delete everything related to > > > that item (history, etc). Do you have any idea about that? > > > > > > > > > But then I just don't understand why these problem records prevent zebra > > > from reindexing the newly entered records... :/ > > > > > > Anyway, I have done the koha-rebuild-zebra -v -f library command to > > > reindex everything. > > > See below the new report from that new perl script, we moved from 138 > > > non-indexed records to 56 non-indexed records, just because I have > > > modified a few records (5?) and because I have rerun rebuild-zebra (so it > > > indexed the newly entered records which had no accent problems anyway). > > > > > > Many thanks for your help! > > > Would greatly appreciate your expertise about SQL queries to delete > > > everything for faulty records. > > > > > > Sonia. > > > > > > > > > > > > 1000 done > > > 2000 done > > > biblionumber 2315 not indexed > > > 3000 done > > > 4000 done > > > biblionumber 5230 not indexed > > > biblionumber 5234 not indexed > > > 5000 done > > > 6000 done > > > 7000 done > > > 8000 done > > > 9000 done > > > 1 done > > > 11000 done > > > 12000 done > > > 13000 done > > > 14000 done > > > 15000 done > > > 16000 done > > > 17000 done > > > biblionumber 19992 not indexed > > > biblionumber 20030 not indexed > > > 18000 done > > > biblionumber 20177 not indexed > > > biblionumber 20234 not indexed > > > biblionumber 20316 not indexed > > > biblionumber 20317 not indexed > > > biblionumber 20397 not indexed > > > biblionumber 20398 not indexed > > > biblionumber 20399 not indexed > > > biblionumber 20400 not indexed > > > biblionumber 20418 not indexed > > > biblionumber 20446 not indexed > > > biblionumber 20448 not indexed > > > biblionumber 20491 not indexed > > > biblionumber 20494 not indexed > > > biblionumber 20495 not indexed > > > biblionumber 20499 not indexed > > > biblionumber 20500 not indexed > > > biblionumber 20501 not indexed > > > biblionumber 20502 not indexed > > > biblionumber 20505 not indexed > > > biblionumber 20590 not indexed > > > biblionumber 20614 not indexed > > > biblionumber 20615 not indexed > > > biblionumber 20616 not indexed > > > biblionumber 20617 not
Re: [Koha] SQL queries to delete records from the database?
* Sonia P. (sossola...@hotmail.com) wrote: > > Hello > > It seems that we can't use 'delete' commands in the Koha admin website (build > SQL reports or so). How can I do that? > I need to delete these records, I know the proper command, I just want to be > allowed to do it. > Yes, that is because its a hugely dangerous thing to allow someone to type a delete into the web interface. Imagine, delete from issues; For doing these kind of operations, you should do it in the mysql prompt on the commandline. After backing up the database of course. Chris > (I am now down to 20 faulty records, so I will just delete them from the > database one by one, I know they are only present in the 'biblio' table) > > Cheers, > > Sonia. > > > > From: sossola...@hotmail.com > > To: koha@lists.katipo.co.nz > > Date: Sun, 22 Jul 2012 00:00:42 +0200 > > Subject: [Koha] SQL queries to delete records from the database? > > > > > > Hi there > > > > (re: rebuild-zebra stops working very often, which is not a proper title > > any more... :p) > > > > I tried to edit a few records from these non-indexed records. As we speak > > another language, we have accent issues. So I need to make sure (again) > > that our librarians doesn't use any accent on the letters. So all the > > records which I have found from that list of non-indexed records have > > accent problems. > > > > On some records, I can edit the content and remove the accents. > > > > > > But for some records, I can't even edit the content, because then there > > is an error with Koha, the page doesn't show anything with something > > like that: << Software error: Can't call method "field" on an > > undefined value at /usr/share/koha/lib/C4/Items.pm line 1421.>> > > which is just a random error because it can't find the record anymore > > (it finds the records on certain pages, for instance if I want to see > > its history, but it can't find the record on any page where it should > > show its title). So no way to solve the problem using the Koha > > interface... > > > > > > I guess I need a sql query to delete these records from the > > database, but something that would really delete everything related to > > that item (history, etc). Do you have any idea about that? > > > > > > But then I just don't understand why these problem records prevent zebra > > from reindexing the newly entered records... :/ > > > > Anyway, I have done the koha-rebuild-zebra -v -f library command to reindex > > everything. > > See below the new report from that new perl script, we moved from 138 > > non-indexed records to 56 non-indexed records, just because I have > > modified a few records (5?) and because I have rerun rebuild-zebra (so it > > indexed the newly entered records which had no accent problems anyway). > > > > Many thanks for your help! > > Would greatly appreciate your expertise about SQL queries to delete > > everything for faulty records. > > > > Sonia. > > > > > > > > 1000 done > > 2000 done > > biblionumber 2315 not indexed > > 3000 done > > 4000 done > > biblionumber 5230 not indexed > > biblionumber 5234 not indexed > > 5000 done > > 6000 done > > 7000 done > > 8000 done > > 9000 done > > 1 done > > 11000 done > > 12000 done > > 13000 done > > 14000 done > > 15000 done > > 16000 done > > 17000 done > > biblionumber 19992 not indexed > > biblionumber 20030 not indexed > > 18000 done > > biblionumber 20177 not indexed > > biblionumber 20234 not indexed > > biblionumber 20316 not indexed > > biblionumber 20317 not indexed > > biblionumber 20397 not indexed > > biblionumber 20398 not indexed > > biblionumber 20399 not indexed > > biblionumber 20400 not indexed > > biblionumber 20418 not indexed > > biblionumber 20446 not indexed > > biblionumber 20448 not indexed > > biblionumber 20491 not indexed > > biblionumber 20494 not indexed > > biblionumber 20495 not indexed > > biblionumber 20499 not indexed > > biblionumber 20500 not indexed > > biblionumber 20501 not indexed > > biblionumber 20502 not indexed > > biblionumber 20505 not indexed > > biblionumber 20590 not indexed > > biblionumber 20614 not indexed > > biblionumber 20615 not indexed > > biblionumber 20616 not indexed > > biblionumber 20617 not indexed > > biblionumber 20618 not indexed > > biblionumber 20626 not indexed > > biblionumber 20627 not indexed > > biblionumber 20628 not indexed > > biblionumber 20648 not indexed > > biblionumber 20677 not indexed > > biblionumber 20686 not indexed > > biblionumber 20849 not indexed > > biblionumber 20917 not indexed > > biblionumber 20921 not indexed > > biblionumber 21016 not indexed > > biblionumber 21039 not indexed > > biblionumber 21045 not indexed > > biblionumber 21097 not indexed > > biblionumber 21138 not indexed > > biblionumber 21141 not indexed > > biblionumber 21142 not indexed > > biblionumber 21145 not indexed > > 19000 done > > biblionumber 21810 not indexed > > biblionumber 21811 not inde
Re: [Koha] SQL queries to delete records from the database?
Hello It seems that we can't use 'delete' commands in the Koha admin website (build SQL reports or so). How can I do that? I need to delete these records, I know the proper command, I just want to be allowed to do it. (I am now down to 20 faulty records, so I will just delete them from the database one by one, I know they are only present in the 'biblio' table) Cheers, Sonia. > From: sossola...@hotmail.com > To: koha@lists.katipo.co.nz > Date: Sun, 22 Jul 2012 00:00:42 +0200 > Subject: [Koha] SQL queries to delete records from the database? > > > Hi there > > (re: rebuild-zebra stops working very often, which is not a proper title any > more... :p) > > I tried to edit a few records from these non-indexed records. As we speak > another language, we have accent issues. So I need to make sure (again) that > our librarians doesn't use any accent on the letters. So all the records > which I have found from that list of non-indexed records have accent > problems. > > On some records, I can edit the content and remove the accents. > > > But for some records, I can't even edit the content, because then there > is an error with Koha, the page doesn't show anything with something > like that: << Software error: Can't call method "field" on an > undefined value at /usr/share/koha/lib/C4/Items.pm line 1421.>> > which is just a random error because it can't find the record anymore > (it finds the records on certain pages, for instance if I want to see > its history, but it can't find the record on any page where it should > show its title). So no way to solve the problem using the Koha > interface... > > > I guess I need a sql query to delete these records from the > database, but something that would really delete everything related to > that item (history, etc). Do you have any idea about that? > > > But then I just don't understand why these problem records prevent zebra from > reindexing the newly entered records... :/ > > Anyway, I have done the koha-rebuild-zebra -v -f library command to reindex > everything. > See below the new report from that new perl script, we moved from 138 > non-indexed records to 56 non-indexed records, just because I have modified > a few records (5?) and because I have rerun rebuild-zebra (so it indexed the > newly entered records which had no accent problems anyway). > > Many thanks for your help! > Would greatly appreciate your expertise about SQL queries to delete > everything for faulty records. > > Sonia. > > > > 1000 done > 2000 done > biblionumber 2315 not indexed > 3000 done > 4000 done > biblionumber 5230 not indexed > biblionumber 5234 not indexed > 5000 done > 6000 done > 7000 done > 8000 done > 9000 done > 1 done > 11000 done > 12000 done > 13000 done > 14000 done > 15000 done > 16000 done > 17000 done > biblionumber 19992 not indexed > biblionumber 20030 not indexed > 18000 done > biblionumber 20177 not indexed > biblionumber 20234 not indexed > biblionumber 20316 not indexed > biblionumber 20317 not indexed > biblionumber 20397 not indexed > biblionumber 20398 not indexed > biblionumber 20399 not indexed > biblionumber 20400 not indexed > biblionumber 20418 not indexed > biblionumber 20446 not indexed > biblionumber 20448 not indexed > biblionumber 20491 not indexed > biblionumber 20494 not indexed > biblionumber 20495 not indexed > biblionumber 20499 not indexed > biblionumber 20500 not indexed > biblionumber 20501 not indexed > biblionumber 20502 not indexed > biblionumber 20505 not indexed > biblionumber 20590 not indexed > biblionumber 20614 not indexed > biblionumber 20615 not indexed > biblionumber 20616 not indexed > biblionumber 20617 not indexed > biblionumber 20618 not indexed > biblionumber 20626 not indexed > biblionumber 20627 not indexed > biblionumber 20628 not indexed > biblionumber 20648 not indexed > biblionumber 20677 not indexed > biblionumber 20686 not indexed > biblionumber 20849 not indexed > biblionumber 20917 not indexed > biblionumber 20921 not indexed > biblionumber 21016 not indexed > biblionumber 21039 not indexed > biblionumber 21045 not indexed > biblionumber 21097 not indexed > biblionumber 21138 not indexed > biblionumber 21141 not indexed > biblionumber 21142 not indexed > biblionumber 21145 not indexed > 19000 done > biblionumber 21810 not indexed > biblionumber 21811 not indexed > 2 done > biblionumber 22320 not indexed > biblionumber 22338 not indexed > biblionumber 22386 not indexed > biblionumber 22583 not indexed > 21000 done > biblionumber 23506 not indexed > biblionumber 23507 not indexed > biblionumber 23508 not indexed > 22000 done > 23000 done > 24000 done > 25000 done > 26000 done > 27000 done > 28000 done > 29000 done > 3 done > 31000 done > 32000 done > 56 bibliorecords not indexed > > ___ > Koha mailing list http://koha-community.org > Koha@lists.katipo.co.n
[Koha] backup & restore problem
hi, i work on koha 3.2, i've done backup koha database in deffirent ways (mysqldump, phpmyadmin, ) when i try to restore it, the books' data do not restore to catalogue but to resorvior, anyone have a solution to this problem, please. mohammad abid qassim programmer ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
