The Ballard Locks are a block from where I live. 

Select Adjustments > Color Match…

For my first attempt, I loaded a Roy Lichtenstein. Yikes!

For attempt two, I used a painting by Thomas Kinkade. Much better!

The Ballard Locks mashed with Thomas Kinkade.

Thanks to Paint.NET and some cool third party plugins, I don’t miss PhotoShop at all.

My goal was to connect the reader with the page they believed the link was taking them to. So instead of returning a simple 404 Page Not Found “Tough Luck” screen, I wanted to connect the reader to the resource they requested. When I looked at the requests, my human eyes could usually figure out what page they had incorrectly linked to. Many of the broken link requests fell into three patterns.

1. Truncated. Often the link had a missing trailing slash or a few letters.
2. Extra Characters. The other common element was that the link was correct, but somehow extra letters got attached to the URL.
3. Mistyped Characters. Sometimes when a URL is hand copied, a zero becomes an “O” or a one becomes an “l”.

Here is an example of a valid link to INeedCoffee and 3 examples of how things could go wrong. http://www.ineedcoffee.com/07/aeropress/

1. Truncated: http://www.ineedcoffee.com/07/aeropres (trailing “s” and slash is missing from request)
2. Extra Characters: http://www.ineedcoffee.com/07/aeropress/ target= (when the url href doesn’t get closed properly, the link sent includes the characters after the link until the quote is closed)
3. Mistyped Characters: http://www.ineedcoffee.com/07/aer0press/ (in this example, an “o” is typed as a zero)

One Stored Procedure and One User Defined Function

The first thing I noticed was that it was rare for the garbage characters to appear between the base domain name (www.ineedcoffee.com) and the article URL (/07/aeropress/). My idea was to compile a full list of valid URLs on my site and then perform a character by character comparison. For every character that matches, I would score a point. The URL with the most points would likely be the correct URL.

The Stored Procedure

In the stored procedure, I create a temp table loaded with valid URLs. I don’t include the home page. For INeedCoffee this means a link to each article, each section and every contributor page.

IF OBJECT_ID('prcBadLinkSuggest') IS NOT NULL
DROP PROCEDURE prcBadLinkSuggest
GO
CREATE PROCEDURE prcBadLinkSuggest
    @errorURL VARCHAR(300)
AS
DECLARE @goodURL AS VARCHAR(200)
DECLARE @title AS VARCHAR(100)

SELECT TOP 1 G.url AS GoodURL, G.title
FROM (
SELECT url, title FROM Content
UNION
SELECT contributorURL AS URL, contributorName AS Title
FROM Contributors
UNION
SELECT sectionURL AS URL, sectionName AS Title
FROM Sections
) G
ORDER BY dbo.udfCompareURLs(G.url, @errorURL) DESC

The User Defined Function

In the stored procedure prcBadLinkSuggest, the UDF udfCompareURLs is being called in the ORDER BY clause. The errorURL and a valid URL are passed. The UDF returns a score based upon character matching. The valid URL with the highest score is returned from the stored procedure. Notice that the SELECT is returning just one row and the ORDER BY is sorting on the Score in descending order (high to low).

IF OBJECT_ID('udfCompareURLs') IS NOT NULL
    DROP FUNCTION udfCompareURLs
GO
CREATE FUNCTION udfCompareURLs(
    @goodURL    VARCHAR(200),
    @errorURL    VARCHAR(200)
    )
RETURNS INT
AS
BEGIN
    DECLARE @iCount AS INT
    DECLARE @score    AS INT
    DECLARE @eChar    AS CHAR
    DECLARE @gChar    AS CHAR
    DECLARE @urlLength    AS INT

    SET @iCount = 1
    SET @score = 0
    SET @urlLength = LEN(@errorURL)
    WHILE (@iCount < @urlLength)
    BEGIN
        SELECT @eChar = SUBSTRING(@errorURL,@iCount,1)
        SELECT @gChar = SUBSTRING(@goodURL,@iCount,1)
        IF @eChar = @gChar
            SET @score = @score + 1
        SET @iCount = @iCount + 1
    END
    RETURN @Score
END

The User Defined Function takes the length of the ErrorURL and then constructs a loop where it does a character by character comparison building a score of matches.

Last Words

I tested this code using actual 404 data and it was extremely accurate in guessing the correct web page. It isn’t bullet-proof as it would return a low score if the garbage appears early in the URL string, but that case is extremely rare.

This past Saturday I noticed some bizarre blog behavior. I was getting an email backup of this WordPress blog every minute for several days. Even after disabling the backup plugin, the email backups kept coming. So in a desperate attempt to do something, I upgraded to the latest version of WordPress.

Database upgrade required

After running the auto-install to the latest version, I got a screen telling saying “Database upgrade required”. When I pressed the button for that screen, I was told the Upgrade was complete. But it wasn’t. Clicking that button sent me back to the “Database upgrade required” screen. Endless loop.

Joe from ArtLung.com tipped me off to the post Solution for WordPress WP-Options Issue – Database Upgrade Required. Basically, the database version exists on both the install and as a field in the database and they need to be equal for things to run. The solution is to update the database field so they are equal.

UPDATE Fails on WordPress Database

Using HeidiSQL I executed an UPDATE command to fix everything.

UPDATE WP_Options SET option_value = ’19470′ WHERE option_name = ‘db_version’

No luck.

/* SQL Error (1142): UPDATE command denied to user ‘xxx’ for table ‘wp_options’ */

Now I was stumped, so I reached out to my host. They informed me my database was full.

Database Full

Turns out my WordPress database had filled up. It went from a few MBs to 50 MBs. How did that happen on less than 100 blog posts? Seems my Redirection plugin had created a log file of over 54,000 rows.  I attempted to delete the log file from the settings page, but it failed, so I did it from HeidiSQL. That dropped the database back down to under 3 MB. To prevent this from happening again, I found a setting for the plugin that truncates that log entries. I set it to 30 days.

All Is Well

Once the database had free space, UPDATE commands worked and the WordPress install went successfully. The endless backup problem was solved as well, once that plugin was able to update the database.

Hopefully, this post helps at least one other blogger.

Google did better job searching my site than my own code, so I looked at their Google Custom Search solution. I didn’t like their free ad version and I didn’t what to pay them $100 each and every year for the non-ad version. I decided that not only could I write my own search engine that was just as fast, but I could also deliver better results to the users. After all, I knew my content better than anyone else.

Assigning a Quality Score

The first thing I noticed about Google’s search results is that the best article on a given topic often wasn’t listed first. It had no way to know quality, but I did. So I added a quality score of 1 to 5 for every article. The default was 3. The best content was rated a 4 or 5. Articles that needed better photos or improved in some way, were given a 1 or 2. Later I’d also use this quality score when assigning weight on the sitemap.

Web Form -> Server Side Code -> Stored Procedure

The HTML search form is pretty basic. A single text box and a submit button. What server side code you use to call the stored procedure is irrelevant. ASP.NET, Classic ASP, PHP – it is all good. The server side code will call the search stored procedure.

Two Temp Tables

The search stored procedure will have two temp tables: #searchWords and #searchResults. The purpose of #searchWords is to chop up any search phrase into individual words and then record their position. Later that position will be used to order search results, which more weight being placed on the first and second word in a search query. The #searchResults table are the results being returned to the web page.

CREATE TABLE #searchWords (
    word      VARCHAR(100),
    position    INT
)
CREATE TABLE #searchResults (
    url        VARCHAR(100),
    title      VARCHAR(100),
    longDesc   VARCHAR(MAX),
    quality    TINYINT,
    score      INT
)

Splitting Search Phrases

For this functionality, I found some code on StackOverflow that did the job. The SplitWordList user-defined function by Terrapin works perfectly. If the user places the search term inside quotes, I do not call the SplitWordFunction and inside enter the entire phrase as one row in the #searchWords table.

INSERT INTO #searchWords SELECT word, position from SplitWordList(@searchString)

Count String Function

For the actual search, I used the Count String Occurrence Function. The search words are compared first against the article title and then the content itself.

CREATE FUNCTION [dbo].[udfCountString](
    @InputString    VARCHAR(MAX),
    @SearchString    VARCHAR(100)
)
RETURNS INT
BEGIN
    RETURN (LEN(@InputString) -
            LEN(REPLACE(@InputString, @SearchString, ''))) /
            LEN(@SearchString)
END

I Like Cursors

The most straight forward approach I could think of for getting search results was to use two cursors. One with the content and one with the search words. Then write the hits to the #searchResults temp table. But cursors are often frowned upon for poor performance. I decided I would first code the search engine using Cursors and then if I ran into a performance problem, I’d come up with an alternate solution. But I didn’t need to, as I got rocking fast results using CURSORS.

DECLARE ContentCursor CURSOR FAST_FORWARD FOR
SELECT url, title, longDesc, quality, page
FROM Articles 

DECLARE SearchWordCursor CURSOR DYNAMIC FOR
SELECT word, position FROM #searchWords
OPEN SearchWordCursor 

