Bug #47108 Cannot convert value '0000-00-00 00:00:00' from column XX to TIMESTAMP
Submitted: 3 Sep 2009 18:02 Modified: 4 Sep 2009 21:06
Reporter: Dmitriy Ro Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.8 OS:Any
Assigned to: CPU Architecture:Any

[3 Sep 2009 18:02] Dmitriy Ro
Description:
The date in '0000-00-00 00:00:00' format was successfully stored in TIMESTAMP column. This operation has to be reversible. Perhaps correct behavior will be returning java.sql.Date with 0 settings of time. Returning NULL looks not logical.

How to repeat:
Inser data in TIMESTAMP column using
"'CONVERT('''yyyy-MM-dd HH:mm:ss''', DATETIME)'", when Java Date object is null, then try to retrieve data back.

Suggested fix:
See note in description.
[3 Sep 2009 18:04] Dmitriy Ro
Here is trace:

java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 14 to TIMESTAMP.
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
        at com.mysql.jdbc.ResultSetRow.getTimestampFast(ResultSetRow.java:1328)
        at com.mysql.jdbc.ByteArrayRow.getTimestampFast(ByteArrayRow.java:124)
        at com.mysql.jdbc.ResultSetImpl.getTimestampInternal(ResultSetImpl.java:6669)
        at com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:5988)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.javaarchitect.pool.ConnectionPool$1$1$1.invoke(ConnectionPool.java:99)
        at $Proxy8.getTimestamp(Unknown Source)
        at org.javaarchitect.sqlfair.Runsql$1.next(Runsql.java:126)
        at org.aldan3.util.TemplateEngine.process(TemplateEngine.java:748)
        at org.aldan3.util.TemplateEngine.processResource(TemplateEngine.java:253)
        at org.aldan3.util.TemplateEngine.process(TemplateEngine.java:274)
        at org.aldan3.servlet.BasePageService.processView(BasePageService.java:274)
        at org.aldan3.servlet.BasePageService.serve(BasePageService.java:161)
        at org.aldan3.servlet.FrontController.doRequest(FrontController.java:215)
        at org.aldan3.servlet.FrontController.service(FrontController.java:136)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
        at rogatkin.web.WebAppServlet$SimpleFilterChain.doFilter(WebAppServlet.java:2146)
        at rogatkin.web.WebAppServlet$WebAppContextFilter.doFilter(WebAppServlet.java:1909)
        at rogatkin.web.WebAppServlet$SimpleFilterChain.doFilter(WebAppServlet.java:2123)
        at rogatkin.web.WebAppServlet.service(WebAppServlet.java:894)
        at Acme.Serve.Serve$ServeConnection.runServlet(Serve.java:2003)
        at Acme.Serve.Serve$ServeConnection.parseRequest(Serve.java:1967)
        at Acme.Serve.Serve$ServeConnection.run(Serve.java:1789)
        at Acme.Utils$ThreadPool$PooledThread.run(Utils.java:1215)
        at java.lang.Thread.run(Thread.java:619)
Caused by: java.sql.SQLException: Value '[B@1198ff2' can not be represented as java.sql.Timestamp
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
        at com.mysql.jdbc.ResultSetRow.getTimestampFast(ResultSetRow.java:1027)
        ... 27 more
[4 Sep 2009 13:08] Tonci Grgin
Hi Dmitriy and thanks for your report.

There is a whole section of manual devoted to this problem. Just a short excerpt:
• Datetimes with all-zero components (0000-00-00 ...)— These values can not be represented reliably in Java. Connector/J 3.0.x always converted them to NULL when being read from a ResultSet.
Connector/J 3.1 throws an exception by default when these values are encountered as this is the most correct behavior according to the JDBC and SQL standards. This behavior can be modified using the zeroDateTimeBehavior configuration property.
The allowable values are:
• exception (the default), which throws an SQLException with an SQLState of S1009.
• convertToNull, which returns NULL instead of the date.
• round, which rounds the date to the nearest closest value which is 0001-01-01.
Starting with Connector/J 3.1.7, ResultSet.getString() can be decoupled from this behavior via noDatetimeString-Sync=true (the default value is false) so that you can get retrieve the unaltered all-zero value as a String. It should be noted that this also precludes using any time zone conversions, therefore the driver will not allow you to enable noDatetimeString-Sync and useTimezone at the same time.

and so on...

So please post entire code required to reproduce as well as connection string so I can check on your claims.
[4 Sep 2009 21:06] Dmitriy Ro
Upon Tonci Grgin's response, I have the issue resolved providing default behavior as zeroDateTimeBehavior=convertToNull. It is more logical for my case, since I create a framework, which puts '0000-00-00 00:00:00' in database when corresponding data model filed of type Date has value null. I can argue what's default behavior out of the box should be, however it is out of scope of the reported issue. Thanks.