MySQL Bugs: #57022: cannot execute a store procedure with output parameters

Bug #57022	cannot execute a store procedure with output parameters
Submitted:	25 Sep 2010 19:12	Modified:	13 Oct 2010 11:06
Reporter:	Alexander Klepinin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.13	OS:	Any
Assigned to:	Tony Bedford	CPU Architecture:	Any

Description:
Once again there is a problem with invocation of stored procedures containing output parameters. According to bug #56095, it was fixed, but looks like the fix was incomplete.

The problem occurs when one tries to invoke stored procedure located in another database (and thus referenced by full name) and containing output parameters. Such invocation results with exception like the following:
'Parameter index of 1 is out of range (1, 0)'

Note: the problem does not appear with 5.1.8 version of Connector/J, but it seems, that it is just somehow hidden.

How to repeat:
Steps to reproduce the problem:
1. Take clean instance of MySQL server (I used MySQL community edition mysql-5.1.37-win32; anyway, IMHO, server version does not matter)

2. Execute the following SQL:
--------------
CREATE DATABASE IF NOT EXISTS `TEST1`;
CREATE DATABASE IF NOT EXISTS `TEST2`;
delimiter //
create procedure `TEST1`.`PROC`(IN val INT, OUT outval INT)
BEGIN
  set outval = val + 1;
END//
delimiter ;
--------------

3. Check that procedure can be invoked:
--------------
use `TEST2`;
call `TEST1`.`PROC`(1,@val);
select @val;
--------------

Value of @val should be printed as 2. It is correct.

4. Now from Java code try to perform the following (I intentionally omit some code to simplify description; I can send complete java source to reproduce the problem):
        String strDriver = "com.mysql.jdbc.Driver";
        String strURL = "jdbc:mysql://localhost:3306/mysql";
        String strUserID = "root";
        String strPassword = "";

        Class.forName(strDriver).newInstance();
        Connection conn = DriverManager.getConnection(strURL, strUserID, strPassword);
        CallableStatement stmt = conn.prepareCall("call `TEST1`.`PROC`(1, ?)");
        stmt.registerOutParameter(1, Types.INTEGER);
        stmt.execute();
        int iResult = stmt.getInt(1);
        System.out.println("Returned value is:"+iResult);

This code results with mentioned above message:
'Parameter index of 1 is out of range (1, 0)'

Suggested fix:
I looked through sources of Connector/J 5.1.13. 
In com.mysql.jdbc.CallableStatement there is determineParameterTypes() method which contains the following code:

            String procName = extractProcedureName();

            java.sql.DatabaseMetaData dbmd = this.connection.getMetaData();

            boolean useCatalog = false;

            if (procName.indexOf(".") == -1) {
                useCatalog = true;
            }

            paramTypesRs = dbmd.getProcedureColumns(this.connection
                    .versionMeetsMinimum(5, 0, 2)
                    && useCatalog ? this.currentCatalog : null, null, procName,
                    "%"); //$NON-NLS-1$

In my scenario this code fails in the following manner:
1. result of extractProcedureName() is a full name of procedure, so it can contain both catalog name and procedure name. Moreover, both components can be enquoted. So it can not be just checked for presence of dot character to determine presence of catalog (dot can be a part of procedure name itself).
2. call to dbmd.getProcedureColumns() does not expect to get full name of procedure on input (it expects different parts of the name be passed via separate parameters), so it (accordingly) returns empty resultset (and it leads to absence of information about output parameters which is reported later on attempt to register output parameter).

So to fix the problem this code should be rewritten to parse full procedure name into parts.

Hi Alexander and thanks for your report.

Bug#56095 was closed, without test case, by the *reporter*, not me so it has nothing to do with your report.

From the first look, the problem is in this.Database() ("this" being the connection) which refers to "mysql" which has no such function as you created it in TEST database.

Will check on this.

Hi Tonci,

First of all, thanks for quick reply!

> Bug#56095 was closed, without test case, by the *reporter*, not me so it has 
> nothing to do with your report.

Ok. I just did my best to make sure that I do not submit bug report for known problem.

> From the first look, the problem is in this.Database() ("this" being the 
> connection) which refers to "mysql" which has no such function as you created 
> it in TEST database.

I would not think that the problem is in this.Database(), because even if you change test scenario to use database TEST1 at the moment of invocation of the procedure, the call still fails. The origin of the problem stays the same: when code tries to get information on stored procedure parameters it does not parse procedure name correctly. 

