How to create MARC records from ISBNs and import into Koha using MarcEdit

How to create MARC records from ISBNs and import into Koha using MarcEdit

  1. Download and install MarcEdit from http://marcedit.reeset.net
  2. Compile the list of ISBNs for which you want MARC records in a text file, one ISBN per line (you can also use book titles, author etc. but ISBN is the one best able to give you an exact match

  3. Open MarcEdit and open the Z39.50/SRU Client tool


  4. Select Batch Search and open the text file (.txt) with the ISBNs from the step above. Make sure the search parameter is set to ISBN.


  5. Select the database from which you want to download the MARC records. I recommend using U.S. Library of Congress (UTF 8) but you can use any one, or add your own if you have the database credentials).

  6. Click the green arrow to start the download. You will be prompted to save the MARC file
  7. The script will start running and query the chosen database for MARC records matching the ISBNs. For each match, a response will be returned stating “1 records found in database…”. If no record is found, the response will state “0 records found in database”. In this example, 7 out of the 10 ISBNs were matched and the MARC records downloaded to the file you created in the step above. If you are not satisfied with the success rate, change the database and try again.


  8. Open the MARC file in which the results were saved. Select the default character encoding (usually either MARC-8 or UTF8). Check the “Translate to UTF8” option. Then click “Execute” and then “Edit Records”


  9. To import the MARC records into Koha correctly, you will need to add at least two additional fields and related subfields, namely:
    1. 942$c (bibliographic item type) and
    2. 952
      1. Subfield $a (branch code for the home branch),
      2. Subfield $b (branch code for the holding branch),
      3. Subfield $y (Koha item type),
      4. Subfield $p (Koha item barcode), and
      5. Subfield $o (Classification number – in this tutorial, we will simply copy the classification number from field 082$a. More editing will need to be done after import to insert the full call number)
  10. To make it easier, sort the fields by going to Tools > Sort by > Sort fields within the record by > Sort All Fields
  11. Add field 942$c first. Go to Tools > Add/Delete Field. “BK” represents the Koha Item type (in this case BK stands for Books/Workbooks/Booklets. Make sure this matches the item type of your particular set of MARC records. If you are working on different item types, it is better to group them (in step 2 preferrably) and compile the MARC records in batches.
  12. Close the Add/Delete Field dialogue
    1. Under field/sub-field, enter a random number (one that will not be confused with any of the existing fields. In this case, I used 999 because I’m pretty sure it doesn’t conflict with any existing records. And it must have 3 characters. We are going to delete this field eventually, so don’t worry!).
    2. For number prefix, put a prefix that matches the one you will be using for all your other records, including ones which you are going to create directly in Koha.
    3. If this is your first batch, it’s safe to put “1” as your Start Digit. Otherwise, make sure you continue your existing bar-code sequence.
    4. Make sure your Digit Length also matches all your existing bar-codes.
    5. Select the first 3 options (Always insert, Insert in new records, Generate in current file).
    6. Click OK. All your records should now have field 999$a with bar-codes in the format {prefix}{xxxx} (if you chose to use a prefix that is. In this tutorial, it looks like this: KOHA0003).

  13. Next, you want to create bar-codes for the records and place them in field 952 (under sub-field “p”). Go to Tools > Generate Control Numbers.
  14. Next, you want to add field 952. This has more subfields, and in this example, we are only going to add 5 (you can add more should you need to – please refer to this article for more info https://wiki.koha-community.org/wiki/Holdings_data_fields_(9xx)). In this tutorial, the home branch code is KOHA, the holding branch code is KOHA, the Koha item type is BK, the barcode is in the format KOHAxxxx, and the call number will be copied from field 082$a.
  15. Go to Tools > Build New Field and enter the following in the “Pattern” field – =952 \\$aKOHA$bKOHA$yBK$o{082$a}$p{999$a} (remember to replace the variables in the subfields with your own branch specific data). Click process and close the dialogue box.


  16. All your records should now have a field 952 populated with subfield data.


  17. Now you can delete the arbitrary field 999 created earlier. Go to Tools > Add/Delete Field. Input 999 under “Field” and click “Delete Field”. Close the dialogue


  18. Compile the records into MARC records. Go to File > Compile File into MARC. Choose a location in which to save the .mrc file and enter the file name. Under “Save as type”, select UTF8-MARC file (unless you are using another format).
  19. Open your Koha admin page and go to Tools >Catalog > Stage MARC records for import
  20. Choose the file you save in the step above and click “Upload file”
  21. On the next page, select the options you want. If you are not sure what to choose, leave everything as default. It’s a good idea to fill in the “Comments about this file” section – use concise but adequate description that will help you to identify the sort of MARC records being staged for future reference.
  22. Click “Stage for import” then, on the next page, click “Manage staged records”
  23. Select the correct framework for the new records and click “Import this batch into the catalog”
  24. You have successfully created MARC records using ISBNs and MarcEdit!
Z39.50 Search Fails On Koha 17 – Separate Application and Database Server [Solved]

Z39.50 Search Fails On Koha 17 – Separate Application and Database Server [Solved]

Background

I’m testing out a new setup for my Koha installations – using separate servers for the applications and the databases. After a lot of headbutting, i finally got it working – until i tried to copy catalogue using the z39.50 search.

(Btw, running Koha 17.05.03.000 on Ubuntu 16.04LTS 64Bit, my application server is running MySQL Ver 14.14 Distrib 5.7.19, and my database server is running MariaDB Ver 15.1 Distrib 10.2.8-MariaDB)

All my z3950 searches were returning empty results.

I added new targets (the default ones are Columbia and Library of Congress), but the error kept recurring. When i checked the error logs, here’s what came up

sudo nano /var/log/koha/sample/intranet-error.log 

[Mon Sep 25 17:15:20.857869 2017] [cgi:error] [pid 46441] [client xxx.xxx.xxx.xxx:xxxx] AH01215: [Mon Sep 25 17:15:20 2017] z3950_search.pl:                                                          record_type, encoding, z3950random): /usr/share/koha/intranet/cgi-bin/cataloguing/z3950_search.pl, referer: http://xxx.xxx.x.xx:xxxx/cgi-bin/koha/cataloguing/z3950_search.pl?biblionumber=0&frameworkcode=

[Mon Sep 25 17:15:20.858260 2017] [cgi:error] [pid 46441] [client xxx.xxx.xxx.xxx:xxxx] AH01215: [Mon Sep 25 17:15:20 2017] z3950_search.pl:                                     VALUES (?, ?, ?, ?, ?, ?, ?)" with ParamValues: 0='1', 1=36, 2="00593nam a22001935a 4500001000600000005001700006008004100023035002100064906004500085955002600130010001700156040001300173050002400186245004800210250002800258260008100286300002500367922000700392\x1e31484\x1e19900511111633.2\x1e900511s1989    mnu           000 0 eng  \x1e  \x1f9(DLC)   90154590\x1e  \x1fa7\x1fbibc\x1fcorignew\x1fd2\x1fencip\x1ff19\x1fgy-gencatlg\x1e  \x1faby34 to bc00 05-11-90\x1e  \x1fa   90154590 \x1e  \x1faDLC\x1fcDLC\x1e00\x1faIN PROCESS (ONLINE)\x1e00\x1faGuide to criminal law & procedure research.\x1e  \x1faWESTLAW law student ed.\x1e  \x1faSt. Paul, MN (P.O. Box 64526, St. Paul 55164-1003) :\x1fbWest Pub. Co.,\x1fcc1989.\x1e  \x1faiii, 20 p. ;\x1fc28 cm.\x1e  \x1faco\x1e\x1d", 3="<?xml version="1.0" encoding="UTF-8"?>: /usr/share/koha/intranet/cgi-bin/cataloguing/z3950_search.pl, referer: http://xxx.xxx.x.xx:xxxx/cgi-bin/koha/cataloguing/z3950_search.pl?biblionumber=0&frameworkcode=

[Mon Sep 25 17:15:20.858373 2017] [cgi:error] [pid 46441] [client xxx.xxx.xxx.xxx:xxxx] AH01215: [Mon Sep 25 17:15:20 2017] z3950_search.pl: <collection: /usr/share/koha/intranet/cgi-bin/cataloguing/z3950_search.pl, referer: http://xxx.xxx.x.xx:xxxx/cgi-bin/koha/cataloguing/z3950_search.pl?biblionumber=0&frameworkcode= [Mon Sep 25 17:15:20.858488 2017] [cgi:error] [pid 46441] [client xxx.xxx.xxx.xxx:xxxx] AH01215:

[Mon Sep 25 17:15:20 2017] z3950_search.pl:   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance": /usr/share/koha/intranet/cgi-bin/cataloguing/z3950_search.pl, referer: http://xxx.xxx.x.xx:xxxx/cgi-bin/koha/cataloguing/z3950_search.pl?biblionumber=0&frameworkcode=

[Mon Sep 25 17:15:20.858638 2017] [cgi:error] [pid 46441] [client xxx.xxx.xxx.xxx:xxxx] AH01215: [Mon Sep 25 17:15:20 2017] z3950_search.pl:   xsi:schemaLocation="http://www.loc.gov/MARC21/slim http://www.loc.gov/standards/marcxml/schema/MARC21slim.xsd": /usr/share/koha/intranet/cgi-bin/cataloguing/z3950_search.pl, referer: http://xxx.xxx.x.xx:xxxx/cgi-bin/koha/cataloguing/z3950_search.pl?biblionumber=0&frameworkcode= [Mon Sep 25 17:15:20.858747 2017] [cgi:error] [pid 46441] [client xxx.xxx.xxx.xxx:xxxx] AH01215:

[Mon Sep 25 17:15:20 2017] z3950_search.pl:   xmlns="http://www.loc.gov/MARC21/slim">: /usr/share/koha/intranet/cgi-bin/cataloguing/z3950_search.pl, referer: http://xxx.xxx.x.xx:xxxx/cgi-bin/koha/cataloguing/z3950_search.pl?biblionumber=0&frameworkcode= [Mon Sep 25 17:15:20.858838 2017] [cgi:error] [pid 46441] [client xxx.xxx.xxx.xxx:xxxx] AH01215: [Mon Sep 25 17:15:20 2017] z3950_search.pl: : /usr/share/koha/intranet/cgi-bin/cataloguing/z3950_search.pl, referer: http://xxx.xxx.x.xx:xxxx/cgi-bin/koha/cataloguing/z3950_search.pl?biblionumber=0&frameworkcode= [Mon Sep 25 17:15:20.858931 2017] [cgi:error] [pid 46441] [client xxx.xxx.xxx.xxx:xxxx] AH01215:

[Mon Sep 25 17:15:20 2017] z3950_search.pl: <record>: /usr/share/koha/intranet/cgi-bin/cataloguing/z3950_search.pl, referer: http://xxx.xxx.x.xx:xxxx/cgi-bin/koha/cataloguing/z3950_search.pl?biblionumber=0&frameworkcode= [Mon Sep 25 17:15:20.859060 2017] [cgi:error] [pid 46441] [client xxx.xxx.xxx.xxx:xxxx] AH01215: [Mon Sep 25 17:15:20 2017] z3950_search.pl:   <leader>00593nam a22001935a 4500</leader>: /usr/share/koha/intranet/cgi-bin/cataloguing/z3950_search.pl, referer: http://xxx.xxx.x.xx:xxxx/cgi-bin/koha/cataloguing/z3950_search.pl?biblionumber=0&frameworkcode= [Mon Sep 25 17:15:20.859167 2017] [cgi:error] [pid 46441] [client xxx.xxx.xxx.xxx:xxxx] AH01215:

[Mon Sep 25 17:15:20 2017] z3950_search.pl:   <controlfield tag="001">31484</controlfield>: /usr/share/koha/intranet/cgi-bin/cataloguing/z3950_search.pl, referer: http://xxx.xxx.x.xx:xxxx/cgi-bin/koha/cataloguing/z3950_search.pl?biblionumber=0&frameworkcode= [Mon Sep 25 17:15:20.859284 2017] [cgi:error] [pid 46441] [client xxx.xxx.xxx.xxx:xxxx] AH01215: [Mon Sep 25 17:15:20 2017] z3950_search.pl:   <controlfield tag="005">19900511111633.2</controlfield>: /usr/share/koha/intranet/cgi-bin/cataloguing/z3950_search.pl, referer: http://xxx.xxx.x.xx:xxxx/cgi-bin/koha/cataloguing/z3950_search.pl?biblionumber=0&frameworkcode=

[Mon Sep 25 17:15:20.859471 2017] [cgi:error] [pid 46441] [client xxx.xxx.xxx.xxx:xxxx] AH01215: [Mon Sep 25 17:15:20 2017] z3950_search.pl:   <controlfield tag="008">900511s1989    mnu           000 0 eng  </controlfield>: /usr/share/koha/intranet/cgi-bin/cataloguing/z3950_search.pl, referer: http://xxx.xxx.x.xx:xxxx/cgi-bin/koha/cataloguing/z3950_search.pl?biblionumber=0&frameworkcode= [Mon Sep 25 17:15:20.859608 2017] [cgi:error] [pid 46441] [client xxx.xxx.xxx.xxx:xxxx] AH01215: [Mon Sep 25 17:15:20 2017] z3950_search.pl:   <datafield tag="035" ind1=" " ind2=" ">: /usr/share/koha/intranet/cgi-bin/cataloguing/z3950_search.pl, referer: http://xxx.xxx.x.xx:xxxx/cgi-bin/koha/cataloguing/z3950_search.pl?biblionumber=0&frameworkcode=

[Mon Sep 25 17:15:20.859718 2017] [cgi:error] [pid 46441] [client xxx.xxx.xxx.xxx:xxxx] AH01215: [Mon Sep 25 17:15:20 2017] z3950_search.pl:     <subfield code="9">(DLC)   90154590</subfield>: /usr/share/koha/intranet/cgi-bin/cataloguing/z3950_search.pl, referer: http://xxx.xxx.x.xx:xxxx/cgi-bin/koha/cataloguing/z3950_search.pl?biblionumber=0&frameworkcode= [Mon Sep 25 17:15:20.859816 2017] [cgi:error] [pid 46441] [client xxx.xxx.xxx.xxx:xxxx] AH01215: [Mon Sep 25 17:15:20 2017] z3950_search.pl:   </datafield>: /usr/share/koha/intranet/cgi-bin/cataloguing/z3950_search.pl, referer: http://xxx.xxx.x.xx:xxxx/cgi-bin/koha/cataloguing/z3950_search.pl?biblionumber=0&frameworkcode= [Mon Sep 25 17:15:20.859930 2017] [cgi:error] [pid 46441] [client xxx.xxx.xxx.xxx:xxxx] AH01215:

[Mon Sep 25 17:15:20 2017] z3950_search.pl:   <datafield tag="906" ind1=" " ind2=" ">: /usr/share/koha/intranet/cgi-bin/cataloguing/z3950_search.pl, referer: http://xxx.xxx.x.xx:xxxx/cgi-bin/koha/cataloguing/z3950_search.pl?biblionumber=0&frameworkcode= [Mon Sep 25 17:15:20.860034 2017] [cgi:error] [pid 46441] [client xxx.xxx.xxx.xxx:xxxx] AH01215: [Mon Sep 25 17:15:20 2017] z3950_search.pl:     <subfield code="a">7</subfield>: /usr/share/koha/intranet/cgi-bin/cataloguing/z3950_search.pl, referer: http://xxx.xxx.x.xx:xxxx/cgi-bin/koha/cataloguing/z3950_search.pl?biblionumber=0&frameworkcode=

[Mon Sep 25 17:15:20.860138 2017] [cgi:error] [pid 46441] [client xxx.xxx.xxx.xxx:xxxx] AH01215: [Mon Sep 25 17:15:20 2017] z3950_search.pl:     <subfield code="b">ibc</subfield>: /usr/share/koha/intranet/cgi-bin/cataloguing/z3950_search.pl, referer: http://xxx.xxx.x.xx:xxxx/cgi-bin/koha/cataloguing/z3950_search.pl?biblionumber=0&frameworkcode= [Mon Sep 25 17:15:20.860243 2017] [cgi:error] [pid 46441] [client xxx.xxx.xxx.xxx:xxxx] AH01215: [Mon Sep 25 17:15:20 2017] z3950_search.pl:     <subfield code="c">orignew</subfield>: /usr/share/koha/intranet/cgi-bin/cataloguing/z3950_search.pl, referer: http://xxx.xxx.x.xx:xxxx/cgi-bin/koha/cataloguing/z3950_search.pl?biblionumber=0&frameworkcode= [Mon Sep 25 17:15:20.860346 2017] [cgi:error] [pid 46441] [client xxx.xxx.xxx.xxx:xxxx] AH01215:

[Mon Sep 25 17:15:20 2017] z3950_search.pl:     <subfield code="d">2</subfield>: /usr/share/koha/intranet/cgi-bin/cataloguing/z3950_search.pl, referer: http://xxx.xxx.x.xx:xxxx/cgi-bin/koha/cataloguing/z3950_search.pl?biblionumber=0&frameworkcode= [Mon Sep 25 17:15:20.860450 2017] [cgi:error] [pid 46441] [client xxx.xxx.xxx.xxx:xxxx] AH01215: [Mon Sep 25 17:15:20 2017] z3950_search.pl:     <subfield code="e">ncip</subfield>: /usr/share/koha/intranet/cgi-bin/cataloguing/z3950_search.pl, referer: http://xxx.xxx.x.xx:xxxx/cgi-bin/koha/cataloguing/z3950_search.pl?biblionumber=0&frameworkcode=

[Mon Sep 25 17:15:20.860553 2017] [cgi:error] [pid 46441] [client xxx.xxx.xxx.xxx:xxxx] AH01215: [Mon Sep 25 17:15:20 2017] z3950_search.pl:     <subfield code="f">19</subfield>: /usr/share/koha/intranet/cgi-bin/cataloguing/z3950_search.pl, referer: http://xxx.xxx.x.xx:xxxx/cgi-bin/koha/cataloguing/z3950_search.pl?biblionumber=0&frameworkcode= [Mon Sep 25 17:15:20.860658 2017] [cgi:error] [pid 46441] [client xxx.xxx.xxx.xxx:xxxx] AH01215: [Mon Sep 25 17:15:20 2017] z3950_search.pl:     <subfield code="g">y-gencatlg</subfield>: /usr/share/koha/intranet/cgi-bin/cataloguing/z3950_search.pl, referer: http://xxx.xxx.x.xx:xxxx/cgi-bin/koha/cataloguing/z3950_search.pl?biblionumber=0&frameworkcode= [Mon Sep 25 17:15:20.860753 2017] [cgi:error] [pid 46441] [client xxx.xxx.xxx.xxx:xxxx] AH01215:

[Mon Sep 25 17:15:20 2017] z3950_search.pl:   </datafield>: /usr/share/koha/intranet/cgi-bin/cataloguing/z3950_search.pl, referer: http://xxx.xxx.x.xx:xxxx/cgi-bin/koha/cataloguing/z3950_search.pl?biblionumber=0&frameworkcode= [Mon Sep 25 17:15:20.860858 2017] [cgi:error] [pid 46441] [client xxx.xxx.xxx.xxx:xxxx] AH01215: [Mon Sep 25 17:15:20 2017] z3950_search.pl:   <datafield tag="955" ind1=" " ind2=" ">: /usr/share/koha/intranet/cgi-bin/cataloguing/z3950_search.pl, referer: http://xxx.xxx.x.xx:xxxx/cgi-bin/koha/cataloguing/z3950_search.pl?biblionumber=0&frameworkcode= [Mon Sep 25 17:15:20.860983 2017] [cgi:error] [pid 46441] [client xxx.xxx.xxx.xxx:xxxx] AH01215: [Mon Sep 25 17:15:20 2017] z3950_search.pl:     <subfiel...", 4='biblio', 5='UTF-8', 6=0] at /usr/share/koha/lib/C4/ImportBatch.pm line 1568.: /usr/share/koha/intranet/cgi-bin/cataloguing/z3950_search.pl, referer: http://xxx.xxx.x.xx:xxxx/cgi-bin/koha/cataloguing/z3950_search.pl?biblionumber=0&frameworkcode= [Mon Sep 25 17:15:20.862919 2017] [cgi:error] [pid 46441] [client xxx.xxx.xxx.xxx:xxxx] AH01215:

[Mon Sep 25 17:15:20 2017] z3950_search.pl: DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails (`koha_sample`.`import_biblios`, CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`) REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE) [for Statement "INSERT INTO import_biblios (import_record_id, title, author, isbn, issn) VALUES (?, ?, ?, ?, ?)" with ParamValues: 0='0', 1="Guide to criminal law & procedure research.", 2=undef, 3=undef, 4=undef] at /usr/share/koha/lib/C4/ImportBatch.pm line 1607.: /usr/share/koha/intranet/cgi-bin/cataloguing/z3950_search.pl, referer: http://xxx.xxx.x.xx:xxxx/cgi-bin/koha/cataloguing/z3950_search.pl?biblionumber=0&frameworkcode=

[Mon Sep 25 17:15:20.950248 2017] [cgi:error] [pid 46441] [client xxx.xxx.xxx.xxx:xxxx] AH01215: [Mon Sep 25 17:15:20 2017] z3950_search.pl: DBD::mysql::st execute failed: Field 'marcxml_old' doesn't have a default value [for Statement "INSERT INTO import_records (import_batch_id, record_sequence, marc, marcxml, : /usr/share/koha/intranet/cgi-bin/cataloguing/z3950_search.pl, referer: http://xxx.xxx.x.xx:xxxx/cgi-bin/koha/cataloguing/z3950_search.pl?biblionumber=0&frameworkcode=

The Problem

These last two lines seem to be where the issue lies.

z3950_search.pl: DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails (`koha_sample`.`import_biblios`, CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`) REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE) [for Statement "INSERT INTO import_biblios (import_record_id, title, author, isbn, issn) VALUES (?, ?, ?, ?, ?)" with ParamValues: 0='0', 1="Guide to criminal law & procedure research.", 2=undef, 3=undef, 4=undef] at /usr/share/koha/lib/C4/ImportBatch.pm line 1607.: /usr/share/koha/intranet/cgi-bin/cataloguing/z3950_search.pl, referer: http://xxx.xxx.x.xx:xxxx/cgi-bin/koha/cataloguing/z3950_search.pl?biblionumber=0&frameworkcode=

