MySQL Bugs: #32525: Connector/J option "noDatetimeStringSync=true" seems to be ignored

Bug #32525	Connector/J option "noDatetimeStringSync=true" seems to be ignored
Submitted:	20 Nov 2007 14:19	Modified:	12 Nov 2009 16:23
Reporter:	Chris Wilson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.5	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	0000-00-00, connector, date, datetime, ignored, j, option

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

"Datetimes with all-zero components (0000-00-00 ...) can not be represented reliably in Java... 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... 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."

The test case below attempts to use this option to retrieve a 0000-00-00 value as a string, and fails.

May be related to http://forums.mysql.com/read.php?39,124365,124365 or http://osdir.com/ml/db.mysql.java/2005-04/msg00011.html.

How to repeat:
public class SqlDatabaseTest extends TestCase
{
    public void testConnectorCannotReadZeroDateAsString() throws Exception
    {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        String dsn = "jdbc:mysql://localhost:3306/test?" +
        		"zeroDateTimeBehaviour=convertToNull&noDatetimeStringSync=true";
        Connection conn = DriverManager.getConnection(dsn, "test", "");
        
        try
        {
            PreparedStatement stmt = conn.prepareStatement(
                "CREATE TABLE temp (ID int4 not null auto_increment primary key, " +
                "tm date not null)");
            stmt.execute();
            stmt = conn.prepareStatement("INSERT INTO temp " +
            		"SET tm = \"0000-00-00\"");
            stmt.execute();
            stmt = conn.prepareStatement("SELECT tm FROM temp");
            ResultSet rs = stmt.executeQuery();
            assertTrue(rs.next());
            assertEquals("0000-00-00", rs.getString(1));
            rs.close();
            stmt.close();
        }
        finally
        {
            PreparedStatement stmt = conn.prepareStatement("DROP TABLE temp");
            stmt.execute();
        }
        
        conn.close();
    }
}

What should happen: test should pass (no exception thrown).

What does happen: getString throws an exception: 

java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date
	at com.mysql.jdbc.ResultSet.getNativeDate(ResultSet.java:3481)
	at com.mysql.jdbc.ResultSet.getNativeDate(ResultSet.java:3421)
	at com.mysql.jdbc.ResultSet.getNativeConvertToString(ResultSet.java:3369)
	at com.mysql.jdbc.ResultSet.getNativeString(ResultSet.java:4001)
	at com.mysql.jdbc.ResultSet.getStringInternal(ResultSet.java:5115)
	at com.mysql.jdbc.ResultSet.getString(ResultSet.java:4961)
	at com.qwirx.db.SqlDatabaseTest.testMysqlConnectorJIsBroken(SqlDatabaseTest.java:449)
	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:585)
	at junit.framework.TestCase.runTest(TestCase.java:154)
	at junit.framework.TestCase.runBare(TestCase.java:127)
	at junit.framework.TestResult$1.protect(TestResult.java:106)
	at junit.framework.TestResult.runProtected(TestResult.java:124)
	at junit.framework.TestResult.run(TestResult.java:109)
	at junit.framework.TestCase.run(TestCase.java:118)
	at junit.framework.TestSuite.runTest(TestSuite.java:208)
	at junit.framework.TestSuite.run(TestSuite.java:203)
	at org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:130)
	at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)

Test case on latest c/J 5.1 sources

Attachment: TestBug32525.java (text/x-java), 1.85 KiB.

Hi Chris and thanks for your report. I can not repeat it with test case attached so until you provide me with more info there is not much I can do. Details on JVM and connection string are inside test case.

Attached test case output:
.Loading JDBC driver 'com.mysql.jdbc.Driver'
Done.

Done.

Connected to 5.0.50-pb1046-log
java.vm.version         : 1.5.0_12-b04
java.vm.vendor          : Sun Microsystems Inc.
java.runtime.version    : 1.5.0_12-b04
os.name                 : Windows XP (Pro SP2)
os.version              : null
sun.management.compiler : HotSpot Client Compiler

badDateTime = 0000-00-00

Time: 0,297

OK (1 test)

