Bug #19274 failure to convert datetime '0000-00-00 00:00:00' to TIMESTAMP

• Submitted: 22 Apr 2006 13:23
• Modified: 22 Apr 2006 22:30
• Reporter: Dharmacari Sadara
• Status: Not a Bug
• Category: Connector / J
• Severity: S1 (Critical)
• Version: 3.1.12 and 5.0.0 beta
• OS: Windows (windows XP SP2)
• CPU Architecture: Any

[22 Apr 2006 13:23] Dharmacari Sadara

Description:
I'm using Connector/J with QuantumDB in Eclipse.

My target MySQL databases are 4.0.22-nt-max running on Windows XP SP2 locally and 4.1.18 running on CentOS 3. Same behaviour whichever database is my target.

When selecting a record with the datatype DATETIME which has a value of '0000-00-00 00:00:00', I get the exception:

Cannot convert value '0000-00-00 00:00:00' from column <n> to TIMESTAMP

This is Quantum's SQL log:

Error Executing: select * from test:java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 1 to TIMESTAMP.
java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 1 to TIMESTAMP.
	at com.mysql.jdbc.ResultSet.getTimestampFromString(ResultSet.java:5712)
	at com.mysql.jdbc.ResultSet.getTimestampInternal(ResultSet.java:5740)
	at com.mysql.jdbc.ResultSet.getTimestamp(ResultSet.java:5393)
	at sun.reflect.GeneratedMethodAccessor8.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at com.quantum.util.proxy.ProxyFactory$InvocationHandlerImpl.invoke(ProxyFactory.java:37)
	at $Proxy3.getTimestamp(Unknown Source)
	at com.quantum.sql.SQLStandardResultSetResults.parseResultSet(SQLStandardResultSetResults.java:110)
	at com.quantum.sql.SQLStandardResultSetResults.create(SQLStandardResultSetResults.java:58)
	at com.quantum.sql.MultiSQLServer.execute(MultiSQLServer.java:135)
	at com.quantum.sql.MultiSQLServer.execute(MultiSQLServer.java:90)
	at com.quantum.actions.BaseExecuteAction.getSQLResults(BaseExecuteAction.java:186)
	at com.quantum.actions.BaseExecuteAction.execute(BaseExecuteAction.java:164)
	at com.quantum.actions.ExecuteAction.execute(ExecuteAction.java:99)
	at com.quantum.actions.BaseExecuteAction.execute(BaseExecuteAction.java:124)
	at com.quantum.actions.BaseExecuteAction.run(BaseExecuteAction.java:110)
	at org.eclipse.ui.actions.BaseSelectionListenerAction.runWithEvent(BaseSelectionListenerAction.java:167)
	at org.eclipse.jface.action.ActionContributionItem.handleWidgetSelection(ActionContributionItem.java:538)
	at org.eclipse.jface.action.ActionContributionItem.access$2(ActionContributionItem.java:488)
	at org.eclipse.jface.action.ActionContributionItem$6.handleEvent(ActionContributionItem.java:441)
	at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:66)
	at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:843)
	at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3080)
	at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:2713)
	at org.eclipse.ui.internal.Workbench.runEventLoop(Workbench.java:1699)
	at org.eclipse.ui.internal.Workbench.runUI(Workbench.java:1663)
	at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:367)
	at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:143)
	at org.eclipse.ui.internal.ide.IDEApplication.run(IDEApplication.java:103)
	at org.eclipse.core.internal.runtime.PlatformActivator$1.run(PlatformActivator.java:226)
	at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:376)
	at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:163)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.eclipse.core.launcher.Main.invokeFramework(Main.java:334)
	at org.eclipse.core.launcher.Main.basicRun(Main.java:278)
	at org.eclipse.core.launcher.Main.run(Main.java:973)
	at org.eclipse.core.launcher.Main.main(Main.java:948)

I have been unable to find a workaround.

How to repeat:
Create test database:

CREATE TABLE `test` (
  `test` datetime NOT NULL default '0000-00-00 00:00:00'
) TYPE=MyISAM;

Insert default-value record (Connector/J will handle this OK):

INSERT INTO `test` ( `t1` )
VALUES (
'0000-00-00 00:00:00'
)

select * from test

[22 Apr 2006 16:18] Dharmacari Sadara

Connector/J 3.0.17 does not exhibit this behaviour.

[22 Apr 2006 20:36] Mark Matthews

This is intended behavior. Please see the "upgrading" section of the manual for Connector/J for more details:

http://dev.mysql.com/doc/refman/5.0/en/cj-upgrading.html

(always a good idea to check this first when upgrading between major versions, either that or read the changelog, or both).

[22 Apr 2006 22:00] Dharmacari Sadara

which bit was intended? surely not being able to create a record it can't read. please explain!

[22 Apr 2006 22:12] Dharmacari Sadara

ok. 'zeroDateTimeBehavior' configuration property. just the job.

[22 Apr 2006 22:13] Mark Matthews

Quoting from the section referenced in the previous comment:

"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, and '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 ' noDatetimeStringSync=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 noDatetimeStringSync and useTimezone at the same time."

Java can _not_ represent an all-zero datetime value (try it, you'll see that you get a date that is nowhere close to zero anything, other than midnight), so we give the user an option as to how they want them handled. Either throw an exception (most correct, since neither JDBC nor standard SQL can deal with all-zero datetimes), convert to NULL or round to the closest date.

[22 Apr 2006 22:30] Dharmacari Sadara

thanks for your time, mark.

[16 Aug 2006 8:57] Erki Harand

For new JDBC users (like me) I'd like to point out the exact solution to this problem. You can pass configuration params to Connector/J by DB URL.

So to fix this problem use the following URL:
jdbc:mysql://localhost:3306/mydbname?zeroDateTimeBehavior=convertToNull