Friday, June 6, 2008

debugging hibernate/mysql systems

Just a quick note, on debugging hibernate/mysql systems.

In my ‘webtwo’ system I had the following constraint in the db:

constraint fk_submission_item_image foreign key (image_id) references image_data(id) ON DELETE cascade,

However, image_data didn’t exist.

During operation it caused the error

18:34:56,009 WARN [JDBCExceptionReporter] SQL Error:
1452, SQLState: 23000
18:34:56,009 ERROR [JDBCExceptionReporter] Cannot add
or update a child row: a foreign key constraint
fails (`webtwo_dev/submission_item`, CONSTRAINT
`fk_submission_item_image` FOREIGN KEY (`image_id`)
18:34:56,009 ERROR [AbstractFlushingEventListener]
Could not synchronize database state with session
Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.convert
at org.hibernate.exception.JDBCExceptionHelper.convert

I thought this indicated a problem with my Hibernate/java mapping since I’ve been working with the database for a few months without any errors being thrown by MySQL during db creation. I also thought that I had successfully populated all the tables.

In retrospect this wasn’t the case: I had not populated the tables and it is also not surprising that MySQL didn’t signal an error since I “SET FOREIGN_KEY_CHECKS = 0; “ during table creation -- so, my bad.

The moral is that any unpopulated table may be fundamentally misspecified.

This experience does serve to reinforce my heuristic to populate all the tables via an initial data population script at least for testing purposes. Realistically, this requires two load scripts: one to populate the data required for operation (ie., roles, menu values, etc.), and a second to do a test load to verify database integrity.

No comments: