无法从Java执行MySQL存储过程

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

我正在从运行在tomcat中的Web应用程序连接到Linux机器上运行的MySQL（5.08）数据库。

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之后。我已经给了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..

在java类中..

I am connecting to the db as follows:

我按如下方式连接到db：

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.

我还尝试了包含noAccessToProcedureBodies = true选项的url。

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

谁能告诉我这里有什么问题？有什么我需要检查的吗？

4 个解决方案

#1

There are two ways to solve this:

有两种方法可以解决这个问题：

set the connection's noAccessToProcedureBodies=true property

设置连接的noAccessToProcedureBodies = true属性

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.

然后，JDBC驱动程序将为参数创建“INOUT”字符串，而不需要像异常所说的元数据。
Grant SELECT privileges on mysql.proc to the database user

将mysql.proc的SELECT权限授予数据库用户

For example in the mysql prompt:

例如在mysql提示符中：
```
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).

当然，这将允许应用程序读取整个mysql.proc表，该表包含有关所有数据库（包括源代码）中所有存储过程的信息。

#2

Following steps worked for me in mysql

以下步骤在mysql中为我工作

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

步骤1：添加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.

第2步：GRANT ALL ON logparser.proc TO root @'％'; logparser是DB名称，proc是过程名称。

#3

Running below queries should fix your issue.

在查询下运行应解决您的问题。

GRANT <SELECT, CREATE, UPDATE, DELETE, ALL> PRIVILEGES ON mysql.proc TO '<user>'@'%';
FLUSH PRIVILEGES;

#4

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

您可以将mysql.proc表中的定义器更改为数据库用户。

select * from mysql.proc;

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

选择存在问题的存储过程并将定义器更改为'yourdbuser'@'localhost'。

#1