tomcat(SpringBoot) Error querying database. Cause: java.sql.SQLSyntaxErrorException:

mysql group by 版本问题
今天服务器过期了,服务器重启后，之前的mysql设置mode无效报错信息如下:

Error querying database. Cause: java.sql.SQLSyntaxErrorException:

"\n### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Expression #24 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'investment.ec.elected_admin_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by\n### The error may exist in file [/usr/local/tomcat/apache-tomcat-8.5.51/webapps/investment-sign/WEB-INF/classes/com/dao/xml/UserReviewMapper.xml]\n### The error may involve defaultParameterMap\n### The error occurred while setting parameters\n### SQL: select  ur.id,ur.user_id,ur.open_id,ur.user_investment_id,ur.investment_id,ur.amount,ur.period,ur.NAME,ur.phone,ur.email,ur.cert_type,ur.car_id,ur.organ,ur.sex,ur.bank_card_address,ur.bank,ur.bank_card_id,ur.job,ur.address,ur.review_msg,ur.STATUS,ur.create_time,                  inv.`name` as investmentName,ec.elected_admin_id as electedAdminId,au.`name` as electedAdminName,ur.deal_cycle,u.test_total_score as testTotalScore         from user_review ur         INNER JOIN user u ON u.id = ur.user_id         LEFT JOIN investment inv ON ur.investment_id = inv.id         LEFT JOIN elect_contact ec ON ec.user_id = u.id         LEFT JOIN adminuser au on au.id=ec.elected_admin_id          WHERE u.`status` = '1'             and ur.`status` != '3'          GROUP BY ur.id         order by ur.create_time desc         limit ?,?\n### Cause: java.sql.SQLSyntaxErrorException: Expression #24 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'investment.ec.elected_admin_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by\n; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Expression #24 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'investment.ec.elected_admin_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"

登录mysql -uroot -p

SET @@global.sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_IN_DATE,NO_ZERO_DATE,PIPES_AS_CONCAT';
SET @@sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,PIPES_AS_CONCAT';

这种设置暂时解决问题,重启服务器又要重新设置

彻底解决办法：

找到mysql的my.cnf文件在[mysqld]下边加入：

[mysqld]
character-set-server=utf8
max_connections=1000
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION  

service mysqld restart

重启MySQL数据库就好了

设置完重启mysql并且重启tomcat 项目

1.记一次Mac环境下修改数据库的sql_mode