Bug 22738

Hi, this might turn out to be a problem with your setup. We just fixed an LDAP that received quite a bit of testing, so I assume that LDAP should be working ok.
It might be work asking about your issue on the mailing list instead.

Some things that come to mind:
- Is the patron account you try to log in with already created in Koha?
- Are you adding patron accounts on first log in with LDAP?
- If that is the case, have you mapped categorycode? (patron category code needs to match exactly what was set up in Koha)

Indeed.. we need more to go on here I'm afraid.

It does look like a possible mapping issue to me.

First, Thank you all for your response. 
 I checked nearly anything i guess. In the LDAP configuration i played with any setting i could do. Disabled replication, update, bind and/or mapping itself. But still the same issue. I found something in a forum that mapping needs to be enabled for each column/attribute of the borrowers table where NULL is not allowed. Even if i set those values to true the issue still exists. I also disabled the setting that a patron is allowed to change the password via opac, issue still exists. Like Katrin said, i have checked the category code and tried to send it as default value within the LDAP Config:

<categorycode is="employeeType"   >MA</categorycode>

The complete LDAP Config looks like this:

<useldapserver>1</useldapserver>
  <!-- LDAP SERVER (optional) -->
  <ldapserver id="xx.xx.org">
    <hostname>ldap://xx.xxx.80.5</hostname>
    <base>dc=xx,dc=org</base>
    <user>cn=admin,dc=xx,dc=org</user>             <!-- DN, if not anonymous -->
    <pass>xxxxxxxx</pass>          <!-- password, if not anonymous -->
    <replicate>0</replicate>       <!-- add new users from LDAP to Koha database -->
    <update>0</update>             <!-- update existing users in Koha database -->
    <auth_by_bind>0</auth_by_bind> <!-- set to 1 to authenticate by binding instead of password comparison, e.g., to use Active Directory -->
    <anonymous_bind>0</anonymous_bind> <!-- set to 1 if users should be searched using an anonymous bind, even when auth_by_bind is on -->
    <update_password>0</update_password> <!-- set to 0 if you don't want LDAP                      passwords synced to the local database -->
    <mapping>                  <!-- match koha SQL field names to your LDAP 
                                record field names -->
      <firstname    is="givenname"      ></firstname>
      <surname      is="sn"             ></surname>
      <address      is="postaladdress"  >53113</address>
      <city         is="l"              >Bonn</city>
      <zipcode      is="postalcode"     >53113</zipcode>
      <branchcode   is=""               >BIB</branchcode>
      <userid       is="uid"            ></userid>
      <password     is="userpassword"   ></password>
      <cardnumber   is="employeeNumber" ></cardnumber>
      <email        is="mail"           ></email>
      <categorycode is="employeeType"   >MA</categorycode>
      <phone        is="telephonenumber"></phone>
      <dateenrolled is=""               >2019-04-17</dateenrolled>
      <dateexpiry   is=""               >4749-04-17</dateexpiry>
   </mapping>
  </ldapserver>

I nearly tried any combination of the mapping fields and also of the config parameter (0 or 1), but nothing worked for me. 

A question regarding sth Katrin asks, where do i check if i am adding a patron account on first log in with LDAP? 

Thanks again for all your help and responses.

