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
。