34

I am connecting to a MySQL (5.08) database running on a linux machine from a web application running in tomcat.

I get the following exception when I try to execute a stored procedure:

com.hp.hpl.chaos.web.exception.DBException: getNextValue for operatorinstance[Additional Information from SQL Exception][SQLErrorCode: 0 SQLState: S1000
    at com.hp.hpl.chaos.web.util.SQLUtil.getNextValue(SQLUtil.java:207)
        ..............

Caused by: java.sql.SQLException: User does not have access to metadata required to determine stored procedure parameter types. If rights can not be granted, configure connection with "noAccessToProcedureBodies=true" to have driver generate parameters that represent INOUT strings irregardless of actual parameter types.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
at com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(DatabaseMetaData.java:1619)
at com.mysql.jdbc.DatabaseMetaData.getProcedureColumns(DatabaseMetaData.java:4034)
at com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:709)
at com.mysql.jdbc.CallableStatement.<init>(CallableStatement.java:513)
at com.mysql.jdbc.Connection.parseCallableStatement(Connection.java:4583)
at com.mysql.jdbc.Connection.prepareCall(Connection.java:4657)
at com.mysql.jdbc.Connection.prepareCall(Connection.java:4631)
at com.hp.hpl.chaos.web.util.SQLUtil.getNextValue(SQLUtil.java:196)
... 17 more

After installing mysql on the machine. I have given the follwing grant options to root

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'pass';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

In the java class..

I am connecting to the db as follows:

String url = "jdbc:mysql://ipaddress:3306/test";
                con = DriverManager.getConnection(url,"root", "pass");

I have also tried the url with the noAccessToProcedureBodies=true option included.

Can someone tell me what is wrong here? Is there anything I need to check?

Mat
  • 202,337
  • 40
  • 393
  • 406
  • sorry the grant should read *.* instead of . –  Jun 12 '09 at 13:26
  • I thinking that MySQL's GRANTs don't always work as you might expect. I've sometimes found that things work if you use specific GRANT permissions rather than *.* – skaffman Jun 12 '09 at 13:29

4 Answers4

77

There are two ways to solve this:

  1. set the connection's noAccessToProcedureBodies=true property

    For example as part of the connection string:

    jdbc:mysql://ipaddress:3306/test?noAccessToProcedureBodies=true

    The JDBC driver will then create "INOUT" strings for the arguments without requiring meta data like the exception says.

  2. Grant SELECT privileges on mysql.proc to the database user

    For example in the mysql prompt:

    GRANT SELECT ON mysql.proc TO 'user'@'localhost';

    Of course this would allow the application to read the entire mysql.proc table that contains information about all stored procedures in all databases (including source code).

Josef Pfleger
  • 74,165
  • 16
  • 97
  • 99
  • 4
    This (and http://stackoverflow.com/q/1880461/7708) are examples of what makes me dislike using MySQL. – Alan Krueger Apr 20 '12 at 21:01
  • 1
    @Kush: In my particular experience the Connector/J 5.1.12 seems not aware of the noAccessToProcedureBodies parameter; the latest (currently) 5.1.x version, the 5.1.26 one, is aware of it. Hope this helps. – Giorgio Vespucci Aug 08 '13 at 12:00
  • First option works for me. I'm using JDBC MySQL connector driver, 5.1.30 version – yaki_nuka Feb 22 '16 at 14:23
  • 1
    Instead of granting on "mysql.proc" why not grant on "schema.proc" where schema is your db schema? – Jose Oct 26 '16 at 14:18
  • if you access mysql server from remote host use GRANT SELECT ON mysql.proc TO 'user'@'%'; – WARRW Apr 07 '18 at 03:33
  • Why is not enough to establish grant all privileges on test.* to user@localhost in order to execute the procedure? The 'all privileges' includes EXECUTE for procedures, right? – user3637971 Sep 06 '22 at 16:11
3

Running below queries should fix your issue.

GRANT <SELECT, CREATE, UPDATE, DELETE, ALL> PRIVILEGES ON mysql.proc TO '<user>'@'%';
FLUSH PRIVILEGES;
Vishal Kumar
  • 111
  • 1
  • 2
2

Following steps worked for me in mysql

Step 1 : add follwong
Connection con = DriverManager.getConnection("jdbc:mysql://ipaddress:3306/logparser?noAccessToProcedureBodies = true ", "root", "");

Step 2 : GRANT ALL ON logparser.proc TO root@'%'; where logparser is DB name and proc is procedure name.

Vijay Bhatt
  • 1,351
  • 13
  • 13
0

You can change the definer in the mysql.proc table to your database user.

select * from mysql.proc;

choose the stored proc that has problem and change the definer to 'yourdbuser'@'localhost'.

Akshaya
  • 1
  • 1