Weekblog: Day 3 – The JDBC Whubbidah huh?

28 08 2008

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

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




3 responses

28 08 2008

in mysql 5.x, timestamps can be null, but you have to specify it as

28 08 2008

Thanks Imre, that definately did it. Even found it in the manual. Fixed! Committed!

28 08 2008

NOT NULL default 0 for timestamp does -not- equate to ‘NULL’. It refers to a timestamp with no time having passed from the base time your system is ‘counting’ from.. whatever that may mean… which is, of course, not portable, and causes a mess like you were seeing above. 😉

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: