This handy little shell script will reset your entire apache installation to what it was like when you first installed it.

1
2
3
4

APACHE_PKGS=`sudo dpkg --get-selections | grep apache | cut -f 1`
echo $APACHE_PKGS
sudo apt-get remove --purge $APACHE_PKGS
sudo apt-get install $APACHE_PKGS

Worked on Ubuntu 11.04. Source: http://bit.ly/mR0bOq

Boto provides an amazing interface to AWS APIs. Here is a simple example to spin up a t1.micro sized EC2 instance of the default Amazon Linux 64 bit AMI.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

from boto.ec2.connection import EC2Connection
key = 'aws access key'
secret = 'aws secret access key'
connection = EC2Connection(
              aws_access_key_id=key,
              aws_secret_access_key=secret)
ami = 'ami-8e1fece7' # AMI ID of the Amazon Linux x86_64 AMI 
reservation = c.run_instances(ami,
              min_count=1,
              max_count=1, 
              key_name='MyKeyPairName', 
              instance_type='t1.micro')
instance = reservation.instances[0]
while instance.update() != 'running':
  sleep(5)

min_count and max_count determine how many instances are to be started. By default, Amazon limits you to starting a maximum of 20 instances. If you need to start more, they require you to contact them with your use case.

I was looking to accomplish a simple task – print debug messages in a Django View that would tell me what’s going on. Came across the following in a post:

You can configure it in your `settings.py`:

1 2 3 4 5

import logging logging.basicConfig( level = logging.DEBUG, format = ''%(asctime)s %(levelname)s %(message)s'', )

Then call it from any of your views:

1 2 3

def my_view(request): import logging logging.debug("A log message")

He then goes on to explain more advanced use cases using the Python Debugger, but this much was enough for what I was looking to do with it.

Django is awesome. Python is awesome. Mac OS is sort-of ok. So is MySQL. But geez, getting Python’s MySQL connector (MySQLdb) to play nice on the Mac OS made me start to lose my hair. Django needs this to be able to reach a MySQL server (which I wanted to use) to be able seamlessly run syncdb etc.

There are varied sets of sparse instructions all over the interwebs on how to get MySQL db to compile for Mac OS; some ask you to edit source files before compiling, some are out of date and nearly all of them just don’t work.

But… it’s a Mac… doesn’t it just work?! Apparently not. </end rant>

The solution, this time, came to me in the form of MacPorts. MacPorts is a brilliant system that is the closest replacement to aptitude, for someone coming from a more Ubuntu-esque background (and I guess its existence makes sense, what with FreeBSD ports and all). A few simple lines to get you going on this:

1
2
3
4
5

# install django and dependencies
sudo port install py26-django
# set py26 as default
sudo port select --set python python26
sudo port install py26-mysql

Just to make sure these worked:

1
2
3
4
5
6
7
8

