Parameter number 2 is not an OUT

1 问题描述

换了一个 MySQL 数据库，原来调用正常的存储过程，现在抛错了。具体错误如下：

Caused by: java.sql.SQLException: Parameter number 2 is not an OUT parameter
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
    at com.mysql.cj.jdbc.CallableStatement.checkIsOutputParam(CallableStatement.java:643)
    at com.mysql.cj.jdbc.CallableStatement.registerOutParameter(CallableStatement.java:1714)
    at com.mysql.cj.jdbc.CallableStatement.registerOutParameter(CallableStatement.java:1722)
    at com.alibaba.druid.filter.FilterChainImpl.callableStatement_registerOutParameter(FilterChainImpl.java:3803)
    at com.alibaba.druid.filter.FilterAdapter.callableStatement_registerOutParameter(FilterAdapter.java:451)
    at com.alibaba.druid.filter.FilterChainImpl.callableStatement_registerOutParameter(FilterChainImpl.java:3800)
    at com.alibaba.druid.filter.FilterAdapter.callableStatement_registerOutParameter(FilterAdapter.java:451)
    at com.alibaba.druid.filter.FilterChainImpl.callableStatement_registerOutParameter(FilterChainImpl.java:3800)
    at com.alibaba.druid.proxy.jdbc.CallableStatementProxyImpl.registerOutParameter(CallableStatementProxyImpl.java:52)
    at com.alibaba.druid.pool.DruidPooledCallableStatement.registerOutParameter(DruidPooledCallableStatement.java:55)
    at org.apache.ibatis.executor.statement.CallableStatementHandler.registerOutputParameters(CallableStatementHandler.java:111)
    at org.apache.ibatis.executor.statement.CallableStatementHandler.parameterize(CallableStatementHandler.java:95)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.parameterize(RoutingStatementHandler.java:64)
    at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:88)
    at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62)
    at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:89)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)
    at com.fsti.cc.core.ds.interceptor.SwitchDataSourceInterceptor.intercept(SwitchDataSourceInterceptor.java:189)
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
    at com.sun.proxy.$Proxy91.query(Unknown Source)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:76)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427)
    ... 38 more

2 原因分析

因为代码没有变化，只是换了一个新数据库，所以出现问题的原因肯定在这个新的 MySQL 数据库中。
在 Navigate 中看了一下新数据库的用户账号，没有 Execute 权限：

勾选上之后，重新执行程序。抛出另一个错误：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.

具体错误堆栈日志为：

Caused by: 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.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
    at com.mysql.cj.jdbc.DatabaseMetaData.getCallStmtParameterTypes(DatabaseMetaData.java:1458)
    at com.mysql.cj.jdbc.DatabaseMetaData.getProcedureOrFunctionColumns(DatabaseMetaData.java:3163)
    at com.mysql.cj.jdbc.DatabaseMetaData.getProcedureColumns(DatabaseMetaData.java:3032)
    at com.mysql.cj.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:793)
    at com.mysql.cj.jdbc.CallableStatement.<init>(CallableStatement.java:586)
    at com.mysql.cj.jdbc.CallableStatement.getInstance(CallableStatement.java:485)
    at com.mysql.cj.jdbc.ConnectionImpl.parseCallableStatement(ConnectionImpl.java:1516)
    at com.mysql.cj.jdbc.ConnectionImpl.prepareCall(ConnectionImpl.java:1541)
    at com.mysql.cj.jdbc.ConnectionImpl.prepareCall(ConnectionImpl.java:1532)
    at com.alibaba.druid.filter.FilterChainImpl.connection_prepareCall(FilterChainImpl.java:501)
    at com.alibaba.druid.wall.WallFilter.connection_prepareCall(WallFilter.java:401)
    at com.alibaba.druid.filter.FilterChainImpl.connection_prepareCall(FilterChainImpl.java:497)
    at com.alibaba.druid.filter.FilterAdapter.connection_prepareCall(FilterAdapter.java:910)
    at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareCall(FilterEventAdapter.java:88)
    at com.alibaba.druid.filter.FilterChainImpl.connection_prepareCall(FilterChainImpl.java:497)
    at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareCall(ConnectionProxyImpl.java:315)
    at com.alibaba.druid.pool.DruidPooledConnection.prepareCall(DruidPooledConnection.java:551)
    at org.apache.ibatis.executor.statement.CallableStatementHandler.instantiateStatement(CallableStatementHandler.java:87)
    at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59)
    at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:87)
    at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62)
    at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:89)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)
    at com.fsti.cc.core.ds.interceptor.SwitchDataSourceInterceptor.intercept(SwitchDataSourceInterceptor.java:189)
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
    at com.sun.proxy.$Proxy91.query(Unknown Source)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:76)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427)
    ... 38 more

提示加上 &noAccessToProcedureBodies=true。

3 问题解决

在连接 URL 中加上 &noAccessToProcedureBodies=true 之后，就存储过程调用就正常了。
这种方式虽然可以解决问题，但存在一些问题，比如调用存储过程时，将没有类型检查等等 。
所以更好的解决方式是为数据库用户分配“获取存储过程元数据（MetaData）”的权限 ，具体命令为: grant Select on mysql.proc to [数据库账号]。

示例为：grant Select on mysql.proc to 'cloud_user'@localhost。