z3950_search.pl: DBD::mysql::st execute failed: Field 'marcxml_old' doesn't have a default value [for Statement "INSERT INTO import_records (import_batch_id, record_sequence, marc, marcxml, : /usr/share/koha/intranet/cgi-bin/cataloguing/z3950_search.pl, referer: http://xxx.xxx.x.xx:xxxx/cgi-bin/koha/cataloguing/z3950_search.pl?biblionumber=0&frameworkcode=

To be honest, i’m on shaky ground when it comes to MySQL, the problem seems to be with the table import_biblios and the foreign constraint key import_biblios_ibfk_1. I’ll leave it for the SQL gurus to explain.

The Solution

Anyway, it took me a long time to find the solution, partly because i was being thick, and partly because i’m using an unfamiliar setup. I eventually found the solution on this list https://lists.katipo.co.nz/pipermail/koha/2016-November/046591.html

To resolve, edit the MySQL configuration file (my.cnf) file on the database server (which, in my case, is using MariaDB. At first i did this on both servers, but i found it only has an effect on the database server.)

sudo nano -Bu /etc/mysql/my.cnf

(the options -Bu allow me to backup the original file and undo edits. You don’t need to use them, but its a good idea)

Add the following line under [mysqld] section, create it  (the [mysqld] section) at the end of the file if it doesn’t exist.

sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

So your /etc/mysql/my.cnf will look like this:

# # The MySQL database server configuration file.

# # You can copy this to one of:

# - "/etc/mysql/my.cnf" to set global options,

# - "~/.my.cnf" to set user-specific options. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with

# --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients # It has been reported that passwords should be enclosed with ticks/quotes # escpecially if they contain "#" chars... # Remember to edit /etc/mysql/debian.cnf when changing the socket location.

# Here is entries for some specific programs # The following values assume you have at least 32M ram
!includedir /etc/mysql/conf.d/

[mysqld]

sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

(sorry if i’m over-explaining things, but i find with a lot of tutorials on the net, they assume you know a lot, when often, the opposite is true. But, i digress…)

Then restart MySQL

sudo service mysql restart

Now try to search using z3950 again. It should work.

You can check the error logs on the application server in real time, while you are attempting the z3950 search, by doing the following:

sudo tail -f /var/log/koha/sample/intranet-error.log

If there are any further problems, you should see them on the output.

Final Thoughts

I didn’t immediately think of the above solution, which is mentioned in the Koha installation wiki https://wiki.koha-community.org/wiki/Koha_on_ubuntu_-_packages#Ubuntu_MySQL_Security_Tweak, because i thought the issue only affects MySQL and not MariaDB.

