异常一例 ：调用存储过程异常，MybatisPlusException：Failed to process, Error SQL

异常一例 ：调用存储过程异常，MybatisPlusException：Failed to process, Error SQL

芋道（1.8.0-SNAPSHOT）框架下，开发的一个系统，禁止多租户。

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, Error SQL: call proc_convert_dept_recruitment()
### Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, Error SQL: call proc_convert_dept_recruitment()
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:96)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
    at com.sun.proxy.$Proxy128.selectOne(Unknown Source)
    at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:160)
    at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:89)
    at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
    at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
    at com.sun.proxy.$Proxy444.updateDeptRecruitmentBelogId(Unknown Source)
    at com.wistronits.hrjob.batch.platform.feishu.service.BaseDataSyncJobNew.syncDeptInfo(BaseDataSyncJobNew.java:990)
    at com.wistronits.hrjob.batch.platform.feishu.service.BaseDataSyncJobNew.sync(BaseDataSyncJobNew.java:204)
    at com.wistronits.hrjob.batch.platform.feishu.service.BaseDataSyncJobNew$$FastClassBySpringCGLIB$$64c1d3b5.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:793)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:708)
    at com.wistronits.hrjob.batch.platform.feishu.service.BaseDataSyncJobNew$$EnhancerBySpringCGLIB$$e5a4301d.sync(<generated>)
    at com.wistronits.hrjob.batch.platform.feishu.jobhandler.SyncEmp.syncEmpJobHandler(SyncEmp.java:32)
    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 com.xxl.job.core.handler.impl.MethodJobHandler.execute(MethodJobHandler.java:31)
    at com.xxl.job.core.thread.JobThread.run(JobThread.java:166)
Caused by: org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, Error SQL: call proc_convert_dept_recruitment()
### Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, Error SQL: call proc_convert_dept_recruitment()
    at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:153)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145)
    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.GeneratedMethodAccessor149.invoke(Unknown Source)
    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)
    ... 27 common frames omitted
Caused by: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, Error SQL: call proc_convert_dept_recruitment()
    at com.baomidou.mybatisplus.core.toolkit.ExceptionUtils.mpe(ExceptionUtils.java:39)
    at com.baomidou.mybatisplus.extension.parser.JsqlParserSupport.parserSingle(JsqlParserSupport.java:52)
    at cn.iocoder.yudao.framework.datapermission.core.db.DataPermissionDatabaseInterceptor.beforeQuery(DataPermissionDatabaseInterceptor.java:67)
    at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:78)
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
    at com.sun.proxy.$Proxy209.query(Unknown Source)
    at sun.reflect.GeneratedMethodAccessor88.invoke(Unknown Source)
    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.github.yulichang.interceptor.MPJInterceptor.intercept(MPJInterceptor.java:76)
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
    at com.sun.proxy.$Proxy209.query(Unknown Source)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
    ... 34 common frames omitted
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: ")" ")"
    at line 1, column 36.

Was expecting one of:

    "!"
    "+"
    "-"
    "?"
    "@"
    "@@"
    "CONNECT_BY_ROOT"
    "CURRENT"
    "GROUP_CONCAT"
    "NOT"
    "NULL"
    "XMLSERIALIZE"
    "{d"
    "{t"
    "{ts"
    "~"
    <K_TIME_KEY_EXPR>
    <S_CHAR_LITERAL>
    <S_DOUBLE>
    <S_HEX>
    <S_LONG>

    at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:31468)
    at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:31301)
    at net.sf.jsqlparser.parser.CCJSqlParser.PrimaryExpression(CCJSqlParser.java:11463)
    at net.sf.jsqlparser.parser.CCJSqlParser.BitwiseXor(CCJSqlParser.java:11131)
    at net.sf.jsqlparser.parser.CCJSqlParser.MultiplicativeExpression(CCJSqlParser.java:11084)
    at net.sf.jsqlparser.parser.CCJSqlParser.AdditiveExpression(CCJSqlParser.java:11047)
    at net.sf.jsqlparser.parser.CCJSqlParser.BitwiseAndOr(CCJSqlParser.java:10993)
    at net.sf.jsqlparser.parser.CCJSqlParser.ConcatExpression(CCJSqlParser.java:10969)
    at net.sf.jsqlparser.parser.CCJSqlParser.SimpleExpression(CCJSqlParser.java:10955)
    at net.sf.jsqlparser.parser.CCJSqlParser.SimpleExpressionList(CCJSqlParser.java:10595)
    at net.sf.jsqlparser.parser.CCJSqlParser.Execute(CCJSqlParser.java:13246)
    at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:301)
    at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:153)
    at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatement(CCJSqlParserUtil.java:188)
    at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:63)
    at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:38)
    at com.baomidou.mybatisplus.extension.parser.JsqlParserSupport.parserSingle(JsqlParserSupport.java:49)
    ... 46 common frames omitted

2024-09-27 14:10:09.952 ERROR 1772 --- [1-1727417250624] c.w.h.b.p.f.service.BaseDataSyncJobNew   : BaseDataSyncJob.syncDeptInfo发生异常: {}

SQL只是普通的调用无参数存储过程：

    <select id="updateDeptbelogId"   statementType="CALLABLE">
      call proc_convert_dept_p()
    </select>

    <select id="updateDeptRecruitmentBelogId"   statementType="CALLABLE">
      call proc_convert_dept_recruitment()
    </select>

Mybatis-plus 调用存储过程爬坑 

https://blog.51cto.com/u_10705830/5387335

参考此文章，他的是因为多租户的插件产生的同样异常，但我的系统并没有开启多租户，此插件并未加载，所以他的最终解决方法不适用于我。

但在他的分析过程中，有这样一句提醒了我“增加不必要的参数来解决”，首先异常消息中提示的是括号不匹配，其次是别人通过增加参数可以回避此问题，那么问题是不是因为我这个是无参数的存储过程，而又写了括号，所以才出的错呢？

尝试修改为：

    <select id="updateDeptbelogId"   statementType="CALLABLE">
      call proc_convert_dept_p
    </select>

OK啦，可以正常执行了。

补充：2024/10/18，此处可能还会涉及别外一个权限问题

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.

给当前的用户分配下权限即可解决：

grant execute on db_name.* to `user-name`@`%`;
grant select on mysql.proc to `user-name`@`%`;