I’ve been modifying some details of our SnipSnap instance and noticed some odd behaviour. I did a lot of manipulation of the database — imported a dump of an existing copy, updated a bunch of records, poured data from one table to another, and then did some queries.
At this point, I noticed something odd. The “creation time” on a number of my records was today’s date/time. I expected the creation time to be equivalent to the creation time in the database I took the dump from. But it wasn’t.
I checked the table definition. It looks like this:
CREATE TABLE Snip ( name VARCHAR(100) NOT NULL, applicationOid VARCHAR(100) NOT NULL, content TEXT, cTime TIMESTAMP, mTime TIMESTAMP, cUser VARCHAR(55), ...
After double-checking the dump file, I discovered the following:
CREATE TABLE `snip` ( `name` varchar(100) NOT NULL default '', `applicationOid` varchar(100) NOT NULL default '', `content` text, `cTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `mTime` timestamp NOT NULL default '0000-00-00 00:00:00', `cUser` varchar(55) default NULL, ...
I could find no place where the column was redefined. The whole “on update” qualifier didn’t make any sense.
After more than just a bit of hunting, I finally found this documentation about MySQL’s implementation of the Timestamp data type, and how it’s different than Datetimes.
There are several ways in which this behaviour was invisible to me.
- First, I’ve used enough database products to recognize both Datetime and Timestamp as data types that represent “date and time information”. I know that Timestamp is the ANSI standard term, and that some products (like Oracle) favour the term Datetime. I would not have expected a Timestamp column to have some different behaviour than a Datetime column.
- The two columns — cTime and mTime — appear to be defined the same way, and yet the behaviour of the columns is different. The best theory I imagined was that cTime was a special name. I didn’t really imagine that its position as the first Timestamp column was significant. Perhaps because of programming, I think more naturally in terms of “special words” than “special positions”.
- The original table definition was wrong. Clearly cTime is meant to indicate creation time whereas mTime was modification time. If that’s the intent, then mTime should have been defined first, rather than cTime.
As I realize what’s invisible about this form of implicit behaviour, I’m reminded of Donald Norman’s book, The Design of Everyday Things. In the book, Norman argues that one of the classic design flaws is to make important information invisible, and one of the imporant ways of improving people’s ability to use stuff that you’ve designed is to make the invisible visible.