RTFM Series : Memcached and “DBI Connection failed: Access denied for user [..]”

Stumped by the Koha v 18.05’s refusal to access the database after a koha-remove followed by a koha-create using the same instance name? Then read on!

This post applies strictly to Koha 18.05 which was released on May 24, 2018. The new version with its new features has created a lot of excitement among the user. However the version has some major changes and unless you **CLOSELY** read and understand the release notes you will asking for trouble.

In this post we are going to talk about Memcached which is turned on by default from 18.05. If you overlook this fact, you may see yourself wasting hours trying to troubleshoot a problem, which may inexplicably (not quite even though it looks that way) go away after re-starting your system.

Last weekend my young friend Jayanta Nayek spent nearly a day trying to understand why he was getting the error – “DBI Connection failed: Access denied for user” whenever he tried to access the web-installer part of the staff client on his new 18.05 instance. Since he was on a test system, he had followed the old rinse-and-repeat routine. So when his installation did not work out for the first time, he ran sudo koha-remove library and then re-ran sudo koha-create --create-db library to start afresh.

When he re-created the instance i.e. “library”, he was stumped with the following error :

Software error:

DBIx::Class::Storage::DBI::catch {...} (): DBI Connection failed: Access denied for user 'koha_library'@'localhost' (using password: YES) at /usr/share/perl5/DBIx/Class/Storage/DBI.pm line 1490. at /usr/share/koha/lib/Koha/Database.pm line 103

Of course, when he tried to access the koha_library database from the mysql command-line client using the user and pass from his /etc/koha/sites/library/koha-conf.xml, it worked perfectly. But if he came back to the browser and tried to access the web-installer, the error would return.

So what was happening here? In one word – memcached! Memcached is an open source, distributed memory object caching system that alleviates database load to speed up dynamic Web applications. The system caches data and objects in memory to minimize the frequency with which an external database or API (application program interface) must be accessed. (Source: What is Memcached?).

In simple terms what this means for Koha is that memcached caches the frequent database queries fired off by Koha. And if an SQL result set has not changed since it was last queried *and* is already stored into memcached, it offers the data from in-memory hashes rather than using a more time-consuming database lookup process. Memcached (along with plack is intended to make Koha work faster under heavier loads).

When Jayanta would run sudo koha-remove library and followed it up with sudo koha-create --create-db library the Memcached server was not restarted, and kept holding on to the *original* database access hashes. Whereas following koha-create command to re-create the instance, the database authentication credentials (user & pass values from koha-conf.xml) were changed. That is why when he tried to directly access via the mysql command line client, it worked as the CLI client did not know about memcached. But when he tried to access the web-installer it failed as the connection query hash offered up from memcached had the old and not longer existing credentials.

So, if any reader of this blog should find themselves facing a problem like this, simple run the command sudo service memcached restart and once memcached has restarted, access the web-installer. It will work this time. Since memcached is an in-memory storage, the restart clears up the hashes and when the web-installer tries to access the database, it leads to what is called a “cache-miss” and thus the queries get run against the actual DB using the access credentials stored in koha-conf.xml.

And for goodness sake READ THE RELEASE NOTES ­čśë

Down the Rabbit Hole: Making cardnumber field in Koha longer than 16 characters limit

Making the cardnumber field bigger e.g. for Nigerian student matriculation numbers that overshoot Koha’s 16-char limit

Yesterday Mr. Adigun Samuel Akinwale from Osun, Nigeria, asked for help for his following question:

“How can one increase the character length of the library card number in Koha. It had been fix for 20 characters and was not like that before. In Nigeria most institution use student matric number for the library card in which some are more than 20 characters I try to report it in┬áBugzilla┬ábut it is give some difficulty in reporting.┬áPlease what can be a quick fix.”

Here’s keeping our promise to him, but first let’s get the legalese out of the way.

DISCLAIMER Please be aware that if you follow these instructions, either correctly OR incorrectly, you may end up breaking your Koha installation and/or damaging your data. If that happens, do not expect us to help you fix the mess in any way. If you at all use these instructions, you understand that you are on your own and is entirely responsible for your own safety.