(In reply to morsch from comment #3)
> 
> <categorycode is="employeeType"   >MA</categorycode>
> 
Be careful with that ^
It does not mean fallback on default MA if employeeType is not a valid category code.
It means fallback on default if employeeType is empty.

So, if employeeType contains a non-valid category, this could raise the error you put in your first comment.

Ok, i see. But the field "employeeType" on LDAP Server contains the valid categorycode. Even if a user with with empty employeeType tries to log in the error appears.

(In reply to morsch from comment #5)
> Ok, i see. But the field "employeeType" on LDAP Server contains the valid
> categorycode. Even if a user with with empty employeeType tries to log in
> the error appears.

What happens if you remove the mapping with employeeType and just keep the default value:

<categorycode is="">MA</categorycode>

Supposing MA is also a valid category code

Created attachment 88471 [details]
Screenshot of mysql and koha admin for category code

Yes, did that already. Issue still exists when doing that setting:

<categorycode is="">MA</categorycode>

Yes, "MA" is the categorycode as you can see in the attached picture. There you see table entry from mysql and settings from koha administration gui.

Not very likely, but something I have had problems with in the past: MySQL seems to ignore a whitespace at the end of values, while Perl does not. It happens easily, very hard to spot even when you have access to the data via SQL. Using LIKE or comparing the length helps to identify:
  SELECT * FROM Foo WHERE CHAR_LENGTH(field) != CHAR_LENGTH(TRIM(field))

(In reply to Katrin Fischer from comment #8)
> Not very likely, but something I have had problems with in the past: MySQL
> seems to ignore a whitespace at the end of values, while Perl does not. It
> happens easily, very hard to spot even when you have access to the data via
> SQL. Using LIKE or comparing the length helps to identify:
>   SELECT * FROM Foo WHERE CHAR_LENGTH(field) != CHAR_LENGTH(TRIM(field))

What exactly do you mean? I am not sure where and what do check in detail... Thank you for your help!

If you had "MA " as your categorycode, Perl will not match that to "MA". It's not likely, but we have run into this. 
Try something like:

select * from categories where categorycode like "MA"

If you do 

select * from categories where categorycode = "MA" 

it will always be found, whitespace or not.

(In reply to Katrin Fischer from comment #10)
> If you had "MA " as your categorycode, Perl will not match that to "MA".
> It's not likely, but we have run into this. 
> Try something like:
> 
> select * from categories where categorycode like "MA"
> 
> If you do 
> 
> select * from categories where categorycode = "MA" 
> 
> it will always be found, whitespace or not.

Ahh ok, thanks for that. I did that but sadly there is no whitespace in the category code... When running command ##select * from categories where categorycode = "MA"## the result is the category "MA"... 
Do you know how to disable that a user will be created when logging in via opac? Or will a patron be created by default? I thought if i disable the replicate setting in ldap koha-conf it wont...

(In reply to morsch from comment #11)
> (In reply to Katrin Fischer from comment #10)
> > If you had "MA " as your categorycode, Perl will not match that to "MA".
> > It's not likely, but we have run into this. 
> > Try something like:
> > 
> > select * from categories where categorycode like "MA"
> > 
> > If you do 
> > 
> > select * from categories where categorycode = "MA" 
> > 
> > it will always be found, whitespace or not.
> 
> Ahh ok, thanks for that. I did that but sadly there is no whitespace in the
> category code... When running command ##select * from categories where
> categorycode = "MA"## the result is the category "MA"... 
> Do you know how to disable that a user will be created when logging in via
> opac? Or will a patron be created by default? I thought if i disable the
> replicate setting in ldap koha-conf it wont...

Just to make sure (probably copy and paste error), but did you run the statement with like or =? The replicate setting in the koha-conf.xml should do that. I think there are 3: just auth, create on first login, update with every login. You might want to disable update too. There are also some notes on the manual: https://koha-community.org/manual/18.11/en/html/apis_protocols.html?highlight=ldap#ldap

(In reply to Katrin Fischer from comment #12)
> (In reply to morsch from comment #11)
> > (In reply to Katrin Fischer from comment #10)
> > > If you had "MA " as your categorycode, Perl will not match that to "MA".
> > > It's not likely, but we have run into this. 
> > > Try something like:
> > > 
> > > select * from categories where categorycode like "MA"
> > > 
> > > If you do 
> > > 
> > > select * from categories where categorycode = "MA" 
> > > 
> > > it will always be found, whitespace or not.
> > 
> > Ahh ok, thanks for that. I did that but sadly there is no whitespace in the
> > category code... When running command ##select * from categories where
> > categorycode = "MA"## the result is the category "MA"... 
> > Do you know how to disable that a user will be created when logging in via
> > opac? Or will a patron be created by default? I thought if i disable the
> > replicate setting in ldap koha-conf it wont...
> 
> Just to make sure (probably copy and paste error), but did you run the
> statement with like or =? The replicate setting in the koha-conf.xml should
> do that. I think there are 3: just auth, create on first login, update with
> every login. You might want to disable update too. There are also some notes
> on the manual:
> https://koha-community.org/manual/18.11/en/html/apis_protocols.
> html?highlight=ldap#ldap

I run the = statement.
Ok, thats where i tried to configure it. Tried it without update, replicate and auth. But Problem sill appears. Thats so frustrating...

You need to run the 'like' statement :)

(In reply to Katrin Fischer from comment #14)
> You need to run the 'like' statement :)

Ok, did that. It returns the category as well. That is correct isn't it?

Yes. Sadly, this was my last idea :(

Anyways, thank you very much! Is there anybody else who can help me with this?

Can't replicate, suspect config, not a project blocker. Please, if you need more ideas, send your query to the Koha mailing list. 

Cheers,
Liz Rea

Hi, adding the mailing list CC won't help, especially not the koha-bugs one as this is for the bugzilla changes and only very few are subscribed there (not including the bugzilla user itself). The normal mailing lists has a much wider audience. I suggest sending all information to the koha-devel list:

http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel

Someone might be able to spot the issue, that we haven't been able to figure out.

Severity has been downgraded because we can't reproduce the problem at the moment.

Thank you very much. Hopefully one from the list could help me. Thanks for your help and patience Katrin

I used to fix it by myself. For those of you who are interested in the working ldap config, here it is:

<!-- LDAP SERVER (optional) -->
<ldapserver id="ldap1.xxx.org">
<hostname>ldap://xx.xxx.80.5</hostname>
<base>dc=xx,dc=xx</base>
<user>cn=admin,dc=xx,dc=xx</user>
<pass>xxxxxxxxx</pass>
<replicate>1</replicate>
<update>1</update>
<auth_by_bind>0</auth_by_bind>
<anonymous_bind>0</anonymous_bind>
<principal_name>uid=%s,dc=xx,dc=xx</principal_name>
<update_password>1</update_password> 
<mapping>
      <firstname    is="giveNname"      ></firstname>
      <surname      is="sn"             ></surname>
      <address      is="postalAddress"  ></address>
      <city         is="l"              >Bonn</city>
      <zipcode      is="postalCode"     ></zipcode>
      <branchcode   is="branch"         >BIB</branchcode>
      <userid       is="uid"            ></userid>
      <password     is="userPassword"   ></password>
      <email        is="mail"           ></email>
      <categorycode is="employeeType"   >MA</categorycode>
      <phone        is="telephoneNumber"></phone>
</mapping>
</ldapserver>

Keep in mind to replace all individual settings, like IP, categorycode etc. It seems the problem was the former missing "principal_name" setting. Furthermore you need to check the case sensitivity of the mapping attributes from ldap. You need to type "giveNname" instead of "givenname".

Thx a lot for sharing the fix!