As I related in the previous post, I had been amiss at keeping my sites clean of spam. One of the clever spammers decided to abuse the blog option on one of my sites. Hundreds of entries had been made, so I decided to delete the user account. Drupal has an option to delete everything that user created when you delete the account. I chose that. My mistake. 

My site ended up showing a perpetual list of entries that just had n/a in them. No links, no way of deleting them. Sure, I tried to delete the node ID from the content option, but they wouldn't show up. When I tried to jump to the node ID directly I was told I didn't have rights to view the page. Fun.

The search in Drupal leaves alot to be desired. Frankly, it stinks. So, when you try to search the main Drupal site for support you can't find anything due to the fact it strips out the / in n/a.

You can search the Drupal site using Google's site:drupal.org command, so I was able to find the right answer. Now, this answer scared me, because I am not a MySQL expert. I gingerly ran through the process and it worked. I wanted to document it in case someone else comes across the problem, the answer and wants to see what it's like to fix it. 

The error is caused when you delete a user and their contributions to the site. I have no idea if it's fixed in the latest version of Drupal, but it seems like a huge error in the code.

You first need to open phpMyAdmin and select your Drupal database. You'll see a screen like the following. Now, your screen may look different depending on what theme your hosting company selects for you:

Once you have selected your database you need to go in and select the SQL command button and enter the following command:

SELECT node.* FROM node
LEFT JOIN users ON node.uid=users.uid
WHERE users.uid IS NULL;

This command tells your database to delete all the nodes where the user information is null, or nothing. This occurs when Drupal deletes the user account from the database. Here is a screenshot of the command. Hit GO after you cut and paste it. 

It may take a couple of minutes for this command to work, so don't panic. The more nodes you have in your Drupal databse the longer it's going to take. When it comes back you'll see the following. You want to select all the results and hit the red X for delete.

OK, you're finished. When everything goes according to plan you'll see the following. 

 Open your site and make sure to clear your browser's cache. You should now see the N/A errors are gone.

Bookmark/Search this post with:
delicious | digg | reddit | furl | google | yahoo | technorati | icerocket