[Date Prev][Date Next] [Chronological] [Thread] [Top]
RE: sql-backend - ldapadd fails

To:
Subject: RE: sql-backend - ldapadd fails
From: dpinkard@AccessLine.com
Date: Tue, 29 Nov 2005 17:10:43 -0800
Cc: OpenLDAP-software@OpenLDAP.org

The resolution to this, if anyone else ever gets stuck, is that the provided
sample SQL files do not have enough smarts to actually maintain the ldap
database properly. I think the overal layout/design stinks, but I at least
got it working enough to understand the moving parts. In the
ldap_oc_mappings table, it needs to know how to create additional rows in
the helper tables ('persons', in the case of inetOrgPerson from these
examples), and ldap_attr_mappings is unable to actually stuff the data in as
its add_proc, etc fields are empty. Included are some entries that work a
little better, but require MySQL functions to operate. They can't really
handle multiple updates at once, but it isn't too bad. Changing to mySQL's
auto_increment fields would fix that.

Example additions for MySQL 5 These additions do NOT handle deletions,
however they provide a much better starting point. They are based on the
testdb and back_sql files shipped with 2.3.11. No table changes were made to
the provided tables, however several functions have been added: 

INSERT INTO `ldap_attr_mappings` (`id`, `oc_map_id`, `name`, `sel_expr`,
`sel_expr_u`, `from_tbls`, `join_where`, `add_proc`, `delete_proc`,
`param_order`, `expect_return`) VALUES (1,1,'cn','concat(persons.name,\'
\',persons.surname)',NULL,'persons',NULL,NULL,NULL,3,0),
(2,1,'telephoneNumber','phones.phone',NULL,'persons,phones','phones.pers_id=
persons.id','insert into phones (id,phone,pers_id) values
(newphone(),?,?)',NULL,3,0),
(3,1,'givenName','persons.name',NULL,'persons',NULL,'update persons set
name=? where id=?',NULL,3,0),
(4,1,'sn','persons.surname',NULL,'persons',NULL,'update persons set
surname=? where id=?',NULL,3,0),
(5,1,'userPassword','persons.password',NULL,'persons','persons.password IS
NOT NULL','update persons set password=? where id=?',NULL,3,0),
(6,1,'seeAlso','seeAlso.dn',NULL,'ldap_entries AS
seeAlso,documents,authors_docs,persons','seeAlso.keyval=documents.id AND
seeAlso.oc_map_id=2 AND authors_docs.doc_id=documents.id AND
authors_docs.pers_id=persons.id',NULL,NULL,3,0); 

INSERT INTO `ldap_oc_mappings` (`id`, `name`, `keytbl`, `keycol`,
`create_proc`, `delete_proc`, `expect_return`) VALUES
(1,'inetOrgPerson','persons','id','select
newperson();',NULL,0),(2,'document','documents','id','select max(id)+1 from
ldap_entries;',NULL,0),(3,'organization','institutes','id','select max(id)+1
from ldap_entries;',NULL,0),(4,'referral','referrals','id','select max(id)+1
from ldap_entries;',NULL,0); 

DELIMITER ;; 
DROP FUNCTION IF EXISTS `newperson` ;; 
SET SESSION SQL_MODE="";; 
CREATE FUNCTION `newperson`() RETURNS int(11) DETERMINISTIC 
BEGIN 
DECLARE newkey integer(11); 
select max(id)+1 into newkey from persons; 
insert into persons (id) values (newkey); RETURN newkey; 
END ;; 
SET SESSION SQL_MODE=@OLD_SQL_MODE;; 
DROP FUNCTION IF EXISTS `newphone` ;; 
SET SESSION SQL_MODE="";; 
CREATE FUNCTION `newphone`() RETURNS int(11) DETERMINISTIC 
BEGIN 
DECLARE newkey integer(11); 
select max(id)+1 into newkey from phones; 
RETURN newkey; 
END ;; 
DELIMITER ; 

-----Original Message-----
From: owner-openldap-software@OpenLDAP.org
[mailto:owner-openldap-software@OpenLDAP.org]On Behalf Of
dpinkard@AccessLine.com
Sent: Monday, November 28, 2005 2:02 PM
To: ando@sys-net.it
Cc: OpenLDAP-software@OpenLDAP.org
Subject: RE: sql-backend - ldapadd fails



I'd say that it is likely do to my relative lack of understanding about
LDAP, except the configurations and schemas I'm using were directly lifted
from ~/src/openldap-2.3.11/servers/slapd/back-sql/rdbms_depend/mysql
including slapd.conf which now reads:

include         /usr/local/etc/openldap/schema/core.schema
include         /usr/local/etc/openldap/schema/cosine.schema
include         /usr/local/etc/openldap/schema/inetorgperson.schema

# Define global ACLs to disable default read access.

# Do not enable referrals until AFTER you have a working directory
# service AND an understanding of referrals.
#referral       ldap://root.openldap.org

pidfile         /usr/local/var/slapd.pid
argsfile        /usr/local/var/slapd.args

#######################################################################
# sql database definitions
#######################################################################

database        sql
suffix          "dc=example,dc=com"
rootdn          "cn=Manager,dc=example,dc=com"
#suffix         "o=sql,c=RU"
#rootdn         "cn=root,o=sql,c=RU"
rootpw          secret
dbname          ldap
dbuser          ldap
dbpasswd        *********
subtree_cond    "ldap_entries.dn LIKE CONCAT('%',?)"
insentry_stmt   "INSERT INTO ldap_entries (dn,oc_map_id,parent,keyval)
VALUES (?,?,?,?)"
has_ldapinfo_dn_ru      no


The ldif I was adding is:
dn: cn=Dan Pinkard,dc=example,dc=com
objectClass: inetOrgPerson
cn: Dan Pinkard
sn: Pinkard


As you mentioned the tracelog does explain things a little, but I'm not sure
what it's telling me to fix:
==>backsql_add("cn=Dan Pinkard,dc=example,dc=com")
oc_check_required entry (cn=Dan Pinkard,dc=example,dc=com), objectClass
"inetOrgPerson"
oc_check_allowed type "objectClass"
oc_check_allowed type "cn"
oc_check_allowed type "sn"
oc_check_allowed type "structuralObjectClass"
oc_check_allowed type "entryUUID"
oc_check_allowed type "creatorsName"
oc_check_allowed type "createTimestamp"
oc_check_allowed type "entryCSN"
oc_check_allowed type "modifiersName"
oc_check_allowed type "modifyTimestamp"
   backsql_add("cn=Dan Pinkard,dc=example,dc=com"): create procedure is not
defined for structuralObjectClass "inetOrgPerson" - aborting
send_ldap_result: conn=0 op=1 p=3
send_ldap_response: msgid=2 tag=105 err=53
ber_flush: 58 bytes to sd 9
<==backsql_add("cn=Dan Pinkard,dc=example,dc=com"): 53 "operation not
permitted within namingContext"
connection_get(9): got connid=0
connection_read(9): checking for input on id=0


The confusing part here is that it makes no reads to determine anything at
all.. it just 




-----Original Message-----
From: Pierangelo Masarati [mailto:ando@sys-net.it]
Sent: Monday, November 28, 2005 1:44 PM
To: dpinkard@AccessLine.com
Cc: OpenLDAP-software@OpenLDAP.org
Subject: Re: sql-backend - ldapadd fails


On Mon, 2005-11-28 at 12:03 -0800, dpinkard@AccessLine.com wrote:
> Some time ago someone posted about a problem with the SQL back-end
> connecting to a MySQL database. In my case I
> 'm using unixODBC 2.2.11 and MySQL 5.0.15 against openldap-2.3.11, and I'm
> getting the same problem I saw listed here some months ago. 
> 
> ldapadd -x -h 192.168.0.3 -D "cn=Manager,dc=example,dc=com" -wsecret -f
> example.ldif3
> adding new entry "cn=Dan Pinkard,dc=example,dc=com"ldap_add: Server is
> unwilling to perform (53)
>         additional info: operation not permitted within namingContext

To say anything more about that we need to know more, because back-sql
is far from something that just works out of the box.  In detail, I need
to know about your slapd.conf and your settings in ldap_oc_mappings and
ldap_attr_mappings tables; a schematic of your ldap_entries would help
as well.

> I know that the ODBC and SQL pieces are functioning, as it can read
> successfully when I execute ldapsearch -x -h 192.168.0.3 -b
> 'dc=example,dc=com' '(objectclass=*)', however I do not even get a
> connection to the MySQL database when I attempt the ldap add. 

sounds like back-sql doesn't let you there because it knows in advance
it has no clues about how to write to the RDBMS; should be a mapping
problem, but I'm just guessing.

I'd note that back-sql write routines return that error code and that
very message in many, many places; each of those places, however,
produces a very specific log message (at "trace" level).  If you look at
the server logs at "trace" level you'll quickly discover if and what is
missing from your configuration/metadata.

p.




Ing. Pierangelo Masarati
Responsabile Open Solution

SysNet s.n.c.
Via Dossi, 8 - 27100 Pavia - ITALIA
http://www.sys-net.it
------------------------------------------
Office:   +39.02.23998309          
Mobile:   +39.333.4963172
Email:    pierangelo.masarati@sys-net.it
------------------------------------------
Follow-Ups:
- RE: sql-backend - ldapadd fails
  - From: Quanah Gibson-Mount <quanah@stanford.edu>
- RE: sql-backend - ldapadd fails
  - From: Pierangelo Masarati <ando@sys-net.it>
Prev by Date: Re: ldap_sasl_interactive_bind_s: Can't contact LDAP server (-1)
Next by Date: RE: sql-backend - ldapadd fails
Index(es):
- Chronological
- Thread