今天要把java服务部署到生产环境,丢上去启动后发现有些接口报错了:
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:
Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated
column 'enterprise.a.ent_no' 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.2。
上网查了下只需在mysql配置文件设置
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
mysql配置文件路径(centos7):
/etc/my.cnf
将sql_mode加到上图位置即可
完事重启mysql
执行命令 service mysql restart
查了下原因
mysql5.7.x版本,默认是开启了 only_full_group_by 模式的,而only_full_group_by模式下
SELECT后面接的列必须被GROUP BY后面接的列所包含。
SELECT id, name from table group by id,name,sex