So, in fact, there are different ways to reproduce this very bug:
1. use full name to refer to procedure. Here call fails, because name is not split into parts before call to DatabaseMetaData.getProcedureColumns.
2. use quoted name (like `PROC`). Here call fails because at the same very place quotes are not removed before call to DatabaseMetaData.getProcedureColumns.
3. use quoted name, containing dot inside. Here call fails because call to DatabaseMetaData.getProcedureColumns will look procedure in current database (due to detected presence of dot).

Concluding, I do not want to interfere with normal work of MySQL development team, but my experisnce (I am also developer, and in my company I am responsible for working with different databases in Java) tells me that the problem is in incorrect processing of full (quoted, dotted) identifier. And definitely I would be glad to assist you in solving this problem.
--
Sincerely,
 Alexander

Alexander, sorry for being late on this but I do not feel all that well and this is deep in code I "messed" with for past few months. I'll try checking this week.

Alexander, when using FUNCTIONS I can not reproduce the problem. Pseudo code:
  Attach root user to database 1 (test in this case)
  Drop/create database `TST1`
  Drop/create fuunction `TST1`.`PROC`
  CALL `TST1`.`PROC`

Actual code:
         this.stmt = this.conn.createStatement();
         this.stmt.executeUpdate("DROP DATABASE IF EXISTS TST1;"); 
         this.stmt.executeUpdate("CREATE DATABASE TST1;"); 
         this.stmt.executeUpdate("DROP FUNCTION IF EXISTS TST1.PROC;"); 
         this.stmt.executeUpdate("CREATE FUNCTION TST1.PROC(a float, b bigint, c int) RETURNS INT NO SQL"
			+ "\nBEGIN" + "\nRETURN a;" + "\nEND");
         this.stmt.close();
			         
         CallableStatement cStmt = null;
	 cStmt = this.conn.prepareCall("{? = CALL `TST1`.`PROC`(?,?,?)}");
	 cStmt.registerOutParameter(1, Types.INTEGER);
	 cStmt.setFloat(2, 2);
	 cStmt.setInt(3, 1);
	 cStmt.setInt(4, 1);
  
	 cStmt.execute();
	 assertEquals(2f, cStmt.getInt(1), .001);
	 System.out.println("Value should be 2 " + cStmt.getString(1));
	 assertEquals("java.lang.Integer", cStmt.getObject(1).getClass().getName());

	cStmt.close();

Of course, I tested with other names and w/wo backticks, works.
101004 15:41:03    7 Connect	root@192.168.1.4 on test
		    7 Query	SHOW COLLATION
		    7 Query	SET character_set_results = NULL
		    7 Query	SET autocommit=1
		    7 Query	SET sql_mode='STRICT_TRANS_TABLES'
		    7 Query	SELECT VERSION()
		    7 Query	DROP DATABASE IF EXISTS TST1
		    7 Query	CREATE DATABASE TST1
		    7 Query	DROP FUNCTION IF EXISTS TST1.PROC
		    7 Query	CREATE FUNCTION TST1.PROC(a float, b bigint, c int) RETURNS INT NO SQL
BEGIN
RETURN a;
END
		    7 Query	SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE 'PARAMETERS'
		    7 Query	SELECT name, type, comment FROM mysql.proc WHERE name like 'PROC' and db <=> 'test' ORDER BY name
		    7 Query	SELECT `TST1`.`PROC`(2.0,1,1)
		    7 Query	SELECT @com_mysql_jdbc_outparam_0
		    7 Quit	

When using PROCEDURES, I get the problem reported, and it is where I told you it will be, in this.database() (root is attached to test in url):
 13 Query	SELECT name, type, comment FROM mysql.proc WHERE name like 'PROC' and db <=> 'test' ORDER BY name

Fixing.

As a temporary workaround, please use &noAccessToProcedureBodies=true in your JDBC url.

The fix will be to "sanitize" db.sp call just like in last patch for noAccessToProcedureBodies.

I have a working patch for this situation but it will require more testing on corner cases to make sure it does not need rewriting any time soon.

Preliminary patch, not fully tested yet!

Attachment: bug57022.diff (text/x-diff), 2.83 KiB.

Final patch sent for review.

Pushed up to revision 984.

Added an entry to 5.1.14 changelog:

Invoking a stored procedure containing output parameters by its full name, where the procedure was located in another database, generated the following exception:

Parameter index of 1 is out of range (1, 0)

I used mysql connector/j version 5.1.11 and did not have this problem. The code remaining exactly the same and none of the other project dependencies changing, the code threw an exception around this error as soon as I started using driver version 5.1.13. What seems more surprising is that this concerns a stored procedure in the same database and not even another database!

I hope this is researched more and fixed completely.

Thanks.
Ashwin Raj