Bug #20694 Java

• Submitted: 26 Jun 2006 13:01
• Modified: 1 Jul 2006 19:14
• Reporter: Johnny H
• Status: Can't repeat
• Category: Connector / J
• Severity: S2 (Serious)
• Version: 3.1.13
• OS: Any (Any)
• Assigned to: Sveta Smirnova
• CPU Architecture: Any

[26 Jun 2006 13:01] Johnny H

Description:
Users with basic permissions (SELECT, INSERT, DELETE) cannot run a stored procedure because the DatabaseMetaData.getCallStmtParameterTypes method throws the following exception:

Caused by: java.sql.SQLException: Driver requires declaration of procedure to either contain a '\nbegin' or '\n' to follow argument declaration, or SELECT privilege on mysql.proc to parse column types.
	at com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(DatabaseMetaData.java:1323)
	at com.mysql.jdbc.DatabaseMetaData.getProcedureColumns(DatabaseMetaData.java:3640)
	at com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:560)
	at com.mysql.jdbc.CallableStatement.<init>(CallableStatement.java:450)
(...)

What's happening is that when that method runs this code:

	try {
				paramRetrievalRs = paramRetrievalStmt
						.executeQuery("SHOW CREATE PROCEDURE "
								+ procNameBuf.toString());
				parsingFunction = false;
			} catch (SQLException sqlEx) {
				try {
					paramRetrievalRs = paramRetrievalStmt
						.executeQuery("SHOW CREATE FUNCTION "
								+ procNameBuf.toString());
					parsingFunction = true;
				} catch (SQLException ex) {
					throw sqlEx; // the original exception which made us try this in the first place...
				}
			}

			if (paramRetrievalRs.next()) {
				String procedureDef = parsingFunction ? paramRetrievalRs
						.getString("Create Function") : paramRetrievalRs
						.getString("Create Procedure");

paramRetrievalRs.next() returns an empty result.
It is related to that bug somehow where the command succeeds to retrieve an empty declaration of SP (see http://bugs.mysql.com/bug.php?id=14564)

How to repeat:
Create a SP, create a user with minimum permissions, use JDBC to call the SP with that minimal user it throws:

Caused by: java.sql.SQLException: Driver requires declaration of procedure to either contain a '\nbegin' or '\n' to follow argument declaration, or SELECT privilege on mysql.proc to parse column types.
	at com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(DatabaseMetaData.java:1323)
	at com.mysql.jdbc.DatabaseMetaData.getProcedureColumns(DatabaseMetaData.java:3640)
	at com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:560)
	at com.mysql.jdbc.CallableStatement.<init>(CallableStatement.java:450)
(...)

Suggested fix:
see suggested fix for bug <a href="http://bugs.mysql.com/bug.php?id=14564">14564</a>

[1 Jul 2006 19:14] Sveta Smirnova

Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

SELECT, INSERT, DELETE privileges are not sufficient for procedure execution: http://dev.mysql.com/doc/refman/5.0/en/stored-procedure-privileges.html

But when I try to recreate your bug as you said, I get significant error message: java.sql.SQLException: execute command denied to user 'test'@'%' for routine 'test1.p'... So I think you should provide us really information about priveleges assigned to the user.