MySQL Bugs: #22297: JDBC driver has different behaviour for PreparedStatement and CallableStatement

Bug #22297	JDBC driver has different behaviour for PreparedStatement and CallableStatement
Submitted:	13 Sep 2006 7:19	Modified:	1 Oct 2006 10:33
Reporter:	Matthias Hanisch	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.0.3	OS:	Windows (Windows)
Assigned to:		CPU Architecture:	Any
Tags:	CallableStatement, PreparedStatement, stored procedure

Description:
I've encountered a problem executing a stored procedure with a PreparedStatement. In my application I make no difference between different drivers because I want it to be compatible to most drivers.
 Therefore a stored procedure is executed in the following way:

PreparedStatement pstmt=con.prepareStatement("{call sp_test(?)}");
pstmt.setInt(1,1);
ResultSet rs=pstmt.executeQuery();

The problem is that this call does not return the data as expected.

The expected result would be the following:

first column "dbd_test_reports2"=Kuma3108060000020402	second column (without name)=100	
first column "dbd_test_reports2"=McDe3108060000020403	second column (without name)=100	
first column "dbd_test_reports2"=Gate3108060000020404	second column (without name)=100	
first column "dbd_test_reports2"=Jone3108060000020405	second column (without name)=100	
dbd_test_reports2=Bast3108060000020406	second column (without name)=100

The returned data instead looks like this:

first column "dbd_test_reports2"=ma3108060000020402100ZEROüÅCREATE PROCEDURE `sp_test`(pcaseid INT)BEGIN

SET @sql = "DROP TEMPORARY TABLE I	second column(without name)=null

The problem is also that if I use a CallableStatement instead of the PreparedStatement I get the result as expected.

Perhaps the problem is also that I did not use the default charset for the database where the tables/stored procedures are defined. The charset was "latin1" and the table collation "latin1_swedish_ci".

So my question is why there is a difference in using a PreparedStatement for a stored procedure instead of a CallableStatement. I did test the behaviour with other drivers (for other databases) and there a PreparedStatement did behave like a CallableStatement.

The MySql database used is 5.0.18-nt.

How to repeat:
- create a database with charset "latin1" and default table collation "latin1_swedish_ci"
- create a table with some data
- create a stored procedure which returnes data from the table created above
- write a small java program which does the following
 * calls the stored procedure using a CallableStatement (which returns data as expected)
 * calls the stored procedure using a PreparedStatement (which returnes scrambled data)

Suggested fix:
I think that there are two possibilities depending on the cause of the problem.

1st) if the PreparedStatement implementation does not care about charset/collation settings then this should be fixed

2nd) if there is a huge difference between using PreparedStatement or CallableStatement perhaps some implementation could be moved to PreparedStatement as it should be the super class of the CallableStatement implementation

Sql scipt of the database (including tables and the sp)

Attachment: sk.sql (application/octet-stream, text), 26.82 KiB.

Source of Java app to reproduce the error

Attachment: Ticket25381.java (application/java, text), 2.42 KiB.

Screenshot of the run of the Java test app with the different results

Attachment: mysql_bug.gif (image/gif, text), 12.67 KiB.

Hi Matthias and thanks for your complete problem report. I'm sorry I don't see it as bug:

MySQL Connector/J docs:
CallableStatement
Starting with Connector/J 3.1.1, stored procedures are supported when connecting to MySQL version 5.0 or newer via the CallableStatement interface. Currently, the getParameter-MetaData() method of CallableStatement is not supported.
Connector/J exposes stored procedure functionality through JDBC's CallableStatement interface.

Reviewing Java specs you can find that in your case PreparedStatement interface is not appropriate but one should use it's subinterface, CallableStatement.
http://java.sun.com/j2se/1.5.0/docs/api/java/sql/CallableStatement.html
The interface used to execute SQL stored procedures. The JDBC API provides a stored procedure SQL escape syntax that allows stored procedures to be called in a standard way for all RDBMSs. This escape syntax has one form that includes a result parameter and one that does not. If used, the result parameter must be registered as an OUT parameter. The other parameters can be used for input, output or both. Parameters are referred to sequentially, by number, with the first parameter being 1.

Hi Tonci,

it's a pity. From my point of view it is a bug. If you only support executing stored procedures with the CallableStatement you should throw an Exception when using a PreparedStatement. 
 Perhaps I can tell you a bit more about me. I'm developer of i-net Software GmbH, Berlin, Germany. We develop a Java reporting tool and support a lot of JDBC driver for different databases (indeed we also develop own JDBC driver for the main databases). MySQL is the only JDBC driver which does behave that way. All other drivers can also handle stored procedures also with a PreparedStatement. And I think your driver can too. The result is returned. The only thing is that while reading the result the has been a mistake so that scrammbled data is returned.
 Of course CallableStatement is a special interface for stored procedures. But as long as you don't use output parameters a PreparedStatement should work too.
 I don't think that it is a big change to your code to get it work but I would strongly recommend one of these solutions:

