do what I asked you to do, not what you think I wanted

Sometimes software is just too smart for it's own good.

So as I'm working on coding the client for CAATT I was creating some test data in the database, and I found that my input timestamp keeps changing, even though I'm not touching it.

mysql> select * from work_queue;
+---------+---------------------+---------------------+------+----------+------+
| work_id | input               | output              | task | comments | doer |
+---------+---------------------+---------------------+------+----------+------+
|       1 | 2007-01-22 20:43:36 | 0000-00-00 00:00:00 |    1 | NULL     |  501 | 
|       2 | 2007-01-28 20:43:43 | 0000-00-00 00:00:00 |    2 | NULL     |  502 | 
|       3 | 2007-01-28 20:43:51 | 0000-00-00 00:00:00 |    4 | NULL     |  501 | 
|       4 | 2007-01-29 00:25:44 | 0000-00-00 00:00:00 |    5 | NULL     |  501 | 
+---------+---------------------+---------------------+------+----------+------+
4 rows in set (0.00 sec)

mysql> update work_queue set output=now(), comments="howdy" where work_id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from work_queue;
+---------+---------------------+---------------------+------+----------+------+
| work_id | input               | output              | task | comments | doer |
+---------+---------------------+---------------------+------+----------+------+
|       1 | 2007-01-22 20:43:36 | 0000-00-00 00:00:00 |    1 | NULL     |  501 | 
|       2 | 2007-01-28 20:43:43 | 0000-00-00 00:00:00 |    2 | NULL     |  502 | 
|       3 | 2007-01-29 00:26:45 | 2007-01-29 00:26:45 |    4 | howdy    |  501 | 
|       4 | 2007-01-29 00:25:44 | 0000-00-00 00:00:00 |    5 | NULL     |  501 | 
+---------+---------------------+---------------------+------+----------+------+
4 rows in set (0.00 sec)

So I set the completion date and a comment on #3, but it changed the input time on me! ARGH!!! So I went back and looked at my table definition...

mysql> explain work_queue;
+----------+----------------------+------+-----+---------------------+----------------+
| Field    | Type                 | Null | Key | Default             | Extra          |
+----------+----------------------+------+-----+---------------------+----------------+
| work_id  | bigint(20) unsigned  | NO   | PRI | NULL                | auto_increment | 
| input    | timestamp            | NO   |     | CURRENT_TIMESTAMP   |                | 
| output   | timestamp            | NO   |     | 0000-00-00 00:00:00 |                | 
| task     | tinyint(3) unsigned  | NO   |     |                     |                | 
| comments | varchar(4096)        | YES  |     | NULL                |                | 
| doer     | smallint(5) unsigned | YES  |     | NULL                |                | 
+----------+----------------------+------+-----+---------------------+----------------+
6 rows in set (0.00 sec)

Nope, nothing there to indicate why it did that. Off to the manuals I go, finally find this. GRRRR! show create table work_queue confirms that it set both the default AND the on update to CURRENT_TIMESTAMP when the table was created. Several attempts to modify the table fail to achieve anything even remotely close to what I want. In the end I did a mysqldump of the entire database and modified the creation script to be as such:

DROP TABLE IF EXISTS `work_queue`;
CREATE TABLE `work_queue` (
  `work_id` bigint(20) unsigned NOT NULL auto_increment,
  `input` timestamp NOT NULL default CURRENT_TIMESTAMP references work_queue on update NO ACTION,
  `output` timestamp NOT NULL,
  `task` tinyint(3) unsigned NOT NULL,
  `comments` varchar(4096) default NULL,
  `doer` smallint(5) unsigned default NULL,
  UNIQUE KEY `work_id` (`work_id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

So the magic bullet to create a mysql table that has a column that tracks INSERTION time, but NOT UPDATE TIME? A self referential on update no action. And you can only do that when you CREATE the table, there's no way to get alter to accept that as near as I can tell.

Now that that's out of my system, a quick update on the caatt clients: I've decided to do a quick LAMP implementation instead of a native OS/X client. It already knows who you are, asks if you've not told it, and remembers... and it can dump a list of work to do, with overdue item's highlighted separately. Details of a task and marking it complete were what I was working on when this stopped me. Oh yeah, and I slapped together a quick logo/favicon for it with OmniGraffle (and photoshop for cropping, since OmniGraffle's export sometimes picks bizare margins).