And it doesn’t help that there are several MySQL and MariaDB configuration files (/etc/mysql/my.cnf, /etc/mysql/mysql.cnf, /etc/mysql/conf.d/mysql.cnf, /etc/mysql/conf.d/mariadb.cnf).

But, as i was writing this, i realized that i’m running MySQL 5.7 on the application server, and MariaDB 10.2 on the database server. So maybe that’s why i’m still encountering the problem? I’ll upgrade MySQL 5.7 on the application server to MariaDB 10.2 to check if the problem still occurs if i remove the above tweak. But, that’s a battle for another day. Right now, my brain is fried and I have a date with my gf 🙂

[Solved] How To Access A Server From Another Server Via Command Line Using SSH Keys In Ubuntu 16

Server to Server Access Via Command Line Using SSH Keys – Ubuntu 16

Consider this scenario – you have two servers, Server 1 and Server 2 (with user1 and user2 respectively), that you want to connect to each other via the command line using SSH keys and not passwords. Because you are using the command line, you won’t be able to use an SSH client like Putty. The following tutorial will show you how to SSH into Server 1 from Server 2 using SSH keys, and vice-versa.

To Connect To Server 2, from Server 1

Create SSH keys for the user1 on Server 1

On Server 1:

ssh-keygen -t rsa
ssh-copy-id user1@server1
ssh user1@server1

