Weekblog: Day 3 – The JDBC Whubbidah huh?

Today, durnit, I was going to get some work done. I specifically scheduled time after $dayjob to spend working on CONGO v2, and ya know what? I actually did it!

Tonight’s joy was parking at the Panera in Marlborough, plugging in the iPhone for music, and getting down to coding. With the network being twitchy in the restaurant, I actually had about 2 hours of uninterrupted focus time to code, and I was productive!

I finished up much of the underlying work on the Notes system (the DAO and Data object are done), and was working on wiring up the Notes interface into Coconut (the web front end). One of my goals with this whole project is to upgrade how I’m managing database resources, and doing things ‘properly’. For instance, CONGO v1 had absolutely no referential integrity mechanisms in place. No foreign keys of any sort. This meant I could easily have had orphaned data in many of the tables, there was just no way to tell.

The first step to setting up proper RI is to use foreign keys to link one table to another. The newly formed Notes table now has foreign keys linking to the reg_master table (which holds registrant master information). I did learn a couple basic concepts while doing this though. For instance, if the source of a key reference is null, then the foreign key constraint is not checked (handy!). If it has any other value, it must have a corresponding key in the target table. I found out quickly that specifying source columns as NOT NULL in the database scheme wasn’t going to work, particularly on columns that get populated later.

The other thing I wanted to fix was date handling. I had a mishmash of DATE column types and raw text fields storing dates in the old version. MySQL (and most databases) have a handy column type called TIMESTAMP that can not only store time down to fractions of a second, but also support basic math operations (“Show me all rows that have been modified in ‘now() – 3 HOURS’ is a valid construct).

Here’s the problem though. MySQL DATETIME columns cannot be null. So what if I don’t have a date to put into the column yet? Well, the docs say the schema should say ‘colname TIMESTAMP DEFAULT 0’ – I did this, and it seemed to take. But when I tried to retrieve the row in my DAO, I was getting this error:

java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 6 to TIMESTAMP.

The offending line causing this was (‘where’ is a Note object, postDate is a java.sql.Timestamp, and ‘fromwhat’ is a ResultSet):

where.postDate = fromwhat.getTimestamp("note_postdate");

I completely and totally don’t understand this. I’m wondering if this is a bug in the JDBC driver I’m using, or what, but I should not be trying to load a date in the format 0000-00-00 into the java.sql.Timestamp object. It should be returning NULL, and therefore ‘postDate’ should be null, as it was when it was created.

I ended up having to code logic around it to say basically “Don’t try to load the note_postdate value unless circumstances can guarantee there was a value there.” I tried various approaches for ‘Is this column going to be null?’ – but it really appears as if the JDBC driver is delivering a Timestamp object, even though the source is null.

It gets a little better. In the MySQL docs, they say, as I mentioned above, that the declaration for a TIMESTAMP without a value should be ‘default 0’. But after I create my tables from my schema (using that syntax), and then do a ‘SHOW CREATE TABLE reg_notes’, lo and behold, I see:

CREATE TABLE `reg_notes` (
`note_id` int(8) NOT NULL auto_increment,
`note_rid` int(8) NOT NULL default '0',
`note_cid` int(8) default NULL,
`note_postrid` int(8) default NULL,
`note_ackrid` int(8) default NULL,
`note_postdate` timestamp NOT NULL default '0000-00-00 00:00:00',
`note_ackdate` timestamp NOT NULL default '0000-00-00 00:00:00',
`note_message` varchar(100) default NULL,
KEY `id` (`note_id`),
KEY `rid` (`note_rid`),
KEY `note_cid` (`note_cid`),
KEY `note_postrid` (`note_postrid`),
KEY `note_ackrid` (`note_ackrid`),
CONSTRAINT `reg_notes_ibfk_1` FOREIGN KEY (`note_rid`) REFERENCES `reg_master` (`master_rid`) ON DELETE CASCADE,
CONSTRAINT `reg_notes_ibfk_2` FOREIGN KEY (`note_cid`) REFERENCES `con_detail` (`con_cid`) ON DELETE CASCADE,
CONSTRAINT `reg_notes_ibfk_3` FOREIGN KEY (`note_postrid`) REFERENCES `reg_master` (`master_rid`) ON DELETE CASCADE,
CONSTRAINT `reg_notes_ibfk_4` FOREIGN KEY (`note_ackrid`) REFERENCES `reg_master` (`master_rid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Observant folks may note that the note_postdate and note_ackdate columns now have default values of ‘0000-00-00 00:00:00’. Not what I specified in my creation SQL.

I’m past this now, and it’s working, but if anyone has ideas about why this is behaving this way, I’d love to hear it. It’s damned frustrating.

UPDATE 08/28/2008 – Through the magic of RTFM and some nudges from Imre, my table definition was wrong… Here’s what it should be:

note_postdate TIMESTAMP null DEFAULT null,
note_ackdate TIMESTAMP null DEFAULT null,

This little bit of MySQL fun is in fact documented, it’s just buried in the TIMESTAMP page on dev.mysql.com