Bug #80405 Inconsistent exceptions thrown when calling non-existent stored procedures

• Submitted: 17 Feb 2016 11:39
• Modified: 17 Feb 2016 12:52
• Reporter: Erik Ekman
• Status: Verified
• Category: Connector / J
• Severity: S3 (Non-critical)
• Version: 5.1.38
• OS: Any
• CPU Architecture: Any

[17 Feb 2016 11:39] Erik Ekman

Description:
Calling a non-existent stored procedure gives different exceptions depending on type and number of arguments and how they are set.

When the procedure has no arguments or all arguments are set by number:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: PROCEDURE db.non_existing_proc does not exist
        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:422)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
        at com.mysql.jdbc.Util.getInstance(Util.java:387)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:939)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
        at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073)
        at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009)
        at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094)
        at com.mysql.jdbc.CallableStatement.executeLargeUpdate(CallableStatement.java:2403)
        at com.mysql.jdbc.CallableStatement.executeUpdate(CallableStatement.java:873)

which is the exception you normally expect.

When an (input or output) argument is set by name we instead get:
jaa.lang.NullPointerException
        at com.mysql.jdbc.CallableStatement.getNamedParamIndex(CallableStatement.java:1381)
        at com.mysql.jdbc.CallableStatement.setString(CallableStatement.java:2163)

When an output argument is set by index we get:
jaa.sql.SQLException: Parameter number 1 is not an OUT parameter
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
        at com.mysql.jdbc.CallableStatement.checkIsOutputParam(CallableStatement.java:616)
        at com.mysql.jdbc.CallableStatement.registerOutParameter(CallableStatement.java:1794)

This happens when access is allowed to procedure bodies. When noAccessToProcedureBodies=true is set the NullPointerExceptions are replaced with better exceptions.

How to repeat:

Run attached example program against a modern MySQL server

$ javac ProcTest.java
$ java -cp mysql-connector-java-5.1.38.jar:. ProcTest "jdbc:mysql://localhost/db?user=XXX&password=YYY"

and it will print the type and message of each exception.

Suggested fix:
When DatabaseMetaData.getProceduresOrFunctions() runs the SQL

SELECT name, type, comment FROM mysql.proc WHERE name like ? and db <=> ? ORDER BY name

and access to procedure bodies are allowed but no rows are returned, throw the procedure does not exist exception if attempts are made to add a named parameter to the CallableStatement.

[17 Feb 2016 11:39] Erik Ekman

Testcase to get exceptions

Attachment: ProcTest.java (text/x-java), 2.05 KiB.

[17 Feb 2016 12:52] MySQL Verification Team

Hello Erik Ekman,

Thank you for the report and test case.

Thanks,
Umesh