Todd Huss

Let’s say you have an Employees table and you want a column to track the status of an employee such as ['employed', 'resigned', 'retired', 'terminated', etc...]. You have a couple of options including:

1. Using your database’s custom enumeration data-type if it has one
2. Creating an int or char column and let the mapping live in the code
3. Creating a lookup table

(more…)

As I’ve described before, MySQL has some appalling out of the box settings which will thwart your attempts at good data integrity! They’ve clearly seen the light though and at least give you an option to achieve good data integrity through foreign keys in InnoDB and proper handling of non-nullable fields and invalid values through the servers sql-mode setting.

To make matters worse it seems MySQL may have different out of the box settings per platform and per version. For example a friends 5.0.21 install on Windows XP had the sql-mode set with relatively strict settings whereas 5.0.18 on my Mac had an empty sql-mode. This only illustrates the necessity of always explicitly defining these settings with each install yourself so that you get consistent behavior.
(more…)

My hosted Linux server runs Debian Sarge 3.1 with Apache 2. As most Ruby users have heard Apache 2 is rumored to be problematic with FastCGI. This evening I’ve been working on putting up a new Ruby on Rails site and the deployment aspect had me a little worried. I already host a bunch of sites on the box (Java and PHP) and moving them all to Apache 1, Lighttpd, or Mongrel and then thoroughly testing them was a depressing prospect.
(more…)

I’ve often written scripts only to discover once added to crontab that it relies on an environment setting such as having the java command in the PATH or having CVSROOT set. To avoid this I now run scripts I’m developing for cron with env -i to simulate the script running without an environment as follows:

env -i somescript.sh

This has greatly increased my success rate of adding a script to cron and having it work from the get go.

In general when trying to write portable scripts don’t start them with #!/bin/bash, instead start them with #!/usr/bin/env bash. The only caveat is that you DO NOT want to do this for critical security scripts because it opens you up to path based exploits.
(more…)

Being able to quickly and easily create test fixtures for your database is important yet it’s not always easy. There are basically 3 approaches I’ve seen used:

1. Use a MySQL or PostgreSQL dump that gets imported before the tests are run. I’ve never seen this approach used with Oracle, most likely because it’s such a PITA to export to text (or at least it used to be)
2. Just create the schema before the tests and then populate the test data in code
3. Use a database test fixture tool such as the XML based DBUnit for Java or Ruby’s YAML based fixtures
(more…)

My buddy Mike at UrbanaSoft is looking for an experienced full-time Unix and Windows sysadmin in downtown San Francisco. Pay is between $80-$100k depending on experience plus benefits and all that good stuff. If you’re qualified and interested or know someone that is email your resume to mike at urbanasoft dot com and mention that you learned about the job from my blog.

The biggest challege to moving large amounts of data into production with (almost) zero downtime with MySQL is that the old table will be dropped and the new table locked while you’re loading the data. If you try that while serving a lot of traffic you’ll get into trouble as the database connections start stacking up waiting for the lock to free on that table and then you’ll hit max connections giving most of your users a 500 error.

If you need to move big tables live you basically have 2 options: (more…)