Skip to content

# Month: May 2011

## TIMESTAMP vs. DATETIME, which should I be using?

MySQL Data Types May 22, 2011May 26, 2011

They both look the same, have roughly the same use. Nevertheless, they differ in many respects. I’ll note the difference, and note a few pitfalls and peculiarities.

#### Range

TIMESTAMP starts with the epoch, ‘1970-01-01 00:00:01’ UTC and ends with ‘2038-01-19 03:14:07’ UTC. This is all very nice today, and may actually hold up till our retirement, but the younger readers may yet have to deal with the Bug2K+38™, certain to arrive.

DATETIME starts with ‘1000-01-01 00:00:00’ and lasts up to ‘9999-12-31 23:59:59’. More on that later.

In respect of range, your current event logs may well use a TIMESTAMP value, while your grandfather’s and granddaughter’s birth dates may require DATETIME.

In general I would suggest that anything that relates to now, can be a TIMESTAMP. A new entry is added? It is added now. It can be represented by a TIMESTAMP. Anything has an expiry time of a few minutes, perhaps a few days or a month? You’ll be safe using it up till late 2037. Anything else had better use a DATETIME. In particular, dates relating to issues such as birth, insurance, the market etc. fall into this category.

History, however, does not even hold up with DATETIME. Rome fell long before MIN(DATETIME). You will have to manage your own. Not even YEAR will help you out.

#### Storage

TIMESTAMP makes for 4 bytes. DATETIME makes for 8 bytes. Now that we have this behind us, let’s see why. Continue reading » “TIMESTAMP vs. DATETIME, which should I be using?”

## Tales of the trade #5: How to author a super successful MySQL blog

MySQL Humor Planet May 18, 2011May 18, 2011

Check out the following tutorial that teaches you how to become a popular MySQL blog author:

Complete strip: tales-of-the-trade-make-your-mysql-blog-popular-full.png

## Problems with MMM for MySQL

MySQL High availability Replication May 16, 2011May 16, 2011

I recently encountered troubling issues with MMM for MySQL deployments, leading me to the decision to cease using it on production.

At the very same day I started writing about it, Baron published What’s wrong with MMM?. I wish to present the problems I encountered and the reasons I find MMM is flawed. In a period of two weeks, two different deployments presented me with 4 crashes, in 3 different scenarios.

In all the following scenarios, there is an Active/Passive Master-Master deployment, with one VIP (virtual IP) set for writer role, one VIP set for reader role.

#### Problem #1: unjustified failover, broken replication

Unjustified failover must be the common scenario. It’s also a scenario I can live with. A few seconds of downtime are OK with me once in a couple of months.

But on two different installations, a few days apart, I had two seemingly unjustified failovers followed by a troubling issue: replication got broken. Continue reading » “Problems with MMM for MySQL”

## Pop quiz answered: “what would be the results of the following queries?”

MySQL SQL May 12, 2011

The quiz presented poses with an uncommon, though valid SQL syntax: one is allowed to use quoted name aliases. Thus, it is valid to write:

>     SELECT Name AS 'n', Continent AS 'c' FROM countries

But what does the above mean? Let’s see the results of our three questions: Continue reading » “Pop quiz answered: “what would be the results of the following queries?””

## Pop quiz: what would be the results of the following queries?

MySQL SQL May 12, 2011

Here’s a quiz for you. The real query I witnessed was very complicated. I’ve simplified it, but kept a confusing alias. Can you answer the following three questions? Are they even valid?

Given the following countries table data: Continue reading » “Pop quiz: what would be the results of the following queries?”