Recently, a very mysterious problem has cost us quite a lot of time and some headache too. Before I explain, what happened, I want to mention, that it happened on a Drupal 7 Site on a MS SQL Server database. According to an issue on drupal.org, that I will mention later, it may also occur with PostegreSQL, but most likely not with MySQL. But I also have a general advice regarding pathauto patterns and best practices...
What happened?
We've built a website for a client a few weeks ago. We've built all the functionality, the templates, etc and also set up an user account for our customer. A few days ago, the client needed some extra features, like a download area with customizable access permissions. For testing the new functionality, we needed to create an additional user. Creating an user in Drupal is nothing special, isn't it? Of course, it's one of the most basic operations, you'll have on every website, but BAAAAAAMMMMM - this time not! We saw the success message, that the user has been created, but the account didn't exist. Having a look into the database, you could find entries in the users_role table, also in the metatag table, but not in the users table itself! Same problem with updating existing users! It seemed, that the most important database transaction failed kind of silently, while subsequent insert statements defined in insert or update hooks were successful.
Examining the user_save() function
So we started debugging the code, starting from the form submition to the user_save() function. What happened there, was at first (and second and third) sight not really understandable. First of all, the user_save() function starts a DB transaction in a try-catch block, where it rolls the transaction back and re-throws the exception in the catch-Block. Also, the SQL Server driver module sets the PDO error mode - like the shipped DB drivers in Drupal core - correctly, so that every error throws an exception. So obviously, no exception was thrown in our case. Neither was a problem with the drupal_write_record() call inside. Because user_save() would also exit on receiving a FALSE return value from that call. At this moment, Drupal still thinks, that the user was saved correctly, already having it's user ID, and proceeds calling insert/update hooks, and doing other stuff, like creating the success message or - if it's configured - would also send out registration e-mails, referring to the ghost account.
At this point, I've found a workaround: as I saw, that the non-transcational insert/update statements in the update hooks (alias, metatag,...) were executed successfully, I gave it a try to comment out the DB transaction in the user_save() function. With that workaround, we could at least test our download area, but of course it didn't satisfy us. So I continued analyzing the problem. We enabled the PDO error log and found an error, where an integer column was queried with an NVARCHAR argument. Unluckily, the log entry didn't mention, in which query that happened. Also, activating the query log of Devel didn't help. It didn't show the insert/update queries resulting of the post request on save, but only the one from the overview page, where you get redirected after save. Further, I was still wondering why the transaction failed silently.
Finally, Pathauto was found guilty
As next step, I was concentrating on the triggered insert/update hooks, and soon found out, that something wrong happened within the Pathauto implementation. This looked also strange at me at first sight, as on updating an existing user without changing the username should never trigger an update statement, and further nothing, that could stop our transaction. But I was already to close to the solution, to simple quite here and disable path aliases for users.
After following the calls inside its hook implementation, I landed in the _pathauto_path_is_callback() function, having a try/catch block and a comment referring to the "PostgreSQL: PDOException:Invalid text representation when attempting to load an entity with a string or non-scalar ID" issue on drupal.org. There Pathauto is doing check, whether the new path alias is an already registered menu callback, as defined in a hook_menu() implementation. For paths, that are registered for any Drupal entities and expecting a number, you would raise an exception with the menu_get_item() call, when you provide a string instead. That's why they have wrapped a try-catch around it. Example: "node/xxx" instead of "node/123", or "user/username" instead of "user/34234".
After a closer look at the defined pathauto patterns, I have seen that one of our co-workers has defined "user/[user:uid]" as the pattern for users instead of "users/[user:uid]". So he raised a conflict with the existing menu callback of the user module ("user/%user"). Although it should be theoretically allowed to do this, it's not a very wise decision to mix the alias patterns up with existing menu callbacks. Just imagine, if a user's name would be only a number, e.g. "911" for user ID "234". Then user/911 would be a path alias for user/234 - crazy, confusing and not recommendable!
Lessons learned
If you define Pathauto patterns, always try to avoid conflicts with existing menu callbacks!