macbookpro:~ asdf$ python
Python 2.6.6 (r266:84292, May 26 2011, 01:52:07) 
[GCC 4.2.1 (Apple Inc. build 5664)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb
>>> MySQLdb.version_info
(1, 2, 3, ''final'', 0)
>>>

I was using MySQLdb to interact with a MySQL server in Python. The script did some selects and some inserts, nothing fancy. All of a sudden, the selects are working, but the inserts refuse to stick! The script source hadn’t been touched, far as I could tell.

Despite my faith in git status, after meticulously going through each line of code (to ensure absence of PEBKAC), I determined that the script really hadn’t been touched.

Finally remembered, halfway through a new script, I had decided to switch the storage engine from MyISAM to InnoDB and therein, as the Bard would tell us, lies the rub.
Python-MySQLdb needs an additional connection.commit() statement for all the inserts to actually go through to InnoDB!

1
2
3
4
5
6
7
8
9
10
11
12
13
14

connection = MySQLdb.connect(
               host = dbHost,
               user = dbUsername,
               passwd = dbPassword,
               db = dbSchema);
cursor = connection.cursor()
sql = "INSERT INTO ..."
cursor.execute(sql)
cursor.close()
 
# Extra step for InnoDB
connection.commit()
 
connection.close()

A fairly common requirement for database tables is to have a created and last modified timestamp for each record. This seems to be yet another spot where something fairly straightforward requires you to take the scenic route to implement. Ordinarily, the create table statement would look like:

1
2
3
4
5
6
7

CREATE  TABLE `mydb`.`mytable` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `myfield` VARCHAR(255) NOT NULL ,
  `modified` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE NOW() ,
  `created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) )
ENGINE = MyISAM;

And that’s when MySQL threw me a nice

1

Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause.

Upon digging a little further, I found this little nugget in the documentation:

For one TIMESTAMP column in a table, you can assign the current timestamp as the default value and the auto-update value. It is possible to have the current timestamp be the default value for initializing the column, for the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.

Well isn’t that just peachy.

The Solution

To overcome this hurdle, I employed the use of a trigger for the created timestamp and decided to leave the modified as is. The create table statement then turned into:

1
2
3
4
5
6
7

CREATE  TABLE `mydb`.`mytable` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `myfield` VARCHAR(255) NOT NULL ,
  `modified` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE NOW() ,
  `created` TIMESTAMP NULL,
  PRIMARY KEY (`id`) )
ENGINE = MyISAM;

And used the following trigger to process the created timestamp –

1
2
3
4
5
6
7

USE `mydb`;
DELIMITER $$
  CREATE TRIGGER trigger_mytable_before_insert BEFORE INSERT ON `mytable`
  FOR EACH ROW BEGIN
      SET NEW.created = NOW();
  END;
$$

I’ve always found it difficult to wrap my head around the various language date implementations because each one has its idiosyncrasies. Each time I have to work with a new language, I have to re-learn their date handling constructs, which is a bit of a pain. Databases are the worst culprits in this respect because each database offers a date, datetime or timestamp field and each database engine has its own expectation for how that date time field should be queried. SQL Server and C# work together to simplify matters a bit (or so I thought). Consider the following sample:

1
2
3
4
5
6
7
8
9
10
11

string queryString = "Data Source=sqladdr; Initial Catalog=mydb; User Id=usr; Password=pwd;";
SqlConnection conn = new SqlConnection(queryString);
conn.Open();
SqlCommand cmd = new SqlCommand(conn);
cmd.CommandText = "INSERT INTO tblTest (currDate) VALUES (@DateParam)";
SqlParameter param = new SqlParameter();
param.ParameterName = "@DateParam";
param.Value = DateTime.Now;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
conn.Close();

Today I learned, by delegating the task to a friend, that Python exposes the MySQL datetime in a much easier-to-grasp manner. A sample date interaction in python would be:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

from datetime import *
import MySQLdb
 
conn = MySQLdb.connect(
      host="dbhost",
        user="username",
        passwd="pwd",
        db="schema")
cursor = conn.cursor()
cursor.execute("SELECT * FROM tblTest")
row = cursor.fetchone()
if (row is not None):
  # the field index according to the select statement
    date_value = row[0]
# here, date_value is a datetime object. automatically. :D
cursor.execute("INSERT INTO `date_table` (`the_date`) " + \
               "VALUES (''%s'')" % (date_value.isoformat()))
# and just like that, you''re done.
conn.close()

All it takes is to enclose the date in quotes and invoke the date_object.isoformat() method, and voila, you’re done. What can I say, I’m really starting to dig python.

  I was surprised to learn that there is no simple way to rename a database in MySQL without bringing the whole server down and renaming data files.

You’d think it would be a trivial task, especially considering the SQL Server docs provide the following procedure to rename a database:

To rename a database
In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
Make sure that no one is using the database, and then set the database to single-user mode.
Expand Databases, right-click the database to rename, and then click Rename.
Enter the new database name, and then click OK

The closest I’ve come to finding a non-invasive way is by running the following series of commands (courtesy of Stack Overflow):

1
2
3

mysqldump -u username -p -v olddatabase > olddbdump.sql
mysqladmin -u username -p create newdatabase
mysql -u username -p newdatabase < olddbdump.sql

Make sure there are no connections active to the old database at the time as you may not get the freshest data and remember to drop the old database after you’re done. Original Stack Overflow thread: http://bit.ly/14ETgF