Copy the public key for Server 1 and append it to the the authorized keys for Server 2

On Server 1:

scp -p ~/.ssh/your_pub_key.pub user2@server2:

You will be prompted for the password for user2 on Server2
Now, ssh into Server 2 using the following command:

ssh user2@server2

then append/copy the public key from Server 1 to the authorized keys list for Server 2

cat your_pub_key>> ~/.ssh/authorized_keys

To Connect To Server 1, from Server 2

Create SSH keys for the user2 on Server 2

On Server 2:
ssh-keygen -t rsa
ssh-copy-id user2@server2
ssh user2@server2

Copy the public key for Server 2 and append it to the the authorized keys for Server 1

On Server 1:

scp -p ~/.ssh/your_pub_key.pub user2@server2:

You will be prompted for the password for user2 on Server2
Now, ssh into Server 2 using the following command:

ssh user2@server2

then append/copy the public key from Server 1 to the authorized keys list for Server 2

cat your_pub_key>> ~/.ssh/authorized_keys

To test, attempt to access Server 2 from Server 1

ssh -i .ssh/id_rsa user2@server2_ipaddress

That’s it! Now you can connect from either Server 1 to Server 2, or Server 2 to Server 1, using user1 or user2 respectively, in Ubuntu 16 via the command line without having to use passwords. Questions? Comments? Let us know below!