Phew! With that out of our way, let’s looks at Mr. Akinwale’s problem. Koha for quite some time has maintained the cardnumber field in the borrowers table in the database as VARCHAR(16)[1]. For most users around the world, 16 characters is a large enough limit for a card number. But there are exceptions e.g. like this use-case from Nigeria.

A look at the new patron entry module of Koha will show (see screenshot above) that by default it will accept a card number that is minimum 1 and maximum 16 characters long.

cardnumber_01

Explanation : This is where the minimum 1 character requirement for the cardnumber comes from – the BorrowerMandatoryField patron system preference.

If you try to enter a card number larger than 16 characters like Mr. Akinwale, you will be halted in your tracks, thanks to the maxlength attribute of the text field with it’s value set to 16, even though field’s size attribute is set to 20.

Now, if you try to be smart and use the debugger / Firebug to dynamically manipulate the DOM and increase the maxlength to say 24, you will find out when attempting to save the new patron record that Koha will have none of it as you can see below ­čśë

cardnumber_02

Finding the source of the hard-coded value

We of course can not store a larger number in a field smaller than itself. So our first pit stop will be an ALTER TABLE SQL statement to increase the field size to 24.

ALTER TABLE `borrowers` CHANGE `cardnumber` `cardnumber` VARCHAR(24)

As we’ll find out, changing the column cardnumber size, while part of the solution, is *not* the final answer. Changing the field size in the database does not change the maxlength attribute and it won’t still allow us to enter anything larger than 16 character.

Digging for the source, it will take us to the following files: first the template file – memberentrygen.tt as located at the directory intranet-tmpl/prog/en/modules/members. This is where the html generation happens. The template itself is called by the Perl script –┬áintranet/cgi-bin/members/memberentry.pl┬á. So far, there is nothing to show what is setting our cardnumber to a maximum length of 16. But we’ll get there soon. The memberentry.pl calls the functions (aka sub routines) stored in C4::Members.pm Perl module, specifically in our case – a particular sub-routine –┬áC4::Members::get_cardnumber_length().

sub get_cardnumber_length {
    my ( $min, $max ) = ( 0, 16 ); # borrowers.cardnumber is a nullable varchar(16)
    $min = 1 if C4::Context->preference('BorrowerMandatoryField') =~ /cardnumber/;
    if ( my $cardnumber_length = C4::Context->preference('CardnumberLength') ) {
        # Is integer and length match
        if ( $cardnumber_length =~ m|^\d+$| ) {
            $min = $max = $cardnumber_length
                if $cardnumber_length >= $min
                    and $cardnumber_length <= $max;
        }
        # Else assuming it is a range
        elsif ( $cardnumber_length =~ m|(\d*),(\d*)| ) {
            $min = $1 if $1 and $min < $1;
            $max = $2 if $2 and $max > $2;
        }

    }
    return ( $min, $max );
}

This is here the hard-coded value is set my ( $min, $max ) = ( 0, 16 );. Changing it to my ( $min, $max ) = ( 0, 24 ); to match the width of the cardnumber column which we changed in an earlier step, is the answer. As we refresh the “Add patron” page, we will now find that the maxlength attribute of the cardnumber entry field has been changed to 24! Problem solved!

Caveat

While this small hack solves Mr. Akinwale’s problem, it comes with the following baggage:

  1. It is a hard-coded solution that does not solve the actual problem, rather merely shifts the goal post.
  2. You have to ensure that both the cardnumber field’s column width and the change in the get_cardnumber_length() sub-routine are exactly the same value.
  3. This is not an Koha community approved patch. So every time you upgrade your Koha, you will have to re-do the hard coding

The real risk here is accidentally modifying or deleting something unintentional and breaking your Koha, since Members.pm handles patron management functions. So, if you are to follow this example, it is strongly suggested that you first make a backup of the Members.pm and keep in safe. If things go wrong you can simply replace the changed file with the original from the backup. All said and done, these changes should preferably be made only by someone who can understand the code.

P.S. “Down the rabbit hole” is a metaphor for an entry into the unknown; the use comes from “Alice’s Adventures in Wonderland” by Lewis Carroll (real name Charles Lutwidge Dodgson)

References

[1] http://schema.koha-community.org/tables/borrowers.html