无法从 Java 执行 MySQL 存储过程
我正在从 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 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
有两种方法可以解决此问题:
设置连接的
noAccessToProcedureBodies=true
属性例如作为连接字符串的一部分:
然后,JDBC 驱动程序将为参数创建“INOUT”字符串,而无需像异常所述那样需要元数据。
向数据库用户授予对
mysql.proc
的SELECT
权限例如在mysql提示符中:
当然,这将允许应用程序读取整个
mysql.proc
表,其中包含有关所有数据库中所有存储过程的信息(包括源代码)。There are two ways to solve this:
set the connection's
noAccessToProcedureBodies=true
propertyFor example as part of the connection string:
The JDBC driver will then create "INOUT" strings for the arguments without requiring meta data like the exception says.
Grant
SELECT
privileges onmysql.proc
to the database userFor example in the mysql prompt:
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).运行以下查询应该可以解决您的问题。
Running below queries should fix your issue.
以下步骤在 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.您可以将 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'.