MySQL Silent Truncation

I have been burned twice in the past month by default MySQL behaviours. Before we proceed with these gotchas, let's setup a sample database.

Create a sample test database.
$ mysqladmin -u root -p create test
$ mysql -u root -p test

Data truncation or garbage in NOT necessary garbage out. The price of 12,000 was truncated into 12.00
mysql> create table items(price numeric(12, 2));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into items values('12,000');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from items \G;
************************* 1. row *************************
price: 12.00
1 row in set (0.00 sec)

VARCHAR and TEXT in PostgreSQL

There is not performance between varchar and text datatype in PostgreSQL. The use of varchar and text are interchangeable. To quote the PG doc (bold emphasis by me.),
"Tip: There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead."
Further discussion at Reddit.

PostgreSQL Installation in Ubuntu

It has been a while since I last use PostgreSQL professionally and I really miss it a lot. The newly 9.1 installation really brings back all the memories and it's quite sad that I can't remember how to use psql anymore. To make the situation worse, I am now stuck with the horrors, stupidity, and the numerous WTF of maintaining old legacy applications developed using The Shitty Toy Db (TSTD)* with no referential integrity. All this crap using this TSTD makes me long for PostgreSQL even more.

* To be fair to TSTD, if you configure it correctly, is not that bad.

Step 1 : Installation
$ sudo add-apt-repository ppa:pitti/postgresql
$ sudo apt-get update

$ sudo apt-cache search postgresql-9.1 
postgresql-9.1 - object-relational SQL database, version 9.1 server
postgresql-9.1-dbg - debug symbols for postgresql-9.1
postgresql-client-9.1 - front-end programs for PostgreSQL 9.1
postgresql-contrib-9.1 - additional facilities for PostgreSQL

$ sudo apt-get install postgresql-9.1 postgresql-9.1-dbg postgresql-client-9.1 postgresql-contrib-9.1

Step 2 : Setting up your own database account
$ echo $USER
foobar

$ sudo -u postgres createuser --superuser $USER

$ sudo -u postgres psql 
postgres=# \password foobar
Enter new password: 
Enter it again: 
postgres=# \q

$ createdb $USER
$ psql

Step 3 : Have fun.
Step 4 : Profit ?!

Finding GNU/Linux Distro And Architecture

Dealing with quite a few new servers recently and we can't find the original hardware specification as quoted in the invoice. While we can google for the spec based on the hardware vendor and model, however certain parts of the machine may be upgraded or replaced. To be sure, always double-check the hardware spec. To do this, we need to answer two questions.

1) What is the GNU/Linux distro and architecture of the existing server?
$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 11.04
Release: 11.04
Codename: natty

$ uname -a
Linux foobar 2.6.38-11-generic #48-Ubuntu SMP Fri Jul 29 19:02:55 UTC 2011 x86_64 x86_64 x86_64 GNU/Linux

2) What is the hardware specification of the server?
console output
$ sudo lshw

GUI output
$ sudo apt-get install lshw-gtk
$ sudo lshw -X

OR
$sudo lshw-gtk

OR (if you are running Ubuntu or you boot the machine using Live CD)
System -> Preferences -> Hardware Lister

Generating MySQL Qeuries

mysql > SELECT Concat('TRUNCATE TABLE ', TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES;
......
2270 rows in set (0.01 sec)

OMFG! WTF?! What have I done??
For a moment I thought I just accidentally truncates 34 databases and 2270 tables. Phew, luckily is just some string output to the console. No databases, tables, and records were harmed.

About add-apt-repository

I was trying to install latest greatest package from a Personal Package Archives (PPA) in an Ubuntu Server installation where the system complained that add-apt-repository command is missing. Did some googling around.

What is add-apt-repository?
"add-apt-repository is a script which adds an external APT repository to either /etc/apt/sources.list or a file in /etc/apt/sources.list.d/."
-- $ man add-apt-repository
Where is add-apt-repository?
$ sudo apt-get install apt-file
$ sudo apt-file update
$ sudo apt-file search add-apt-repository
python-software-properties: /usr/bin/add-apt-repository
python-software-properties: /usr/share/man/man1/add-apt-repository.1.gz

How to install it?
$ sudo apt-get install python-software-properties
$ man add-apt-repository

Removing Personal Package Archives (PPA) Repository

$ sudo apt-file search ppa-purge
ppa-purge: /usr/sbin/ppa-purge
ppa-purge: /usr/share/doc/ppa-purge/copyright
ppa-purge: /usr/share/man/man1/ppa-purge.1.gz

$ sudo apt-get install ppa-purge
$ sudo ppa-purge ppa://repository_name/subdirectory

Further reading.