Wednesday, November 29, 2006

Development: Empty strings in Oracle and MySQL

Today I was testing some code I've written, on a client's QA environment. These tests revealed, that if your tests succeed locally on a MySQL server environment this doesn't necessarily mean the tests will succeed on an Oracle server. I was having problems with Oracle's interpretation of an empty string. It seems that if you're executing the following statement:

insert into MyTable (c1,c2) values ('val1','');

this is the same as executing:

insert into MyTable (c1,c2) values ('val1',null);

IMHO this is plain wrong. '' does not equal NULL from a programmer's point of view.
Inserting an empty string (or '') can be convenient to make your prepared statements easier.
Suppose, in the above example, c2 is an optional parameter. When c2 is not available you insert ''. In this case a prepared statement to select records from MyTable could be:

select * from MyTable where c1 = ? and c2 = ?

After substitution the resulting statement for MySQL will be:

select * from MyTable where c1 = 'example' and c2 = ''

And for Oracle:

select * from MyTable where c1 = 'example' and c2 = null

Which is wrong.
So, if Oracle replaces my empty string with null, the prepared statement should be:

select * from MyTable where c1 = ? and coalesce(c2,'dontcare') = coalesce(?,'dontcare')

(please see also this link)
This works for both MySQL and Oracle ... of course.

Monday, November 13, 2006

Linux: Fixing authentication in Apache 2, after doing an upgrade

This is the second post on upgrading my Debian box to testing. Obviously everything was b0rken, even authentication mechanisms put in place for Apache2. Apache2 is using the local LDAP server for authenticating users on a subversion repository. Apparently, the people of Apache did some "refactoring" on the naming of their authentication modules. They renamed auth_ldap to authnz_ldap. Enabling this module instead of the outdated on, did not do the trick. I got the following error instead:

[Thu Nov 02 19:07:26 2006] [crit] [client] configuration error: couldn't check user. No user file?: /svn/configs/!svn/act/ed8028d3-8254-bd4c-bc94-c3b93f8b97bb
[Thu Nov 02 19:07:44 2006] [crit] [client] configuration error: couldn't check user. No user file?: /svn/configs/!svn/act/b2296e68-a114-8e49-aea6-bebfe78947bf
[Thu Nov 02 19:14:57 2006] [notice] caught SIGTERM, shutting down

I did some more reading and found this presentation very useful: TH21 - Using LDAP Authentication in Apache 2.2.ppt (I want to post a link, but the site seems down)
After updating my config, authentication started working again. Here it is:

<Location /svn/configs/>
AuthType Basic
AuthName "svn"
AuthBasicProvider ldap
AuthLDAPURL ldap://localhost:389/ou=Users,dc=MyDomain?uid
AuthzLDAPAuthoritative off
require valid-user

That wasn't that bad ... was it ;)

Monday, November 06, 2006

Development: Convert your code to html

If you want to cut and paste code into an html page, you will find the following tools very handy:

Makes blogging and publishing code easier ;)

Sunday, November 05, 2006

Linux: Upgrading Debian woes (1)

So I decided to give my server, running Debian testing, an upgrade. Normally this works like a breeze. I have never encountered any problems during an upgrade, even though I am following testing.
This time was different and things were very b0rken. This is about fixing my first issue: LDAP + libnss + udev.


During the upgrade, the script didn't manage to create a decent backup of the running LDAP server. This LDAP is serving users for my home network and is also the backend for my Samba domain controller. So the LDAP was very b0rken. The only fix I could see was repopulating the entire tree, starting from a fresh, empty LDAP. To help me with this, I found this a very interesting and thorough guide. Since the LDAP is also my Samba backend I am using smbldap-tools to maintain users and such. So repopulating the LDAP wasn't such a big task.


What is udev?
udev works entirely in userspace, using hotplug events the kernel sends whenever a device is added or removed from the kernel. Details about the devices are exported by the kernel to the sysfs filesystem at /sys All device naming policy permission control and event handling is done in userspace. devfs is operated from within the kernel.
This thing is started way before my LDAP boots. Why? Because this thing is necessary to build the /dev tree and modprobing drivers, such as drivers for my network card. So udev starts very early in the boot process looking for users and groups to assign. E.g. /dev/audio is assigned to the audio group.
If a user or group is not found local, it tries to search the LDAP, which is ... not there. Before the upgrade udev tried a few times to reach the LDAP, and finally gave up. Now it didn't. It just kept trying to reach the damn LDAP. This link describes the problem I was having (bug #375077). The solution to this problem was to change the bind_policy so it only tries to reach the LDAP 3 times (or less if you want):

bind_policy hard
nss_reconnect_tries 3
nss_reconnect_sleeptime 1
nss_reconnect_maxconntries 3


After messing around a little, I suddenly got the following error:

ldap-nss.c:1323: do_init: Assertion `cfg->ldc_uris[__session.ls_current_uri] != ((void *)0)' failed.

Now that's what I call a decent error ;) This happened when trying to log in with a user known in the LDAP, but not on the system. After pulling my hair a little and googling a while, I discovered I accidentally messed up the libnss-ldap.conf. This configuration file is necessary for the name switch service to be able to access the ldap. This file contains the root DN and credentials to access/search the LDAP. Therefore this file should be:

  • readable by everyone;

  • or readable by root only, but then you'll need the nscd (name switch caching daemon).

Since I must be able to add and change users on this particular machine, the file contains administrator credentials and is readable for root only. So I need the nscd running which was throwing the above error while asking the name switch service for a user existing in the LDAP. Reverting the libnss-ldap.conf to its original state solved my problems. If you store your /etc in a subversion repository, this is easy ;)
In a next post I will be discussing my next problem: "Fixing authentication in Apache 2, after doing an upgrade"