项目场景:
springboot项目集成了mybatis查询mysql数据库报Caused by: java.sql.SQLSyntaxErrorException: SELECT command denied to user 'hanwebJB'@'XXXXX' for table 'JIS_LICENSE'
问题描述:
在数据查询的时候报错,报错内容如下
Caused by: java.sql.SQLSyntaxErrorException: SELECT command denied to user 'hanwebJB'@'10.207.174.129' for table 'JIS_LICENSE'
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:536)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:115)
at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1983)
at com.mysql.cj.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1826)
at com.mysql.cj.jdbc.PreparedStatement.execute(PreparedStatement.java:1153)
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.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
at com.sun.proxy.$Proxy163.execute(Unknown Source)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
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:83)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
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:434)
... 83 common frames omitted
原因分析:
根据异常提示可以看到是用户连接数据库被拒绝,
第一种分析,连接被拒通常是用户权限问题,通过查询mysql.user表可以查询到自己用户的权限,但查询完发现我这个用户具有权限
第二种分析,由于以前是基于Oracle数据库开发,现在是基于MySQL开发,考虑到Oracle与mysql数据库存在不同特性,基于此去查解决方案,发现mysql数据库在Linux系统上对表名大小写比较敏感,而我的sql中表名是大写,然后去查数据库配置发现了问题,mysql表名大小写配置属性:lower_case_table_names=0 (0:大小写敏感;1:大小写不敏感)
解决方案:
1、更改自己的sql,把sql中的表名改为小写
2、改MySQL数据库配置,操作步骤:
- 关闭MySQL服务
- 在服务运行目录找到my.ini或者my.cnf文件
- 打开文件,找到[mysqld]把下面lower_case_table_names=0,改为lower_case_table_names=1(0:大小写敏感;1:大小写不敏感)
- 重启MySQL服务