The Open Source U

I had to move one of my databases from the main OS drive to a different partition. Being that I’ve not administered my own mysql database before — nothing beyond SQL level administration — I needed to find some sort of guide. I found the following and was quite pleased; I thought that other might be able to use this.

How to move MySql database to another drive or partition.

While this post is not strictly about the internal workings of an open source software package, it does have to do with the administration of notable open source software and hence, worth this blog’s posting.

The one thing to note is that the instructions seem out of date. The instructions were written using Ubuntu Server 7.10 (Gutsy Gibbon) as a basis. The instructions worked for me except for the apparmor note below. I’m currently using Ubuntu Server 8.04 LTS.

You many need to modify apparmor’s configuration file and then restart the service.  You can see the conversation in the comments (on the article) about it, but the brief version of it is:

1. Complete the main instructions first (Understand the entire article before starting.)
   
2. Open nano /etc/apparmor.d/usr.sbin.mysqld
3. Modify entries pointing to the old location to point to then new location. This will look like: /var/www/mysql_datadir/ r, /var/www/mysql_datadir/** rwk,
4. Restart apparmor which can be done via: /etc/init.d/apparmor restart

Again, the full article is: How to move MySql database to another drive or partition.

http://www.ubuntu-howto.info/howto/how-to-move-mysql-databases-to-another-location-partition-or-hard-drive

It’s no secret that this site is ran on top of Apache and MySQL. In fact, since I’ve previously advertised that this is a WordPress site, that is really clear.

Given my recent server move, I’ve been reconfiguring certain things. In fact, I have control that I have never had before, and truthfully I’ve learned things I’ve previously masked myself from. Despite possible difficulties and risks, I shouldn’t have done that to myself; I’d recommend to anyone to take responsibility of setting up their own server. It’s wroth while learning.

That said, I ran in to a small issue where the entire site wasn’t always rendering. While I’ve never completely isolated the problem, I did recall coming across the following on ServerFault:

When Apache and MySQL are installed their default configs assume they are working with more RAM available to them which can cause problems.

Along with that, the author posted starting guidelines which one can use to tweak their own install. While I’m not a Sys-Admin expert, I suspect this would be a good starting guideline. I don’t want to republish other peoples work, and hence the links are below:

• Answer by tasaro, question: How much VPS ram would I need to run WordPress, Apache, SVN & MySQL?
• Diagnosing and Fixing Memory Issues at Linode’s Library

The following article covers a great should-know piece of information for any SQL Server developer. Though, comma delimited strings are not usually desired in database, it can be nice to receive data this way for things like embedding in to a query string or passing over a web service*.

http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string

*I don’t necessarily suggest the web service idea. I was just saying for illustrative purposes…

A couple of great blog articles on sp_msForEachTable. This is an undocumented stored procedure.

http://www.shanedevane.net/msforeachtable
http://blog.sqlauthority.com/2007/01/31/sql-server-reindexing-database-tables-and-update-statistics-on-tables/

http://stackoverflow.com/questions/977400/undocumented-system-procedure-spmsforeachtable-and-the-whereand-parameter

I was working on a SQL Server project and came across a piece of code that I wanted to only execute if the code was running in production. This is a fairly common problem — code that should only execute if on a specific environment.

Most of the time in my career, I’ve seen people just comment out the code in a given environment. [And yes, I've seen bad mistakes with this method.] While this has obvious potential side effects I didn’t have time to give it much thought… Until recently.

I realized that SQL Server 2000 has a system variable called @@ServerName that will always give you the server name for the active connection.

Using this system variable, you can write some very simple code that will only execute other code in a given environment.

How can you use the variable?

Usage of the system variable (and any system variable) is easy. You can use either Select, Print or any logical statements (such as If or While loops).

All of the following are validate:

How to Implement It

How I implemented it was a bit unique; I will first start with a very simple example.

To quickly walk though this code, it is executeing a stored procedure call ValidateData and then we check the @@ServerName variable to find if it matches “ProductionSrvr”. If @@ServerName does, it will execute the update; otherwise the code would go right over the if. This would be great if you didn’t want to update the Users table in the staging or development environments but you did in production.

My twist on implementation

My primary employer does a thing where they lease their servers (and desktop stations) from a provider. The leases expire every 4 years. This means that every four years we migrate databases and web servers to new machines (or virtual machines, as of recent). To my knowledge, we have hundreds of Servers, serving all sorts of software and information (Web sites, databases, Cubes, etc)

Since our SQL Servers are no exception, I wanted to be prepared for such a switch but I still wanted to implement this because of it’s obvious benefits.

I obviously didn’t want to do a find and replace on all of my Stored Procedures and DTS packages (don’t ask) and so I used a function to check the @@ServerName variable against. The function was simple and an example definition follows:

My @@ServerName example above would simply become:

That’s it! I hope others will find this useful.