无法从 Java 执行 MySQL 存储过程

发布于 2024-11-15 19:10:45 字数 1914 浏览 3 评论 0原文

我正在从 tomcat 中运行的 Web 应用程序连接到在 Linux 计算机上运行的 MySQL (5.08) 数据库。

当我尝试执行存储过程时,出现以下异常:

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

在计算机上安装 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)

我已在 java 类中

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

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

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

有人可以告诉我这里出了什么问题吗?有什么需要我检查的吗?

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?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

情深已缘浅 2024-11-22 19:10:45

有两种方法可以解决此问题:

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

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

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

    然后,JDBC 驱动程序将为参数创建“INOUT”字符串,而无需像异常所述那样需要元数据。

  2. 向数据库用户授予对 mysql.procSELECT 权限

    例如在mysql提示符中:

    将 mysql.proc 上的选择授予 'user'@'localhost';
    

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

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).

迟到的我 2024-11-22 19:10:45

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

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

Running below queries should fix your issue.

GRANT <SELECT, CREATE, UPDATE, DELETE, ALL> PRIVILEGES ON mysql.proc TO '<user>'@'%';
FLUSH PRIVILEGES;
岁月静好 2024-11-22 19:10:45

以下步骤在 mysql 中对我有用

第 1 步:添加 Follwong
连接 con = DriverManager.getConnection("jdbc:mysql://ipaddress:3306/logparser?noAccessToProcedureBodies = true ",
“根”, ””);

步骤2:
logparser.proc 上的所有内容授予 root@'%';其中 logparser 是数据库名称,proc 是过程名称。

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.

救星 2024-11-22 19:10:45

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

从 mysql.proc 中选择*;

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

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'.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文