changed notes

On Sat, 2006-07-01 at 08:31 +0000, reiffert@student.physik.uni-mainz.de
wrote:

> Here is what I found out when it's about using views with back-sql
> 
> I was playing with back-sql, in detail with mysql when noticing that
> searching over two views or more get's *VERY* slow. So I was
> looking for an replacement of the setup that can be found in
> man back-sql.
> 
> 
> I finally came to the result of using just *one* view in my mysql-database.
> 
> That gave me a strange error that looks like the follwing
> error message of the sql-server log:
> 
> 
> SELECT DISTINCT ldap_entries.id,ldap_entries.id,'inetOrgPerson' AS
> objectClass,ldap_entries.dn AS dn FROM ldap_entries,ldap_entries WHERE
> ldap_entries.id=ldap_entries.keyval AND ldap_entries.oc_map_id=1 AND 9=9 AND
> 3=3;
> 
> and the errormessage was: ERROR 1066 (42000): Not unique table/alias:
> 'ldap_entries'
> as "FROM ldap_entries,ldap_entries" is not working here.
> 
> 
> So I patched servers/slapd/back-sql/search.c and now everything is working
> at a useable speed for our MUA's (Thunderbird) that use slapd.
> 
> 
> 
> Additional information that can be found at the provided URL:
> - slapd.conf
> - the patch
> - mysql-tables used according to man back-sql
>   - ldap_attr_mappings
>   - ldap_entry_objclasses
>   - the *one* view ldap_entries holding all information
> 
> 
> Please feel free to ask back.

Your hack appears a misuse of back-sql that breaks its generality.  The
fact that you get poor performance out of back-sql, especially when
using views(, especially if your views are not optimized by adding
appropriate indices to keys, but this is out of scope here) is intrinsic
in the design of back-sql.  If you need performance, use back-bdb or
back-hdb.  If you put everything in one view that hijacks the name of a
table that is required internally by back-sql to work appropriately,
and, as such, should not be altered, then you cannot complain about it
breaking basic implicit join and aliasing rules.

Your issue is the result of a misuse of the software; I suggest your
patch be rejected as it would break back-sql destroying its generality
and versatility.  Feel free to use your patched version as a custom
backend that suits your addressbook-only needs, though.

p.




Ing. Pierangelo Masarati
Responsabile Open Solution
OpenLDAP Core Team

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
------------------------------------------

changed notes

On Sat, 1 Jul 2006, Pierangelo Masarati wrote:

> Your hack appears a misuse of back-sql that breaks its generality.  The
> fact that you get poor performance out of back-sql, especially when
> using views(, especially if your views are not optimized by adding
> appropriate indices to keys, but this is out of scope here)

Yes, thats beyond the scope here. Whenever mysql-5 can handle indices
on views ... I thank god and use it!

> is intrinsic
> in the design of back-sql.  If you need performance, use back-bdb or
> back-hdb.

Unfourtunatly I'm in the situation where my company left back-bdb and
put all adresses in a mysql-database, so I'm very sad about the new
situation but I can't change it.

> If you put everything in one view that hijacks the name of a
> table that is required internally by back-sql to work appropriately,
> and, as such, should not be altered, then you cannot complain about it
> breaking basic implicit join and aliasing rules.

Dear Pierangelo, I'm not complaining about broken stuff, all I was
finding out is, that when I hijack the system like I did, it works
pretty much faster for me. Magnitudes.

>
> Your issue is the result of a misuse of the software;

I came here to say "hello, here is my workaround, let's make
it better, so that the software gets better".


> I suggest your
> patch be rejected as it would break back-sql destroying its generality

Acknowledged, as this patch definitly is evil. How can it be done
better?

Implement a slapd.conf variable "hijacker true" with the default
"hijacker false"?
Can it be done in other ways? Or is rejecting the patch everything
that can be done?

>
> and versatility.  Feel free to use your patched version as a custom
> backend that suits your addressbook-only needs, though.

Kind regards
Thomas Reifferscheid

On Sat, 2006-07-01 at 16:56 +0200, Thomas Reifferscheid wrote:

> > Your issue is the result of a misuse of the software;
> 
> I came here to say "hello, here is my workaround, let's make
> it better, so that the software gets better".

OK; right now, I don't consider taking a general software and making it
work for your single table hack only anything any close to "get better".

> 
> > I suggest your
> > patch be rejected as it would break back-sql destroying its generality
> 
> Acknowledged, as this patch definitly is evil. How can it be done
> better?
> 
> Implement a slapd.conf variable "hijacker true" with the default
> "hijacker false"?

Feel free to make it work like that and to maintain your personal copy
like that.

> Can it be done in other ways? Or is rejecting the patch everything
> that can be done?

To me, at least, it looks like a reasonable starting point :).

Unfortunately, I don't have time right now to see if and how this
particular case could be generalized to something that complies to the
data model of back-sql.  It is not yet clear to me why you need to turn
ldap_entries into a view that contains also the data and not only the
connectivity info.

p.




Ing. Pierangelo Masarati
Responsabile Open Solution
OpenLDAP Core Team

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
------------------------------------------

On Sat, 1 Jul 2006, Pierangelo Masarati wrote:

>> Can it be done in other ways? Or is rejecting the patch everything
>> that can be done?
>
> To me, at least, it looks like a reasonable starting point :).
>
> Unfortunately, I don't have time right now to see if and how this
> particular case could be generalized to something that complies to the
> data model of back-sql.  It is not yet clear to me why you need to turn
> ldap_entries into a view that contains also the data and not only the
> connectivity info.


In my first attempt, according to man 5 back-sql, I had
two views:

one was: ldap_entries, containing the dn's (e.g. dn: uid=5, dc=...)
and the key-column and the other stuff required.

the 2nd view was:
key  cn sn mail


When entering a mail-adress into thunderbird, the answer from
slapd took 5-10 seconds and that's way to long.
(A complete ldapsearch took 30 seconds).

With the hijacked version:
the answer arrives immediatly and a complete ldapsearch
lasts 1.3 seconds.
The amount of querys send to mysql reduces as well by factor 3.

So the cause is mysql, thats for sure. But gaining speed at other
places sometimes might be helpful as well.


So you can close the issue, I will handle my own patch
as long as mysql does not support indices on views and
revert my hijacker-version to appropriate stuff afterwards.

That will let both of us have more time.

Thank you.


Kind regards
Thomas Reifferscheid

changed notes
moved from Incoming to Contrib

software misuse
reject patch?