Description:
Connector/J attempts to collect metadata from the server before executing a CallableStatement. The first attempt (for server versions before 5.4) is to query mysql.proc. If this fails, the driver then issues SHOW PROCEDURE STATUS LIKE 'RoutineName' and SHOW FUNCTION STATUS LIKE 'RoutineName'. Due to BUG#56224, these statements may fail, even though the routine or function exist, and necessary metadata is not obtained. No Exception is thrown, however, until CallableStatement.execute() is invoked, when a NullPointerException is thrown because the metadata hasn't been appropriately initialized:
java.lang.NullPointerException
at com.mysql.jdbc.CallableStatement$CallableStatementParamInfo.iterator(CallableStatement.java:367)
at com.mysql.jdbc.CallableStatement.setInOutParamsOnServer(CallableStatement.java:2069)
at com.mysql.jdbc.CallableStatement.execute(CallableStatement.java:876)
The workaround for this problem, of course, is to ensure that the database account being used by the application executing the CallableStatement has SELECT ON mysql.proc access, or, failing that, that the Java code invoking the stored routine uses the same capitalization as the stored routine name as defined in the database.
Regardless, this appears to be a regression from earlier versions, which throw a SQLException on CallableStatement creation:
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:1075)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
at com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(DatabaseMetaData.java:1614)
at com.mysql.jdbc.DatabaseMetaData.getProcedureOrFunctionColumns(DatabaseMetaData.java:4141)
at com.mysql.jdbc.DatabaseMetaData.getProcedureColumns(DatabaseMetaData.java:4059)
at com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:830)
at com.mysql.jdbc.CallableStatement.<init>(CallableStatement.java:633)
at com.mysql.jdbc.CallableStatement.getInstance(CallableStatement.java:521)
at com.mysql.jdbc.ConnectionImpl.parseCallableStatement(ConnectionImpl.java:4081)
at com.mysql.jdbc.ConnectionImpl.prepareCall(ConnectionImpl.java:4165)
at com.mysql.jdbc.ConnectionImpl.prepareCall(ConnectionImpl.java:4139)
How to repeat:
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
public class Csc50112 {
private static String url = "jdbc:mysql://localhost:3306/test";
public static void main(String[] args) {
// first, try it without access to mysql.proc
if(!setUp(false)){
System.out.println("Unable to set up test case!");
System.exit(0);
}
// failure is expected
if(runTest()){
System.out.println("Success!");
} else {
System.out.println("Failure!");
}
// now, try it while access to mysql.proc is available
if(!setUp(true)){
System.out.println("Unable to set up test case!");
System.exit(0);
}
// this succeeds
if(runTest()){
System.out.println("Success!");
} else {
System.out.println("Failure!");
}
}
private static boolean runTest(){
try {
Class.forName("com.mysql.jdbc.Driver");
String user = "csc50112";
String pass = "";
System.out.println("Connecting to: " + url);
Connection conn = DriverManager.getConnection(url, user, pass);
System.out.println("Connection successful!");
CallableStatement cs = conn.prepareCall("{? = CALL TestFunction(?)}");
cs.registerOutParameter(1, Types.INTEGER);
cs.setString(2, "test");
cs.execute();
System.out.println("result is now: " + cs.getInt(1));
return true;
} catch (Exception e) {
e.printStackTrace();
}
return false;
}
private static boolean setUp(boolean includeMySQLSchemaAccess){
boolean complete = false;
try {
Class.forName("com.mysql.jdbc.Driver");
String user = "root";
String pass = "";
Connection conn = DriverManager.getConnection(url, user, pass);
try{
conn.createStatement().execute("DROP USER csc50112@localhost");
} catch(SQLException e){}
try{
conn.createStatement().execute("DROP FUNCTION IF EXISTS testFunction");
} catch(SQLException e){}
try{
conn.createStatement().execute("CREATE USER csc50112@localhost");
conn.createStatement().execute("GRANT ALL ON test.* TO csc50112@localhost");
if(includeMySQLSchemaAccess){
conn.createStatement().execute("GRANT SELECT ON mysql.proc TO csc50112@localhost");
}
conn.createStatement().execute("CREATE FUNCTION testFunction (i VARCHAR(10)) RETURNS INT RETURN 1;");
complete = true;
} catch(SQLException e){
e.printStackTrace();
}
}catch(Exception e){}
return complete;
}
}
Suggested fix:
* Throw SQLException during initialization as in 5.1.12
* Perhaps identify ways to compensate for BUG#56224