MySQL server general query log:
071120 16:16:35	     18 Connect     root@localhost on test
		     18 Query       /* mysql-connector-java-5.1.6 ( Revision: ${svn.Revision} ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'
		     18 Query       SHOW COLLATION
		     18 Query       SET character_set_results = NULL
		     18 Query       SET autocommit=1
		     18 Query       SET sql_mode='STRICT_TRANS_TABLES'
		     18 Query       SELECT VERSION()
		     18 Query       DROP TABLE IF EXISTS bug32525
		     18 Query       CREATE  TABLE bug32525 (ID int4 not null auto_increment primary key, tm date not null)
		     18 Query       INSERT INTO bug32525 SET tm = "0000-00-00"
		     18 Query       SELECT * FROM bug32525
		     18 Query       DROP TABLE IF EXISTS bug32525
		     18 Quit

How can I run this test case? i don't have the BaseTestCase class on which it depends.

Tonci, Chris,

It seems that this would only occur if one is using server-side prepared statements. 

Chris, a potential work-around for the moment is for you to disable server-side prepared statements (newer versions of the driver have them disabled by default, 5.1.5 *should* have them disabled by default, so are they enabled explicitly in your application?).

Tonci, the test case you come up with will have to have "useServerPrepStmts=true" in it for this to be reproduced (notice the getNative*() in the stack trace from ResultSet...Anything with "native" in the method name means the result set was created by a server-side prepared statement).

Thanks Mark will make necessary changes and re-verify.

Chris, BaseTestCase class is found in our sources. If you have c/J sources put my test case in "testsuite.simple" package and it should run.

Modified test case showing the problem

Attachment: TestBug32525.java (text/java), 1.99 KiB.

Verified as described with new test case attached.

1) From what I see in code (ResultSetImpl.java 3732, case Types.TIMESTAMP:), only TIMESTAMP values pay attention to "noDatetimeStringSync" option. If it was intentional then it should be more clearly documented.
2) mysql cl. client test shows no deviation in metadata/field values between ordinary and prepared statements.

Hi Mark,

Thanks, I can confirm that useServerPrepStmts=false fixes the problem. However they must be enabled by default as I did not explicitly enable them. The code I used was exactly as in the test case which I attached, which crashes for me with 5.1.5 and does not enable prepared statements.

Thanks for the workaround though!

Cheers, Chris.

Hi Mark,

Sorry, my mistake, I was testing with 3.1.11 and server-side prepared statements were enabled by default. I can confirm that this does not happen with 5.1.5 and default options.

Cheers, Chris.

Given how server-side prepared statements are implemented, and their depedency on java.util.Date for now, we're not able to fix this for server-side prepared statements. The functionality works for client-side prepared statements (which are default).

I'm sorry, but I don't understand why MySQL refuses to fix this bug?

This statement doesn't make sense to me: "Given ... their dependency on java.util.Date for now, we're not able to fix this for server-side prepared statements."

Surely it is perfectly possible to fix this bug: just stop depending on java.util.Date, which cannot represent all date values that can be stored in a SQL database and is therefore, in my view, a bad choice (even String would be better).

The problem is surely not on the server side, as java.util.Date does not exist in MySQL server. It must be in the connector, and fixable there.

Server-side prepared statements are pretty useful (much better performance for repeated queries than client-side), and to not be able to use them because of a serious problem with date support is a serious limitation with Connector/J.

When one isn't using server-side prepared statements, values are sent from server to client as strings, no matter what type they really are...Hence it's straightforward for the driver to just treat it as a string.

When server-side prepared statements are in use, there are data types at play, and the representations at play are not strings, they're binary. It's not easy to just make this work, given that type conversions have to happen and in general those go through java.util.Date, and then .toString(). 

Given that the usage of all-zero dates in MySQL is a Java application is dubious at best (i.e. they can only be treated as strings, there is no date-handling code in Java or 3rd-party libraries that can deal with such values), it's my opinion that to overhaul the entire code path of server-side prepared statement date and timestamp handling to deal with this is not the best idea given that there are many other options, including storing the value as a string to begin with and performing the type conversion from string->date[time]/timestamp], using a correct data model that doesn't require 'sentinel' values like all-zero datetimes, or using client-side prepared statements and their ability to treat this data as both strings and datetime at the same time.

Feel free to add some use cases here to convince me otherwise, or send in a patch that does this in a clean way, and we'll consider this further.

To clarify the usage of java.util.Date and friends, the whole JDBC api *assumes* that if a value has a SQL type of DATE, DATETIME, TIME or TIMESTAMP that they are forwards/backwards convertible to java.sql.Date and java.sql.Timestamp, because these are the time-related types of the API. Unfortunately these types are not interfaces, and much 3rd party code depends on their implementations, so it's not as simple as putting together a java.sql.Date/Time/Timestamp class that understands all-zero dates just for MySQL.

There is also no clean solution that involves unpacking the date/time values from server-side prepared statements into a String first, and then re-parsing it, given that it's very expensive to do so, and it's the most un-common use case. Most applications want fast, native access to their datetime data, so adding special casing to the driver very well may cause performance and memory impact to the large majority of users who don't use all-zero dates at all.

I do see one potential way to implement this clean, but it won't have great performance if the majority of values are all-zero dates. The driver throws an exception when it encounters such values, instead of bubbling this up, if noDatetimeStringSync is set to true, the driver could return the appropriate representation to the calling user.

Ignore my ranting. Now fixed for 5.1.11. Although still on record that all-zero date time values are about the silliest things we ever put into MySQL.

Thanks Mark :)

An entry has been added to the 5.1.11 changelog:

With the connection string option noDatetimeStringSync set to true, and server-side prepared statements enabled, the following exception was generated if an attempt was made to obtain, using ResultSet.getString(), a datetime value containing all zero components:

java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date