#621 0 timestamp error

2.6.5
closed-rejected
Rob Manning
Core (461)
5
2008-04-05
2008-03-13
sean
No

Hi, I'm using MySQL 5.0.51a.

When I try to insert a zero timestamp (0000-00-00 00:00:00), I get <Error> in the column. Below is the dump:

java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 8 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:1321)
at com.mysql.jdbc.ByteArrayRow.getTimestampFast(ByteArrayRow.java:124)
at com.mysql.jdbc.ResultSetImpl.getTimestampInternal(ResultSetImpl.java:6617)
at com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:5943)
at net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeTimestamp.readResultSet(DataTypeTimestamp.java:494)
at net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.CellComponentFactory.readResultSet(CellComponentFactory.java:561)
at net.sourceforge.squirrel_sql.fw.sql.ResultSetReader.doContentTabRead(ResultSetReader.java:526)
at net.sourceforge.squirrel_sql.fw.sql.ResultSetReader.readRow(ResultSetReader.java:153)
at net.sourceforge.squirrel_sql.fw.datasetviewer.ResultSetDataSet.setResultSet(ResultSetDataSet.java:134)
at net.sourceforge.squirrel_sql.fw.datasetviewer.ResultSetDataSet.setContentsTabResultSet(ResultSetDataSet.java:74)
at net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.table.ContentsTab.createDataSet(ContentsTab.java:420)
at net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.BaseDataSetTab$1.run(BaseDataSetTab.java:123)
at net.sourceforge.squirrel_sql.fw.util.TaskExecuter.run(TaskExecuter.java:82)
at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: Value '[B@10a519' 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)
... 13 more
67239 [Thread-2] ERROR net.sourceforge.squirrel_sql.fw.sql.ResultSetReader - Error reading column data, column index = 10
java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 10 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:1321)
at com.mysql.jdbc.ByteArrayRow.getTimestampFast(ByteArrayRow.java:124)
at com.mysql.jdbc.ResultSetImpl.getTimestampInternal(ResultSetImpl.java:6617)
at com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:5943)
at net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeTimestamp.readResultSet(DataTypeTimestamp.java:494)
at net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.CellComponentFactory.readResultSet(CellComponentFactory.java:561)
at net.sourceforge.squirrel_sql.fw.sql.ResultSetReader.doContentTabRead(ResultSetReader.java:526)
at net.sourceforge.squirrel_sql.fw.sql.ResultSetReader.readRow(ResultSetReader.java:153)
at net.sourceforge.squirrel_sql.fw.datasetviewer.ResultSetDataSet.setResultSet(ResultSetDataSet.java:134)
at net.sourceforge.squirrel_sql.fw.datasetviewer.ResultSetDataSet.setContentsTabResultSet(ResultSetDataSet.java:74)
at net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.table.ContentsTab.createDataSet(ContentsTab.java:420)
at net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.BaseDataSetTab$1.run(BaseDataSetTab.java:123)
at net.sourceforge.squirrel_sql.fw.util.TaskExecuter.run(TaskExecuter.java:82)
at java.lang.Thread.run(Unknown Source)

Discussion

  • Rob Manning
    Rob Manning
    2008-04-05

    • assigned_to: colbell --> manningr
    • status: open --> closed-rejected
     
  • Rob Manning
    Rob Manning
    2008-04-05

    Logged In: YES
    user_id=1287991
    Originator: NO

    No, I don't think this is our bug. See this MySQL connector/J thread:

    http://bugs.mysql.com/bug.php?id=19274

    I can reproduce this behavior; however, you can set a driver property to avoid the exception being thrown from the driver.
    See the driver property called "zeroDateTimeBehavior" (Description: "What should happen when the driver encounters DATETIME values that
    are composed entirely of zeroes (used by MySQL to represent invalid dates)? Valid values are 'exception', 'round' and 'convertToNull'"). You can set this property by highlighting your alias then in Aliases -> "Alias Properties" -> Driver Properties : Name = zeroDateTimeBehavior.

    Rob