Bug #69343 SQL Exception when timstamp column 000-00-00 00:00:00

• Submitted: 29 May 2013 12:26
• Modified: 29 May 2013 13:39
• Reporter: Philippe Boivin
• Status: Not a Bug
• Category: Connector / J
• Severity: S3 (Non-critical)
• Version: 5.1.25
• OS: Any
• Assigned to: Alexander Soklakov
• CPU Architecture: Any
• Tags: java, mysl connector, sql exception, timestamp

[29 May 2013 12:26] Philippe Boivin

Description:
Hello,

I found a bug I think, When we are a timestamp column in a BD and we have a select in the table contains 0000-00-00 00:00:00 like timestamp 

The mysql connector throws a SQL Exception 

How to repeat:
Do a timestamp column on a table and tap select query to retrieve this, if the timestamp is 0000-00-00 00:00:00, the mysql connector throws a SQL Exception 

ex:
java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp

Suggested fix:
I have fix this problem with other function on the select like this.
the "pa.dCreationDate" column may have 0000-00-00 00:00:00.

... FROM_UNIXTIME(COALESCE(UNIX_TIMESTAMP(pa.dCreationDate), 0)) AS dCreationTaskDate, ...

[29 May 2013 13:39] Alexander Soklakov

Hi Philippe,

Thanks for your report.

This is not a bug, Connector/J has connection property "zeroDateTimeBehavior" to handle this situation. According to documentation http://dev.mysql.com/doc/refman/5.6/en/connector-j-reference-configuration-properties.html:

"What should happen when the driver encounters DATETIME values that are composed entirely of zeros (used by MySQL to represent invalid dates)? Valid values are "exception", "round" and "convertToNull"."

Default value is "exception", just change it to appropriate for you.