What is Z39.50?

What is the Z39.50 Protocol?

Whether you are a season veteran or a new-comer to the world of librarians, you will have probably come across the term Z39.50 or heard talk about Z39.50 servers. This article explains what a Z39.50 server is and how it can be of use to you and your library.

Z39.50 is a protocol (a special set of procedures or rules that specifies the correct procedure for applications to communicate with each other) that helps you to search and retrieve information or records from a bibliographic database. It is commonly used by librarians to search the Online Public Access Catalogues (OPAC) of other libraries. It is often implemented in the inter-library loan

What is InterLibrary Loan?

A service by which a library can borrow books or other resources owned by another library. If a patron desires books that are not in their local library but are available in another library (or libraries) that has an existing inter-library loan arrangement with them, the local library can identify which of the partner libraries has the desired resource and place the request to borrow the item. The local library then receives the resource, loans it out to the patron who made the initial request, and arranges for its return. Depending on the arrangements made by the libraries, the item will have due dates and overdue fees set either by the intermediary local library or the lending library which owns the item. A fee will usually be charged for using the inter-library loaning module.
catalogue searches or when downloading MARC bibliographic or authority records for use in cataloguing.

If a librarian (the client) wants to search for bibliographic records from another library (the server), the Z39.50 protocol initiates and establishes communication between the initiating client application, searches the database or databases of the responding server, and retrieves the relevant information that was requested by the client. The retrieved information is then sent back to the client application and either displayed or made available for download.

Z39.50 is very useful for librarians when they are cataloguing and used when copy cataloguing. Copy cataloguing is a process by which a librarian looks for and retrieves an existing bibliographic record that matches an item they want to catalogue and edits the record to suit their library’s particular standards, and then imports the record into their local holdings. It saves an significant amount of time by allowing librarians to simply copy the work that has been done by other libraries (such as the Library of Congress or British Library), instead of reinventing the wheel and doing everything from scratch. Copy cataloguing uses the Z39.50 protocol to connect to, search and retrieve the desired information from the target library’s database(s).

In order to use the Z39.50 protocol, you must have the relevant connection details and in some cases, access credentials such as usernames and passwords. You will also need a Z39.50 application – some come as standalone applications or it can be integrated into a library system such as Koha ILS.

How to Fix Locked Greyed-out Subfields in Koha – (Adding New Authorities When Cataloguing)

How to Add New Authorities When Cataloguing – Unlocking Greyed-out Subfields

When cataloguing in Koha, you may discover that you certain subfields (such as 650a – Topical term or geographic name entry element, etc) may be greyed out and thus you will be unable to enter your own subject or topical headings. If you encounter that problem, the first thing is, don’t panic! Your Koha installation is not broken, it’s simply because the BiblioAddsAuthorities system preference is turned off.

Turning the BiblioAddsAuthorities System Preference On

When the BiblioAddsAuthorities is set to “don’t allow” it means that you will not be allowed to create your own authorities (which is what you will be doing when you enter your own subject or topical fields), and will only be allowed to reference existing authorities. If you want to be able to edit the greyed-out subfields in Koha when cataloguing, simply do the following.

  1. Log in to your Koha staff home page
  2. Click on Koha Administration (Or click the More drop down menu, and select Administration)
  3. Click on Global System Preferences
  4. Click on Authorities
  5. Scroll to General
  6. Set the BiblioAddsAuthorities preference from “don’t allow” to “allow”.

And you are all set! Your will now be able to add and edit your own authorities such as subject and topical fields.

Did you find this article helpful? Do you have any thoughts or suggestions? Let us know in the comments section below!

Koha vs Athenaeum: A Comparison of Integrated Library Systems

Deciding to automate your library system is a big decision, but an even bigger question is “which integrated library system should i choose?”. To help you, we picked two common library systems Koha Integrated Library System and Athenaeum and did a comparison of the two. We looked at some of the factors that will be at the forefront of any librarians mind when making such a decision, including cost (prices are in USD), features, support and more. We also made a list of pros and cons for both Koha and Athenaeum library systems.

