As a follow-up to my earlier post on date conversions, I noticed that the MYSQL database was getting loaded with a lot of zero dates where the original data had null dates, I wondered how to deal with this and if it would present issues… well here is a great tip: Also note that MYSQL more or less requires zero dates and does not like nulls for dates…
The following from http://hartsock.blogspot.com
Magical MySQL URL parameters Save my Groovy Code
I’ve been working with legacy databases in MySQL and basically forbidden from using hibernate by a bug I had with all zero dates of the format ‘0000-00-00’ which is perfectly legal in MySQL but completely disallowed in JDBC.
Stack trace for this problem contains:
SQL Exception: Value ‘0000-00-00’ can not be represented as java.sql.Date
The answer? Apparently there is a hidden setting to use…
driverClassName = “com.mysql.jdbc.Driver”
url = “jdbc:mysql://localhost:3306/legacy?zeroDateTimeBehavior=convertToNull”
username = “legacyUser”
password = “legacyPassword”
That little flag on the URL is absolutely magical and now I can map my Grails objects onto a legacy database. Isn’t life grand?