Useful MySql statements

Grant privileges to a certain user
GRANT ALL PRIVILEGES ON *.* TO someone@localhost IDENTIFIED BY ‘theirpassword’ WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO someone@”%” IDENTIFIED BY ‘theirpassword’ WITH GRANT OPTION;

The second statement is for granting privileges to the account when not on the local server (not localhost). Both statements will give all privileges on all tables and databases.

Grant only trigger privilege
GRANT Trigger ON *.* TO someone@localhost IDENTIFIED BY ‘theirpassword’ WITH GRANT OPTION;

Show all triggers
SHOW TRIGGERS;

Create a trigger
CREATE TRIGGER MyTrigger BEFORE INSERT ON employee
FOR EACH ROW
BEGIN
IF NEW.JobTitle = ‘Manager’ THEN
SET NEW.Wage = 30000;
END IF;
END;

The NEW keyword above references the row which is going to be inserted. Therefore just before the insert is done, if the JobTitle was ‘Manager’, the Wage column would be updated to 30000 before being committed to the database.

Script to display pages or posts for GetSimple CMS in sidebar

I was trying out the GetSimple CMS and although it looks pretty good, I was gutted that it didn’t display the pages/posts that you write. Usually the latest posts that you write appears in the sidebar but with GetSimple, you have to add the links manually. There’s the News Manager (or Extended News Manager) plugin that you can get but I couldn’t get it to work because of lack of documentation for the installation process.

Anyway I decided to look at the code and I managed to do a quick hack to display the pages in the sidebar of GetSimple. The code is as follows:


<?php
//display all pages except index page and private pages
$path 		= GSDATAPAGESPATH;
$filenames = getFiles($path);

$count="0";
$pagesArray = array();
$heading = "<h2>Topics</h2>";
$pages_list;
if (count($filenames) != 0)
{
	foreach ($filenames as $file)
	{
		if (isFile($file, $path, 'xml'))
		{
			$data = getXML($path .$file);
			$status = $data->menuStatus;
			$pagesArray[$count]['title'] = html_entity_decode($data->title, ENT_QUOTES, 'UTF-8');
			$pagesArray[$count]['parent'] = $data->parent;
			$pagesArray[$count]['menuStatus'] = $data->menuStatus;
			$pagesArray[$count]['private'] = $data->private;
			if ($data->parent != '')
			{
				$parentdata = getXML($path . $data->parent .'.xml');
				$parentTitle = $parentdata->title;
				$pagesArray[$count]['sort'] = $parentTitle .' '. $data->title;
			}
			else
			{
				$pagesArray[$count]['sort'] = $data->title;
			}
			$pagesArray[$count]['url'] = $data->url;
			$pagesArray[$count]['date'] = $data->pubDate;
			$parentTitle = '';

			// do not show the index page as well as private pages
			if ($pagesArray[$count]['url'] != 'index' && $pagesArray[$count]['private'] != 'Y')
			{
				$pages_list .= "<li><a href=\"/" . $pagesArray[$count]['url'] . "/\">" . $pagesArray[$count]['title'] . "</a></li>";
				$count++;
			}
		}
	}

	if ($count > 0)
		$pages_list = $heading . "<ul>" . $pages_list . "</ul>";

	echo $pages_list;
}
?>

You will need to paste the code where you want the list of pages to appear. I placed mine directly in the sidebar by going to “Edit Theme”. I don’t have time to make this into a GetSimple plugin but feel free to use it as you wish. The script will list all your pages except the index (homepage) and private pages. You can customise the heading and you can also easily list only 10 pages instead of everything by stopping the loop.

System.Data.InvalidConstraintException: Parent Columns and Child Columns don’t have type-matching columns

If you encounter the error System.Data.InvalidConstraintException: Parent Columns and Child Columns don’t have type-matching columns, then the columns you’re trying to link do not have the same data type. In my case, I was trying to set a relationship between an “articles” table with a “categories” table. The “articles” table has a cat_id field as a foreign key and the “categories” table had its cat_id field as the primary key.

