MySQL過程報 Parameter number N is not an OUT parameter錯誤

壹頁書發表於2017-03-17
MySql
坑,絕對的大坑

今天上線新模組,昨天測試通過的程式碼,居然線上上報錯.
報錯資訊模擬如下:


納尼?
第一反應是程式哪裡寫錯了
大家查了一上午,問題依舊,毫無頭緒.
後來居然被本貓發現了問題(頗自豪啊)
這個其實是許可權不足導致的.

模擬問題如下:

已經存在一個使用者,對mvbox庫下的表,有Insert,update,select許可權.
grant select,insert,update on mvbox.* to li@'localhost' identified by 'li';

新建兩個過程.

  1. drop procedure if exists proc1;
  2. drop procedure if exists proc2;

  4. delimiter $$

  6. create procedure proc1 (in para1 int , out para2 int)
  7. begin
  8.     select para1 into para2;
  9. end $$

  11. create procedure proc2 (in para1 int , out para2 int)
  12. begin
  13.     select para1 into para2;
  14. end $$
  15. delimiter ;
注意, 新建過程之後,沒有對 li 帳號進行授權.

這時執行程式如下


  1. import java.sql.CallableStatement;
  2. import java.sql.Connection;
  3. import java.sql.DriverManager;
  4. import java.sql.SQLException;
  5. import java.sql.Types;

  7. public class T {
  8.     public static void main(String[] args) throws SQLException, ClassNotFoundException {
  9.         Class.forName("com.mysql.jdbc.Driver");
  10.         Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mvbox", "li", "li");

  12.         CallableStatement cp = conn.prepareCall("{call proc1(?,?)}");
  13.         cp.setInt(1, 1);
  14.         cp.registerOutParameter(2, Types.INTEGER);
  15.         cp.execute();
  16.         System.out.println(cp.getInt(2));
  17.         cp.close();
  18.         conn.close();
  19.     }
  20. }

執行之後,結果如下:


增加如下授權之後,再次執行
grant execute on procedure  mvbox.proc1 to  li@'localhost' identified by 'li';

還是報錯,報錯資訊如下:
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)

這個報錯資訊就容易理解了,增加對proc表的select許可權
grant select on mysql.proc to li@'localhost' identified by 'li';

再次執行,成功！！

這時候,如果訪問proc2 過程,則報錯如下:（當然會出錯,因為沒有對帳號li進行授權啊）
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)

但是這個報錯,明確顯示了帳號li對於過程mvbox.proc2 沒有執行許可權.這樣很容易定位解決問題.

在什麼情況下,會因為許可權不足而報Parameter number N is not an OUT parameter的錯誤呢?

就是該帳號沒有任何execute的授權,而執行儲存過程的時候,就會有上述報錯.

並且僅僅是使用JDBC的途徑,如果使用MySQL 的客戶端,報錯資訊也是明確的..



MySQL JDBC的一個坑,一個專有大坑.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-2135539/，如需轉載，請註明出處，否則將追究法律責任。

相關文章