Shulou(Shulou.com)06/01 Report--

A pit, an absolute pit.

Today on-line new module, yesterday's test passed the code, unexpectedly reported an error online.

The error message is simulated as follows:

What?

The first reaction is what's wrong with the program?

Everyone has been looking into it all morning, but the problem is still the same, without a clue.

Later, the problem was discovered by this cat (quite proud)

This is actually caused by a lack of authority.

The simulation questions are as follows:

There is already a user who has Insert,update,select permission for the table under the mvbox library.

Grant select,insert,update on mvbox.* to li@'localhost' identified by 'li'

Create two new procedures.

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; note that the li account is not authorized after the new process.

The execution procedure at this time is as follows

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

}

}

After execution, the results are as follows:

After adding the following authorization, execute it again

Grant execute on procedure mvbox.proc1 to li@'localhost' identified by 'li'

Or error report, the error message is as follows:

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. (CallableStatement.java:630)

At com.mysql.jdbc.JDBC4CallableStatement. (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)

This error message is easy to understand. Add select permissions to the proc table.

Grant select on mysql.proc to li@'localhost' identified by 'li'

Execute it again, success!

At this time, if you access the proc2 process, the error is as follows: (of course, there will be an error, because there is no authorization for the account 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)

But this error clearly shows that the account li does not have permission to execute the process mvbox.proc2. This makes it easy to locate and solve the problem.

Under what circumstances will the error of Parameter number N is not an OUT parameter be reported because of insufficient permissions?

That is, the account does not have any execute authorization, and the above error will occur when the stored procedure is executed.

And it is only the way of using JDBC, if you use the client of MySQL, the error message is clear..

A pit in MySQL JDBC, a proprietary pit.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.