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 9:19	Modified:	1 Oct 2006 12:33
Reporter:	Matthias Hanisch
Status:	Closed
Category:	Connector/J	Severity:	S3 (Non-critical)
Version:	5.0.3	OS:	Microsoft Windows (Windows)
Assigned to:	Tonci Grgin	Target Version:
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