MySQL過(guò)程報(bào)ParameternumberNisnotanOUTparameter錯(cuò)誤

坑,絕對(duì)的大坑

今天上線新模塊,昨天測(cè)試通過(guò)的代碼,居然在線上報(bào)錯(cuò).
報(bào)錯(cuò)信息模擬如下:
MySQL過(guò)程報(bào) Parameter number N is not an OUT parameter錯(cuò)誤

納尼?
第一反應(yīng)是程序哪里寫(xiě)錯(cuò)了
大家查了一上午,問(wèn)題依舊,毫無(wú)頭緒.
后來(lái)居然被本貓發(fā)現(xiàn)了問(wèn)題(頗自豪啊)
這個(gè)其實(shí)是權(quán)限不足導(dǎo)致的.

模擬問(wèn)題如下:

已經(jīng)存在一個(gè)用戶,對(duì)mvbox庫(kù)下的表,有Insert,update,select權(quán)限.
grant select,insert,update on mvbox.* to li@'localhost' identified by 'li';

新建兩個(gè)過(guò)程.

drop procedure if exists proc1;
drop procedure if exists proc2;
delimiter $$
create procedure proc1 (in para1 int , out para2 int)
begin
select para1 into para2;
end $$
create procedure proc2 (in para1 int , out para2 int)
begin
select para1 into para2;
end $$
delimiter ;

注意, 新建過(guò)程之后,沒(méi)有對(duì) li 帳號(hào)進(jìn)行授權(quán).

這時(shí)執(zhí)行程序如下

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
public class T {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
Class.forName("com.MySQL.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mvbox", "li", "li");
CallableStatement cp = conn.prepareCall("{call proc1(?,?)}");
cp.setInt(1, 1);
cp.registerOutParameter(2, Types.INTEGER);
cp.execute();
System.out.println(cp.getInt(2));
cp.close();
conn.close();
}
}

執(zhí)行之后,結(jié)果如下:
MySQL過(guò)程報(bào) Parameter number N is not an OUT parameter錯(cuò)誤

增加如下授權(quán)之后,再次執(zhí)行
grant execute on procedure mvbox.proc1 to li@'localhost' identified by 'li';

還是報(bào)錯(cuò),報(bào)錯(cuò)信息如下:
Exception in thread "main" 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:1094)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:997)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:983)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:928)
at com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(DatabaseMetaData.java:1858)
at com.mysql.jdbc.DatabaseMetaData.getProcedureOrFunctionColumns(DatabaseMetaData.java:4508)
at com.mysql.jdbc.JDBC4DatabaseMetaData.getProcedureColumns(JDBC4DatabaseMetaData.java:106)
at com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:857)
at com.mysql.jdbc.CallableStatement.<init>(CallableStatement.java:630)
at com.mysql.jdbc.JDBC4CallableStatement.<init>(JDBC4CallableStatement.java:46)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)
at com.mysql.jdbc.CallableStatement.getInstance(CallableStatement.java:524)
at com.mysql.jdbc.ConnectionImpl.parseCallableStatement(ConnectionImpl.java:4335)
at com.mysql.jdbc.ConnectionImpl.prepareCall(ConnectionImpl.java:4419)
at com.mysql.jdbc.ConnectionImpl.prepareCall(ConnectionImpl.java:4393)
at T.main(T.java:12)

這個(gè)報(bào)錯(cuò)信息就容易理解了,增加對(duì)proc表的select權(quán)限
grant select on mysql.proc to li@'localhost' identified by 'li';

再次執(zhí)行,成功??！

這時(shí)候,如果訪問(wèn)proc2 過(guò)程,則報(bào)錯(cuò)如下:（當(dāng)然會(huì)出錯(cuò),因?yàn)闆](méi)有對(duì)帳號(hào)li進(jìn)行授權(quán)?。?br />Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: execute command denied to user 'li'@'localhost' for routine 'mvbox.proc2'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)
at com.mysql.jdbc.Util.getInstance(Util.java:383)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1062)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4226)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4158)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2840)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1302)
at com.mysql.jdbc.CallableStatement.execute(CallableStatement.java:921)
at T.main(T.java:15)

但是這個(gè)報(bào)錯(cuò),明確顯示了帳號(hào)li對(duì)于過(guò)程mvbox.proc2 沒(méi)有執(zhí)行權(quán)限.這樣很容易定位解決問(wèn)題.

在什么情況下,會(huì)因?yàn)闄?quán)限不足而報(bào)Parameter number N is not an OUT parameter的錯(cuò)誤呢?

就是該帳號(hào)沒(méi)有任何execute的授權(quán),而執(zhí)行存儲(chǔ)過(guò)程的時(shí)候,就會(huì)有上述報(bào)錯(cuò).

并且僅僅是使用JDBC的途徑,如果使用MySQL 的客戶端,報(bào)錯(cuò)信息也是明確的..

MySQL過(guò)程報(bào) Parameter number N is not an OUT parameter錯(cuò)誤

MySQL JDBC的一個(gè)坑,一個(gè)專(zhuān)有大坑.

本文名稱(chēng)：MySQL過(guò)程報(bào)ParameternumberNisnotanOUTparameter錯(cuò)誤
URL分享：http://aaarwkj.com/article18/gpjsgp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站建設(shè)、定制網(wǎng)站、面包屑導(dǎo)航、網(wǎng)站收錄、網(wǎng)站營(yíng)銷(xiāo)、全網(wǎng)營(yíng)銷(xiāo)推廣

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容

欧美一级特黄大片做受成人-亚洲成人一区二区电影-激情熟女一区二区三区-日韩专区欧美专区国产专区

MySQL過(guò)程報(bào)ParameternumberNisnotanOUTparameter錯(cuò)誤