MySQL Bugs: #44791: Setting/getting holdability on connection does not work properly

Bug #44791	Setting/getting holdability on connection does not work properly
Submitted:	11 May 2009 13:52	Modified:	23 May 2013 15:17
Reporter:	Vivekanand Bachche	Email Updates:
Status:	In progress	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.7	OS:	Windows
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
MySQL JDBC driver does not support HOLD_CURSORS_OVER_COMMIT for resultset holdability.

Setting holdability value to HOLD_CURSORS_OVER_COMMIT does not return warning. getHoldability returns CLOSE_CURSORS_AT_COMMIT even after setting it to HOLD_CURSORS_OVER_COMMIT.

If driver does not implement an attribute for a certain value, then it should return a warning indicating a default/fallback value is used for that attribute so that it does not mislead the JDBC applications. Not returning such warning might cause some applications behave unexpectedly.

"Ideally" applications should-
1. Call supportsResultSetHoldability and perform further steps accordingly.
2. Or it should confirm the value set by calling getHoldability again.

Similarly,
"Ideally" driver should return a warning if it does not support a value option for an attribute and uses defalut value.

How to repeat:
1. Create a connection with a DB.
2. Call connection.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT). This call neither sets holdability to HOLD_CURSORS_OVER_COMMIT nor it returns a warning indicating default value is used.
3. Retrive holdability with connection.getHoldability(). The value returned is ResultSet.CLOSE_CURSORS_AT_COMMIT.

Verified just as described with following test case:
	public void testBug44791() throws Exception {
    	try {
    		System.out.println("java.vm.version         : " + System.getProperty("java.vm.version"));
    		System.out.println("java.vm.vendor          : " + System.getProperty("java.vm.vendor"));
    		System.out.println("java.runtime.version    : " + System.getProperty("java.runtime.version"));
    		System.out.println("os                      : " + System.getProperty("os.name") + ", " + System.getProperty("os.version") + ", " + System.getProperty("os.arch"));
    		System.out.println("sun.management.compiler : " + System.getProperty("sun.management.compiler"));
    		System.out.println("-------------------------------------------------");
    		
    		try {
    			int oldholdconn = this.conn.getHoldability();
    			this.conn.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT); //1
    			int newholdconn = this.conn.getHoldability();
    			//System.out.println("Old: " + oldholdconn + ", New: " + newholdconn); Old: 2, New: 2
    			assertTrue(oldholdconn != newholdconn);
    			
    		} finally {
    			closeMemberJDBCResources();
    		}
        	
    	}
    	finally {
    		closeMemberJDBCResources();
    	}
	}
producing:
.Loading JDBC driver 'com.mysql.jdbc.Driver'
Done.

Done.
Connected to 5.1.31-log

java.vm.version         : 1.5.0_17-b04
java.vm.vendor          : Sun Microsystems Inc.
java.runtime.version    : 1.5.0_17-b04
os                      : Windows Server 2008, 6.0, x86
sun.management.compiler : HotSpot Client Compiler
-------------------------------------------------
Old: 2, New: 2
F
Time: 0,61
There was 1 failure:
1) testBug44791(testsuite.simple.TestBug44791)junit.framework.AssertionFailedError

Mark?

Fixing this bug involves:

1. Returning the proper answer to supportsResultSetHoldability.

2. Throwing an exception if setHoldability requests HOLD_CURSORS_OVER_COMMIT. 

3. Adding a new sqlState value "0A000" for SQL_FEATURE_NOT_SUPPORTED.

This appears to be a valid bug and the issue is in which release to fix it. Fixing it would be an incompatible change from the behavior of 5.1.12. 

While it is unlikely that any correctly running program depends on the incorrect behavior, it might be best to fix this in a minor release (not a bug fix release), i.e. in 5.2.0 and not 5.1.13.

Patch to address holdability bug

Attachment: mysql-44791.patch (application/octet-stream, text), 3.87 KiB.

Why is this not pushed?