OPEN ContentCursor
FETCH NEXT FROM ContentCursor INTO @url, @title, @longDesc, @quality, @page

WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH FIRST FROM SearchWordCursor INTO @word, @position
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- place more weight on the first search term
        SELECT @score = CASE @position
            WHEN 1 THEN 3
            WHEN 2 THEN 2
            ELSE 1
        END
        -- search the TITLE 
        SET @count = dbo.udfCountString(@title, @word)
        IF @count > 0
        BEGIN
            INSERT INTO #searchResults VALUES (@url, @title, @longDesc, @quality, @score * 10)
        END
        -- search the PAGE
        SET @count = dbo.udfCountString(@page, @word)
        IF @count > 0
        BEGIN
            INSERT INTO #searchResults VALUES (@url, @title, @longDesc, @quality, @score)
        END                    

        FETCH NEXT FROM SearchWordCursor INTO @word, @position
    END
    FETCH NEXT FROM ContentCursor INTO @url, @title, @longDesc, @quality, @page
END

CLOSE ContentCursor
DEALLOCATE ContentCursor

CLOSE SearchWordCursor
DEALLOCATE SearchWordCursor

Working With the Results

Before dropping both temp tables, here is the query used to return the search results. If you look at the SQL above you will see that it is possible (likely) that a search hit will take place on both the title and the page content. I ran some tests and determined that a search hit against a word in the title was 10 times more important than the content, so I multiply the score time ten if there is a title match.

To flatten the results, I use a GROUP BY clause in the SQL. Then the results are returned order from highest to lowest scores.

SELECT TOP 20 S.url, S.title, S.longDesc, S.quality, SUM(S.score) AS Score
FROM #searchResults S
GROUP BY S.url, S.title, S.longDesc, S.quality
ORDER BY SUM(S.score) DESC, S.Quality DESC

Better Than Google?

I ran numerous tests comparing my search engine to Google. My hand-coded INeedCoffee search engine delivered better results at equal or faster speeds. And the best part is I don’t need to send Google a check for $100 every year.

There was one thing I wanted to address better recently and that was how I handled incoming broken links.  I used to just redirect every request to the home page, but I’ve since learned that 404 error codes are OK to have.  If the page is missing, returning a 404 code is appropriate.

When I studied the bad incoming links, I isolated about 15 where I could tell what article was intended in the link.  For these links, I wanted to give a 404 error and provide a suggestion on what page is most likely the correct link.  For this I used the Scripting.Dictionary object.  In it, I matched the bad link with the good link.

RequestedBadLink = Request.ServerVariables("QUERY_STRING")

'- Dictionary of known bad URL requests
Set BadLinks = Server.CreateObject("Scripting.Dictionary")
BadLinks.Add "404;http://example.com:80/bad-link","http://example.com/good-link"
'- add more links here
If BadLinks.Exists(RequestedBadLink) Then
    SuggestedURL = BadLinks.Item(RequestedBadLink)
Else
    SuggestedURL = ""
End If

For the bad links, I added “404;” to the front of the link and embedded “:80″ after the domain. This is the format that the QUERY_STRING ServerVariables uses.  Then on your 404 page, you can test to see if you found a Suggested URL and display it for the user.

1. Web Research – Everyone does this.  Find a host that meets all your requirements at the price point you are willing to pay.
2. Sunday or Late Night Email – Let us say that you have 2 or 3 possible web hosts in mind.  Wait until Sunday and then fire off an email to their support staff asking a question.  The question shouldn’t be too difficult as to require lots of research, but not easy enough to be answered by a sales person.
3. Wait For Responses – The winner of your business will be the web host that quickly and professionally responds to your off hour email question.

When I pay for web hosting I want to know that they have competent people working during non-business hours.  Some of these discount web hosts are able to offer rock bottom prices, because they have bare bones technical support.  They staff their rock stars during business hours, because that is when they are most likely to land a new customer.  Sending an email on Sunday, or late in the night, is perfect way to test a web host before handing over your credit card data.

After you have established a relationship with a web host, periodically send an off hour email.  Maybe once or twice a year.  Keep them on their toes.  Consider this test to be part of your website back up strategy.

This site uses WinHost, which I highly recommend for ASP.NET and SQL Server web hosting.  They passed the Sunday email test.

How did I miss the primary reason for using Instapaper?  It’s primary feature is not bookmarking, it is making text legible.  There was this trend in web design around 2003 where font sizes got really small.  I’m afraid it may be coming back and my eyes just can’t take it.  Thankfully with Instapaper, I no longer have to suffer through reading painfully small fonts or gray text on a while background.