Koha ILS versus Athenaeum

Cost

Athenaeum

The first thing any budget conscious librarian will be thinking of when considering a new library system is “How much does it cost?”. Athenaeum comes in two versions – Athenaeum Lite and Athenaeum Pro. The Lite version costs $50/year excluding FileMaker Pro and FileMaker Server. This means you will have to purchase FileMaker Pro separately – pricing is based on the number of users and it costs between $888  (5 users) to $8748 (100 users) per year (custom packages are available if you want more users).

Athenaeum Pro includes FileMaker Pro and “ranges between roughly $650 – $2500”. Based on the cost of the FileMaker Pro suite, we assume that the price is dependant on the number of users who will be connecting to your database. Therefore, if you chose Athenaeum Lite, you will be paying a minimum of $938 ($50 for Athenaeum Lite and $888 for FileMaker Pro) per year for 5 users, while Athenaeum Pro will presumably cost you a minimum of $650 per year for the same number of users. These costs exclude training and support, which must be paid for separately (minimum $495). Future updates and upgrades must also be paid for at a cost of $450.

Koha

Koha on the other hand is a free and open source library management system. This means you can download, install and customize it for free and you don’t pay yearly licensing fees or pricing tiers. You can choose to set it up by on your own if you have the in-house expertise (it runs on Linux server or desktop) or you can partner with support companies like BiblioTech Libraries  who offer premium hosted services at low prices. If you install it yourself, Koha has a worldwide community of developers, librarians and users who are ready to help.

Features

Athenaeum

As we’ve already mentioned – Athenaeum comes in two versions – Lite and Pro.  The documentation on the Athenaeum website is not clear on what the main differences are, but you can expect to either have limited or no access to certain modules such as circulation, borrowers, QuickMarcTM , authorities, MARC imports, FlexiSpellTM, reports, data migration/integration, emailing and network access when using the Lite package.

Koha

Koha ILS does not have tiered access but gives you full administrative access to all the available modules in the system without having to pay any money for future updates and upgrades. Some of the modules that come with Koha include circulation, cataloguing, z39.50 search, patron management, budget management, reports, label and patron card design and printing, automatic notices, stocktaking and inventory management, MARC data manipulation and more.

Users

Athenaeum

Atheneum limits the number of users (both administrative and patron) depending on the FileMaker Pro package you choose. The cheapest FileMaker Pro package costs $888.00 per year for 5 users, and $8748 for up to 100 users. Custom packages are available if you need additional users.

Koha

Koha, a free and open source system, allows you to add an unlimited number of patron and staff users. This makes it the ideal system for libraries that have huge numbers of patrons (such as you would typically have at a school or university). Patrons can access the database and their account via the Online Public Access Catalogue (OPAC) while librarians have administrative access via the staff web page.

Accessibility

Athenaeum

Access to Athenaeum depends on whether you have a standalone or client-server setup. A standalone set up allows you use Athenaeum on a single machine but does not allow for remote connection by other users. In a client-server configuration, other Athenaeum users can connect to your database from their installation via a network link, or using FileMaker Go (for iPads and iPhones) or via a web browser. The limitations on the number of users defined in your FileMaker Pro licensing package still applies.

Koha

Koha has web-based interfaces (for staff and patrons) which are accessible via the web (local network or internet) without any need for additional configuration or software. This makes it platform independent, meaning an unlimited number of users can connect to Koha using any device that has web browser installed regardless of the operating system (OS).

Support

Athenaeum

Athenaeum training and support costs are as follows:

  • Support (12 months) – starts from $495, excluding travel costs and taxes. There are various limitations on the number of hours you can claim.
  • Software updates – starts from $450, excluding GST. The Athenaeum website states that this charge excludes third-party software. It is not clear if FileMaker Pro is counted as such – if so, then the cost of updates will be much higher.
  • Getting the latest web templates – about $299
  • Ad-hoc support – charged at $30 per 15 mins for educational institutions and $45 per 15 mins for commercial organisations.

Koha

In comparison, you can get free support from any one of the Koha mailing lists or community groups. Alternatively, you can pay for dedicated support and training  from Koha ILS vendors such as BiblioTech Libraries from as low as $19.99 per month for hosting and $299 for once-off web-based training.

Vendor Lock-in

A crucial aspect to consider when getting a new library system (or any software for that matter) is the availability of software vendors for that particular system. What we mean by this is how easily you are able switch support services from one vendor to another. If you are limited to a single vendor for your support and services, this situation is known as vendor lock-in. This means that you become completely dependant on the software maker or provider with no alternative. This situation is undesirable because you are basically at the mercy of the whims of that vendor (for example, they can increase prices at any time or shut down, leaving you with no alternatives).

Athenaeum

In our research, we were unable to find alternative vendors for Athenaeum other than the makers of the software themselves, which is a classic case of vendor lock-in.

Koha

Koha ILS is not only developed and maintained by a strong community of thousands of developers and librarians globally, but there are also hundreds of companies worldwide – including BiblioTech Libraries – that offer premium support services. You are free to export your data switch from one vendor to the other at any time, or even go it alone if you have the expertise. And because Koha is not a proprietary system owned by any one individual or company, but is developed by an ever-growing community of volunteers, companies and organisations, it will always be a free software with guaranteed longevity.

Interface

Athenaeum

