MySQL Bugs: #13261: zeroDateTimeBehavior property seems to be ignored

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

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.

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.

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!

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.

This is a bug actually. Fix, please.

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

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