Here is how it works.

1. Set up an account on Instapaper.
2. Add the Read Later bookmarklet to your browser, per their instructions.
3. Go to a page you wish to read that has a hideous web design with dreadful legibility.  For this example, I am going to use the outstanding article Vitamin D — Problems With the Latitude Hypothesis on the Weston A Price website.
4. Press your Read Later bookmarklet.
5. Go back to Instapaper, locate the article on your list and click the Text button.

Painful typography

Add it to Instapaper and then click the Text button.

Presto.  The article is now readable.

Instapaper doesn’t work for every website, but works for most.  My online reading speed and comprehension has never been greater.  I highly recommend Instapaper.

UPDATE: Seems Instapaper has a feature in their Extras section called Instapaper Text.  Drag that bookmarklet to your toolbar and click it whenever you need text cleaned up.  No account needed.  Use this if you don’t need the bookmarking features.   They also give a shout out to a similar solution called Readability.

At this point you can reach out to DreamHost’s support team.  They will tell you that it is the fault of WordPress plugins and then try and upsell you on a VPS account.  They promise not to kill your processes if you get a VPS account.  Kind of like a shop keeper that pays the gangster protection money so his store doesn’t burn down.  How sweet!  My personal opinion is why should I pay for an enhanced service if the basic service is awful? I’ll just switch web hosts.

Back to the question of resources.  Just how much memory does a website running WordPress with some basic plugins use?  As of this writing, I have 2 WordPress sites on DreamHost (not DigitalColony.com).  I installed 2 plugins to help me track my memory usage: WP-Memory-Usage and TPC! Memory Usage.  Below is a screen shot from one of my sites.  The other site shows similar numbers.

With these two plugins I learned a few things:

1. DreamHost limits your PHP memory to just 90 MB.
2. A basic install of WordPress takes about 30 MB of memory on a 64 bit installation of PHP.
3. I activated and deactivated every plugin.  Most used trivial amounts of memory.  No plugin exceeded 2 MB of memory.  Even the much aligned All-In-One SEO plugin used only 1.05 MB.
4. Switching themes had almost no impact on memory usage.
5. With as little as two domains using WordPress on DreamHost you are already reaching the upper limits of memory allocated. So much for unlimited domains.  Perhaps they should rephrase it to unlimited unused domains?
6. DreamHost has a serious LOAD AVERAGE problem.  The numbers in the above screen capture were the lowest I captures.  Often the Load Averages exceeded 10.

Even though I went looking for answers on memory usage, the load average numbers  jumped out at me.  What do they mean and what is a good number?

The article Understanding Linux CPU Load – when should you be worried? is a great tutorial on the topic.  It makes the case that the maximum load should not exceed the number of cores on the server.  My DreamHost server has 4 cores.   I monitored this number all day and it is always in the red zone. The CPU load on DreamHost servers is excessive.

My advice is to stay away from DreamHost.  Their servers are overloaded and if you plan to host more than one WordPress account you’ll experience problems.

UPDATE (Nov 25, 2010) – This morning the DreamHost Load times spiked much higher!

• Load Averages: 144.95 45.93 21.12

Here is the C# code I used to drop in an additional style sheet.

using System.Web.UI.WebControls;

/* add custom CSS */
HtmlLink rowCSS = new HtmlLink();
rowCSS.Href = "highlight.css";
rowCSS.Attributes.Add("rel", "stylesheet");
rowCSS.Attributes.Add("type", "text/css");
Header.Controls.Add(rowCSS);

Below is some code that sends a URL to be tested against the W3C Validator. It returns a true or false.  If you have a lot of URLs to test – like I did – just add some reporting code to flag the ones that don’t pass.

using System.Net;

public bool IsPageValid(string url)
{
    string validatorURL = "http://validator.w3.org/check?uri=";
    string checkURL = validatorURL + url;
    string validStatus = "Invalid";

    // create the request
    HttpWebRequest request = WebRequest.Create(checkURL) as HttpWebRequest;
    // instruct the server to return headers only
    request.Method = "HEAD";
    // make the connection
    HttpWebResponse response = (HttpWebResponse)request.GetResponse();

    WebHeaderCollection headers = response.Headers;

    foreach (string key in headers.Keys)
    {
        if (key == "X-W3C-Validator-Status")
            validStatus = headers[key];
    }

    if (validStatus == "Valid")
    {
        return true;
    }
    else
    {
        return false;
    }
}

Lab Validate DOCTYPE in ASP.NET