However the cat_id column in the “categories” table was defined as a tinyint(4) while the cat_id in “articles” was an int(11) data type. I changed cat_id in “articles” to tinyint(4) and the problem disappeared.

Script to transfer custom CMS into WordPress

This is a script I wrote to import my blog posts from a custom CMS into WordPress. Some things have been hard coded because I needed to do this quickly but you can easily change the script to adapt to your needs. A couple of things worth noting:

  1. You need to be using Windows and have IIS server
  2. I was importing from a MySql database and I have the MySql .NET connector installed on my machine
  3. This is a quick and dirty script and it grabs only the posts and comments (no images etc)

Here’s the wordpress importer script (it’s an asp.net http handler):

<%@ WebHandler Language="C#" %>
using System;
using System.Web;
using System.IO;
using System.Xml;
using System.Collections.Generic;
using MySql.Data.MySqlClient;
public class GisToWP : IHttpHandler {
public void ProcessRequest (HttpContext context)
{
string website = "http://www.example.co.uk/";
string username = "MrCoder";
string title = "Blog Title";
string desc = "Another wordpress blog";
// dataset
System.Data.DataSet db = new System.Data.DataSet();
string conString = "Database=database;Data Source=example.com;User Id=username;Password=password;";
// get the articles/blogposts
using (MySqlConnection con = new MySqlConnection(conString))
{
con.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "select * from articles where article_status = 1"; // change this
cmd.Connection = con;
MySqlDataAdapter dr = new MySqlDataAdapter();
dr.SelectCommand = cmd;
dr.Fill(db, "articles");
con.Close();
}
// get the comments
using (MySqlConnection con = new MySqlConnection(conString))
{
con.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "select * from tblComments where StatusID = 1"; // change this
cmd.Connection = con;
MySqlDataAdapter dr = new MySqlDataAdapter();
dr.SelectCommand = cmd;
dr.Fill(db, "comments");
con.Close();
}
// get the users
using (MySqlConnection con = new MySqlConnection(conString))
{
con.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "select * from tblUsers"; // change this
cmd.Connection = con;
MySqlDataAdapter dr = new MySqlDataAdapter();
dr.SelectCommand = cmd;
dr.Fill(db, "users");
con.Close();
}
// get the categories
using (MySqlConnection con = new MySqlConnection(conString))
{
con.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "select * from article_categories where status = 1"; // change this
cmd.Connection = con;
MySqlDataAdapter dr = new MySqlDataAdapter();
dr.SelectCommand = cmd;
dr.Fill(db, "categories");
con.Close();
}
// link articles to comments
db.Relations.Add("article_comment", db.Tables["articles"].Columns["article_id"], db.Tables["comments"].Columns["ArticleID"], false);
// link articles to categories
db.Relations.Add("article_category", db.Tables["articles"].Columns["cat_id"], db.Tables["categories"].Columns["cat_id"], false);
// link comments to users
db.Relations.Add("comment_user", db.Tables["comments"].Columns["UserID"], db.Tables["users"].Columns["UserID"], false);
// set primary keys
db.Tables["articles"].PrimaryKey = new System.Data.DataColumn[1] { db.Tables["articles"].Columns["article_id"] };
db.Tables["comments"].PrimaryKey = new System.Data.DataColumn[1] { db.Tables["comments"].Columns["CommentID"] };
db.Tables["users"].PrimaryKey = new System.Data.DataColumn[1] { db.Tables["users"].Columns["UserID"] };
db.Tables["categories"].PrimaryKey = new System.Data.DataColumn[1] { db.Tables["categories"].Columns["cat_id"] };
// write the xml file
using (MemoryStream ms = new MemoryStream()) // use memory stream
{
using (TextWriter textWriter = new StreamWriter(ms, System.Text.Encoding.UTF8))
{
XmlTextWriter writer = new XmlTextWriter(textWriter);
writer.Formatting = Formatting.Indented;
writer.WriteStartDocument();
writer.WriteStartElement("rss");
writer.WriteAttributeString("version", "2.0");
writer.WriteAttributeString("xmlns:excerpt", "http://wordpress.org/export/1.0/excerpt/");
writer.WriteAttributeString("xmlns:content", "http://purl.org/rss/1.0/modules/content/");
writer.WriteAttributeString("xmlns:wfw", "http://wellformedweb.org/CommentAPI/");
writer.WriteAttributeString("xmlns:dc", "http://purl.org/dc/elements/1.1/");
writer.WriteAttributeString("xmlns:wp", "http://wordpress.org/export/1.0/");
writer.WriteStartElement("channel");
writer.WriteElementString("title", title);
writer.WriteElementString("link", website);
writer.WriteElementString("description", desc);
writer.WriteElementString("pubDate", DateTime.Now.ToString("ddd, dd MMM yyyy hh:mm:ss +0000"));
writer.WriteElementString("generator", "http://wordpress.org/?v=2.9.2");
writer.WriteElementString("language", "en");
writer.WriteElementString("wp:wxr_version", "1.0");
writer.WriteElementString("wp:base_site_url", website);
writer.WriteElementString("wp:base_blog_url", website);
// categories
foreach (System.Data.DataRow cat in db.Tables["categories"].Rows)
{
writer.WriteStartElement("wp:category");
writer.WriteElementString("wp:category_nicename", cat["cat_name"].ToString());
writer.WriteElementString("wp:category_parent", String.Empty);
writer.WriteStartElement("wp:cat_name");
writer.WriteCData(cat["cat_real_name"].ToString());
writer.WriteEndElement(); // wp:cat_name
writer.WriteEndElement(); // wp:category
}
writer.WriteStartElement("xhtml:meta");
writer.WriteAttributeString("xmlns:xhtml", "http://www.w3.org/1999/xhtml");
writer.WriteAttributeString("name", "robots");
writer.WriteAttributeString("content", "index");
writer.WriteEndElement();
// loop through all articles
foreach (System.Data.DataRow article in db.Tables["articles"].Rows)
{
writer.WriteStartElement("item");
// category name
string categorySlug = db.Tables["categories"].Rows.Find(article["cat_id"])["cat_name"].ToString();
string categoryName = db.Tables["categories"].Rows.Find(article["cat_id"])["cat_real_name"].ToString();
writer.WriteElementString("title", article["article_title"].ToString());
writer.WriteElementString("link", String.Format("{0}{1}/{2}/", website, categorySlug, article["article_permalink"].ToString()));
writer.WriteElementString("pubDate", ((DateTime)article["post_datetime"]).ToString("ddd, dd MMM yyyy hh:mm:ss +0000"));
writer.WriteStartElement("dc:creator");
writer.WriteCData(username);
writer.WriteEndElement();
writer.WriteStartElement("category");
writer.WriteCData(categorySlug);
writer.WriteEndElement();
writer.WriteStartElement("category");
writer.WriteAttributeString("domain", "category");
writer.WriteAttributeString("nicename", categoryName);
writer.WriteCData(categoryName);
writer.WriteEndElement(); // category
writer.WriteStartElement("guid");
writer.WriteAttributeString("isPermalink", "false");
writer.WriteString(String.Format("{0}?p={1}", website, article["article_id"].ToString()));
writer.WriteEndElement(); // guid
writer.WriteElementString("description", String.Empty);
writer.WriteStartElement("content:encoded");
writer.WriteCData(FormatContent(article["article_content"].ToString()));
writer.WriteEndElement();
writer.WriteStartElement("excerpt:encoded");
writer.WriteCData(String.Empty);
writer.WriteEndElement();
writer.WriteElementString("wp:post_id", article["article_id"].ToString());
writer.WriteElementString("wp:post_date", ((DateTime)article["article_datetime"]).ToString("yyyy-MM-dd hh:mm:ss"));
writer.WriteElementString("wp:post_date_gmt", ((DateTime)article["article_datetime"]).AddHours(6).ToString("yyyy-MM-dd hh:mm:ss"));
writer.WriteElementString("wp:comment_status", "closed");
writer.WriteElementString("wp:ping_status", "closed");
writer.WriteElementString("wp:post_name", article["article_permalink"].ToString());
writer.WriteElementString("wp:status", "publish");
writer.WriteElementString("wp:post_parent", "0");
writer.WriteElementString("wp:menu_order", "0");
writer.WriteElementString("wp:post_type", "post");
writer.WriteElementString("wp:post_password", String.Empty);
writer.WriteElementString("wp:is_sticky", "0");
// meta tags
writer.WriteStartElement("wp:postmeta");
writer.WriteElementString("wp:meta_key", "title");
writer.WriteElementString("wp:meta_value", article["article_page_title"].ToString());
writer.WriteEndElement();
writer.WriteStartElement("wp:postmeta");
writer.WriteElementString("wp:meta_key", "description");
writer.WriteElementString("wp:meta_value", article["article_meta_desc"].ToString());
writer.WriteEndElement();
// comments for this article
System.Data.DataRow[] theComments = article.GetChildRows("article_comment");
foreach (System.Data.DataRow comment in theComments)
{
writer.WriteStartElement("wp:comment");
string commenterName = db.Tables["users"].Rows.Find(comment["UserID"])["Fullname"].ToString();
string commenterEmail = db.Tables["users"].Rows.Find(comment["UserID"])["EmailAddress"].ToString();
string commenterIP = db.Tables["users"].Rows.Find(comment["UserID"])["IPAddress"].ToString();
writer.WriteElementString("wp:comment_id", comment["CommentID"].ToString());
writer.WriteElementString("wp:comment_author", commenterName);
writer.WriteElementString("wp:comment_author_email", commenterEmail);
writer.WriteElementString("wp:comment_author_url", String.Empty);
writer.WriteElementString("wp:comment_author_IP", commenterIP);
writer.WriteElementString("wp:comment_date", ((DateTime)comment["CreateDate"]).ToString("yyyy-MM-dd hh:mm:ss"));
writer.WriteElementString("wp:comment_date_gmt", ((DateTime)comment["CreateDate"]).AddHours(6).ToString("yyyy-MM-dd hh:mm:ss"));
writer.WriteStartElement("wp:comment_content");
writer.WriteCData(FormatContent(comment["Content"].ToString()));
writer.WriteEndElement();
writer.WriteElementString("wp:comment_approved", "1");
writer.WriteElementString("wp:comment_type", String.Empty);
writer.WriteElementString("wp:comment_parent", "0");
writer.WriteElementString("wp:comment_user_id", "0");
writer.WriteEndElement(); // wp:comment
}
writer.WriteEndElement(); // item
}
writer.WriteEndElement(); // channel
writer.WriteEndElement(); // rss
// To be safe, flush the document to the memory stream.
writer.Flush();
// Convert the memory stream to an array of bytes.
byte[] byteArray = ms.ToArray();
// Send the XML file to the web browser for download.
context.Response.Clear();
context.Response.AppendHeader("Content-Disposition", "filename=wordpress.xml");
context.Response.AppendHeader("Content-Length", byteArray.Length.ToString());
context.Response.ContentType = "text/xml";
context.Response.BinaryWrite(byteArray);
}
}
}
public string FormatContent(string text)
{
return text.Replace("<p>", "").Replace("</p>", "");
}
public bool IsReusable {
get {
return false;
}
}
}

