qiao.dev

Mysql5.7only_full_group_by问题修复

 
  • 2018年 05月24日

Exception

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'monitor.oup.template_id'; this is incompatible with sql_mode=only_full_group_by
        at sun.reflect.GeneratedConstructorAccessor186.newInstance(Unknown Source)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
        at com.mysql.jdbc.Util.getInstance(Util.java:386)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2683)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2144)
        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2310)
        at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
        at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60)
        ... 137 more

修改 my.cnf

使用mysql客户端执行select @@global.sql_mode; 查询当前全局配置的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

这里直接修改mysql配置文件my.cnf

登录服务器 切换到 mysql用户

  1. ps -ef |grep mysql 
     root     122998 122979  0 16:38 pts/1    00:00:00 su - mysql
 mysql    122999 122998  0 16:38 pts/1    00:00:00 -bash
 root     123555 123532  0 16:44 pts/4    00:00:00 su - mysql
 mysql    123556 123555  0 16:44 pts/4    00:00:00 -bash
 mysql    124088 122999  0 16:48 pts/1    00:00:00 /bin/sh /home/mysql/mysql-5.7.14/bin/mysqld_safe --defaults-file=/home/mysql/mysql-5.7.14_data/my.cnf
 mysql    124319 124088  7 16:48 pts/1    00:00:31 /usr/local/mysql/bin/mysqld --defaults-file=/home/mysql/mysql-5.7.14_data/my.cnf --basedir=/home/mysql/mysql-5.7.14 --datadir=/home/mysql/mysql-5.7.14_data --plugin-dir=/home/mysql/mysql-5.7.14/lib/plugin --log-error=/home/mysql/mysql-5.7.14_data/mydb.err --pid-file=/home/mysql/mysql-5.7.14_data/mydb.pid --socket=/home/mysql/mysql-5.7.14_data/mysql.sock --port=3307
 mysql    124828 122999  2 16:55 pts/1    00:00:00 ps -ef
 mysql    124829 122999  0 16:55 pts/1    00:00:00 grep mysql

  2. vi /home/mysql/mysql-5.7.14_data/my.cnf 追加 sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

  3. 最终结果 
    [client]
[mysqld]
port = 3307
character_set_server=utf8
init_connect='SET NAMES utf8'
basedir=/home/mysql/mysql-5.7.14
datadir=/home/mysql/mysql-5.7.14_data
socket=/home/mysql/mysql-5.7.14_data/mysql.sock
log-error=/home/mysql/mysql-5.7.14_data/mydb.err 
pid-file=/home/mysql/mysql-5.7.14_data/mydb.pid
lower_case_table_names = 1
max_connections=5000
#skip-grant-tables
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

根据集成给留的脚本关闭+启动

/home/mysql/mysql-5.7.14/support-files/mysql.server stop
/home/mysql/mysql-5.7.14/bin/mysqld_safe --defaults-file=/home/mysql/mysql-5.7.14_data/my.cnf &

注意

  • 注意当前操作使用的用户 mysql
搜索