今天从项目同事那拿到一个sql脚本,导入数据库后,微服务一直启动报错,数据库方面用的JPA,报错信息如下:
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.exception.SQLGrammarException: Error accessing tables metadata
...
Caused by: javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.exception.SQLGrammarException: Error accessing tables metadata
...
Caused by: org.hibernate.exception.SQLGrammarException: Error accessing tables metadata
...
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: SELECT command denied to user ''@'%' for column 'c_code' in table 'sys_data_dictionary'
...
在网上搜了一圈,也没有找到特别好的解释 和 解决方案,于是觉得有必要记录一下。
错误原因
主要由于 **数据库视图定义 影响到 访问权限* *,导入的sql脚本的 DEFINER 为 root@% ,目测应该是同事 那边的 视图定义者名称;而我本地数据库的 DEFINER 均为 mysql.sys@localhost,由于在本地找不到 root@%,所以影响到数据库表字段的访问权限。
执行如下sql能看到 当前数据库视图定义:
# 查看mysql的视图定义者
select TABLE_SCHEMA,TABLE_NAME,DEFINER from information_schema.VIEWS;
解决方案
其中 root@% 为原视图定义者,mysql.sys@localhost 为目标视图定义者
select concat("alter DEFINER='mysql.sys'@'localhost' SQL SECURITY DEFINER VIEW ",TABLE_SCHEMA,".",TABLE_NAME," as ",VIEW_DEFINITION,";") from information_schema.VIEWS where DEFINER = 'root@%';
执行效果如下:
每一条alter语句挨个执行,最终效果如下,修改成功!