Oh its horrible when Drupal decides it’s got a headache and throws an error message out:
The website encountered an unexpected error. Please try again later.
I’ve had a few head scratchers over my time and as this website has evolved from the early days of Drupal5 and been through squillions of updates and manual SQL fudges I’m never really surprised when I break something. Anyway, just in case this helps someone this is how I researched and solved this issue.
1 – What Error?
We get the annoyingly bland ‘The website encountered an unexpected error. Please try again later.’ error message
2 – More Detail Please?
So its time to find out what the last error message was in the Drupal Error Log. On your Drupal site go to ADMIN then REPORTS then RECENT LOG MESSAGES. Or if your using the excellent ADMIN MENU MODULE just click it on the REPORTS menu option on the far right of the tool bar.
3 – What does that mean?
so now we can see our error message:
Notice: Undefined variable: key in comment_prepare_thread() (line 900 of/home/projex/www/www/modules/comment/comment.module).
still not that much help but a little googling and the error seemed to indicate that the COMMENT module was farting because old comments were sitting in the comment file and the UID (Unique User Identifier) was from accounts which had been purged, deleted or just generally lost over time.
4 – Research a Solution?
I found this on a Drupal forum:
It turns out the problem is (sort of) to do with comments that were migrated in to D7 from a D6 database. It turns out the administrator of the D6 site which we upgraded to D7 had deleted some users from the user table. In turn, this means there are comments in the comments table that have uids, but there is no corresponding user in the users table as it was deleted in the D6 system.
Now, as I also use LOGIN TOBOGGAN to clear out old SPAM user profiles from the Drupal database – it sounds like this could be the right track to investigate. So, lets check for any comments which are using a UID value that references a non-existent UID value on the USER table. So, just pop into your MYSQL Admin panel and run the following command in the Drupal Database:
SELECT * FROM `comment` WHERE uid NOT IN (SELECT uid FROM users)
This will hopefully return NO ROWS but in my case it showed lots of comments that had been orphaned from *purged* user profiles (and that not good news):
[ click for a detailed view ]
5 – Fix the Problem!?!?
So I got results – What shall I do? If you are having this error because it cant find the user accounts in the UID column then you can (a) delete the comment(s) or (b) keep them and just update them to point at a valid User Profile. So how about pointing them at Anonymous? We can use SQL to do this as well:
UPDATE `comment` SET uid = 0 WHERE uid NOT IN (SELECT uid FROM users)
However, my problem is different because all these comments are already point at UID=0. The Drupal Default ANONYMOUS profile… so lets check the USER table and see if anything weird is going on in there. One quick look later tells me that I do not have a UID=0 in my USER table. DAMN IT! There are many ways to accidentally delete the uid 0 (Anonymous) user: a mistaken query that forgot to use “and uid != 0” or a Views Bulk Operations view that was a little too aggressive.
This is a big problem and I suspect that User – (anonymous) has either been accidentally deleted in a fat finger moment of purging or a bug within one of the purge modules that I have tried recently. Either way The solution here is to create a new “empty” user with uid 0.
The technique to fix it depends on a few factors. Below are different techniques that work on Drupal 7 or Drupal 6.
For Drupal 7, you can run two queries:
insert into users (name, pass, mail, theme, signature, language, init, timezone) values ('', '', '', '', '', '', '', ''); update users set uid = 0 where name = '';
This will insert a new row getting a random UID and then the update query fixes to get the right uid for the user.
For Drupal 6 using a slightly different technique
You can also insert directly with a specific value for the auto-incrementing UID if you tell your database to use a different SQL_MODE.
This pair of statements will bring the user back on Drupal 6:
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; INSERT INTO `users` (`uid`, `name`, `pass`, `mail`, `mode`, `sort`, `threshold`, `theme`, `signature`, `created`, `access`, `login`, `status`, `timezone`, `language`, `picture`, `init`, `data`, `timezone_name`, `signature_format`) VALUES ('0', '', '', '', 0, 0, 0, '', '', 0, 0, 0, 0, NULL, '', '', '', NULL, '', 2);
select name, uid from users where name = '';
Output should be like this:
-+------+-----+ -| name | uid | -+------+-----+ -| | 0 | -+------+-----+ -1 row in set (0.00 sec)
Problem fixed and my blog is working again.. all is good with the world… time for a nice cuppa tea