1. 权限设置 : Caused by: java.sql.SQLException: The userspecified as a definer ('root'@'%') does not exist。
经查明:是用户root并没有获得mysql数据库的所有权限。
解决办法:执行两条sql语句就可以完美解决问题
GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY ".";
FLUSH PRIVILEGES;
第一条是赋予root用户所有的权限
第二条是刷新
2. 配置远程访问
GRANT ALL PRIVILEGES ON *.* TO 'root'@'11.247.10.178'IDENTIFIED BY'h3c' WITH GRANT OPTION;
grant all privileges on *.* to 'root'@'%' identified by 'h3croot' with grant option;
flush privileges;
FLUSH PRIVILEGES;
第一条是赋予11.247.10.178(%表示所有的ip)的主机可以使用root用户进行所有的操作
第二条是刷新。
3. mysql5.7.5及以上:ONLY_FULL_GROUP_BY SQL模式报错
(一)报错:
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘...’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by。
(二) 问题出现的原因:
MySQL 5.7.5及以上功能依赖检测功能。如果启用了ONLY_FULL_GROUP_BY SQL模式(默认情况下),MySQL将拒绝选择列表,HAVING条件或ORDER BY列表的查询引用在GROUP BY子句中既未命名的非集合列,也不在功能上依赖于它们。(5.7.5之前,MySQL没有检测到功能依赖关系,默认情况下不启用ONLY_FULL_GROUP_BY。
(三)解决方案:
(1)调整SQL查询语句
在GROUP BY后面加上查询出来的字段:
SELECT s1.CODE, s1.NAME, s1.AGE, sum(SCORE) as ALLSCORE FROM student s1 LEFT JOIN scores s2 ON s1.CODE = s2.CODE GROUP BY CODE,NAME,AGE ORDER BY ALLSCORE DESC
(2)去掉ONLY_FULL_GROUP_BY
用navcat打开数据库,执行SQL语句:select @@global.sql_mode,查询的值为:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
去掉ONLY_FULL_GROUP_BY,重新设置值,执行:
set @@global.sql_mode =’STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;
需要重启Mysql。