- throw an Exception when a stored procedure is used with a PreparedStatement (as there is no indication that the returned data might not be correct)
- fix the "bug" so that the right data is returned using a PreparedStatement too

Last but not least I want to thank you that you analyzed the problem just within some days. I think you have a great support.

Best regards

Matthias, I said it's my opinion and mine alone. I will consult with colleagues regarding this.

I'm leaning towards it not being a C/J bug, but instead a server bug that either overwrites memory (in your case), or crashes the server (for me). Here's a simplified testcase (using our framework), if I use prepareCall(), which uses client-side emulated prepared statements for the implementation, the testcase runs. If I use prepareStatement() (and haven't disable server-side prepared statements), MySQL-5.0.26 actually crashes for me.

	public void testBug22297() throws Exception {
		if (versionMeetsMinimum(5, 0)) {
			this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug22297");
			
			createTable("tblTestBug2297_1", "("
					+ "id varchar(20) NOT NULL default '',"
					+ "Income double(19,2) default NULL)");	
			
			createTable("tblTestBug2297_2", "("
					+ "id varchar(20) NOT NULL default ''," 
					+ "CreatedOn datetime default NULL)");
			
			this.stmt.executeUpdate("CREATE PROCEDURE testBug22297(pcaseid INT)"
					+ "BEGIN"
					+ "\nSET @sql = \"DROP TEMPORARY TABLE IF EXISTS tmpOrders\";"
					+ " PREPARE stmt FROM @sql;"
					+ " EXECUTE stmt;"
					+ " DEALLOCATE PREPARE stmt;"
					+ "\nSET @sql = \"CREATE TEMPORARY TABLE tmpOrders SELECT id, 100 AS Income FROM tblTestBug2297_1 GROUP BY id\";"
					+ " PREPARE stmt FROM @sql;"
					+ " EXECUTE stmt;"
					+ " DEALLOCATE PREPARE stmt;"
					+ "\n SELECT id, Income FROM (SELECT e.id AS id ,COALESCE(prof.Income,0) AS Income"
					+ "\n FROM tblTestBug2297_2 e LEFT JOIN tmpOrders prof ON e.id = prof.id"
					+ "\n WHERE e.CreatedOn > '2006-08-01') AS Final;" 
					+ "\nEND");

			this.stmt.executeUpdate("INSERT INTO tblTestBug2297_1 (`id`,`Income`) VALUES "
					+ "('a',4094.00),"
					+ "('b',500.00),"
					+ "('c',3462.17),"
					+ " ('d',500.00),"
					+ " ('e',600.00)");
			
			this.stmt.executeUpdate("INSERT INTO tblTestBug2297_2 (`id`,`CreatedOn`) VALUES "
					+ "('d','2006-08-31 00:00:00'),"
					+ "('e','2006-08-31 00:00:00'),"
				+ "('b','2006-08-31 00:00:00'),"
				+ "('c','2006-08-31 00:00:00'),"
				+ "('a','2006-08-31 00:00:00')");
			
			try {
				this.pstmt = this.conn.prepareCall("{CALL testBug22297(?)}");
				this.pstmt.setInt(1, 1);
				this.rs =this.pstmt.executeQuery();
                ResultSetMetaData rsmd= this.rs.getMetaData();
                int columnCount=rsmd.getColumnCount();
                while(this.rs.next()) {
                    for(int i=0;i<columnCount;i++) {
                        System.out.print(rsmd.getColumnName(i+1)+"="+rs.getObject(i+1));
                        System.out.print('\t');
                    }
                    System.out.println();
                }

			} finally {
				closeMemberJDBCResources();
			}
		}
	}

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/12051

My prior comment should be taken to mean that "It should just work", but doesn't because of what I propose is a server bug.

Notice that the patch listed is a workaround for the bug, where the JDBC driver will detect Connection.prepareStatement("{[?=] CALL(...)}"), and substitute a client-side prepared statement (which is what normal CallableStatements extend too) to avoid the execution path which is buggy.

This should still be analyzed further to determine why the server either crashes or overwrites data returned to the client.

Matthias, can you please try with latest snapshot of connector/J and post your findings?

Hi Tonci,

with the provided workaround I get the data as expected. So for me this solution works. But I think there should be some investigations why this crashed Marks MySQL database.

Hi Matthias. Of course there's discussion on this but through our internal channels. If we can prove the crash to be a bug we'll open new bug report. So far, I was unable to reproduce the crash Mark reported.

Continued in BUG#22870

There is a change in Connector/J 5.0.4 to work around this bug. More details are available in the C/J 5.0.4 changelog at http://dev.mysql.com/doc/refman/5.0/en/cj-news-5-0-4.html

Fixed in server (see Bug#22870) and it works with c/J 5.0 latest sources:
Loading JDBC driver 'com.mysql.jdbc.Driver'
Done.

Done.

Connected to 5.0.48-pb1030-log
id=d	Income=100	
id=e	Income=100	
id=b	Income=100	
id=c	Income=100	
id=a	Income=100