[Date Prev][Date Next]
[Chronological]
[Thread]
[Top]
RE: sql-backend - ldapadd fails
I suggest you cook a version of
servers/slapd/back-sql/rdbms_depend/mysql/testdb_metadata.sql
(call it, say, testdb_metadata-5.X.sql) that resembles the example data
provided in pgsql, so that MySQL can be used in OpenLDAP 2.3's back-sql
tests. If this implies relevant changes in the other files, it may be
worth a dedicated
servers/slapd/back-sql/rdbms_depend/mysql-5.X/
directory.
You should submit it thru the ITS: <http://www.openldap.org/its/>,
following the general contribution guidelines provided at
<http://www.openldap.org/devel/contributing.html> to speed up
integration. If you think there's anything relevant that is specific to
the use of back-sql with MySQL 5.X with respect to previous versions of
the same RDBMS, you may want to discuss it in a FAQ entry. I see you
already loaded some stuff in
<http://www.openldap.org/faq/data/cache/1236.html>; it's a matter of
personal taste, but I find that posting a bit confusing. If you feel
like providing your contribution in a more complete form, I'd discuss in
the FAQ only the headlines of your enhancement (e.g. eliminate the non-
specific portions of those SQL entries and mark in bold what's the
difference with respect to existing code), and load the bulk files into
an ITS entry (candidate for inclusion in mainstream code). Then, in the
FAQ, you can provide a link to the ITS entry.
Thanks for contributing. p.
On Tue, 2005-11-29 at 17:10 -0800, dpinkard@AccessLine.com wrote:
>
> 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
> ------------------------------------------
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
------------------------------------------