Articles

For cases when a procedure is created by another user (DEFINER) than the calling user the 'noAccessToProcedureBodies' JDBC parameter can help.

It has been introduced in MySQL Connector version 5.0.3. If it is set to true and user cannot access procedure body with SHOW CREATE PROCEDURE or from mysql.proc then the driver will treat all parameters as IN VARCHAR (but still allowing to call registerOutParameter on them) and no exception will be thrown.

A Java hosting user reported stored procedure related error in Tomcat log:

SQLException 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.

Most commonly this is due to mismatch between calling user and definer. For example if you asked server admin to create procedures or they have been created with different user then the one you are now calling them with. Another case when this can happen is an older JDBC driver and MySQL combo.

Please note that the below Java code always creates the testing procedure as the same user that will be calling it. The code may work fine but if you call another procedure that has different definer than caller that you will get the error again.

Proposed solutions that can solve the problem (or at least give some hints) are:

1. Modify connection parameters (username, database, password) and try the code below. It will drop, create and run stored procedure
2. Upgrade to newest mysql-connector
3. Add the noAccessToProcedureBodies=true to JDBC string. If you have any OUT parameter the call may still fail with No value specified for parameter. If you have INOUT you can get OUT or INOUT argument for routine is not a variable or NEW pseudo-variable in BEFORE trigger. In this cases use registerOutParameter().
4. Try using the parameter useInformationSchema=true in JDBC string (without noAccessToProcedureBodies parameter)
5. In MySQL client try running SHOW CREATE PROCEDURE and SELECT * FROM mysql.proc
6. In MySQL client run SHOW PROCEDURE STATUS and check if definer is set to your user (the procedure was not created by different user)
7. Recheck if all privileges are assigned to the user on the database (for example drop and recreate the user in cPanel) or run SHOW GRANTS FOR dbusername@localhost;
8. Check if you can successfully run SELECT * FROM information_schema.ROUTINES;
9. Request correction of definer (if it is root) from our support
10. Check with our support if your account can be moved to a server with newer MySQL version

Here goes contents of noAccessToProcedureBodies.jsp that you can use to test stored procedure deletion, creation and call results. Please place it in your Tomcat's webapps/ROOT or similar place and make sure respective WEB-INF/lib conatins mysql-connector.

<%@page import="java.sql.*" %>
<%
String database = "dbname";
String username = "dbuser";
String password = "secret";
String url = "jdbc:mysql://localhost:3306/"+database;
// String url = "jdbc:mysql://localhost:3306/"+database+"?noAccessToProcedureBodies=true";
// String url = "jdbc:mysql://localhost:3306/"+database+"?useInformationSchema=true";

Connection conn = null;
Statement stmt = null;

String param1 = "John";
String param2 = "Doe";
String nameProcedure1 = "UCSTRING";
String queryDrop1 = "DROP PROCEDURE IF EXISTS "+nameProcedure1;
String createProcedure1 = "CREATE PROCEDURE ucstring (IN p1 VARCHAR(255), INOUT p2 VARCHAR(255), OUT p3 VARCHAR(255))\n" +
"BEGIN\n" +
"SELECT CONCAT(UPPER(p1), ' ', UPPER(p2));\n" +
"SELECT CONCAT(CONCAT(UCASE(MID(p1,1,1)),MID(p1,2)), ' ', CONCAT(UCASE(MID(p2,1,1)),MID(p2,2))) INTO p3;\n" +
"SELECT CONCAT(UPPER(p1), ' ', UPPER(p2)) INTO p2;\n" +
"END";

try {
 Class.forName("com.mysql.jdbc.Driver").newInstance(); 
 conn = DriverManager.getConnection(url, username, password); 
 DatabaseMetaData dm = conn.getMetaData();
 out.println("JDBC URL: "+ dm.getURL() + "</br>");
 out.println("Database version: " + dm.getDatabaseProductName()+ " " + dm.getDatabaseProductVersion()+ "</br>");
 out.println("Driver version: " + dm.getDriverVersion()+ "</br>");
 out.println("JDBC version: "+ dm.getJDBCMajorVersion()+ "." + dm.getJDBCMinorVersion()+ "</br><hr>");

// remove procedure if it exists
 out.println("Calling DROP PROCEDURE "+nameProcedure1 + "<br/>");
 stmt = conn.createStatement();
 stmt.execute(queryDrop1);

// create procedure
 stmt = conn.createStatement();
 stmt.executeUpdate(createProcedure1);
 out.println("Calling CREATE PROCEDURE " +nameProcedure1 + "<br/>");
 out.println("<pre>DELIMITER //\n"+createProcedure1+"//\nDELIMTER ;</pre>");

// call procedure
 CallableStatement cs = conn.prepareCall("{call " + nameProcedure1 + "(?,?,?)}");
 out.println("Calling CALL "+ nameProcedure1 + "('" + param1 +"','" + param2 + "','?')<br/>");
 cs.setString(1, param1);
 cs.setString(2, param2);
// uncomment below 2 lines when using noAccessToProcedureBodies=true and the definer is not you
// cs.registerOutParameter(2, Types.VARCHAR);
// cs.registerOutParameter(3, Types.VARCHAR);
 cs.executeQuery();
 String result1 = cs.getString(2);
 String result2 = cs.getString(3);
 out.println("p2 (INOUT): "+ result2 + 
 "<br/>p3 (OUT): " + result1);

 stmt.close();
} catch (SQLException e) {
 out.println(e.getMessage());
 e.printStackTrace();
} finally {
 if(conn != null) conn.close();
}
%>

Feel free to leave a comment if you still couldn't resolve it or found another solution.