Our experience with Athenaeum’s interface (our tests were done on the Athenaeum Lite package) was that it came across as a bit clunky and difficult to navigate. What passes for the OPAC (Online Public Access Catalogue) has three tabs (issue, return, and check) and a search bar. In the Lite package, only the “check” tab works – we assumed that the issue and return tabs are either only available on the Pro version or are dead. There is no account management section for resetting passwords, viewing historical and current checkouts, placing holds, etc. in the Lite package.

Koha

Koha has two separate interfaces that are accessible via separate web or ip addresses via a web browser – the staff page for the librarians and an OPAC for the patrons. On the Staff page, all the modules and settings are neatly and readily accessible from the Home page. Navigation is quite simple and easy as well. On the OPAC, patrons can do searches by keyword, title, author, call number, subject, ISBN or series title. Patrons can also view their historical and current circulation data, fines and charges (and pay for them online too!), tags, change passwords, update their account details, make purchase suggestions, and create reading lists. As with the staff page, navigation is quite easy and straightforward. Both web interfaces are also customizable to include things like logo, custom links, notices, branding etc.

Summary of Pros and Cons: Koha vs. Athenaeum

Athenaeum

Pros Cons
Windows based, installation is relatively simple. Requires multiple modules (Athenaeum software, FileMaker modules etc) hence cumbersome and clunky.
Customisation is fairly simple. Templates cost a minimum $299. Costly – Athenaeum Lite will cost about $1600 for 5 users per year while the Pro version starts from about $1200 (including support). This does not include updaters ($450) and web templates ($299)
Limited users – FileMaker Pro costs $8748 for up to 100 users per year. Most libraries will typically have hundreds of users, making it very costly.
Yearly licensing – licenses have to be paid yearly.
Limited paid support – customer support must be paid for separately (minimum $450 per year). Even then, there are limits to the number of hours that can be claimed.
 Vendor Lock-in – apart from Athenaeum itself, we could not find any other companies that offer support and services for their software. This means that clients have no alternatives if they want a different support services, prices change, standards of service drop or if the company goes defunct.
Access – access across various devices requires additional software and configurations.
Isolation- there is no user community through which librarians can interact, exchange ideas and experiences, or receive free support.
WYSIWYG – What You See, Is What You Get. There is no incentive for the software makers to develop new features requested by customers.
Non-customizable – because it is not open-source, clients cannot modify or custom

Koha Integrated Library System

Pros Cons
Free – if you have the in-house expertise, you do not have to spend a single dollar to use Koha. You can install it and run it yourself. Requires a knowledge of Linux OS if you are installing it by yourself.
No licensing – updates and upgrades are delivered to you for free. Customization requires some knowledge of web-design.
Open-source – you can customize the software to suit your particular needs or add on custom modules.
No tiers – you have full access to every module and all the settings.
Web-based – once installed on a server, both patron and staff users can access the system using a web-browser via a local network or the internet.
Cloud capable – Koha can be hosted on remote servers by companies such as BiblioTech Libraries, which saves you money because you won’t need to purchase any hardware. Simply connect to your library system using your existing devices over the internet.
OS independent – you can access the staff page and OPAC via any web capable device (computer, tablet, phone etc) without additional software.
Unlimited users – there is no limit to the number of users, either staff or patron.
Unlimited database storage – there is no limit to the number of items or records you can add into your database.
Accessible – you library system can be accessed remotely via the internet from anyplace at anytime.
Strong community – Koha has a global active and extremely helpful community of librarians, developers, tech experts and ordinary users that offers free support and advice.
Regularly updated – Koha has two major upgrades per year (which include new features) and frequent updates which keeps reliable and bug-free.
Multiple support companies – if you want premium support services, there are dozens of companies and individuals such as BiblioTech Libraries who are ready to partner with you.
Supports electronic payments – patrons can pay library charges and fines using PayPal.
Customizable – both the staff page and OPAC web interfaces are highly customizable.
Widely available help – there are several websites and manuals online that give you how-tos, tips and tricks for using Koha.
Developed and maintained by librarians – Koha was made for librarians, by librarians.
Modules and Features Users can make suggestions/requests or even pay for new modules and features.
Interface – Koha has a clean, simple and easily navigable interface.
Compliant – Koha adheres to global library and web standards making it compatible with other systems and technologies. One such feature is the z39.50 module, which allows for sharing data with other libraries from all over the world.
WorldWide- koha is used by thousands of libraries all over the world.
Enterprise Class- Koha is used by major institutions and organizations worldwide, including leading universities, government institutions and massive library consortia.
Community owned – Koha is not developed and maintained by a group of volunteer librarians, developers and companies. It is not dependant on any one organization and therefore has guaranteed longevity and will always be free.

The Verdict

The verdict is clearly in Koha’s favour. While Athenaeum has the slight advantage of being a Windows- and Mac-based product, which are the most popular OSs, it’s disadvantages far outweigh that isolated benefit. Koha has better development, a cleaner design and superior features compared to Athenaeum, as well as more support options. It is also far more widely used and thus provides the opportunity for librarians, who are often isolated, to become part of a vibrant and helpful community.

However, the real knockout punch is the pricing. Libraries typically tend to have small budgets, which makes Athenaeum a deal breaker due to its high cost. Koha on the other end, is a free and open source library system, and for those who need it, has numerous options for low cost premium support. Contact us today at BiblioTech Libraries for more information on how you can partner with us to deliver the best library experience to your staff, patrons and institution.