+1 (717) 461-8095 /+263 (775) 937-458 /+263 (777) 337 348 enquiries@bibliotechzw.com

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.

  • Facebook
  • Twitter
  • LinkedIn

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.

  • Facebook
  • Twitter
  • LinkedIn

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 🙂

Subscribe To Our Newsletter

Join our mailing list to receive the latest special offers and news updates from our team.

You have Successfully Subscribed!

Share This