无法从 Java 执行 MySQL 存储过程

Can't execute a MySQL stored procedure from Java

提问by

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:

我按如下方式连接到数据库：

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

I have also tried the url with the noAccessToProcedureBodies=trueoption included.

我也试过noAccessToProcedureBodies=true包含选项的网址。

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

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

回答by Josef Pfleger

There are two ways to solve this:

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

1. set the connection's noAccessToProcedureBodies=trueproperty

   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 SELECTprivileges on mysql.procto 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.proctable that contains information about allstored procedures in alldatabases (including source code).

1. 设置连接的noAccessToProcedureBodies=true属性

   例如作为连接字符串的一部分：

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

   然后，JDBC 驱动程序将为参数创建“INOUT”字符串，而不需要像异常所说的元数据。

2. 授予SELECT特权mysql.proc的数据库用户

   例如在 mysql 提示中：

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

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

回答by Vijay Bhatt

Following steps worked for me in mysql

以下步骤在 mysql 中对我有用

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

第一步：添加以下
连接 con = DriverManager.getConnection("jdbc:mysql://ipaddress:3306/logparser?noAccessToProcedureBodies = true ", "root", "");

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

第 2 步：全部启用logparser。procTO root@'%'; 其中 logparser 是数据库名称，proc 是过程名称。

回答by Vishal Kumar

Running below queries should fix your issue.

运行以下查询应该可以解决您的问题。

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

回答by Akshaya

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

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

select * from mysql.proc;

从 mysql.proc 中选择 *；

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

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