MySQL步骤报ParameternumberNisnotanOUTparameter错误
发布时间:2022-06-29 12:42 所属栏目:115 来源:互联网
导读:今天上线新模块,昨天测试通过的代码,居然在线上报错. 纳尼? 第一反应是程序哪里写错了 大家查了一上午,问题依旧,毫无头绪. 后来居然被本猫发现了问题(颇自豪啊) 这个其实是权限不足导致的. 模拟问题如下: 已经存在一个用户,对mvbox库下的表,有Insert,update,
今天上线新模块,昨天测试通过的代码,居然在线上报错. 纳尼? 第一反应是程序哪里写错了 大家查了一上午,问题依旧,毫无头绪. 后来居然被本猫发现了问题(颇自豪啊) 这个其实是权限不足导致的. 模拟问题如下: 已经存在一个用户,对mvbox库下的表,有Insert,update,select权限. grant select,insert,update on mvbox.* to li@'localhost' identified by 'li'; 新建两个过程. 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 ; 注意, 新建过程之后,没有对 li 帐号进行授权. 这时执行程序如下 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(); } } 执行之后,结果如下: MySQL过程报 Parameter number N is not an OUT parameter错误 增加如下授权之后,再次执行 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 的客户端,报错信息也是明确的.. (编辑:ASP站长网) |
相关内容
网友评论
推荐文章
热点阅读