False assumptions with MySQL

I spent far too much time this past week trying to find out why a Drupal was not  assigning authenticated user permissions to users who clicked on the validation link they were emailed. Instead, it would simply remove the temporary unverified user permission.

It's supposed to do the latter, but then also follow it up with the former. It turns out the problem was a Drupal bug that was triggered by my MySQL setup.

My Drupals all use a server that is a master in a set-up with two masters an three slaves. This has some implications for the auto_increment integer data type. In order to avoid clashes when two masters each insert a new record simultaneously, a master gets a specific instruction about which IDs it may assign.

These instructions specify how many numbers to skip - typically the number of masters in a cluster - and an offset to add, which is unique for each master server.

This means that when Drupal populates the database with basic roles at install time, the unique IDs assigned to the authenticated user is not 2, but 1 plus the offset that the server is configured to apply to auto-increment numbers.

In my case the role ID for authenticated user ended up being 3.

Unfortunately, this ID is hard-coded as 2 in the Drupal codebase, so whenever a module did the right thing and checked permissions based on the define role ID, the database would return "access denied", as it had no info for ID 2.

Oops.

I've made a patch for Drupal 6 and submitted it, so hopefully this will no longer be an issue in Drupal 6.13 and onward.