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:Microsoft Windows
Assigned to: Assigned Account CPU Architecture:Any
Triage: D3 (Medium)

[11 May 2009 13:52] Vivekanand Bachche
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.
[19 Aug 2009 7:14] Tonci Grgin
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?
[17 Jun 2010 15:51] Craig Russell
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.
[17 Jun 2010 15:51] Craig Russell
Patch to address holdability bug

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

[24 Aug 2010 13:07] Tonci Grgin
Why is this not pushed?