How to use the wordpress importer script?

You will need to change the sql statements where I’ve put the comments (change this) to select your posts, comments, users and categories from your database. When you run the script, it will prompt you to download an xml file (or show you an output in your browser which you can “View Source” and save to your PC. This will be your wordpress WRX xml file which you will use to import your blog into wordpress.

For wordpress 3.2, you will have to go to Tools -> Import and then select WordPress from the list. It will then ask you to install the WordPress Importer plugin and activate it. Once this is done, you can then upload this WRX file and your blogposts will be imported in your new blog.

WordPress blog posts being truncated during import

If you happen to see truncated data in your posts, it might be caused by character encoding problems. To fix mine, I opened the WRX file in Notepad++ and went to Encoding -> Encode in UTF-8 and it showed me invalid characters. Instead of replacing the bad characters, I ran the script again, copy the data from view source, pasted in Notepad++, selected the encoding to be UTF-8 before saving the WRX file. When I imported the xml file this time, no blog posts were truncated.

How to test your website on a new server before changing nameservers

Sometimes moving to a new server is a must for a number of reasons:

  1. You need more resources because your traffic is growing bigger and your current hosting package cannot cope with the demand
  2. Your current hosting provider is not reliable, so you want to move your website to a new webhost
  3. You have changed your codebase, so you need another OS (eg if you had a site in php but have converted it to .net, you will have to move from a linux webserver to a windows one)

Once everything has been tested locally (on the development machine), you want to make sure that it’s working properly on the remote machine as well, that is, on the new webhost. What you do is to configure the DNS on the webhost for the website. Let’s call the website www.example.com and deal with a linux server where you’ll get CPanel. When you sign up with the webhost, they normally ask you whether you want to host an existing website or a new one. If you chose an existing one, the DNS will already be configured for you. If you wanted to have another domain on the same account, you can use addon domain functionality in CPanel to do that. Plesk on Windows servers will allow you to create the dns zone for a new website as well.

Once you’ve added the DNS, you can edit your hosts file if you’re using Windows OS. It’s located at C:\Windows\System32\Drivers\etc and you’ll probably have to open it in Notepad with administrator’s priviledge to be able to make changes to it. All you have to do it add an entry as follows:

100.101.102.103 www.example.com

Replace 100.101.102.103 with the IP address of your new server. You will then need to clear the cache of your browser. When you try to browse to www.example.com then, you will see your website on your new server (new webhost). If there are any problems, you can fix it there and then. This allows you to properly test your website on a new webhost before actually changing your nameservers.

Once you are satisfied that the website will be working properly on the new webhost, you can then change your nameservers with your domain registrar so that your live website is pointed to the new web host. Changing the nameservers is like saying “hey, I’m not on the old server anymore, I’ve changed address, and this is my new address”.

Note that testing the website the above way is not 100% accurate because there are certain things which require the right nameservers changes before they work properly. An example is wordpress cron jobs (wp-cron.php) which did not work using this method.

How to import your blog into wordpress

If you already have an existing blog on the well known sites like blogger, livejournal, typepad etc, you will easily be able to import your blog into wordpress. However if like me, you have a custom Content Management System (CMS) but need getting the data into wordpress, then things get a bit tricky.

Here’s the WordPress Importer for custom CMS or blog

Initially when I created my own CMS, I did that because I wanted more control over things. I wanted to do things my own way and then there’s the sense of achievement that you’re running your own blog and you’re not like everyone else using wordpress or another famous blogging platform. However php is not really my cup of tea. Yes I can code in php but I don’t really like it. I prefer C# and after converting my CMS to .NET, I decided I no longer wanted to maintain the codebase for the CMS coded in Php. I chose wordpress as my blogging platform because I’m more familiar with it and there’s tons of plugins nowadays to do just about anything and there’s loads of themes to choose from (and they’re free!!!) and the platform is well maintained and secure. I couldn’t convert them to .NET because I’d need Windows server to run them on and let’s face it, Linux is much cheaper and more available than Windows.

So the time comes for me to convert my existing CMS to wordpress and because it my a tailor-made CMS, I have to do a manual conversion. There are 2 options for that:

Use a database script

When wordpress has been installed, the database and tables would have been created. You can look at the table definition and insert records for the posts and comments from your old tables. The database script would copy the records for the posts and comments to a temporary (holding) table and then insert the data into wordpress tables.

I decided not to go down this path because I would need to properly analyse the tables in wordpress before I was sure I was putting data into the correct fields and not breaking any constraints or relationships.

Use the WordPress eXtended RSS file format (WXR)

This is similar to an RSS file which is an xml document with the posts and comments. All you have to do is create this xml file and go to wordpress and from Tools->Import, you will get the option to upload the WXR xml file and all your posts and comments will be copied over to your new wordpress blog.

This was a better way for me to convert my old blog to wordpress. I made an ASP.NET Http Handler create the xml file for me to use on the new blog. You will have to understand what elements are required in the WordPress file format for a successful import though. I was worried about something though – I have a page title and a page subject as well as a description field for any given webpage. I wanted to preserve these when moving over to wordpress and since the subject line and title did not match, it can have drastic effect in terms of SEO. However when the import was done, I had to enable a plugin (Platinum SEO pack) and the titles and subject were displayed correctly. Note that the wordpress importer is a plugin which needs to be installed before you can upload your WXR file to import your old blog.

For those interested, I’ll be posting the code for generating the wordpress xml file (WXR format). Code already posted above!

Give write permission to Worker Process on IIS 7

When you create a website in IIS 7/7.5 on Windows 2008, an application pool is automatically created for it to isolate this website from others running on the same server. You should check that the application pool identity is not Network Service but ApplicationPoolIdentity instead. Now if you your web app needs to write to disk, you will need to give this worker process write permission to the directory/file that needs to be written to. Here are the steps to do that:

  1. Right click on the directory/file that you want the permission applied to
  2. Select “Properties”, then “Security”, followed by “Edit” and then “Add”
  3. Enter “IIS AppPool\DefaultAppPool” in the “Enter the object names to select:” text box (replace DefaultAppPool with the application pool identity for your website)
  4. Click on “Check Names”, then “OK”
  5. Next tick the checkboxes necessary for write permission and any other special permissions

That’s it.

NOTE: Only an identifier (SID) is created when an app pool is created which is added to the IIS AppPool group. This identity is not a real account and will not show in the Windows User Management Console.

Setting custom errors in asp.net

If you want more control over what is rendered when there’s a problem with your website, then you will need to set custom errors for any problems you foresee. Two common examples of this are “Page not found aka 404 error” and “Internal server error aka 500 errror”. The 404 err0r happens when a page that doesn’t exist is being requested while the 500 error happens when an error has not been properly trapped in your web application logic.

If you do not set custom error pages, default error pages for those errors will be displayed. There are not very nice looking albeit they convey the message to the more technical person and not the average user. So with custom error pages, you can have your own design which can be in line with your current theme or have something more sophisticated to show to the user and tell the latter what the problem is in very simple terms. However setting these custom error pages can be a problem if you do not understand how they are called.

There are 2 ways to achieve this. The first one relies on you trapping the error in your code and calling your error page. If for example, someone calls a page with an invalid PostId (a post id which does not exist in your database), you can fire off your own custom 404 page. Just remember that you need to do the following before setting the http response code to 404:

Response.TrySkipIisCustomErrors = true;

The second way to have custom error pages in asp.net is to set them in the web.config file. There’s a section under system.web for customErrors and here’s how you can use it:


<customErrors mode="On" redirectMode="ResponseRewrite" defaultRedirect="~/PageError.aspx">
 <error statusCode="404" redirect="~/PageNotFound.aspx"/>
 <error statusCode="500" redirect="~/PageError.aspx"/>
 </customErrors>

It is important to have a defaultRedirect set, otherwise IIS default error page will be shown for other statusCode that you have not defined.

You will also need to edit your web.config file and add the following to the system.webserver section:

<httpErrors errorMode="Custom">
 <remove statusCode="500" subStatusCode="-1"/>
 <error statusCode="500" path="/PageError.aspx" responseMode="ExecuteURL"/>
 <remove statusCode="404" subStatusCode="-1"/>
 <error statusCode="404" path="/PageNotFound.aspx" responseMode="ExecuteURL"/>
 </httpErrors>


customErrors vs httpErrors – What’ the difference?

For all requests which are served by asp.net, customErrors will be called. Any requests which are handled by IIS will be transferred to httpErrors. You can try it for yourself. If you have a folder which does not have a default document (eg default.aspx), then there will be an error. If you don’t have your httpErrors section defined, you won’t see your custom error page but the default IIS error page.

Remember to have the line Response.TrySkipIisCustomErrors = true in your error pages, otherwise IIS default documents might kick in.

Note that I’ve tested this on IIS7.5 and .net framework 4.0.

Working with subdomain on localhost

If you need to test sub domains on your development machine before deploying your web application, then you might be wondering how this can be done. Fortunately there’s an easy way to do this and all you have to do is add an entry in the hosts file for the subdomain.

Location of the hosts file : C:\Windows\System32\drivers\etc

Note that the hosts file does not have an extension and resides in the same location for Windows 7, Vista or XP.

You will need to open this hosts file in Notepad as an administrator, otherwise you wouldn’t be able to edit the file. Since all IP address starting with 127 belongs to localhost, you can therefore add a second entry in the hosts file for the subdomain that you want. An example is shown below:

127.0.0.1    localhost.com
127.0.0.2    test.localhost.com

Once the changes are saved, you will be able to browse to test.localhost.com. If you want to setup a website in IIS for that sub domain, then you will need to edit the bindings so that it listens to 127.0.0.2 on port 80.

How to do maintenance work on a live website?

When you first launch a website, you’re not too concerned whether the site is down or having problems because initially you have few or no visitors at all. So you’ll not be upsetting anyone if the website is down but that is a huge problem is your website is established and you need to perform updates or maintenance work to your site. People might be accessing your site and they could be put off if it does not respond properly or it shows errors.

Steps to follow to update a live website

  1. The first thing that you need to do is point the live website at a different directory (web app).
  2. Then you need to ftp your new files to the directory of your live site
  3. And lastly you’ll need to point the live site back to the original directory

This sounds really easy but there are things to take into consideration. While your site is down, your first priority is to inform your visitors that you’re performing maintenance work on the site. You could put up a page with a nice little message for that. What I’ve done is create a web app specifically for that purpose. It has an HttpModule that intercepts all incoming requests and redirects to a maintenance page. So no matter which webpage of my website people are trying to view during the maintenance period, they will be shown the maintenance page.

Now that’s all good but you need to inform search engine crawlers as well, otherwise they might think that your page does not exist or have been modified and that could result in either your pages being deindexed or dropped in rankings. To ensure that bots know your maintaining your website, you’ll need to respond with a 503 http status code. This informs them that the downtime is temporary and your site will be back up soon. In my case, this is handled in my httpmodule which issues the 503 status code.

Prevent IIS from showing a default error page

My maintenance web app was tested on my Windows 7 laptop and everything worked fine here but when I uploaded the site content to my server, my custom maintenance page did not show up and instead I was presented with an ugly default error page. The http status code was 503 (which is correct) but the page itself came from IIS and was not the one I designed. To solve this problem, I had to add Response.TrySkipIisCustomErrors = true in my httpmodule to prevent IIS from intercepting the request (http://www.west-wind.com/weblog/posts/745738.aspx). Here’s the code:

private void Application_BeginRequest(Object source, EventArgs e)
{
HttpApplication application = (HttpApplication)source;
HttpContext context = application.Context;

context.Response.TrySkipIisCustomErrors = true;
context.Response.Status = "503 Service Unavailable";
context.Response.StatusCode = 503;
context.Server.Transfer("~/Maintenance.aspx", false);
}

The use of the app_offline.htm

When I first heard of app_offline.htm, I was really excited because it was such a painless way to go into maintenance mode. You just add this file in your website contents and bang, you are in maintenance mode. All calls are intercepted and the http status code becomes 503. You can put a nice little message in that html file for your visitors and design it beautifully. There are a couple of problems with this approach though:

  • First, your web app needs to be in a stable state. If your modifying dll in your bin or web.config, that’s all going to render your live website into an unstable application and app_offline.htm will be pretty much useless and your visitors or search crawlers will not be happy. I had this problem as my site is precompiled and changing one file means that the whole app needs to be rechanged. So in the end, I had to write the http module to overcome this problem.
  • Second, you cannot insert images in the app_offline.htm because relative paths will not work. The workaround is to have the image hosted on another server (maybe a free one) and have it embedded in the html (http://pbodev.wordpress.com/2009/12/20/app_offline-htm-with-an-image-yes-we-can/).
  • Third, you might need to have enough content in the html file (it needs to be a certain size) for it to display properly across all browsers. So if you have just a one line sentence, some people may be presented with an ugly default error document.

Maintenance work on a website is inevitable but as long as you have a process in place, everything should work out fine. Just remember to maintain your website when you have the least number of visitors.