Bug #13261 zeroDateTimeBehavior property seems to be ignored
Submitted: 16 Sep 2005 11:35 Modified: 23 Sep 2005 7:42
Reporter: Gustav Oesgaard Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:3.1.10 OS:Windows (Windows XP Professional)
Assigned to: CPU Architecture:Any

[16 Sep 2005 11:35] Gustav Oesgaard
Description:
I´ve been using Connector/J 3.0 for a while but have now decided to use Connector/J 3.1 (using 3.1.10) instead. 

I noticed that the behavior for reading DATETIME fields containing "0000-00-00 00:00:00" (which was read without problem before) has changed so that an Exception is thrown when trying to read it.

I´ve tried to modify this behaviour according to instructions in the upgrading section of the Connector/J documentation by setting the "zeroDateTimeBehavior"-property (included in the JDBC URL parameter) to "convertToNull". This, however, doesn't work. I´ve tried setting it to "round" too but the Exception is thrown anyway.

My code looks something like this: 

... 
MysqlDataSource dataSource = new MysqlDataSource(); 
dataSource.setURL("jdbc:mysql://localhost:3306/databaseName?zeroDateTimeBehavior=convertToNull");
connection = dataSource.getConnection(username, password); 

Statement stmt = connection.createStatement(); 

// reg_date is of type DATETIME 
ResultSet rs = stmt.executeQuery("SELECT reg_date FROM order"); 
while(rs.next()) { 
System.out.println(rs.getString(1)); // This is where the Exception (java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 1 to TIMESTAMP) is thrown when finding a "0000-00-00 00:00:00". 
} 
... 

How to repeat:
Pass zeroDateTimeBehavior=round or zeroDateTimeBehavior=convertToNull  with JDBC URL parameter and try fetching a DATETIME field with value "0000-00-00 00:00:00".

Suggested fix:
Make sure the property zeroDateTimeBehavior is read and honored.
[16 Sep 2005 12:46] Mark Matthews
This is tested in the testsuite, as well as we have users using this feature.

What happens in either of the following situations:

Call .setZeroDateTimeBehavior("...") on the MysqlDataSource

or

Use com.mysql.jdbc.Driver.connect(), with the given URL?

I just tested this by swapping out the direct connection in our testsuite with the way you use a DataSource and it works for me, converting the dates to null as expected.
[16 Sep 2005 13:55] Gustav Oesgaard
Thanks for quick response!

The "Call .setZeroDateTimeBehavior("...") on the MysqlDataSource"-suggestion worked!

I don't know how to perform your second suggestion (com.mysql.jdbc.Driver.connect()).

I also noticed that using: 

connection = DriverManager.getConnection(url, username, password); 

with the property included in the URL worked!
[23 Sep 2005 7:42] Gustav Oesgaard
Has to be set via accessor methods on the datasource (Call .setZeroDateTimeBehavior()) or by getting connection using "DriverManager.getConnection(url, username, password);".

As I far as I understand, this wasn't a bug but a defined behavior that just wasn't documented. Will however be documented for 3.1.11.
[3 Apr 2008 14:23] Stepan Koltsov
This is a bug actually. Fix, please.
[3 Apr 2008 20:28] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44888
[30 Jul 2008 14:53] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/50724