I am implementing an OpenLDAP installation that utilizes inetOrgPerson as the main user structure with roughly forty attributes that may be used with each user. Of the forty attributes, I have added a custom schema which includes 15 custom attributes. I am using MySQL 5 as the backend via backsql.
The problem I am seeing is that for a given user, if I write values to all 40 attributes and then read them back using an LDAP browser, three of the attributes do not return their values. The three attributes are: cn, userPassword, and employeeType.
I have run slapd with the debug level of -1 (all) to capture a trace of what happens when I read an attribute that correctly returns its value and also a trace of reading an attribute that does not return its value (cn, userPassword, or employeeType). Comparing the two traces, the only appreciable difference between the two is as follows, which is in the failing trace:
==>backsql_id2entry() backsql_id2entry(): custom attribute list ==>backsql_get_attr_vals(): oc="inetOrgPerson" attr="employeeType" keyval=8 backsql_get_attr_vals(): error executing attribute count query 'SELECT COUNT(*) FROM users WHERE users.id=? AND ' Return code: -1 nativeErrCode=1064 SQLengineState=37000 msg="[MySQL][ODBC 3.51 Driver][mysqld-5.0.45-community-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1" ==>backsql_get_attr_vals(): oc="inetOrgPerson" attr="objectClass" keyval=8
I also set up a MySQL error trace and ran the two attribute reads and came up with the only appreciable difference being the SQL statement, as above:
43 Query SELECT COUNT(*) FROM users WHERE users.id=8 AND
It appears to me that the SQL statement is not being completed for some reason, since in the slapd trace where the attribute read is successful, the backsql_get_attr_vals(); just prints out, number of values in query: 1, followed by, number of values in query: 0, followed by the actual data packets containing the value of the attribute.
I can provide additional information if needed. I was unable to find information about this problem on the OpenLDAP site.
Kevin Burnett
Kevin Burnett wrote:
I am implementing an OpenLDAP installation that utilizes inetOrgPerson as the main user structure with roughly forty attributes that may be used with each user. Of the forty attributes, I have added a custom schema which includes 15 custom attributes. I am using MySQL 5 as the backend via backsql.
The problem I am seeing is that for a given user, if I write values to all 40 attributes and then read them back using an LDAP browser, three of the attributes do not return their values. The three attributes are: cn, userPassword, and employeeType.
I have run slapd with the debug level of -1 (all) to capture a trace of what happens when I read an attribute that correctly returns its value and also a trace of reading an attribute that does not return its value (cn, userPassword, or employeeType). Comparing the two traces, the only appreciable difference between the two is as follows, which is in the failing trace:
==>backsql_id2entry() backsql_id2entry(): custom attribute list ==>backsql_get_attr_vals(): oc="inetOrgPerson" attr="employeeType" keyval=8 backsql_get_attr_vals(): error executing attribute count query 'SELECT COUNT(*) FROM users WHERE users.id=? AND ' Return code: -1 nativeErrCode=1064 SQLengineState=37000 msg="[MySQL][ODBC 3.51 Driver][mysqld-5.0.45-community-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1" ==>backsql_get_attr_vals(): oc="inetOrgPerson" attr="objectClass" keyval=8
I also set up a MySQL error trace and ran the two attribute reads and came up with the only appreciable difference being the SQL statement, as above:
43 Query SELECT COUNT(*) FROM users WHERE users.id=8 AND
It appears to me that the SQL statement is not being completed for some reason, since in the slapd trace where the attribute read is successful, the backsql_get_attr_vals(); just prints out, number of values in query: 1, followed by, number of values in query: 0, followed by the actual data packets containing the value of the attribute.
I can provide additional information if needed. I was unable to find information about this problem on the OpenLDAP site.
Kevin Burnett
You don't say what slapd version you are using. Please provide the basics.
On Friday 09 November 2007 22:46:10 Kevin Burnett wrote:
I am implementing an OpenLDAP installation that utilizes inetOrgPerson as the main user structure with roughly forty attributes that may be used with each user. Of the forty attributes, I have added a custom schema which includes 15 custom attributes. I am using MySQL 5 as the backend via backsql.
As the problem you describe is obviously related to the use of back-sql, is there a specific reason you are using back-sql, or just because you think it is convenient? If there is no specific reason, you may wish to reconsider, as back-sql is not as well supported, feature rich, or anywhere near as fast as the primary (bdb, hdb) backends, where most likely you would not have experienced any problems that you are experiencing here.
Regards, Buchan
Kevin Burnett wrote:
I have run slapd with the debug level of -1 (all) to capture a trace of what happens when I read an attribute that correctly returns its value and also a trace of reading an attribute that does not return its value (cn, userPassword, or employeeType). Comparing the two traces, the only appreciable difference between the two is as follows, which is in the failing trace:
==>backsql_id2entry() backsql_id2entry(): custom attribute list ==>backsql_get_attr_vals(): oc="inetOrgPerson" attr="employeeType" keyval=8 backsql_get_attr_vals(): error executing attribute count query 'SELECT COUNT(*) FROM users WHERE users.id=? AND ' Return code: -1 nativeErrCode=1064 SQLengineState=37000 msg="[MySQL][ODBC 3.51 Driver][mysqld-5.0.45-community-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1" ==>backsql_get_attr_vals(): oc="inetOrgPerson" attr="objectClass" keyval=8
I also set up a MySQL error trace and ran the two attribute reads and came up with the only appreciable difference being the SQL statement, as above:
43 Query SELECT COUNT(*) FROM users WHERE users.id=8 AND
This is clearly incorrect. However, it is not easy to determine where it fails without looking at your back-sql metadata. I suspect that attribute's metadata provides a non-NULL but empty join_where value.
It appears to me that the SQL statement is not being completed for some reason, since in the slapd trace where the attribute read is successful, the backsql_get_attr_vals(); just prints out, number of values in query: 1, followed by, number of values in query: 0, followed by the actual data packets containing the value of the attribute.
I can provide additional information if needed. I was unable to find information about this problem on the OpenLDAP site.
To provide even more extensive logging, you can rebuild back-sql after manually defining BACKSQL_TRACE in back-sql.h; this is really a developers'-only option, so you should not use it in production, as the logging is really verbose.
p.
Ing. Pierangelo Masarati OpenLDAP Core Team
SysNet s.r.l. 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 ---------------------------------------
Pierangelo,
You suggested, "This is clearly incorrect. However, it is not easy to determine where it fails without looking at your back-sql metadata. I suspect that attribute's metadata provides a non-NULL but empty join_where value."
I wanted to thank you for this insight because this is exactly what my problem was. In the ldap_attr_mappings table, for the three attributes in question, the join_where column was not set to Null. Since I do not currently need a join syntax, I simply set the three values to Null. The attributes now return their values correctly.
Thanks for making a great product, OpenLDAP.
Kevin Burnett
On Nov 15, 2007 4:08 AM, Pierangelo Masarati ando@sys-net.it wrote:
Kevin Burnett wrote:
I have run slapd with the debug level of -1 (all) to capture a trace of what happens when I read an attribute that correctly returns its value and also a trace of reading an attribute that does not return its value (cn, userPassword, or employeeType). Comparing the two traces, the only appreciable difference between the two is as follows, which is in the failing trace:
==>backsql_id2entry() backsql_id2entry(): custom attribute list ==>backsql_get_attr_vals(): oc="inetOrgPerson" attr="employeeType" keyval=8 backsql_get_attr_vals(): error executing attribute count query 'SELECT COUNT(*) FROM users WHERE users.id=? AND ' Return code: -1 nativeErrCode=1064 SQLengineState=37000 msg="[MySQL][ODBC 3.51 Driver][mysqld-5.0.45-community-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1" ==>backsql_get_attr_vals(): oc="inetOrgPerson" attr="objectClass" keyval=8
I also set up a MySQL error trace and ran the two attribute reads and came up with the only appreciable difference being the SQL statement, as above:
43 Query SELECT COUNT(*) FROM users WHERE users.id=8 AND
This is clearly incorrect. However, it is not easy to determine where it fails without looking at your back-sql metadata. I suspect that attribute's metadata provides a non-NULL but empty join_where value.
It appears to me that the SQL statement is not being completed for some reason, since in the slapd trace where the attribute read is successful, the backsql_get_attr_vals(); just prints out, number of values in query: 1, followed by, number of values in query: 0, followed by the actual data packets containing the value of the attribute.
I can provide additional information if needed. I was unable to find information about this problem on the OpenLDAP site.
To provide even more extensive logging, you can rebuild back-sql after manually defining BACKSQL_TRACE in back-sql.h; this is really a developers'-only option, so you should not use it in production, as the logging is really verbose.
p.
Ing. Pierangelo Masarati OpenLDAP Core Team
SysNet s.r.l. 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
openldap-software@openldap.org
-
Buchan Milne -
Gavin Henry -
Kevin Burnett -
Pierangelo Masarati